How to find which tables have FK bases on PK parent table

select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints
where constraint_type='R'
and status='ENABLED'
and r_constraint_name in (select constraint_name from all_constraints where constraint_type in ('P','U') and table_name='TABLE_NAME');

Dump Oracle String

SQL> select dump('Michael Georgiou') from dual;

DUMP('MICHAELGEORGIOU')
-------------------------------------------------------------------------
Typ=96 Len=16: 77,105,99,104,97,101,108,32,71,101,111,114,103,105,111,117

SQL> select ascii('M') from dual;

ASCII('M')
----------
77

SQL> select ascii('i') from dual;

ASCII('I')
----------
105

Complete list of ascii characters are listed below:

Dec Hex Oct Char Description
0 0 000 null
1 1 001 start of heading
2 2 002 start of text
3 3 003 end of text
4 4 004 end of transmission
5 5 005 enquiry
6 6 006 acknowledge
7 7 007 bell
8 8 010 backspace
9 9 011 horizontal tab
10 A 012 new line
11 B 013 vertical tab
12 C 014 new page
13 D 015 carriage return
14 E 016 shift out
15 F 017 shift in
16 10 020 data link escape
17 11 021 device control 1
18 12 022 device control 2
19 13 023 device control 3
20 14 024 device control 4
21 15 025 negative acknowledge
22 16 026 synchronous idle
23 17 027 end of trans. block
24 18 030 cancel
25 19 031 end of medium
26 1A 032 substitute
27 1B 033 escape
28 1C 034 file separator
29 1D 035 group separator
30 1E 036 record separator
31 1F 037 unit separator
32 20 040 space
33 21 041 !
34 22 042 "
35 23 043 #
36 24 044 $
37 25 045 %
38 26 046 &
39 27 047 '
40 28 050 (
41 29 051 )
42 2A 052 *
43 2B 053 +
44 2C 054 ,
45 2D 055 -
46 2E 056 .
47 2F 057 /
48 30 060 0
49 31 061 1
50 32 062 2
51 33 063 3
52 34 064 4
53 35 065 5
54 36 066 6
55 37 067 7
56 38 070 8
57 39 071 9
58 3A 072 :
59 3B 073 ;
60 3C 074 <
61 3D 075 =
62 3E 076 >
63 3F 077 ?
Dec Hex Oct Char
64 40 100 @
65 41 101 A
66 42 102 B
67 43 103 C
68 44 104 D
69 45 105 E
70 46 106 F
71 47 107 G
72 48 110 H
73 49 111 I
74 4A 112 J
75 4B 113 K
76 4C 114 L
77 4D 115 M
78 4E 116 N
79 4F 117 O
80 50 120 P
81 51 121 Q
82 52 122 R
83 53 123 S
84 54 124 T
85 55 125 U
86 56 126 V
87 57 127 W
88 58 130 X
89 59 131 Y
90 5A 132 Z
91 5B 133 [
92 5C 134 \
93 5D 135 ]
94 5E 136 ^
95 5F 137 _
96 60 140 `
97 61 141 a
98 62 142 b
99 63 143 c
100 64 144 d
101 65 145 e
102 66 146 f
103 67 147 g
104 68 150 h
105 69 151 i
106 6A 152 j
107 6B 153 k
108 6C 154 l
109 6D 155 m
110 6E 156 n
111 6F 157 o
112 70 160 p
113 71 161 q
114 72 162 r
115 73 163 s
116 74 164 t
117 75 165 u
118 76 166 v
119 77 167 w
120 78 170 x
121 79 171 y
122 7A 172 z
123 7B 173 {
124 7C 174 |
125 7D 175 }
126 7E 176 ~
127 7F 177 DEL

Transportable tablespaces in Oracle 10g

SYS@ORCL > exec dbms_tts.transport_set_check('TBS1',TRUE,TRUE);

PL/SQL procedure successfully completed.

SYS@ORCL > select * from transport_set_violations;

VIOLATIONS
--------------------------------------------------------------------------------
Sys owned object SYS_C007034 in tablespace TBS1 not allowed in pluggable set
Sys owned object DATAE in tablespace TBS1 not allowed in pluggable set
Sys owned object SYS_IL0000021148C00002$$ in tablespace TBS1 not allowed in plu
ggable set

Sys owned object SYS_LOB0000021148C00002$$ in tablespace TBS1 not allowed in pl
uggable set

-----------------------------------------------------------------------------------
[oracle@tom datafiles]$ expdp "'sys/oracle as sysdba'" transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp

Export: Release 10.2.0.2.0 - Production on Thursday, 12 July, 2007 1:08:54

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": 'sys/******** AS SYSDBA' transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29341: The transportable set is not self-contained

-----------------------------------------------------------------------------------

[oracle@tom datafiles]$ expdp system/oracle transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp

Export: Release 10.2.0.2.0 - Production on Thursday, 12 July, 2007 1:16:42

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/tbs.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 01:18:21


Name (192.168.0.2:oracle): ftp
331 Password required for ftp.
Password:
230 User ftp logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> bin
200 Type set to I.
ftp> put tbs1_01.dbf
local: tbs1_01.dbf remote: tbs1_01.dbf
227 Entering Passive Mode (192,168,0,2,9,101).
150 Opening data connection for tbs1_01.dbf.
226 File received ok
10493952 bytes sent in 9.2 seconds (1.1e+03 Kbytes/s)

RMAN> convert datafile 'tbs1_01.dbf'
2> from platform='Linux IA (32-bit)'
3> db_file_name_convert="ts","win";

Starting backup at 12-JUL-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TBS1_01.DBF
converted datafile=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-ORCL_I-1121005791_TS-TBS1_FNO-7_
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished backup at 12-JUL-07

C:\>impdp system/oracle directory=data_pump_dir dumpfile=tbs.dmp transport_datafiles=tbs_win01.dbf

Import: Release 10.2.0.1.0 - Production on Thursday, 12 July, 2007 1:49:14

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=data_pump_dir dumpfile=tbs.dmp transport_d
atafiles=tbs_win01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user MICHAEL does not exist in the database

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 01:49:20


--------------------------------------------------------------------
SQL> create user michael identified by michael;

User created.
--------------------------------------------------------------------


C:\>create user michael identified by michael;
'create' is not recognized as an internal or external command,
operable program or batch file.

C:\>impdp system/oracle directory=data_pump_dir dumpfile=tbs.dmp transport_datafiles=tbs_win01.dbf

Import: Release 10.2.0.1.0 - Production on Thursday, 12 July, 2007 1:50:07

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=data_pump_dir dumpfile=tbs.dmp transport_d
atafiles=tbs_win01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 01:50:12


-------------------------------------------------------------------------
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
TBS1

SQL> alter tablespace tbs1 read write;
--------------------------------------------------------------------------

Explain Data Block Address

| Document Scope: |
The scope of the document is to understand and use Data Block Address .
The Second argument of ORA-600 [3374] corresponds to the expected Data Block Address.
They are 11206682 and 2617431000. The blocks in these DBA are corrupted.
Let's convert DBA to corresponding (file#,block#)

Example1 :ORA-00600: internal error code, arguments: [3374], [16777298], [825371952],[875376697], [11824], [], [], []

SQL> SELECT dbms_utility.data_block_address_block(16777298)
2 FROM dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16777298)
-----------------------------------------------
82

SQL>
SQL> SELECT dbms_utility.data_block_address_file(16777298)
2 FROM dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16777298)
----------------------------------------------
4

The corruption block is the block_id 82 in file_id 4. To find which database objects
belong the corrupted block,use the following query.

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = 4
and 82 between block_id AND block_id + blocks - 1;

TABLESPACE_NAME SEGMENT_TYPE OWNER
------------------------------ ------------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
USERS TABLE SYS
T3


The example below , shows how to find the data block addreess base on file_id and some block_id.


SQL> select block_id,block_id + blocks - 1,relative_fno,file_id from dba_extents where segment_name='T3';

BLOCK_ID BLOCK_ID+BLOCKS-1 FILE_ID
---------- ----------------- ----------
81 88 4

SQL> SELECT dbms_utility.make_data_block_address(4,82)
2 FROM dual;

DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(4,82)
------------------------------------------
16777298


Relate Note :
Ref : http://sysdba.wordpress.com/2006/04/05/how-to-check-for-and-repair-block-corruption-with-rman-in-oracle-9i-and-oracle-10g/

Expdp and impdp examples

#############################################################################################
# #
# impdp using network_link #
# #
#############################################################################################

Note:
--------------------------------------------------------------------------------------------
Database Link that link to Source database. In my example , I create the following database
link.
--------------------------------------------------------------------------------------------
SQL>create or replace public database link LINUX
connect to system identified by oracle using 'TOM';

---Create ALL depended tablespace ( default tablespace and temporary tablespace)


WINDOWS
+++++++

C:\>impdp system/oracle schemas=papikos network_link=LINUX

Import: Release 10.2.0.1.0 - Production on Friday, 13 July, 2007 15:05:08

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** schemas=papikos network_link=LINUX
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "PAPIKOS"."P1" 100 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 15:07:00

#############################################################################################
# #
# impdp using REMAP_TABLESPACE #
# #
#############################################################################################

Note:
--------------------------------------------------------------------------------------------
The user katerina has default tablespace the tablespace TOOLS. During the import I
remap the katerinas's tablespace to TBS1.
--------------------------------------------------------------------------------------------
C:\>impdp system/oracle schemas=katerina2 remap_tablespace=tools:TBS1 network_link=LINUX

Import: Release 10.2.0.1.0 - Production on Friday, 13 July, 2007 16:25:24

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** schemas=katerina2 remap_tablespace=tools:TBS1 network_link=
LINUX
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "KATERINA2"."K1" 100 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 16:26:22

Partitioning in 10g

Types of Partitioning
Range
Hash
Composite
List
Range
=====
Based on range of the partition key values.
It is used most commonly for dates.
Range partitioning is defined by the PARTITION BY RANGE(partition_key_column), and for
each sub partition in VALUES LESS THAN(value_list).The literal MAXVALUE represent a
virtual infinite value that sorts higher that any other value for the data type
including the null value.

Range
=====
( Partitioning historical data )
The example below shows how to create a table with range partitioning

CREATE TABLE PROFILE_HISTORY
(
PROFILE_ID NUMBER,
ACCESS_DATE DATE,
CONSTRAINT PROFILE_HISTORY_PK PRIMARY KEY (PROFILE_ID,ACCESS_DATE)
)
PARTITION BY RANGE(ACCESS_DATE)
(
PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')),
PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),
PARTITION P5 VALUES LESS THAN ( TO_DATE('01-12-2007','DD-MM-YYYY'))
);

The example below shows how to create a INDEX with range partitioning on a normal
table

CREATE TABLE PROFILE_HISTORY2
(
PROFILE_ID NUMBER,
ACCESS_DATE DATE,
CONSTRAINT PROFILE_HISTORY_PK2 PRIMARY KEY (PROFILE_ID)
);

CREATE INDEX PROFILE_HISTORY2_INDX
ON PROFILE_HISTORY2(ACCESS_DATE)
GLOBAL
PARTITION BY RANGE(ACCESS_DATE)
(
PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')),
PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),
PARTITION P5 VALUES LESS THAN (MAXVALUE)
);

The example below shows how to create a INDEX with range partitioning on a partition
table

CREATE INDEX PROFILE_HISTORY_INDX
ON PROFILE_HISTORY(ACCESS_DATE)
GLOBAL
PARTITION BY RANGE(ACCESS_DATE)
(
PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')),
PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),
PARTITION P5 VALUES LESS THAN (MAXVALUE)
);

The Local partition index
=========================

The Local partitiom index is created by specified the literal LOCAL.
The local index is created based on partition table.

CREATE TABLE PROFILE_HISTORY3
(
PROFILE_ID NUMBER,
ACCESS_DATE DATE,
CONSTRAINT PROFILE_HISTORY_PK3 PRIMARY KEY (PROFILE_ID)
)
PARTITION BY RANGE(ACCESS_DATE)
(
PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')),
PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),
PARTITION P5 VALUES LESS THAN ( TO_DATE('01-12-2007','DD-MM-YYYY'))
);

create index PROFILE_HISTORY3_indx on PROFILE_HISTORY3(PROFILE_ID,ACCESS_DATE)
LOCAL;

SQL> select partition_name
from dba_ind_partitions
where index_name='PROFILE_HISTORY3_INDX';

PARTITION_NAME
------------------------------
P1
P2
P3
P4
P5


CREATE TABLE PROFILE_HISTORY4
(
PROFILE_ID NUMBER,
ACCESS_DATE DATE,
CUSTOMER_ID NUMBER,
CONSTRAINT PROFILE_HISTORY_PK4 PRIMARY KEY (PROFILE_ID)
)
PARTITION BY RANGE(ACCESS_DATE,CUSTOMER_ID)
(
PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')AND CUSTOMER_ID 10),
PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),
PARTITION P5 VALUES LESS THAN ( TO_DATE('01-12-2007','DD-MM-YYYY'))
);



CREATE TABLE PROFILE_HISTORY6
(
PROFILE_ID NUMBER,
ACCESS_DATE DATE,
CUSTOMER_ID NUMBER,
CONSTRAINT PROFILE_HISTORY_PK6 PRIMARY KEY (PROFILE_ID)
)
PARTITION BY HASH(PROFILE_ID,CUSTOMER_ID);

CREATE TABLE PROFILE_HISTORY7
(
PROFILE_ID NUMBER,
ACCESS_DATE DATE,
CUSTOMER_ID NUMBER,
CONSTRAINT PROFILE_HISTORY_PK7 PRIMARY KEY (PROFILE_ID)
)
PARTITION BY HASH(PROFILE_ID,CUSTOMER_ID) PARTITION 4;

Flashback version query

Loading data
select current_scn from v$database;
insert into katerina.vt1 values(1);
insert into katerina.vt1 values(2);
insert into katerina.vt1 values(3);
insert into katerina.vt1 values(4);
commit;
select current_scn from v$database;
update katerina.vt1 set id=10 where id=1;
delete katerina.vt1 where id=2;
commit;
update katerina.vt1 set id=100 where id=10;
delete katerina.vt1 where id=3;
commit;
insert into katerina.vt1 values(2);
insert into katerina.vt1 values(3);
commit;


select
versions_startscn,
versions_endscn,
versions_xid,
versions_operation,
id
from katerina.vt1
versions between scn minvalue and maxvalue;

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V ID
----------------- --------------- ---------------- - ----------
9343741 06000400C5050000 I 3
9343741 06000400C5050000 I 2
9343739 04001A00A8040000 D 3
9343739 04001A00A8040000 U 100
9342873 020002009C050000 D 2
9342873 9343739 020002009C050000 U 10
9342868 01002B005B040000 I 4
9342868 9343739 01002B005B040000 I 3
9342868 9342873 01002B005B040000 I 2
9342868 9342873 01002B005B040000 I 1


Interpreat the results
=======================
My table has 4 rows


select
versions_startscn,
versions_endscn,
versions_xid,
versions_operation,
id
from katerina.vt1
versions between scn minvalue and maxvalue
AS OF SCN 9342873;

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V ID
----------------- --------------- ---------------- - ----------
9342873 020002009C050000 D 2
9342873 020002009C050000 U 10
9342868 01002B005B040000 I 4
9342868 01002B005B040000 I 3
9342868 9342873 01002B005B040000 I 2
9342868 9342873 01002B005B040000 I 1


select
id
from katerina.vt1
AS OF SCN 9342868;


ID
----------
1
2
3
4
select
id

from katerina.vt1
AS OF SCN 9342873;

ID
----------
10
3
4

Flashback table example Oracle 10g

sqlplus katerina/katerina

create table dept
(
deptid number primary key,
deptname varchar2(2)
);

insert into dept values ( 1,'a');
insert into dept values ( 2,'b');
insert into dept values ( 3,'c');
insert into dept values ( 4,'d');
insert into dept values ( 5,'e');

commit;

create table emp
(
empid number primary key,
salary number,
deptid number references dept(deptid)
);



insert into emp values ( 10,1000,1);
insert into emp values ( 20,1200,1);
insert into emp values ( 30,1300,2);
insert into emp values ( 40,1400,2);

commit;

create table maxSalary
(
emp_max_salary number
);

create or replace function mazSalary return number
is
n number;
begin
select max(salary) into n from emp;
return n;
end;
/

create or replace trigger emp_sal_audit
after update of salary on emp
for each row
begin
update maxsalary set emp_max_salary = mazSalary;
end;
/

SQL> select * from dept;

DEPTID DE
---------- --
1 a
2 b
3 c
4 d
5 e

SQL> select * from emp;

EMPID SALARY DEPTID
---------- ---------- ----------
10 1000 1
20 1200 1
30 1300 2
40 1400 2

SQL> select mazSalary from dual;

MAZSALARY
----------
1400

But update will be occured by the Rollis programmer



update emp set deptid=1;
--where empid=40
update emp set salary=20000 where deptid=1
commit;


DBA actions
============

select
versions_startscn,
versions_endscn,
versions_operation,
empid,
deptid,
salary
from katerina.emp
versions between scn minvalue and maxvalue;


VERSIONS_STARTSCN VERSIONS_ENDSCN V EMPID DEPTID SALARY
----------------- --------------- - ---------- ---------- ----------
9375531 U 40 1 20000
9375531 U 30 1 20000
9375531 U 20 1 20000
9375531 U 10 1 20000
9375512 9375531 U 40 1 1400
9375512 9375531 U 30 1 1300
9375512 9375531 U 20 1 1200
9375512 9375531 U 10 1 1000
9375512 10 1 1000
9375512 20 1 1200
9375512 30 2 1300

VERSIONS_STARTSCN VERSIONS_ENDSCN V EMPID DEPTID SALARY
----------------- --------------- - ---------- ---------- ----------
9375512 40 2 1400

select
versions_startscn,
versions_endscn,
versions_operation,
deptid,
deptname
from dept
versions between scn minvalue and maxvalue;

VERSIONS_STARTSCN VERSIONS_ENDSCN V DEPTID DE
----------------- --------------- - ---------- --
1 a
2 b
3 c
4 d
5 e


SQL> conn /as sysdba;
Connected.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
9375865

SQL> alter table katerina.emp enable row movement;

Table altered.

SQL> flashback table katerina.emp to scn 9375512-1;

Flashback complete.

SQL> select * from katerina.emp;

EMPID SALARY DEPTID
---------- ---------- ----------
10 1000 1
20 1200 1
30 1300 2
40 1400 2

VERSIONS_STARTSCN VERSIONS_ENDSCN V EMPID DEPTID SALARY
----------------- --------------- - ---------- ---------- ----------
9375961 I 10 1 1000
9375961 I 20 1 1200
9375961 I 30 2 1300
9375961 I 40 2 1400
9375961 D 40 1 20000
9375961 D 30 1 20000
9375961 D 20 1 20000
9375961 D 10 1 20000
9375531 9375961 U 40 1 20000
9375531 9375961 U 30 1 20000
9375531 9375961 U 20 1 20000

VERSIONS_STARTSCN VERSIONS_ENDSCN V EMPID DEPTID SALARY
----------------- --------------- - ---------- ---------- ----------
9375531 9375961 U 10 1 20000
9375512 9375531 U 40 1 1400
9375512 9375531 U 30 1 1300
9375512 9375531 U 20 1 1200
9375512 9375531 U 10 1 1000
9375512 10 1 1000
9375512 20 1 1200
9375512 30 2 1300
9375512 40 2 1400


delete * from emp;
delete * from dept;

SQL> flashback table katerina.emp to scn 9376535-1;
flashback table katerina.emp to scn 9376535-1
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (KATERINA.SYS_C005648) violated - parent key
not found


SQL> alter table katerina.dept enable row movement;

Table altered.

SQL> flashback table katerina.emp,katerina.dept to scn 9376535-1;

Flashback complete.

SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from katerina.emp;

EMPID SALARY DEPTID
---------- ---------- ----------
10 1000 1
20 1200 1
30 1300 2

Configure ntpd on Linux

rac1
chkconfig --level 3 ntpd on
/etc/init.d/ntpd start
On client just add in the crontab the following line to update every night 2 morning
rac2
* 2 * * * /usr/sbin/ntpdate -s -b -p 8 -u rac1

Compress directories using tar and gzip

Compress folder Test/ to Test.tar.gz
tar czfv Test.tar.gz Test/


Uncompress Test.tar.gz to folder Test/
tar -xzf Test.tar.gz

Change Oracle WebCache or Apache port to 80 in unix

As root
===========
cd $ORACLE_HOME/Apache/Apache/bin
chown root .apachectl
chmod 6750 .apachectl

As oracle
====================
Stop Oracle Web Cache using Application Server Control, or with the following
opmn command, as the oracle user (not root):

% opmnctl stopproc ias-component=WebCache

As root
============

# cd $ORACLE_HOME/webcache/bin
# webcache_setuser.sh setroot

As oracle
=============
Start Oracle Web Cache using Application Server Control, or with the following
opmn command, as the oracle user (not root):

% opmnctl startproc ias-component=WebCache

Change the port to 80 in the web cache administration site .

Report Server tips and docs ( Windows )

Setup Greek fonts when the report server run under windows env
=================================================================

How to Configure Reports to Generate PDF with Eastern European National Characters Doc ID: 223949.1

How to setup images
======================
How to Deploy Iconic Images via a JAR File in Forms 9i/10g? Doc ID: 232413.1

How to test a report
========================
http://localhost/reports/rwservlet?destype=cache&desformat=PDF&report=test.rdf

How to test a report with database connection
===============================================
http://localhost/reports/rwservlet?server=reportserverName&report=test_report.rdf&desformat=PDF&destype=cache&userid=username/password@develop_cy

Set Rac Database in NoARCHIVELOG

export ORACLE_SID=instance_name

sqlplus "/ as sysdba"

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1271564 bytes
Variable Size 314575092 bytes
Database Buffers 750780416 bytes
Redo Buffers 7114752 bytes
SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> exit
SQL> startup mount
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1271564 bytes
Variable Size 314575092 bytes
Database Buffers 750780416 bytes
Redo Buffers 7114752 bytes
Database mounted.
SQL>alter database noarchivelog;


Database altered.
Set the Cluster_database parameter again to true.

SQL> alter system set cluster_database=true scope=spfile;
System altered.
SQL>shutdown immediateWe will again use srvctl to start the database and associated service

[/home/oracle>srvctl start database -d

ssh without password

The example consists of 3 nodes , the node,tracker1 and tracker2 . The purpose of the example is to show how to setup ssh in order to login to other nodes with out a password .

Do for all
===============
mkdir ~/.ssh
chmod 755 ~/.ssh
/usr/bin/ssh-keygen -t rsa
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
chmod 644 ~/.ssh/authorized_keys
exec /usr/bin/ssh-agent $SHELL
/usr/bin/ssh-add



from node
==============
ssh hadoop@tracker2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
ssh hadoop@tracker1 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

scp ~/.ssh/authorized_keys hadoop@tracker2:~/.ssh/.

Running OS Commands and Scripts from PL/SQL Using java on Unix

This article is coming from :

http://www.dba-oracle.com/job_scheduling/commands_scripts_plsql.htm

Some extra tips are added in order to run on UNIX System. It is tested on Oracle 11g --Redhat Linux 5 el

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (isWindows()) {
finalCommand = new String[4];
finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
}
else {
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}

final Process pr = Runtime.getRuntime().exec(finalCommand);
new Thread(new Runnable() {
public void run() {
try {
BufferedReader br_in = new BufferedReader(new
InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println(buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process output.");
ioe.printStackTrace();
}
}
}).start();

new Thread(new Runnable() {
public void run() {
try {
BufferedReader br_err = new BufferedReader(new
InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println(buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process error.");
ioe.printStackTrace();
}
}
}).start();
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}

public static boolean isWindows() {
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
return true;
else
return false;
}

};
/

CREATE OR REPLACE PROCEDURE host_command (p_command IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
/

BEGIN
DBMS_JAVA.grant_permission ('RDFLDR', 'java.io.FilePermission',
'<>', 'read ,write, execute, delete');

DBMS_JAVA.grant_permission ('RDFLDR', 'SYS:java.lang.RuntimePermission',
'writeFileDescriptor', '');

DBMS_JAVA.grant_permission ('RDFLDR', 'SYS:java.lang.RuntimePermission',
'readFileDescriptor', '');
DBMS_JAVA.grant_permission( 'RDFLDR','SYS:java.io.FilePermission', '/bin/sh', 'execute' );
END;
/

SET SERVEROUTPUT ON SIZE 1000000
CALL DBMS_JAVA.SET_OUTPUT(1000000);
BEGIN
host_command (p_command => 'which java');
END;
/

Create cluster on OC4J 10.3

Step 1: Ensure that servers are ping each other
Step 2: Check the opmn ports ( $ORACLE_HOME/opmn/conf/opmn.xml)

Step 3:
#Server 1
opmnctl config topology update discover="server2.domain.cy:6200"
opmnctl reload
#Server 2
opmnctl config topology update discover="server1.domain.cy:6200"
opmnctl reload


Check the status of the cluster
===============================
opmnctl @cluster status


delete a note
==============

opmnctl config topology delete discover
opmnctl reload

Oracle secure backup

go to obtool web , configure , host , choose host and to be client,administrator,mediaServer

Create Virtual library
============================
obtool --user admin --password oracle mkdev -t library -o -S 4 -a ACER_ONE:/vlib -v vlib
Create Virtual Tape
obtool --user admin --password oracle mkdev -t tape -o -a ACER_ONE:/vt -v -l vlib -d 1 vt

C:\>obtool
ob> lsdev
library vlib in service
drive 1 vt in service
ob> lsmf --long
OSB-CATALOG-MF:
Write window: 7 days
Keep volume set: 14 days
Appendable: yes
Volume ID used: unique to this media family
Comment: OSB catalog backup media family
RMAN-DEFAULT:
Keep volume set: content manages reuse
Appendable: yes
Volume ID used: unique to this media family
Comment: Default RMAN backup media family
ob>

ob> lsdev
library vlib in service
drive 1 vt in service


Create database selector
=========================

mkssel --dbid 1190329173 --host ACER_ONE --content full --family RMAN-DEFAULT ssel1

ob> lsssel
Name Database Database Host name Content Copy Media Device Resource
name id # family restriction wait time
____________________________________________________________________________________________________
ssel1 all 1190329173 ACER_ONE full * RMAN-DEFA- none 1 hour
ULT

Backup with RMAN

RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
SEND 'OB_DEVICE vt';
BACKUP DATABASE;
}

Recreate dbconsole on RAC Database 10.2

export ORACLE_SID=orclinst1

-- drop repository if exist

emca -deconfig dbcontrol db -repos drop

---reconfigure again

emca -config dbcontrol db -repos create -cluster

References :
How to manage DB Control 10.2 for RAC Database with emca Doc ID: 395162.1How To Drop, Create And Recreate DB Control In A 10g Database Doc ID: 278100.1

ORACLE RAC KSH SCRIPT

#!/bin/ksh
#############################
# Put program variable here
#
db1=orcl
db2=RMAN
###############################
while [ : ] ; do
while [ : ] ; do
echo ' ';
echo '*******************************************';
echo ' M E N U ';
echo '*******************************************';
echo ' 1. Stop Cluster '
echo ' 2. Stop database '
echo ' 3. Stop instance '
echo ' 4. Start database '
echo ' 5. Start instance '
echo ' 6. Start Cluster '
echo ' 7. Cluster status '
echo ' 8. Databases status '
echo ' 9. OCFS2 status '
echo ' 10. Show OCR backups '
echo ' 11. Show RMAN backups'
echo ' 99. EXIT '
echo '*******************************************';
echo ' ';
read reply
case $reply in
1)
print ' +---------------------------+'
print ' | STOP CLUSTER |'
print ' +---------------------------+'
print '*** You choose to stop Oracle cluster services. ***'
print '*** By stopping Oracle cluster services all available database will be stop ***'
print 'To continue press [Y/y]'
read ans
if [[ $ans == @(Y|y) ]]; then
srvctl stop database -d $db1 -o immediate
srvctl stop database -d $db2 -o immediate
/etc/init.d/init.crs stop
$ORA_CRS_HOME/bin/crs_stat -t
fi
break ;;
2)
print ' +---------------------------+'
print ' | STOP DATABASE |'
print ' +---------------------------+'
print '*** You choose to stop Oracle database , this action will stop all instances in all nodes ***'
print 'To continue press [Y/y]'
read ans
if [[ $ans == @(Y|y) ]]; then
print '\n Enter the database name \c'
read db
srvctl stop database -d $db -o immediate
fi
break ;;
3)
print ' +---------------------------+'
print ' | STOP INSTANCE |'
print ' +---------------------------+'
print '*** You choose to stop Oracle instance on this node ***'
print 'To continue press [Y/y]'
read ans
if [[ $ans == @(Y|y) ]]; then
print '\n Enter the database name \c'
read db
print '\n Enter the instance name \c'
read instance
srvctl stop instance -d $db -i $instance
fi
break ;;
4)
print ' +---------------------------+'
print ' | START DATABASE |'
print ' +---------------------------+'
print '\n Enter the database name \c'
read db
srvctl start database -d $db -o open
break ;;
5)
print ' +---------------------------+'
print ' | DATABASE STATUS |'
print ' +---------------------------+'
print '\n Enter the database name \c'
read db
print '\n Enter the instance name \c'
read instance
srvctl start instance -d $db -i $instance
break ;;
6)
print ' +---------------------------+'
print ' | START CLUSTER |'
print ' +---------------------------+'
print ' Starting cluster components , and all avaible databases '
/etc/init.d/init.crs start
srvctl start database -d $db1 -o open
srvctl start database -d $db2 -o open
srvctl start service -d ORCL -s TEST
srvctl start service -d RMAN -s RMAN_CATALOG
$ORA_CRS_HOME/bin/crs_stat -t
break ;;
7)
print ' +---------------------------+'
print ' | CLUSTER STATUS |'
print ' +---------------------------+'
$ORA_CRS_HOME/bin/crs_stat -t
break ;;
8)
print ' +---------------------------+'
print ' | DATABASE STATUS |'
print ' +---------------------------+'
if ps -aef | grep pmon | grep $db1 > /dev/null ; then
print "Database $db1 is Up."
else
print "Database $db1 is down."
exit 1
fi
if ps -aef | grep pmon | grep $db2 > /dev/null ; then
print "Database $db2 is Up."
else
print "Database $db2 is down."
exit 1
fi
break ;;
9)
print ' +---------------------------+'
print ' | OCFS2 STATUS |'
print ' +---------------------------+'
/etc/init.d/o2cb status
break ;;
10) print 'Show OCR backups'
break ;;
99) exit
break ;;
esac
done
done

Backup RAC Database

select resource_name
current_utilization,
max_utilization
from v$resource_limit
where resource_name like 'g%s_%';


--Client TNS names must be point on Vip-address

srvctl stop database -d ORCL -o immediate

srvctl start database -d ORCL -o open


Create services

srvctl add service -d ORCL -s TEST -r ORCL1 -a ORCL2

start the service

srvctl start service -d ORCL -s TEST
srvctl start service -d RMAN -s RMAN_CATALOG

TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.domain.local)(PORT = 1526))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.domain.local)(PORT = 1526))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)
############################################################################################################

Backup RAC Database using RMAN with catalog
================================================

As sys create the rman's tablespace and rman user
=================================================
create tablespace RMAN
datafile '/u01/oradata/oracle/databases/RMAN/rman_01.dbf' size 512M autoextend on
segment space management auto;

create user rman identified by rman
default tablespace RMAN
quota unlimited on RMAN;

grant recovery_catalog_owner to rman;
grant connect, resource to rman;


Create RAM Service
====================

srvctl add service -d RMAN -s RMAN_CATALOG -r RMAN1 -a RMAN2

srvctl start service -d RMAN -s RMAN_CATALOG


For each node add the following
=================================
RMAN_CATALOG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.dl.mlsi.gov.cy)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2.dl.mlsi.gov.cy)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RMAN_CATALOG)
)
)


test service
============
sqlplus rman/rman@RMAN_CATALOG




Create catalog
===============
rman catalog rman/rman@RMAN_CATALOG

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 8 12:40:03 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to recovery catalog database

RMAN> create catalog tablespace "RMAN";


Register database with catalog
===============================
C:\>rman target sys/oracle@CPSWEB

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 8 14:49:10 2007

connected to target database: CPS (DBID=3507563536)
connected to target database: CPSWEB (DBID=3789508129)



RMAN> connect catalog rman/rman@RMAN_CATALOG

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete


RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 7;

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;


/************

alter system set db_recovery_file_dest='/u01/oradata/oracle/backup/flash_recovery_area' scope=both;

********/




where backupScript.sh :

#!/bin/ksh
####################################################################################################
# BACKUP DATABASES WITH RMAN
####################################################################################################
sid=`print $RAC_NODE_ID`
export ORACLE_SID=cpsinst$sid
rman TARGET / catalog rman/rman@RMAN_CATALOG cmdfile=/u01/oradata/oracle/backup/scripts/scriptForBackup.rman
export ORACLE_SID=cpsweb$sid
rman TARGET / catalog rman/rman@RMAN_CATALOG cmdfile=/u01/oradata/oracle/backup/scripts/scriptForBackup.rman
####################################################################################################
# EXPORT DATABASES WITH DATAPUMP #
# Comments:
# create directory BACKUP_DIR as '/u01/oradata/oracle/backup/exports/CPSWEB/day1';
####################################################################################################




sid=`print $RAC_NODE_ID`
export ORACLE_SID=cpsinst$sid
print ' Cleaning export area for CPS database '
print '-----------------------------------------'
rm -rf /u01/oradata/oracle/backup/exports/CPS/day5/*.dmp
mv /u01/oradata/oracle/backup/exports/CPS/day4/*.dmp /u01/oradata/oracle/backup/exports/CPS/day5
mv /u01/oradata/oracle/backup/exports/CPS/day3/*.dmp /u01/oradata/oracle/backup/exports/CPS/day4
mv /u01/oradata/oracle/backup/exports/CPS/day2/*.dmp /u01/oradata/oracle/backup/exports/CPS/day3
mv /u01/oradata/oracle/backup/exports/CPS/day1/*.dmp /u01/oradata/oracle/backup/exports/CPS/day2
expdp "'/ as sysdba'" full=y directory=BACKUP_DIR dumpfile=CPS_full.dmp
sid=`print $RAC_NODE_ID`
export ORACLE_SID=cpsweb$sid
print ' Cleaning export area for CPSWEB database '
print '-----------------------------------------'
rm -rf /u01/oradata/oracle/backup/exports/CPSWEB/day5/*.dmp
mv /u01/oradata/oracle/backup/exports/CPSWEB/day4/*.dmp /u01/oradata/oracle/backup/exports/CPSWEB/day5
mv /u01/oradata/oracle/backup/exports/CPSWEB/day3/*.dmp /u01/oradata/oracle/backup/exports/CPSWEB/day4
mv /u01/oradata/oracle/backup/exports/CPSWEB/day2/*.dmp /u01/oradata/oracle/backup/exports/CPSWEB/day3
mv /u01/oradata/oracle/backup/exports/CPSWEB/day1/*.dmp /u01/oradata/oracle/backup/exports/CPSWEB/day2
expdp "'/ as sysdba'" full=y directory=BACKUP_DIR dumpfile=CPSWEB_full.dmp
####################################################################################################
# BACKUP VOTING #
# Voting disk contains and manages information of all the node memberships #
####################################################################################################
dd if=/u01/oradata/oracle/cluster/CRS/crs0.ora of=/u01/oradata/oracle/backup/voting-disk/crs0.ora
dd if=/u01/oradata/oracle/cluster/CRS/crs1.ora of=/u01/oradata/oracle/backup/voting-disk/crs1.ora
dd if=/u01/oradata/oracle/cluster/CRS/crs2.ora of=/u01/oradata/oracle/backup/voting-disk/crs2.ora
####################################################################################################
# BACKUP OCR #
# Files that manage the cluster and RAC Configuration #
####################################################################################################
print 'last OCR backup are:'
ocrconfig -showbackup
# add crontab job as root on both nodes
# for node 1
# 0 20 * * 1-5 /disk01/oracle/product/10.2.0/db_1/bin/ocrconfig -export /u01/oradata/oracle/backup/flash_recovery_area/scripts/ocr-backup/ocr-export-node1.ora -s online
# for node 2
# 0 20 * * 1-5 /disk01/oracle/product/10.2.0/db_1/bin/ocrconfig -export /u01/oradata/oracle/backup/flash_recovery_area/scripts/ocr-backup/ocr-export-node2.ora -s online


where scriptForBackup.rman :

resync catalog;
run
{
allocate channel ch1 type disk;
backup incremental level 0 as backupset tag=full_backup (database);
sql 'alter system archive log current';
release channel ch1;
allocate channel ch1 type disk;
backup archivelog all tag=archives_backup
delete all input;
release channel ch1;
}
crosscheck backupset ;
crosscheck archivelog all;
delete noprompt obsolete;
restore validate database;
list backup;
exit;




/***** THIS DONE IN RMAN DATABASE *******/

/*
begin dbms_scheduler.drop_job (job_name=>'daily_backup');
end;
/
begin dbms_scheduler.drop_program(program_name => 'backup_database');
end;
/
begin dbms_scheduler.drop_schedule(schedule_name =>'weekly_backup_at_20pm'); end;
/
*/


begin
dbms_scheduler.create_schedule
(
schedule_name => 'weekly_backup_at_20pm',
repeat_interval => 'FREQ=WEEKLY; INTERVAL=1; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=20',
comments => 'schedule to run weekly at 20 pm'
);
dbms_scheduler.create_program
(
program_name => 'backup_database',
program_type => 'EXECUTABLE',
program_action => '/u01/oradata/oracle/backup/scripts/backupScript.ksh',
enabled => TRUE,
comments => 'Backup database using rman and then backup rman database via hot backup.'
);
dbms_scheduler.create_job
(
job_name=>'daily_backup',
program_name =>'backup_database',
schedule_name=> 'weekly_backup_at_20pm',
enabled => true,
comments => 'Backup database using rman and then backup.'
);
end;
/

Returns The First Day Of A Month

CREATE OR REPLACE FUNCTION fday_ofmonth(value_in DATE)
RETURN DATE IS
vMo VARCHAR2(2);
vYr VARCHAR2(4);
BEGIN
vMo := TO_CHAR(value_in, 'MM');
vYr := TO_CHAR(value_in, 'YYYY');
RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY');
EXCEPTION
WHEN OTHERS THEN
RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');
END fday_ofmonth;
/
REF : http://www.psoug.org/reference/date_func.html

Returns The Number Of Seconds Between Two Date-Time Values

CREATE OR REPLACE FUNCTION time_diff (
DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS

NDATE_1 NUMBER;
NDATE_2 NUMBER;
NSECOND_1 NUMBER(5,0);
NSECOND_2 NUMBER(5,0);

BEGIN
-- Get Julian date number from first date (DATE_1)
NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

-- Get Julian date number from second date (DATE_2)
NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

-- Get seconds since midnight from first date (DATE_1)
NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

-- Get seconds since midnight from second date (DATE_2)
NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/

REF : http://www.psoug.org/reference/date_func.html