Oracle Practical Questions and Answers
Question 1
(a) Create a tablespace oracletest with an initial size of 100M and a data file [Link]:
CREATE TABLESPACE oracletest
DATAFILE '[Link]'
SIZE 100M;
(b) Create a user called staff with staff as password in the tablespace:
CREATE USER staff IDENTIFIED BY staff
DEFAULT TABLESPACE oracletest;
(c) Grant connect and create table to staff:
GRANT CONNECT, CREATE TABLE TO staff;
(d) Create a table called student with fields StID (Primary Key), FullName, and Address:
CREATE TABLE student (
StID NUMBER PRIMARY KEY,
FullName VARCHAR2(100),
Address VARCHAR2(200)
);
(e) View all existing users and their default tablespace:
SELECT username, default_tablespace FROM dba_users;
Question 2
(a) Create role called staffrole:
CREATE ROLE staffrole;
(b) Grant insert, update and delete on student table to staffrole:
GRANT INSERT, UPDATE, DELETE ON [Link] TO staffrole;
(c) Query to check all privileges assigned to staffrole:
SELECT * FROM dba_tab_privs WHERE grantee = 'STAFFROLE';
Question 3
(a) Create new user called partimestaff and grant staffrole:
CREATE USER partimestaff IDENTIFIED BY partimestaff;
GRANT CONNECT TO partimestaff;
GRANT staffrole TO partimestaff;
(b) Check granted roles to partimestaff:
SELECT * FROM dba_role_privs WHERE grantee = 'PARTIMESTAFF';
(c) Insert data into student table as partimestaff:
INSERT INTO [Link] (StID, FullName, Address) VALUES (101,
'Ali Haji', 'Stone Town');
INSERT INTO [Link] (StID, FullName, Address) VALUES (102,
'Zahra Omar', 'Mbweni');
(d) Create stored procedure sp5tudent:
CREATE OR REPLACE PROCEDURE sp5tudent(
p_StID IN NUMBER,
p_FullName IN VARCHAR2,
p_Address IN VARCHAR2
)
AS
BEGIN
INSERT INTO [Link] (StID, FullName, Address)
VALUES (p_StID, p_FullName, p_Address);
END;
(e) Execute the procedure to insert two rows:
EXEC sp5tudent(103, 'Fatma Said', 'Kisauni');
EXEC sp5tudent(104, 'Salum Juma', 'Chukwani');
(f) View all inserted data in student table:
SELECT * FROM [Link];