ORACLE CLUSTERS - EXAMPLE

In order to create clustered table you must first create a cluster.

SQL> create cluster emp_clust(deptno number);

Cluster created.

Now we will create two tables dept and emp that are part of cluster emp_clust( clustered by deptno column)


SQL> CREATE TABLE dept (
2 deptno NUMBER primary key,
3 dname varchar(10),loc varchar(20))
4 cluster emp_clust(deptno);

Table created.

SQL> CREATE TABLE emp (
2 empno NUMBER(5) PRIMARY KEY,
3 ename VARCHAR2(15) NOT NULL,
4 deptno NUMBER REFERENCES dept)
5 CLUSTER emp_clust(deptno);

Table created.

.
SQL> CREATE INDEX emp_dept_index
2 ON CLUSTER emp_clust;

Index created.

Dictionary Tables to query information regarding clusters:

user_clusters,

user_clu_columns;


SQL> select * from tab;

INSERTING VALUES

SQL> insert into dept values(10,'a','b');

1 row created.

SQL> insert into dept values(20,'a','b');

1 row created.

SQL> insert into dept values(30,'a','b');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into emp values(100,'king',10);

1 row created.

SQL> insert into emp values(101,'king',10);

1 row created.

SQL> insert into emp values(102,'miller',20);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

SQL> select * from dept;

MANAGING PARTITIONS

CREATING PARTITIONS

CREATE TABLESPACE smpp_log5 DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\ smpp_log5.DBF’ SIZE 10G AUTOEXTEND ON NEXT 512M EXTENT MANAGEMENT LOCAL;

