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';

ARCHIVELOG MODE AND FLASHBACK FEATURE 10g

STEP 1: Ser the database in Archivelog Mode.
SQL> SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST1=”LOCATION= d:\archiving” SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_10=’’;
Checking Archivelog mode and related parameters
SQL>ARCHIVE LOG LIST
SQL>SHOW PARAMETER LOG;

STEP 2: Setting the parameter for Flashback.
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;
Setting Flash Recovery Area
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE= 50G SCOPE=BOTH;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=’ E:\oracle\product\10.2.0/flash
_recovery_area ‘ SCOPE= BOTH;

STEP3: Setting Flashback Database feature ON
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
SQL>SELECT FLASHBACK_ON FROM V$DATABASE;
Related Views:
V$Recovery_file_Dest
V$ Flash_Recovery_Area_usage.
SQL> Select * from v$Flash_Recovery_Area_usage;