Oracle HR Schema EMP & DEPT Tables Script

CREATE TABLE BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM  NUMBER);

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500, NULL, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

COMMIT;

PINNING IN THE SHARED POOL

PINNING IN THE SHARED POOL

The shared pool is the portion of the SGS that contains, among other things, the p-code of compiled subprograms as they are run. The first time a stored a store subprogram is called, the p-code is loaded from disk into the shared pool. Once the object is no longer referenced, it is free to be aged out. Objects are aged out of the shared pool using an LRU(Least Recently Used) algorithm.

The DBMS_SHARED_POOL package allows you to pin objects in the shared pool. When an object is pinned, it will never be aged out until you request it, no matter how full the pool gets or how often the object is accessed. This can improve performance, as it takes time to reload a package from disk.

DBMS_SHARED_POOL has four procedures

Ø  KEEP

Ø  UNKEEP

Ø  SIZES

Ø  ABORTED_REQUEST_THRESHOLD

KEEP

The DBMS_SHARED_POOL.KEEP procedure is used to pin objects in the pool.

Syntax:

            PROCEDURE KEEP(object_name varchar2,flag char default ‘P’);

Here the flag represents different types of flag values for different types of objects.

P          --         Package, function or procedure

Q         --         Sequence

R          --         Trigger

C          --         SQL Cursor

T          --         Object type

JS        --         Java source

JC        --         Java class

JR        --         Java resource

JD        --         Java shared data

UNKEEP

UNKEEP is the only way to remove a kept object from the shared pool, without restarting the database. Kept objects are never aged out automatically.

Syntax:

            PROCEDURE UNKEEP(object_name varchar2, flag char default ‘P’);

SIZES

SIZES will echo the contents of the shared pool to the screen.

Syntax:

            PROCEDURE SIZES(minsize number);

Objects with greater than the minsize will be returned. SIZES uses DBMS_OUTPUT to return the data.

ABORTED_REQUEST_THRESHOLD

When the database determines that there is not enough memory in the shared pool to satisfy a given request, it will begin aging objects out until there is enough memory. It enough objects are aged out, this can have a performance impact on other database sessions. The ABORTED_REQUEST_THRESHOLD can be used to remedy this.

Syntax:

            PROCEDURE ABORTED_REQUEST_THRESHOLD(threshold_size number);

Once this procedure is called, oracle will not start aging objects from the pool unless at least threshold_size bytes is needed.

Delete duplicate records from a table

1 ) Write a Query To Delete The Repeted Rows from emp table;

SQL>Delete from emp where rowid not in(select min(rowid)from emp group

by ename)

               

2) TO DISPLAY 5 TO 7 ROWS FROM A TABLE

SQL>select ename from emp

         where rowid in(select rowid from emp where rownum<=7

         minus

  select rowid from empi where rownum<5)

3)  DISPLAY  TOP N ROWS FROM TABLE?

SQL>SELECT * FROM

                (SELECT *  FROM EMP ORDER BY ENAME DESC)

                 WHERE ROWNUM <10;

4) DISPLAY   TOP 3 SALARIES FROM EMP;

SQL>SELECT SAL FROM ( SELECT  * FROM EMP ORDER  BY SAL DESC )

                   WHERE ROWNUM <4

5) DISPLAY  9th FROM THE EMP TABLE?

SQL>SELECT ENAME FROM EMP

                WHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10

                MINUS

                SELECT ROWID FROM EMP WHERE ROWNUM <10)

                select second max salary from emp;

                select max(sal) fromemp where sal<(select  max(sal) from emp);

Creating Partition on existing table?

We cannot create a partition on existing non partition table.
If you try to create a partition on non partition table you will get below error.

ORA-14020: this physical attribute may not be specified for a table partition

We can create a partition using below method.

step 1: Create new table same as source table with partition
step2: insert the data from source table to partition table.
step3: drop the source table.
step4: Rename the partition table as source table name.

INDEXES CREATION, ENABLE, DISABLE, UNUSABLE & REBUILD ONLINE..

CREATE TABLE A(id NUMBER,NAME VARCHAR2(33));

insert into a VALUES (1,'RAVI');
insert into a VALUES(2,'SINDHU');
insert into a VALUES(3,'RESHWANTH');
insert into a VALUES(4,'MANJIRA');
commit;

create index id_indx on a(id);

DISABLE INDEXES:
---------------
alter index id_indx unusable;

ENABLE INDEXES:
-----------------
alter index id_indx rebuild online;

FUNCTION BASED INDEXES ENBLING N DISABLING:
------------------------------------------
create index a_name on a (upper(name));

alter index a_name disable;

alter index a_name enable;

Oracle Database Statistics (DBMS_STATS)

Table and Index Stats

Table statistics can be gathered for the database, schema, table or partition.

EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_dictionary_stats;

-- Manually start and stop to sample a representative time (several hours) of system activity.
EXEC DBMS_STATS.gather_system_stats('start');
EXEC DBMS_STATS.gather_system_stats('stop');

-- Sample from now until a specific number of minutes.
DBMS_STATS.gather_system_stats('interval', interval => 180); 

he DELETE_SYSTEM_STATS procedure will delete all workload stats 

and replace previously gathered noworkload stats with the default values.

EXEC DBMS_STATS.set_system_stats('iotfrspeed', 4096); 

Locking Stats

To prevent statistics being overwritten, you can lock the stats at schema, table or partition level.

EXEC DBMS_STATS.lock_schema_stats('SCOTT');
EXEC DBMS_STATS.lock_table_stats('SCOTT', 'EMP');
EXEC DBMS_STATS.lock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');