Skip to content

Posts from the ‘Datapump’ Category

Jan 3

Data pump command import exclude

impdp system/Admin123 directory=exp_dir dumpfile=exp_dump.dmp logfile=exp_dump.log schemas=scott exclude=TABLE:\”IN \(\’EMP\’,\’DEPT\’\)\”

Jan 3

Datapump command exclude table

Datapump Command exclude table :

expdp system/Admin123 directory=exp_dir dumpfile=exp_dump.dmp logfile=exp_dump.log schemas=scott exclude=TABLE:\”IN \(\’EMP\’\)\”

 

For two table exclude

expdp system/Admin123 directory=exp_dir dumpfile=exp_dump.dmp logfile=exp_dump.log schemas=scott exclude=TABLE:\”IN \(\’EMP\’,\’DEPT\’\)\”

Apr 10

1 Comment

expdp and impdp tablespace on same database

Just a example for export tablespace and import tablespace on the same database when the tablespace existing

Step1 :

Step 2: export tabespace

Step 3: import tablespace but errors shown when it ends because of already existing objects there

Step 4: use table_exists_action=replace

Dec 10

4 Comments

Simple cronjob for datapump

Today morning I just written a small script for taking full database using datapump and after I scheduled on every one week with cron.

#!/bin/bash
#Script to perform a full database  export backup using by datapump on every wednesday
# Script Written by
# Mohamed Azar http://mohamedazar.com
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
ORACLE_SID=devcrm; export ORACLE_SID
expdp system/Admin123 directory=exportdb dumpfile=exportdb-$(date +%Y-%m-%d).dmp logfile=exportdb-$(date +%Y-%m-%d).log full=y exclude=statistics
# Send log to mail
tail -4 /u01/datapump/dumps/exportdb-$(date +%Y-%m-%d).log | mailx -s “Cronjob Oracle Export Output:SIEBELDEV” mazar@ace-ins.com

