Oracle database
Oracle 10g
Documents Related to Oracle 10g
1) Steps for Creating Oracle 10g Database Manually.[1]
DataGuard
Physical Standby Database Creation
Practical Steps to Standy database creation for Oracle version 10g and above.[2]
Logical Standby
Linking
Others
[[[meta:Help:Table|Wiki|Pipe Syntax]]]
Usage
Allows to establish a link to a subject: Template:NameOfTemplate
Assignments
Assignments for Learner Oracle DBA’s
Note: Every Question has some or the other issues, you have to solve the issues. Follow step by step as they may be dependent on earlier question.
There may be certain things missing, you should troubleshoot what is missed and how to fix it. For Any doubts in Question verify with me.
1) Create the user NBCDBO, NBCAPP, NBC_GUSER and assign the default tablespace as USERS (50M), these users should store data in NBC_DATA (size 100M) to store
index in NBC_IDX (100M) Note: Does not use default CONNECT and RESOURCE roles as they are obsolete now and not recommended.
2) Grant permission to login (NBCDBO, NBCAPP, NBC_GUSER) and to create objects to only NBCDBO (tables / index /views / sequence and synonym)
3) Grant permission to login and to create procedure/function/packages/TRIGGER to NBCAPP (to create programs based on objects created on NBCDBO)
4) Create table EMP and DEPT in schema NBCDBO, all tables should go to TBS NBC_DATA and indexes should go to TBS NBC_IDX Also run the script (cretabs.sql) provided to create table and other objects.
5) Create relation on both tables EMP and DEPT.
6) Create unique index on ENAME and non unique index on DEPTNO of EMP table, both index should go to NBC_IDX tablespace.
7) Verify the tables/indexes have been created correct tablespaces i.e. all tables should be in tablespace NBC_DATA and indexes NBC_IDX.
8) Places the tables in correct tablespace, if they are not already. What are step to correct them.
9) Load data into respective tables provided in .csv files.
10) User NBC_GUSER should have only read only and execute Privileges for the objects create in NBCAPP and NBCDBO. NBC_GUSER should not be able to insert/update/delete directly in any objects. Also NBC_GUSER should not be able drop or Create objects.
11) Create below procedures in schema NBCAPP.
If you find compilation error, use this query to find error in code.
COLUMN POS FORMAT A10 COLUMN TEXT FORMAT A68 WORD SELECT line||'/'|| position POS,text FROM user_errors WHERE name = 'your procedure name' ORDER BY line /
CREATE OR REPLACE PROCEDURE query_emp (v_id IN emp.empno%TYPE, v_name OUT emp.ename%TYPE, v_salary OUT emp.sal%TYPE, v_comm OUT emp.comm%TYPE) AUTHID CURRENT_USER IS BEGIN SELECT ename,sal,comm INTO v_name,v_salary,v_comm FROM emp WHERE empno=v_id; END query_emp; /
CREATE OR REPLACE PROCEDURE leave_emp (v_id IN emp.empno%TYPE) IS BEGIN DELETE FROM emp WHERE empno = v_id; log_execution; END leave_emp; /
CREATE OR REPLACE PROCEDURE log_execution IS BEGIN INSERT INTO log_table (user_id, log_date) VALUES (user, sysdate); END log_execution; /
CREATE TABLE log_table (user_id VARCHAR2(30), log_date DATE) /
CREATE OR REPLACE PROCEDURE add_dept (v_name IN dept.dname%TYPE DEFAULT 'unknown', v_loc IN dept.loc%TYPE DEFAULT 'unknown') IS BEGIN INSERT INTO dept VALUES (dept_deptno.NEXTVAL,v_name,v_loc); END add_dept; /
SELECT * FROM dept /
BEGIN
add_dept;
add_dept('TRAINING','NEW YORK');
add_dept(v_loc=>'DALLAS',v_name=>'EDUCATION');
add_dept(v_loc=>'BOSTON');
END;
/
SELECT * FROM dept /
CREATE OR REPLACE FUNCTION get_sal (v_id IN emp.empno%TYPE) RETURN NUMBER IS v_salary emp.sal%TYPE :=0; BEGIN SELECT sal INTO v_salary FROM emp WHERE empno=v_id; RETURN (v_salary); END get_sal; /
Rem Code for GET_SAL VARIABLE g_salary NUMBER EXECUTE :g_salary := get_sal(7934) PRINT g_salary
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) OR
(TO_CHAR (sysdate, 'HH24') NOT BETWEEN '08' AND '18')
THEN
IF DELETING
THEN RAISE_APPLICATION_ERROR (-20502,
'You may only delete from EMP during normal hours.');
ELSIF INSERTING
THEN RAISE_APPLICATION_ERROR (-20500,
'You may only insert into EMP during normal hours.');
ELSIF UPDATING ('SAL')
THEN RAISE_APPLICATION_ERROR (-20503,
'You may only update SAL during normal hours.');
ELSE
RAISE_APPLICATION_ERROR (-20504,
'You may only update EMP during normal hours.');
END IF;
END IF;
END;
/
CREATE OR REPLACE PACKAGE comm_package IS PROCEDURE reset_comm (v_comm in NUMBER); END comm_package; /
CREATE OR REPLACE PACKAGE BODY comm_package
IS
FUNCTION validate_comm
(v_comm IN NUMBER)
RETURN BOOLEAN
IS
v_max_comm NUMBER;
BEGIN
SELECT max(comm)
INTO v_max_comm
FROM emp;
IF v_comm > v_max_comm
THEN RETURN(FALSE);
ELSE RETURN(TRUE);
END IF;
END validate_comm;
PROCEDURE reset_comm
(v_comm IN NUMBER)
IS
l_comm NUMBER := 10;
BEGIN
IF validate_comm(v_comm)
THEN l_comm:=v_comm;
ELSE
RAISE_APPLICATION_ERROR
(-20210,'Invalid commission');
END IF;
END reset_comm;
END comm_package;
/
EXECUTE comm_package.reset_comm(15)
12) Show total count objects,contraints,object grants from each schema.
13) Show the object wise count for each schema NBCAPP,NBCDBO,NBC_GUSER.
14) Show the CONSTRAINT TYPE wise count of constraints for each schema NBCAPP,NBCDBO,NBC_GUSER.
15) Take the count of object privileges
12) Execute the above procedure / Function / package from schema NBCAPP and capture the result after execution of each procedure or whatever the procedure
has made the effect/changes to tables, like what was earlier values before change and what are the values after change.
16) Execute Procedure => query_emp , FUNCTION => get_sal and Package => comm_package from schema NBC_GUSER.
17) You might have given direct privileges to NBCAPP,NBCDBO,NBC_GUSER. Give same privileges using roles. Revoke all the privileges give to NBCAPP,NBCDBO,NBC_GUSER but before revoking extract all the privileges assigned to all 3 users, so that they can be assigned to some role, Here it becomes tedious to know what all privileges are given to each user, so you should have taken care by granting through ROLES. So it’s always better and standard in all different organization to assign privileges using ROLES.
---> Create Below Roles with given privileges assigned to them.
Role: NBC_DEV_ROLE ================== ALTER SESSION, ADVISOR, CREATE CLUSTER, CREATE DATABASE LINK, CREATE DIMENSION, CREATE INDEXTYPE, CREATE JOB, CREATE LIBRARY, CREATE MATERIALIZED VIEW, CREATE OPERATOR, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, QUERY REWRITE, RESUMABLE, SELECT_CATALOG_ROLE ==> this is use to select catalog tables starting with DBA_% , V$%
ROLE :NBC_APP_ROLE ================== ALTER SESSION, CREATE SESSION, CREATE PROCEDURE,
ROLE :NBC_ENDUSER _ROLE ======================== ALTER SESSION, CREATE SESSION, SELECT, INSERT, UPDATE, DELETE ON ALL TABLES AND VIEWS OWNED BY NBCDBO. Also execute of packages owned by NBCAPP.
18) As soon as you the Roles , see the status of objects (i.e. Procedure ) created in NBCAPP schema.
19) Now Grant the above created roles to different Users (NBCDBO , NBCAPP , NBC_GUSER). Each user can have as many numbers of roles.
20) See the status of Objects in NBCAPP , if they are still invalid then make them Valid.
21) Create a copy of emp table with name employees with tablespace NBC_DATA.
22) Create index on deptno of table employees.
23) Move the table employees to separate tablespace NBC_IDX.
24) Now query new table employees with where clause deptno=10 and fix any issues.
25) Analyze all the table and indexes
26) Take backup of Statistics generated by previous command.
27) Delete all the Statistics generated by Analyze.
28) Restore the statistics from backup.
