Oracle database

Oracle 10g
Documents Related to Oracle 10g

1) Steps for Creating Oracle 10g Database Manually.

Physical Standby Database Creation
Practical Steps to Standy database creation for Oracle version 10g and above.

Linking
HomePage

Help:Link

Help:Practice_page

Oracle.com

[mailto:gummula_malesh@yahoo.com Contact Malesh]

Others
Wiki|Pipe Syntax]

Usage
Allows to establish a link to a subject:

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.