#Remove old previous dumpfile
rm -rf /u01/datapump/dumps/previous/*.*

#Find out last week dumpfiles and move to previous directory
cd /u01/datapump/dumps
chown -Rf oracle:oinstall *.dmp *.log
chmod -Rf 775 *.dmp *.log
find -mtime +6 -exec mv {} /u01/datapump/dumps/previous \;
gzip /u01/datapump/previous/*.*
gzip *.dmp *.log

Cronjob schedule :

I scheduled here , every week on wednesday at 04:00pm

[oracle@siebelpoc cronjob]$ vi cron.exportdb
0 16 * * 3 /u01/datapump/dumps/scripts/exportdb.sh

[oracle@siebelpoc cronjob]$crontab cron.exportdb

Feb 22

1 Comment

Can I Store Datapump dumpfiles in ASM diskgroup?

One of my friend asked me , Can I store Datapump dumpfile in asm diskgroup?. Yes you can. Now we can see How do we create directory and store dumpfile.

Step 1: Go To ASM Instance and Create New Directory.

C:\Documents and Settings\Administrator>set oracle_sid=+asm

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 22 15:27:13 2011

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

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter diskgroup data add directory '+DATA/dumpset';

Diskgroup altered.

Step 2:  Go to DB Instance

 Create Directory for dumpfile and logfile


Dumpfile Directory (ASM Disk)

SQL> create or replace directory dp_asm as '+DATA/dumpset';

Directory created.

Log file Directory (Local File System).

SQL> create or replace directory logfile as 'C:\azar';

Directory created.

SQL> grant read,write on directory dp_asm to system;

Grant succeeded.

SQL> grant read,write on directory logfile to system;

Grant succeeded.

Step 3: Doing Export using datapump


SQL> $expdp system/Admin123 directory=dp_asm dumpfile=testasm.dmp schemas=scott
logfile=logfile:testasm.log

Export: Release 10.2.0.1.0 - Production on Tuesday, 22 February, 2011 15:35:00

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=dp_asm dump
file=testasm.dmp schemas=scott logfile=logfile:testasm.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 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
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  +DATA/dumpset/testasm.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:36:01
SQL>

Step 4: Go to ASM Instance and Check the file created in ASM


SQL> select file_number,creation_date,bytes from v$asm_file where type='DUMPSET';

FILE_NUMBER CREATION_      BYTES
----------- --------- ----------
        283 22-FEB-11     212992

SQL>

Jan 23

14 Comments

How to export from oracle 11.2 and import into 10.2 version

I think many OTN users asked repeately this question How do i export from higher version and import into lower version.I just show here How do we do.

First : Using Datapump from Oralce 11.2 and import into 10.2 version

Source DB 11.2 Version :

Step 1: Create Directory

SQL> create or replace directory test_dir as 'D:\backupnew\dump';

Directory created.

SQL> grant read,write on directory test_dir to scott;

Grant succeeded.

SQL> conn scott/tiger@azardb
Connected.
SQL> create table testversion(version varchar2(20));

Table created.

SQL> insert into testversion values('oralce11gr2');

1 row created.

SQL> commit;

Commit complete.

Step 2: Export Table using Datapump

C:\Users\mazar>expdp scott/tiger@azardb directory=test_dir dumpfile=testver.dmp tables=testversion

Export: Release 11.2.0.1.0 - Production on Sun Jan 23 15:54:13 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@azardb directory=test_dir dumpfile=testver.dmp tables=testversion
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."TESTVERSION"                       5.031 KB       1 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  D:\BACKUPNEW\DUMP\TESTVER.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 15:54:40

 Now Go to Target DB 10.2 Version

Step 3: Create Directory for Scott  User.

SQL> create or replace directory test_dir as 'd:\newdump';

Directory created.

SQL> grant read,write on directory test_dir to scott;

Grant succeeded.

Step 3: I just Copied this TESTVER.DMP file into target DB 10.2 Directory and import it

D:\oracle\product\10.2.0\db_2\BIN>impdp scott/tiger@ace directory=test_dir dumpf
ile=testversion.dmp tables=testversion

Import: Release 10.2.0.1.0 - 64bit Production on Sunday, 23 January, 2011 16:04:
31

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39143: dump file "d:\newdump\testversion.dmp" may be an original export dump
 file

It showing error, So you need to export Data in source db 11.2 using Version parameter

Step 4:

C:\Users\mazar>expdp scott/tiger@azardb directory=test_dir dumpfile=testver.dmp tables=testversion
 version=10.2 reuse_dumpfiles=yes

Export: Release 11.2.0.1.0 - Production on Sun Jan 23 16:06:47 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@azardb directory=test_dir dumpfile=testver.dmp tables=testversion 
version=10.2 reuse_dumpfiles=yes
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."TESTVERSION"                       4.968 KB       1 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  D:\BACKUPNEW\DUMP\TESTVER.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 16:06:54

Step 5: Again I copied this dump file to target DB 10.2 directory and import it.

D:\oracle\product\10.2.0\db_2\BIN>impdp scott/tiger@ace directory=test_dir dumpf
ile=testver.dmp tables=testversion remap_tablespace=users_tbs:users

Import: Release 10.2.0.1.0 - 64bit Production on Sunday, 23 January, 2011 16:08:
37

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/********@ace directory=test_dir d
umpfile=testver.dmp tables=testversion remap_tablespace=users_tbs:users
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."TESTVERSION"                       4.968 KB       1 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 16:08:39
D:\oracle\product\10.2.0\db_2\BIN>

Now I successfully import it.

Same Like If you want Import oracle 9i version from export taking of 10g version dumpfile, you just use to export 9.2 export client of Source DB 10.2 data and then import it.

Sourcedb : azardb , Version :11.2

Unfortunately I don’t have Oracle 9i software.So i jsut used 11.2 and 10.2

D:\oracle\product\10.2.0\db_2\BIN>exp scott/tiger@azardb file=d:\newdump\testver
sion.dmp tables=test

Export: Release 10.2.0.1.0 - Production on Sun Jan 23 15:10:32 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           TEST          1 rows exported
Export terminated successfully without warnings.

Target DB : ACE Version :10.2

D:\oracle\product\10.2.0\db_2\BIN>set oracle_sid=ace

D:\oracle\product\10.2.0\db_2\BIN>imp scott/tiger@ace file=d:\newdump\testversio
n.dmp tables=test

Import: Release 10.2.0.1.0 - Production on Sun Jan 23 15:11:03 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                         "TEST"          1 rows imported
Import terminated successfully without warnings.

Dec 11

Datapump REUSE_DUMPFILES parameter

REUSE_DUMPFILES parameter is using for overwriting preexisting dump file.

It  default parameter is NO.

Data Pump Export will return an error if you specify a dump file name that already exists. The REUSE_DUMPFILES parameter allows you to override that behavior and reuse a dump file name.

This is compatabile for Oracle 11g Version, Not in Oracle 10g version.

see below Example :


C:\Users\mazar>expdp scott/tiger@azardb dumpfile=reusedump.dmp directory=data_pump_dir

Export: Release 11.2.0.1.0 - Production on Sat Dec 11 12:17:12 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@azardb dumpfile=reusedump.dmp directory=data_pump_dir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.945 KB       4 rows
. . exported "SCOTT"."EMP"                               8.578 KB      14 rows
. . exported "SCOTT"."MYTEST"                            5.429 KB       1 rows
. . exported "SCOTT"."SALGRADE"                          5.875 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\REUSEDUMP.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:18:05

C:\Users\mazar>expdp scott/tiger@azardb dumpfile=reusedump.dmp directory=data_pump_dir

Export: Release 11.2.0.1.0 - Production on Sat Dec 11 12:18:17 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "C:\app\oracle\mazar\admin\azardb\dpdump\reusedump.dmp"
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

 




C:\Users\mazar>expdp scott/tiger@azardb dumpfile=reusedump.dmp directory=data_pump_dir reuse_dumpfiles=y

Export: Release 11.2.0.1.0 - Production on Sat Dec 11 12:18:31 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@azardb dumpfile=reusedump.dmp directory=data_pump_dir reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.945 KB       4 rows
. . exported "SCOTT"."EMP"                               8.578 KB      14 rows
. . exported "SCOTT"."MYTEST"                            5.429 KB       1 rows
. . exported "SCOTT"."SALGRADE"                          5.875 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\REUSEDUMP.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:19:10

Ref :

http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_export.htm

Dec 11

4 Comments

Datapump compression parameter

Here I just like to show How compression datapump parameter working in Oracle 11g R2 ( see Below Screenshot How  size vary from others.)

Default: METADATA_ONLY

Purpose

Specifies which data to compress before writing to the dump file set

Syntax and Description

COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}
  • ALL enables compression for the entire export operation. The ALL option requires that the Oracle Advanced Compression option be enabled.
  • DATA_ONLY results in all data being written to the dump file in compressed format. The DATA_ONLY option requires that the Oracle Advanced Compression option be enabled.
  • METADATA_ONLY results in all metadata being written to the dump file in compressed format. This is the default.
  • NONE disables compression for the entire export operation.
Restrictions
  • To make full use of all these compression options, the COMPATIBLE initialization parameter must be set to at least 11.0.0.
  • The METADATA_ONLY option can be used even if the COMPATIBLE initialization parameter is set to 10.2.
  • Compression of data (using values ALL or DATA_ONLY) is valid only in the Enterprise Edition of Oracle Database 11g

For Example  , See Below Screenshot:

Compression =METADATA_ONLY


C:\Users\mazar>set oracle_sid=azardb

C:\Users\mazar>expdp scott/tiger@azardb dumpfile=compressmeta.dmp directory=data_pump_dir compression=metadata_only

Export: Release 11.2.0.1.0 - Production on Sat Dec 11 10:19:38 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@azardb dumpfile=compressmeta.dmp directory=data_pump_dir compression=metadata_only
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.945 KB       4 rows
. . exported "SCOTT"."EMP"                               8.578 KB      14 rows
. . exported "SCOTT"."MYTEST"                            5.429 KB       1 rows
. . exported "SCOTT"."SALGRADE"                          5.875 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\COMPRESSMETA.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:21:05

Compression =ALL


C:\Users\mazar>expdp scott/tiger@azardb dumpfile=compressall.dmp directory=data_pump_dir compression=all

Export: Release 11.2.0.1.0 - Production on Sat Dec 11 10:21:59 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@azardb dumpfile=compressall.dmp directory=data_pump_dir compression=all
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              4.984 KB       4 rows
. . exported "SCOTT"."EMP"                               5.625 KB      14 rows
. . exported "SCOTT"."MYTEST"                            4.789 KB       1 rows
. . exported "SCOTT"."SALGRADE"                          4.898 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\COMPRESSALL.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:22:41

Compression =DATA_ONLY


C:\Users\mazar>expdp scott/tiger@azardb dumpfile=compressdata.dmp directory=data_pump_dir compression=data_only

Export: Release 11.2.0.1.0 - Production on Sat Dec 11 10:23:23 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@azardb dumpfile=compressdata.dmp directory=data_pump_dir compression=data_only
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              4.984 KB       4 rows
. . exported "SCOTT"."EMP"                               5.625 KB      14 rows
. . exported "SCOTT"."MYTEST"                            4.789 KB       1 rows
. . exported "SCOTT"."SALGRADE"                          4.898 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\COMPRESSDATA.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:24:02

Compression =NONE

C:\Users\mazar>expdp scott/tiger@azardb dumpfile=compressnone.dmp directory=data_pump_dir compression=none

Export: Release 11.2.0.1.0 - Production on Sat Dec 11 10:24:28 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@azardb dumpfile=compressnone.dmp directory=data_pump_dir compression=none
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.945 KB       4 rows
. . exported "SCOTT"."EMP"                               8.578 KB      14 rows
. . exported "SCOTT"."MYTEST"                            5.429 KB       1 rows
. . exported "SCOTT"."SALGRADE"                          5.875 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\COMPRESSNONE.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:25:09

Screenshot, How size vary from every compression parameter.

Ref :

http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_export.htm

Dec 8

Can I rename Export Dumpfile?

One of User Asked me, Can I rename my dumpfle, Yes You can

Just see Example :


SQL> conn scott/tiger;
Connected.

SQL> create table mytest(empname varchar2(20),city varchar2(20));

Table created.

SQL> insert into mytest values('azar','riyadh');

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> grant read,write on directory data_pump_dir to scott;

Grant succeeded.

I just Do Export


SQL> $expdp scott/tiger directory=data_pump_dir dumpfile=expscott.dmp tables=mytest

Export: Release 11.2.0.1.0 - Production on Wed Dec 8 10:50:32 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=data_pump_dir dumpfile=expscott.dmp tables=mytest
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."MYTEST"                            5.429 KB       1 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\EXPSCOTT.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 10:52:25


SQL> conn scott/tiger
Connected.
SQL> drop table mytest;

Table dropped.

SQL> commit;

Commit complete.

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

I just renamed TESTSCOTT.DMP  FOR EXPSCOTT.DMP


SQL> $impdp scott/tiger directory=data_pump_dir dumpfile=testscott.dmp tables=mytest

Import: Release 11.2.0.1.0 - Production on Wed Dec 8 10:56:03 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=data_pump_dir dumpfile=testscott.dmp tables=mytest
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."MYTEST"                            5.429 KB       1 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 10:56:14

SQL> conn scott/tiger;
Connected.
SQL> select * from mytest;

EMPNAME              CITY
-------------------- --------------------
azar                 riyadh

Nov 9

4 Comments

Datapump query parameter

This post show you  about Datapump query parameter used in impdp and expdp.

For Export :

Default: none

Purpose

Enables you to filter the data that is exported by specifying a clause for a SQL SELECT statement, which is applied to all tables in the export job or to a specific table.

Syntax and Description

QUERY = [schema.][table_name:] query_clause

The query_clause is typically a WHERE clause for fine-grained row selection, but could be any SQL clause. For example, an ORDER BY clause could be used to speed up a migration from a heap-organized table to an index-organized table. If a [schema.]table_name is not supplied, the query is applied to (and must be valid for) all tables in the export job. A table-specific query overrides a query applied to all tables.

When the query is to be applied to a specific table, a colon must separate the table name from the query clause. More than one table-specific query can be specified, but only one can be specified per table. Oracle highly recommends that you place QUERY specifications in a parameter file; otherwise, you might have to use operating system-specific escape characters on the command line before each quotation mark. To specify a schema other than your own in a table-specific query, you need the EXP_FULL_DATABASE role.

Restrictions

The QUERY parameter cannot be used in conjunction with the following parameters:

  • CONTENT=METADATA_ONLY
  • ESTIMATE_ONLY
  • TRANSPORT_TABLESPACES

Example For Export :


SQL> create table empquery as select * from scott.emp;

Table created.

SQL> select * from empquery;

     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
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                         20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400       30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                       30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                      10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                        20
      7839 KING       PRESIDENT            17-NOV-81       5000                            10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                         20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Here I wrote some export commands in *.par file method”

See below open notepad and write commands here like below


tables=empquery
query=empquery:"WHERE deptno > 20"
nologfile=y
directory=data_pump_dir
dumpfile=queryme.dmp 

and save as *.par file.


C:\Users\mazar>expdp testnet/testnet parfile=C:\app\oracle\mazar\admin\azardb\dpdump\querytest.par

Export: Release 11.2.0.1.0 - Production on Tue Nov 9 14:49:16 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TESTNET"."SYS_EXPORT_TABLE_05":  testnet/******** parfile=C:\app\oracle\mazar\admin\azardb\dpdump\querytest.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TESTNET"."EMPQUERY"                        8.273 KB       6 rows
Master table "TESTNET"."SYS_EXPORT_TABLE_05" successfully loaded/unloaded
******************************************************************************
Dump file set for TESTNET.SYS_EXPORT_TABLE_05 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\QUERYME.DMP
Job "TESTNET"."SYS_EXPORT_TABLE_05" successfully completed at 14:49:40

After exported , I just dropped table and import that dump file what inside have for test purpose.


SQL> drop table empquery;

Table dropped.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\mazar>impdp testnet/testnet tables=empquery  directory=data_pump_dir dumpfile=queryme.dmp logfile=queryme.log

Import: Release 11.2.0.1.0 - Production on Tue Nov 9 14:52:26 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TESTNET"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TESTNET"."SYS_IMPORT_TABLE_01":  testnet/******** tables=empquery directory=data_pump_dir dumpfile=queryme.dmp logfile=queryme.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTNET"."EMPQUERY"                        8.273 KB       6 rows
Job "TESTNET"."SYS_IMPORT_TABLE_01" successfully completed at 14:52:32

and then connect testnet user

SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

6 rows selected.

You can see only DEPTNO=30 ROWS avilable.

For Import :

At Same Table again i was created , and select the execute command what i show in first steps

SQL> create table empquery as select * from scott.emp;

Table created.

SQL> select * from empquery;

     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
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


C:\Users\mazar>expdp testnet/testnet tables=empquery  directory=data_pump_dir dumpfile=queryexport.dmp logfile=queryexport.log

Export: Release 11.2.0.1.0 - Production on Tue Nov 9 14:55:59 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TESTNET"."SYS_EXPORT_TABLE_05":  testnet/******** tables=empquery directory=data_pump_dir dumpfile=queryexport.dmp logfile=queryexport.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TESTNET"."EMPQUERY"                        8.585 KB      14 rows
Master table "TESTNET"."SYS_EXPORT_TABLE_05" successfully loaded/unloaded
******************************************************************************
Dump file set for TESTNET.SYS_EXPORT_TABLE_05 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\QUERYEXPORT.DMP
Job "TESTNET"."SYS_EXPORT_TABLE_05" successfully completed at 14:56:20


C:\Users\mazar>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 9 14:57:59 2010

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

Enter user-name: testnet/testnet

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table empquery;

Table dropped.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Here I create *.par file like below

tables=empquery
query=empquery:"WHERE deptno <= 20"
nologfile=y
directory=data_pump_dir
dumpfile=queryexport.dmp 

and save as *.par file.

C:\Users\mazar>impdp testnet/testnet tables=empquery parfile=D:\backup\sqlloader\queryimp.par

Import: Release 11.2.0.1.0 - Production on Tue Nov 9 15:00:38 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TESTNET"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TESTNET"."SYS_IMPORT_TABLE_01":  testnet/******** tables=empquery parfile=D:\backup\sqlloader\queryimp.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTNET"."EMPQUERY"                        8.585 KB       8 out of 14 rows
Job "TESTNET"."SYS_IMPORT_TABLE_01" successfully completed at 15:00:58

C:\Users\mazar>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 9 15:01:23 2010

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

Enter user-name: testnet/testnet

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set lines 500
SQL> select * from empquery;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

8 rows selected.

SQL>
« Older Entries