CREATE TABLE TBL_SMPP_LOGS_BACKUP1
( "DATE_TIME" DATE,
"ANI" VARCHAR2(15),
"DNIS" VARCHAR2(20),
"MESSAGE" VARCHAR2(160),
"PROMO_TYPE" VARCHAR2(10),
"RESPONSE" VARCHAR2(20),
"CIRCLE_ID" NUMBER(*,0))
PARTITION BY RANGE(date_time)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('04/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOGS1,
PARTITION P2 VALUES LESS THAN(TO_DATE('06/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOG2,
PARTITION P3 VALUES LESS THAN(TO_DATE('08/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOG3,
PARTITION P4 VALUES LESS THAN(TO_DATE('10/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOG4
);

CREATE INDEX DATE1_idx ON TBL_SMPP_LOGS_BACKUP1(date_time) LOCAL
(PARTITION P1 TABLESPACE SMPP_LOGS1,
PARTITION P2 TABLESPACE SMPP_LOG2,
PARTITION P3 TABLESPACE SMPP_LOG3,
PARTITION P4 TABLESPACE SMPP_LOG4);

CREATE TABLE TBL_SMPP_LOGS1_BACKUP1
( "DATE_TIME" TIMESTAMP (6),
"ANI" VARCHAR2(15),
"DNIS" VARCHAR2(10),
"MESSAGE" VARCHAR2(160),
"PROMO_TYPE" VARCHAR2(10),
"RESPONSE" VARCHAR2(20))
PARTITION BY RANGE(date_time)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('04/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOGS1,
PARTITION P2 VALUES LESS THAN(TO_DATE('06/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOG2,
PARTITION P3 VALUES LESS THAN(TO_DATE('08/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOG3,
PARTITION P4 VALUES LESS THAN(TO_DATE('10/03/2009','DD/MM/YYYY')) TABLESPACE SMPP_LOG4
);

ADDING PARTITIONS
SQL> CREATE TABLESPACE smpp_log6 DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\ smpp_log6.DBF' SIZE 10G AUTOEXTEND ON NEXT 512M EXTENT MANAGEMENT LOCAL;
Tablespace created.
SQL> ALTER TABLE TBL_SMPP_LOGS1_BACKUP1 ADD PARTITION P6 VALUES LESS THAN (TO_DATE('17/03/2009','DD/MM/YYYY')) TABLESPACE smpp_log6;
Table altered.
SQL> ALTER TABLE TBL_SMPP_LOGS_BACKUP1 ADD PARTITION P6 VALUES LESS THAN (TO_DATE('17/03/2009','DD/MM/YYYY')) TABLESPACE smpp_log6;
Table altered.

SQL> SELECT INDEX_NAME,PARTITION_NAME,STATUS,TABLESPACE_NAME,NUM_ROWS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME='DATE2_IDX';
ALTER INDEX DATE2_IDX REBUILD PARTITION P5
TABLESPACE SMPP_LOG5;
SQL> ALTER INDEX DATE2_IDX REBUILD PARTITION P5
2 TABLESPACE SMPP_LOG5;
Index altered.
SQL> ALTER INDEX DATE2_IDX REBUILD PARTITION P3 TABLESPACE SMPP_LOG3;
Index altered.
SQL> ALTER INDEX DATE2_IDX REBUILD PARTITION P2 TABLESPACE SMPP_LOG2;
Index altered.
SQL> ALTER INDEX DATE2_IDX REBUILD PARTITION P1 TABLESPACE SMPP_LOGS1;
Index altered.
SQL> SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='TBL_SMPP_LOGS1_BACKUP1';


CREATE TABLESPACE smpp_log5 DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\ smpp_log5.DBF’ SIZE 10G AUTOEXTEND ON NEXT 512M EXTENT MANAGEMENT LOCAL;
ALTER TABLE TBL_SMPP_LOGS1_BACKUP1 ADD PARTITION P5 VALUES LESS THAN (TO_DATE('13/03/2009','DD/MM/YYYY')) TABLESPACE smpp_log5;
***********************************
CREATE INDEX DATE3_idx ON TBL_SMPP_LOGS1_BACKUP1(date_time) LOCAL
(PARTITION P1 TABLESPACE SMPP_LOGS1,
PARTITION P2 TABLESPACE SMPP_LOG2,
PARTITION P3 TABLESPACE SMPP_LOG3,
PARTITION P4 TABLESPACE SMPP_LOG4);

DBMS_SCHEDLUER ( ORACLE 10G)

STEP 1: Create a Procedure which you want to schedule.

CREATE OR REPLACE PROCEDURE PROC1
AS
BEGIN
INSERT INTO TEMP (ANI) VALUES(9856678955);
END;


STEP 2 :Schedule a Procedure (Job) using DBMS_SCHEDULER.

BEGIN
dbms_scheduler.create_job( job_name=>'JOB1',
job_type=>'STORED_PROCEDURE',
job_action=>'SCOTT.PROC1',
number_of_arguments=>0,
start_date=>TRUNC(SYSDATE),
repeat_interval=>'FREQ=MINUTELY;INTERVAL=1',
job_class=> 'DEFAULT_JOB_CLASS',
enabled=>TRUE,
auto_drop=>FALSE,
comments=>NULL);
END;
/



DROPPING A JOB:If you want to drop a existing job:

exec dbms_scheduler.drop_job(’SCOTT.JOB1’);

Some example of parameter repeat_interval:

1. repeat_interval=>’FREQ=daily;byhour=20;byminute=0;bysecond=0′.
The job will runs daily from Monday to Sunday at 20:00 hours.
2. repeat_interval=>’FREQ=daily;byhour=20;byminute=30;bysecond=0′.
The job will runs daily from Monday to Sunday at 20:30 hours.
3. repeat_interval=>’FREQ=monthly;bymonthday=10,20; byminute=0;bysecond=0;’
The job will on 10th and 20th day of each month.
4. repeat_interval=>’FREQ=yearly;bymonth=JAN,APR,SEP,DEC;BYMONTHDAY=21;
byhour=20;byminute=30;bysecond=0′ The job will run on 21st of months
JAN, APR, SEP and DEC at 20:30 hours.

UPDATING A SCHEDULED JOB

DBMS_SCHEDULER.SET_ATTRIBUTE (
name=>’SCOTT.JOB2’,
attribute=>’job_action’,
value=>’SCOTT.PROC2’);

Import & Export using Data pump in Oracle 10G

EXPORTING SCHEMA USING EXPORT DATA PUMP
STEP 1: Creating directory
CONNECT AS SYSDBA
SQL> CREATE DIRECTORY ORACLE_EXP AS ‘d:\Export_backup’;
SQL>GRANT READ,WRITE ON DIRECTORY ORACLE_EXP TO DBUSER;

STEP 2: Exporting Schema

RUN->Cmd
C:\Documents and Settings\sangeeta>expdp dbuser/rfc12345 directory=oracle_exp dumpfile=exp.dmp

dbuser id a User and rfc12345 is a password
EXAMPLE :IMPORTING SCHEMA USING IMPORT DATA PUMP (IN different database-ORCL)
STEP 1: COPY EXP.DMP IN DIRECTORY ‘D:\ORACLE_EXP’
STEP2: PREPARING SCHEMA
SQL>CREATE USER DBUSER IDENTIFIED BY RFC12345;
SQL> GRANT CONNECT,DBA,RESOURCE TO DBUSER;
SQL> CREATE DIRECTORY ORACLE_EXP AS ‘D:\ORACLE_EXP’;
SQL>GRANT READ,WRITE ON DIRECTORY ORACLE_EXP TO DBUSER;
Create the required tablespaces
SQL> CREATE TABLESPACE VOICECHAT DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ VOICECHAT.DBF’ SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL;

Set the default tablespace
SQL> ALTER USER DBUSER DEFAULT TABLESPACE VOICECHAT;

STEP3: IMPORTING SCHEMA
RUN->Cmd
C:\Documents and Settings\sangeeta>SQL>IMPDP DBUSER/RFC12345 DIRECTORY=ORACLE_EXP DUMPFILE=EXP.DMP

COMMANDS FOR EXPORTING AND IMPORTING TABLES

EXPDP RFC_DB/RFC12345@VMS TABLES=TBL_VOICECHAT,TBL_ASTRO DIRECTORY=ORACLE_EXP DUMPFILE=TBL_VC_ASTR_EXP.DMP LOGFILE=EXPTB.LOG;

IMPDP RFC_DB/RFC12345@VMS TABLES= TBL_VOICECHAT,TBL_ASTRO DIRECTORY=ORACLE_EXP DUMPFILE=TBL_VC_ASTR_EXP.DMP LOGFILE=IMPTB.LOG;

Other performance improvement steps to consider for Oracle 10g

INCREASE THE SIZE OF SYSTEM AND SYSAUX TABLESPACE BY ADDING A DATAFILE

SQL>ALTER TABLESPACE "SYSAUX" ADD DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\SYSAUX02' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

SQL>ALTER TABLESPACE "SYSTEM" ADD DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\SYSTEM02.DBF' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

CHANGE THE VALUES OF BELOW GIVEN PARAMETERS AS REQUIRED

SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=200;
SQL>ALTER SYSTEM SET UNDO_RETENTION = 900 SCOPE=SPFILE;
SQL>ALTER SYSTEM SET CURSOR_SHARING = SIMILAR SCOPE=SPFILE;
SQL> ALTER SYSTEM SET LOG_CHECKPOINTS_TO_ALERT=TRUE SCOPE =BOTH;

YOU CAN CREATE SEPARATE TABLESPACE FOR STORING INDEXES
E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\INDEX01.DBF
INDEX_TB 1073741824

YOU CAN INCREASE THE SIZE OF TEMPORARY TABLESPACE BY ADDING ONE TEMPFILE

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\TEMP02 ‘ SIZE 1024M;

SQL> SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME=’DEFAULT_TEMP_TABLESPACE’;

Increasing the size of redo logfile

Step 1: Add new logfile groups of required size

SQL>alter database add logfile group 4('E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\REDO04a.rdo','E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\REDO04b.rdo') size 500M

SQL>alter database add logfile group 5('E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\REDO05a.rdo','E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\REDO05B.rdo') size 500M
SQL>alter database add logfile group 6('E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\REDO06a.rdo','E:\ORACLE\PRODUCT\10.2.0\ORADATA\VMS\REDO06B.rdo') size 500M

Step 2: Drop the previous logfile groups
SQL>ALTER DATABASE DROP LOGFILE GROUP 1
SQL>ALTER DATABASE DROP LOGFILE GROUP 2
SQL>ALTER DATABASE DROP LOGFILE GROUP 3

SQL> SELECT * FROM V$LOG;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
4 1 272 524288000 2 YES INACTIVE 36943246 26-FEB-09
5 1 273 524288000 2 YES INACTIVE 37596143 26-FEB-09
6 1 274 524288000 2 NO CURRENT 38261614 27-FEB-09

MANAGING INSTANCE IN ORACLE 10G

INCREASING THE SIZE OF OF SGA AND PGA
ALTER SYSTEM SET SGA_MAX_SIZE=1024M SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=1024M SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=512M SCOPE=SPFILE;
ALTER SYTEM SET WORK_AREA_SIZE_POLICY=AUTO SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP

CHECKING THE VALUE OF THE PARAMETERS
SQL> SHOW PARAMETER SGA_MAX_SIZE;
SQL> SHOW PARAMETER SGA_TARGET;
SQL> SHOW PARAMETER PGA_AGGREGATE_TARGET;
SQL> SHOW PARAMETER WORKAREA_SIZE_POLICY;

KEEPING FREQUENTLY USED AND LARGE PL/SQL PROCEDURES IN BUFFER POOL
STEP 1: Run the script as sysdba (ONE TIME)
TO KEEP PROCEDURES IN BUFFER POOL
SQL> @ E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\DBMSPOOL.SQL

STEP 2: Keep the procedures in Shared Pool
EXECUTE DBMS_SHARED_POOL.KEEP(‘ORCL.PROCEDURE1’);
EXECUTE DBMS_SHARED_POOL.KEEP(‘ORCL.PROCEDURE12’);

Check v$db_object_cache view to see how much memory is consumed by each package;
SELECT SUM(SHARABLE_MEM)/(1024*1024) SPACE_IN_MB FROM V$DB_OBJECT_CACHE WHERE KEPT='YES';

SELECT NAME,LOADS,PINS,EXECUTIONS,KEPT FROM V$DB_OBJECT_CACHE WHERE TYPE='PROCEDURE';