advertisements
_____________________________________________________________________________________________________________________
In normal export utility (EXP) we have consistent=y option to ensure the data consistency of the dump. For Data Pump there is no consistent parameter whereas it has FLASHBACK_SCN and FLASHBACK_TIME parameters to achieve this goal. These two parameters are mutually exclusive. In FLASHBACK_SCN you have to pass the SCN number as the argument and in FLASHBACK_TIME you have to pass timestamp value.
Example:
Checking the SCN Number before the table creation
SQL> select CURRENT_SCN, dbms_flashback.get_system_change_number from v$database;
CURRENT_SCN GET_SYSTEM_CHANGE_NUMBER
----------- ------------------------
369457983 369457983
SQL> create table emp
(
empno number(4) not null,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7, 2),
comm number(7, 2),
deptno number(2)
);
Table created.
SQL> insert into emp values (7369, 'smith', 'clerk', 7902,to_date('17-dec-1980', 'dd-mon-yyyy'), 800, null, 20);
1 row created.
SQL> commit;
Commit complete.
Checking the SCN Number after the table creation and first row inserted.
SQL> select CURRENT_SCN, dbms_flashback.get_system_change_number from v$database;
CURRENT_SCN GET_SYSTEM_CHANGE_NUMBER
----------- ------------------------
369458034 369458034
SQL> insert into emp values (7499, 'allen', 'salesman', 7698,to_date('20-feb-1981', 'dd-mon-yyyy'), 1600, 300, 30);
1 row created.
SQL> commit;
Commit complete.
Checking the SCN Number after the table creation and second row inserted. Currently we have 2 rows in the table.
SQL> select CURRENT_SCN, dbms_flashback.get_system_change_number from v$database;
CURRENT_SCN GET_SYSTEM_CHANGE_NUMBER
----------- ------------------------
369458041 369458041
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 smith clerk 7902 17-DEC-80 800 20
7499 allen salesman 7698 20-FEB-81 1600 300 30
Now I am going try with EXPDP with each SCN and see the differences below.
- First I am going try with SCN number (369457983) before creating the table. In this expdp is not exporting the table even it has 2 rows in it. It is throwing error.
expdp directory=exp_dir dumpfile=flash_test.dmp logfile=flash_test.log flashback_scn=369457983 tables=scott.emp
Export: Release 11.2.0.2.0 - Production on Mon May 14 03:14:28 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release
. . .
. . .
. . .
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/export/flash_test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 03:14:39
- Secondly I am trying with the scn number after inserting first row to the table and it will export only one row.
expdp directory=exp_dir dumpfile=flash_test.dmp logfile=flash_test.log flashback_scn=369458034 tables=scott.emp
Export: Release 11.2.0.2.0 - Production on Mon May 14 03:13:37 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release
. . .
. . .
. . .
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."EMP" 8.046 KB 1 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/export/flash_test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 03:13:53
- In last example I am going to try with the scn number after inserting 2nd row and it will export 2 rows.
expdp directory=exp_dir dumpfile=flash_test.dmp logfile=flash_test.log flashback_scn=369458041 tables=scott.emp
Export: Release 11.2.0.2.0 - Production on Mon May 14 03:15:14 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release
. . .
. . .
. . .
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."EMP" 8.093 KB 2 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/export/flash_test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 03:15:26
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment