Oracle DBMS_SCHEDULER example

The DBMS_JOB package is replaced by the DBMS_SCHEDULER package in oracle 10g
( oracle job )


login as sys or system

sqlplus / as sysdba

sqlplus>


this job will run daily 4 a.m


begin
dbms_scheduler.create_job
(
job_name => 'RMAN_FULL_F',
job_type => 'EXECUTABLE',
job_action => '/usr/bin/ksh',
start_date =>sysdate+ 1/288 ,
number_of_arguments => 1,
repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0',
enabled => false,
comments => 'oracle backup RMAN '
);
end;
/

PL/SQL procedure successfully completed.

(start_date =>sysdate+ 1/288 this will start job after 5min )

Notice that the JOB_TYPE can be PLSQL_BLOCK, STORED_PROCEDURE, and EXECUTABLE.
The REPEAT_INTERVAL
can be HOURLY, DAILY, MINUTELY, YEARLY or BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR, BYMINUTE, BYSECOUND.
For example, FREQ=BYWEEKNO=4,7,52 or BYDAY=MON, etc.




exec dbms_scheduler.set_job_argument_value(job_name=>'RMAN_FULL_F',argument_pos
ition=>1 ,argument_value=>'/aptus/oracle/admin/aptdb/script/rman_oracle.sh') ;

PL/SQL procedure successfully completed.

(rman_oracle.sh is the novagenesis Rman backup file )

to eable the job

SQL> exec dbms_scheduler.enable(name=>'RMAN_FULL_F');

PL/SQL procedure successfully completed.



to see the detail of job
select status,run_duration,actual_start_date,additional_info from dba_scheduler_job_run_details
where job_name='RMAN_FULL_F'

oracle dictionary info dict.sql

dict comment
dict info
dictionary comments
@dict.sql
dict.sql


break on sort_key skip1 nodup
col sort_key noprint
col comments format a78 word

def table = &&1

