COMPRESSION in datapump oracle

COMPRESSION parameter is used with EXPDP, to compress the generated dump file. NOTE – For using compression parameter with datapump, we need to have Oracle advance compression license.   COMPRESSION Reduce the size of a dump file. Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE. In this below demo, we will compare the dump […]


If we try to export a dumpfile with the name, which is already present in that directory. then we will get error like ORA-27038: created file already exists ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31641: unable to create dump file “/export/home/oracle/dbaclass_estim.dmp“ ORA-27038: created file already exists Additional information: 1 So if the […]

SAMPLE parameter in EXPDP to export subset of data

Let’s say we have a very big table, and for testing purpose, we need only a subset of data i.e to let’s say 10 percent of data from that table, then SAMPLE parameter can be used in EXPDP. We will explain its use in below demo. SAMPLE Percentage of data to be exported. DEMO:



ESTIMATE_ONLY parameter can be used in EXPDP command, to estimate the disk space required for the export job, without doing the actual export. So when we are not sure what will be the export dump size, then better to use this option first, before generating the export dump. By default ESTIMATE_OPTION=N DEMO: PARFILE WITH ESTIMATE […]


We can use the flashback parameter ( FLASHBACK_SCN or FLASHBACK_TIMESTAMP) in expdp to take a consistent backup of the table as of a specific point in time. Flashback method depends upon undo data. So if we are trying to take an export of the table with the flashback for which data is not available in […]

CONTENT parameter in datapump

CONTENT parameter is used in expdp or impdp, to load/unload data only, metadata only or both. CONTENT Specifies data to unload. Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.   CONTENT=ALL is the default value CONTENT=METADATA_ONLY: It will take export only the metadata, i.e skeleton/DDL on the object/schema. It won’t export any of the data/rows. […]

Shell script to delete old archives using RMAN

If the requirement is to delete archive log backups automatically (without taking backup), then below shell script can be configured in crontab. prepare the shell script. cat #!/bin/bash export ORACLE_HOME=/oracle/app/oracle/product/ export ORACLE_SID=PARIS12C export PATH=$ORACLE_HOME/bin:$PATH delBackup () { rman log=/home/oracle/arch_del.log << EOF connect target / DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE ‘sysdate-1’; CROSSCHECK ARCHIVELOG ALL; […]

Query clause in oracle datapump expdp

             QUERY clause can be used in expdp or impdp to export/import subset of the data or data with specific conditions. DEMO: Export dump of a table from emp_tab WHERE created > sysdate -40 . Filter can be added on any column depending upon the requirement. SQL> select count(*) from […]


DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS is a very useful parameter mostly used with table_exists_action=APPEND . In general, if you are appending data ( table_exists_action=APPEND) to an existing table with a unique constraint and if the new rows violating the unique constraint, then the import job will fail( even if only one row violates the constraint) DATA_OPTION=SKIP_CONSTRAINT_ERRORS parameter will help […]

SQLFILE parameter in impdp for generating DDL

It can be used, only with IMPDP. This helps in generating the DDLs from a dumpfile. Suppose I have dump file of table DBACLASS.DEP_TAB . If you need the DDL of the table, then use SQLFILE with IMPDP command as below. This will not do the actual import. It will just dump the metadata (DDL) […]

Page 1 of 512345
Skip to toolbar