Shulou(Shulou.com)06/01 Report--

Experimental environment

Operating system: CentOS 7.1

Database: Oracle 11.2.0.4

When exporting data using Oracle Data Pump after 10g, the query parameter is provided in expdp, which can be used when you need to export part of the data in the table conditionally, just like the where condition in the select statement. Flashback_scn and flashback_time parameters are also provided to specify the time point for export. In this case, oracle uses flashback query query to export scn data, and flashback query uses undo, so there is no need to turn on the flashback database feature. That is, as long as the undo information is not overwritten, export actions based on flashback_scn and flashback_time can still be performed even if the database is restarted.

-- create a test table

SYS@seiang11g > create table seiang.test1 (id number,name varchar2 (20)) tablespace seiang

Table created.

SYS@seiang11g > create table seiang.test2 (id number,time date) tablespace seiang

Table created.

SYS@seiang11g > select owner,table_name,tablespace_name from dba_tables where owner='SEIANG'

OWNER TABLE_NAME TABLESPACE_NAME

-

SEIANG TEST2 SEIANG

SEIANG TEST1 SEIANG

-- insert test data

SYS@seiang11g > insert into seiang.test1 select level,lpad (level,20,'*') from dual connect by level commit

Commit complete.

SYS@seiang11g > insert into seiang.test2 select level,sysdate-50+level from dual connect by level commit

Commit complete.

-- create a directory

SYS@seiang11g > create directory dumpdir as'/ u01qqappAccord expandable impacted dump'

Directory created.

-- the test uses query export, using parameter file export and single statement export respectively

1. Export using parameter file (query condition does not need to be escaped with'\'in parfile)

[oracle@seiang11g ~] $vim / u01/app/oracle/exp_imp_dump/parfile_expdp.par

QUERY=seiang.test1: "where idselect dbms_flashback.get_system_change_number from dual

GET_SYSTEM_CHANGE_NUMBER

-

1676032 (record the first SCN)

-- insert 30 test data again

SYS@seiang11g > insert into seiang.test1 select level,lpad (level,20,'#') from dual connect by level commit

Commit complete.

SYS@seiang11g > insert into seiang.test2 select level,sysdate-50+level from dual connect by level commit

Commit complete.

SYS@seiang11g > select count (*) from seiang.test1

COUNT (*)

-

one hundred and thirty

SYS@seiang11g > select count (*) from seiang.test2

COUNT (*)

-

one hundred and thirty

SYS@seiang11g > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@seiang11g > startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size 2252824 bytes

Variable Size 822087656 bytes

Database Buffers 419430400 bytes

Redo Buffers 8892416 bytes

Database mounted.

Database opened.

-- check the SCN number (2nd)

SYS@seiang11g > select current_scn from v$database

CURRENT_SCN

-

1676913 (record the second SCN)

-- insert 10 test data again

SYS@seiang11g > insert into seiang.test1 select level,lpad (level,20,'@') from dual connect by level commit

Commit complete.

SYS@seiang11g > insert into seiang.test2 select level,sysdate-50+level from dual connect by level commit

Commit complete.

SYS@seiang11g > select count (*) from seiang.test1

COUNT (*)

-

one hundred and forty

SYS@seiang11g > select count (*) from seiang.test2

COUNT (*)

-

one hundred and forty

-- check the SCN number (3rd)

SYS@seiang11g > select current_scn from v$database

CURRENT_SCN

-

1677000 (record the third SCN)

-- Test query and flashback_scn (the first SCN) export

[oracle@seiang11g ~] $expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1_1.dmp tables=seiang.test1 query=seiang.test1:\ "where id\ 10000 AND ku$.cust_id = c.cust_id)"'

If, as in the following query, KU$ is not used for a table alias, then the result will be that all rows are unloaded:

QUERY='sales: "WHERE EXISTS (SELECT cust_id FROM customers c WHERE cust_credit_limit > 10000 AND cust_id = c.cust_id)"'

The maximum length allowed for a QUERY string is 4000 bytes including quotation marks, which means that the actual maximum length allowed is 3998 bytes.

Example

The following is an example of using the QUERY parameter:

> expdp hr PARFILE=emp_query.par

The contents of the emp_query.par file are as follows:

QUERY=employees: "WHERE department_id > 10 AND salary > 10000" NOLOGFILE=YES DIRECTORY=dpump_dir1 DUMPFILE=exp1.dmp

This example unloads all tables in the hr schema, but only the rows that fit the query expression. In this case, all rows in all tables (except employees) in the hrschema will be unloaded. For the employees table, only rows that meet the query criteria are unloaded.

Oracle uses data pump (expdp/impdp) to implement migration

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.