WITH dict_view AS
( SELECT CASE
WHEN SUBSTR(table_name,1,5) = 'USER_' THEN 1
WHEN SUBSTR(table_name,1,4) = 'ALL_' THEN 2
WHEN SUBSTR(table_name,1,4) = 'DBA_' THEN 3
ELSE 4
END AS sort_key
, table_name
, comments
FROM dictionary
WHERE table_name LIKE UPPER('%&&1%')
UNION
SELECT CASE
WHEN SUBSTR(v.view_name,1,5) = 'USER_' THEN 1
WHEN SUBSTR(v.view_name,1,4) = 'ALL_' THEN 2
WHEN SUBSTR(v.view_name,1,4) = 'DBA_' THEN 3
ELSE 4
END AS sort_key
, NVL(s.synonym_name,v.view_name)
, NVL(d.comments,c.comments)
FROM dba_views v
LEFT OUTER JOIN dictionary d
ON d.table_name = v.view_name
LEFT OUTER JOIN all_synonyms s
ON s.table_name = v.view_name
AND s.table_owner = v.owner
AND s.owner = 'PUBLIC'
LEFT OUTER JOIN dba_tab_comments c
ON c.table_name = v.view_name
AND c.owner = v.owner
WHERE ( v.view_name LIKE UPPER('V\_$%&&1%') ESCAPE '\'
OR v.view_name LIKE UPPER('GV\_$&&1%') ESCAPE '\'
OR v.view_name LIKE UPPER('DBA\_$&&1%') ESCAPE '\' )
AND d.table_name IS NULL )
SELECT sort_key, table_name, comments
FROM ( SELECT sort_key, table_name, comments , ROW_NUMBER() OVER(PARTITION BY table_name ORDER BY table_name NULLS LAST) AS seq
FROM dict_view )
WHERE seq = 1
ORDER BY sort_key, table_name
/
undefine 1



SQL> @dict
Enter value for 1: file
old 11: WHERE table_name LIKE UPPER('%&&1%')
new 11: WHERE table_name LIKE UPPER('%file%')
old 31: WHERE ( v.view_name LIKE UPPER('V\_$%&&1%') ESCAPE '\'
new 31: WHERE ( v.view_name LIKE UPPER('V\_$%file%') ESCAPE '\'
old 32: OR v.view_name LIKE UPPER('GV\_$&&1%') ESCAPE '\'
new 32: OR v.view_name LIKE UPPER('GV\_$file%') ESCAPE '\'
old 33: OR v.view_name LIKE UPPER('DBA\_$&&1%') ESCAPE '\' )
new 33: OR v.view_name LIKE UPPER('DBA\_$file%') ESCAPE '\' )

TABLE_NAME COMMENTS
------------------------------ ------------------------------------------------------------------------------
USER_DBFS_HS_FILES
USER_FILE_GROUPS Details about file groups
USER_FILE_GROUP_EXPORT_INFO Details about export information of file group versions
USER_FILE_GROUP_FILES Details about file group files
USER_FILE_GROUP_TABLES Details about the tables in the file group repository
USER_FILE_GROUP_TABLESPACES Details about the transportable tablespaces in the file group repository
USER_FILE_GROUP_VERSIONS Details about file group versions
USER_SCHEDULER_FILE_WATCHERS Scheduler file watch requests owned by the current user

ALL_FILE_GROUPS Details about file groups
ALL_FILE_GROUP_EXPORT_INFO Details about export information of file group versions
ALL_FILE_GROUP_FILES Details about file group files
ALL_FILE_GROUP_TABLES Details about the tables in the file group repository
ALL_FILE_GROUP_TABLESPACES Details about the transportable tablespaces in the file group repository
ALL_FILE_GROUP_VERSIONS Details about file group versions
ALL_SCHEDULER_FILE_WATCHERS Scheduler file watch requests visible to the current user

DBA_DATA_FILES Information about database data files
DBA_EXP_FILES Description of export files
DBA_FILE_GROUPS Details about file groups
DBA_FILE_GROUP_EXPORT_INFO Details about export information of file group versions
DBA_FILE_GROUP_FILES Details about file group files
DBA_FILE_GROUP_TABLES Details about the tables in the file group repository
DBA_FILE_GROUP_TABLESPACES Details about the transportable tablespaces in the file group repository
DBA_FILE_GROUP_VERSIONS Details about file group versions
DBA_HIST_DATAFILE Names of Datafiles
DBA_HIST_FILEMETRIC_HISTORY File Metrics History
DBA_HIST_FILESTATXS Datafile Historical Statistics Information
DBA_HIST_IOSTAT_FILETYPE Historical I/O statistics by file type
DBA_HIST_IOSTAT_FILETYPE_NAME File type names for historical I/O statistics
DBA_HIST_TEMPFILE Names of Temporary Datafiles
DBA_PROFILES Display all profiles and their limits
DBA_SCHEDULER_FILE_WATCHERS All scheduler file watch requests in the database
DBA_SQL_PROFILES set of sql profiles
DBA_TEMP_FILES Information about database temp files

GV$ASM_FILE Synonym for GV_$ASM_FILE
GV$ASM_FILESYSTEM Synonym for GV_$ASM_FILESYSTEM
GV$BACKUP_DATAFILE Synonym for GV_$BACKUP_DATAFILE
GV$BACKUP_SPFILE Synonym for GV_$BACKUP_SPFILE
GV$CONTROLFILE Synonym for GV_$CONTROLFILE
GV$CONTROLFILE_RECORD_SECTION Synonym for GV_$CONTROLFILE_RECORD_SECTION

Oracle List of constraints owned by the current user

constraint list
List of constraints owned by the current user
List constraints for a table owned by the current user




SQL> CREATE OR REPLACE TYPE db_constraint_ot AS OBJECT
( owner VARCHAR2(30)
, constraint_name VARCHAR2(30)
, constraint_type VARCHAR2(1)
, table_name VARCHAR2(30)
, search_condition VARCHAR2(32767)
, r_owner VARCHAR2(30)
, r_constraint_name VARCHAR2(30)
, delete_rule VARCHAR2(9)
, status VARCHAR2(8)
, deferrable VARCHAR2(14)
, deferred VARCHAR2(9)
, validated VARCHAR2(13)
, generated VARCHAR2(14)
, bad VARCHAR2(3)
, rely VARCHAR2(4)
, last_change DATE
, index_owner VARCHAR2(30)
, index_name VARCHAR2(30)
, invalid VARCHAR2(7)
, view_related VARCHAR2(14) )
/

Type created.

SQL> CREATE OR REPLACE TYPE db_constraint_tt AS TABLE OF db_constraint_ot;
2 /

Type created.

SQL> CREATE OR REPLACE TYPE varchar2_tt AS TABLE OF VARCHAR2(4000) ;
2 /

Type created.







def table = &1
set term off
store set sqlplus_settings.sql replace
TTITLE OFF
var tabname VARCHAR2(30)
var results REFCURSOR
exec :tabname := UPPER('&table')
col table new_value table
SELECT UPPER(:tabname) AS "TABLE" FROM dual;
set def on term on autoprint off feed off lines 110

col type hea "Type"
col constraint_name hea "Constraint name"
col table_name hea "Table name"
col table_owner format a20 hea "Owner"
col status hea "Status"
col deferrable hea "Deferrable?"
col deferred hea "Deferred?"

col search_condition format a60 word hea "Definition"

DECLARE
v_table_exists VARCHAR2(1) := 'Y';
v_constraints DB_CONSTRAINT_TT := DB_CONSTRAINT_TT();

CURSOR c_constraints (cp_tablename all_constraints.table_name%TYPE)
IS
SELECT c.owner
, CASE
WHEN c.generated = 'GENERATED NAME' AND c.constraint_name LIKE 'SYS\_%' ESCAPE '_'
THEN '[' || c.constraint_name || ']'
ELSE c.constraint_name
END AS constraint_name
, c.constraint_type
, c.table_name
, c.search_condition
, TO_CHAR(NULL) AS column_list
, DECODE(c.constraint_type,
'R', 'Foreign key (%COLS%) to ' || r.table_name
|| ' (' || c.r_constraint_name
|| ')'
|| DECODE(c.delete_rule, 'CASCADE', ', ' || c.delete_rule),
'P', 'Primary key (%COLS%)',
'U', 'Unique key (%COLS%)'
) key_description
, CAST
( MULTISET
( SELECT column_name
FROM all_cons_columns
WHERE owner = c.owner
AND constraint_name = c.constraint_name
ORDER BY position
) AS VARCHAR2_TT ) key_columns
, c.r_owner
, c.r_constraint_name
, c.delete_rule
, c.status
, c.deferrable
, c.deferred
, c.validated
, c.generated
, c.bad
, c.rely
, c.last_change
, c.index_owner
, c.index_name
, c.invalid
, c.view_related
FROM all_constraints c
, all_constraints r
WHERE c.table_name = cp_tablename
AND c.owner = USER
AND r.constraint_name (+)= c.r_constraint_name
AND r.owner (+)= c.r_owner;
BEGIN
FOR r IN c_constraints(:tabname)
LOOP
v_constraints.EXTEND;

IF r.key_columns.COUNT > 0 THEN
FOR i IN r.key_columns.FIRST..r.key_columns.LAST LOOP
r.column_list := r.column_list || r.key_columns(i) || ', ';
END LOOP;
r.column_list := RTRIM(r.column_list,', ');
END IF;

r.key_description := REPLACE(r.key_description,'%COLS%', r.column_list);

v_constraints(c_constraints%ROWCOUNT) :=
DB_CONSTRAINT_OT
( r.owner
, r.constraint_name
, r.constraint_type
, r.table_name
, NVL(r.search_condition,r.key_description)
, r.r_owner
, r.r_constraint_name
, r.delete_rule
, r.status
, r.deferrable
, r.deferred
, r.validated
, r.generated
, r.bad
, r.rely
, r.last_change
, r.index_owner
, r.index_name
, r.invalid
, r.view_related );
END LOOP;

IF v_constraints.COUNT = 0 THEN
-- Nothing in ALL_CONSTRAINTS - check table exists:
SELECT MIN('N') INTO v_table_exists
FROM dual
WHERE NOT EXISTS
( SELECT 1 FROM all_tables
WHERE table_name = :tabname
AND owner = USER );

IF v_table_exists = 'N' THEN
DBMS_OUTPUT.PUT_LINE('No such table "' || :tabname || '"');
END IF;
END IF;

OPEN :results FOR
SELECT CASE
WHEN search_condition LIKE '"%" IS NOT NULL' THEN 'Not Null'
WHEN constraint_type = 'C' THEN 'Check'
WHEN constraint_type = 'U' THEN 'Unique'
WHEN constraint_type = 'P' THEN 'Primary'
WHEN constraint_type = 'R' THEN 'FK'
ELSE constraint_type
END AS type
, constraint_name
, search_condition
, status
FROM TABLE(v_constraints)
ORDER BY
CASE
WHEN constraint_type = 'P' THEN '1'
WHEN constraint_type = 'U' THEN '2'
WHEN constraint_type = 'R' THEN '3'
WHEN constraint_type LIKE '"%" IS NOT NULL' THEN '4'
WHEN constraint_type = 'C' THEN '5'
ELSE constraint_type
END
, constraint_name;
END;
/

set term on

print :results





TTITLE ON LEFT 'Tables referencing &table:' SKIP1

SELECT -- r_owner AS table_owner
table_name
, constraint_name
, status
, deferrable
, deferred
FROM all_constraints
WHERE constraint_type = 'R'
AND r_owner = USER
AND r_constraint_name IN
( SELECT constraint_name
FROM user_constraints
WHERE table_name = :tabname
AND constraint_type IN ('P','U') );

prompt
TTITLE ""
TTITLE OFF
@sqlplus_settings.sql




SQL> create table anuj1( x number primary key ) ;


test this script


SQL> @cons
Enter value for 1: ANUJ1

Type Constraint name Definition Status
-------- ------------------------------ ------------------------------------------------------------ --------
Primary SYS_C0021021 Primary key (X) ENABLED





SQL> create table anuj2( n number, CONSTRAINT anuj2_fk FOREIGN KEY(n) REFERENCES anuj1(x) );

SQL> desc anuj2
Name Null? Type
----------------------------------------------------------- -------- N NUMBER


SQL> @cons
Enter value for 1: anuj1

Type Constraint name Definition Status
-------- ------------------------------ ------------------------------------------------------------ --------
Primary SYS_C0021021 Primary key (X) ENABLED

Tables referencing ANUJ1:
Table name Constraint name Status Deferrable? Deferred?
------------------------------ ------------------------------ -------- -------------- ---------
ANUJ2 ANUJ2_FK ENABLED NOT DEFERRABLE IMMEDIATE





save_sqlplus_settings.sql
set termout off
store set sqlplus_settings replace
clear breaks
clear columns
clear computes
set feedback off
set verify off
set termout on
set define "&"




restore_sqlplus_settings.sql
set termout off
@sqlplus_settings
clear breaks
clear columns
clear computes
set termout on
============================


or


SQL> col table_name format a32
col columns format a40
set lines 140
set pages 200
select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
order by table_name
/


TABLE_NAME CONSTRAINT_NAME COLUMNS
-------------------------------- ------------------------------ ----------------------------------------
EMP FK_DEPTNO DEPTNO