Task is to export Range-wise data from a table using Data-Pump in 11gr2 on Windows.
- Oracle Provides Query Parameter in Datapump to export only selected data from the whole table.
1. First we need to export selected data.
2. Then export table structure.
3. Import .dmpfile which generated on first step.
4. ORDATO is the date column.
5. Before Exporting Count the total rows in a table.
SQL> select count(*) from otorders;
COUNT(*)
-----------------
682701
D:\app\oracle\query_exp> expdp dumpfile=query.dmp directory=query_exp logfile=queryexp.log tables=hmis.otorders query=\"where ORDATO between to_date('12
-NOV-13','dd-mm-yy') and to_date ('19-jan-14','dd-mm-yy')\" content=data_only
Export: Release 11.2.0.3.0 - Production on Thu Aug 13 15:40:04 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_04": system/******** dumpfile=query.dmp directory=query_exp logfile=queryexp.log tables=hmis.otorders query="where
ORDATO between to_date('12-NOV-13','dd-mm-yy') and to_date ('19-jan-14','dd-mm-yy')" content=data_only
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
. . exported "HMIS"."OTORDERS" 1.602 MB 8068 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_04 is:
D:\APP\ORACLE\QUERY_EXP\QUERY.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_04" successfully completed at 15:40:12
===== xxxx =====
Exporting Table Structure:-
D:\app\oracle\query_exp> expdp dumpfile=metadata.dmp directory=query_exp content=metadata_only tables=hmis.otorders log=metadata.log
Export: Release 11.2.0.3.0 - Production on Thu Aug 13 16:02:49 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=metadata.log" Location: Command Line, Replaced with: "logfile=metadata.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYSTEM"."SYS_EXPORT_TABLE_04": system/******** dumpfile=metadata.dmp directory=query_exp content=metadata_only tables=hmis.otorders logfile=
metadata.log reuse_dumpfiles=true
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYSTEM"."SYS_EXPORT_TABLE_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_04 is:
D:\APP\ORACLE\QUERY_EXP\METADATA.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_04" successfully completed at 16:04:29
#################################################################################
Import Table Structure on Target :-
D:\app\oracle\impquery> impdp dumpfile=METADATA.DMP directory=impquery remap_schema=hmis:sam
Import: Release 11.2.0.3.0 - Production on Thu Aug 13 16:39:29 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=METADATA.DMP directory=impquery remap_schema=hmis:sam
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 16:39:38
D:\app\oracle\impquery> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 13 16:39:54 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn sam/s
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP TABLE
OTORDERS TABLE
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
- Here you can observe only table structure is exported without data that's why its showing here '0' records.
SQL> select count(*) from otorders;
COUNT(*)
----------
0
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
- Now import the data using DATA_ONLY option.
D:\app\oracle\impquery> impdp dumpfile=QUERY.DMP directory=impquery log=contentdata.log content=DATA_ONLY remap_schema=hmis:sam
Import: Release 11.2.0.3.0 - Production on Thu Aug 13 16:52:46 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=contentdata.log" Location: Command Line, Replaced with: "logfile=contentdata.log"
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=QUERY.DMP directory=impquery logfile=contentdata.log content=DATA_ONLY remap_schema=h
mis:sam
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SAM"."OTORDERS" 1.602 MB 8068 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 16:52:54
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Now Check the Exported Range Wise rows.
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
D:\app\oracle\impquery> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 13 16:53:09 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
- After Importing Count the total rows in a table.
SQL> select count(*) from otorders;
COUNT(*)
-----------------
8068
Happy Learning...
No comments:
Post a Comment