Search This Blog

Monday, November 30, 2009

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

Friday, November 20, 2009

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

Tuesday, November 10, 2009

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