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) […]


TABLE_EXISTS_ACTION parameter is required while importing table, which is already present in the database. We can put required value, according to our requirement.   TABLE_EXISTS_ACTION Action to take if imported object already exists. Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.   TABLE_EXISTS_ACTION=APPEND: With this option, while importing the table, if the table exists in […]

ENCRYPTION in expdp impdp

We can enable password based encryption for expdp dumps, by using different encryption related parameters in expdp/impdp command. ENCRYPTION RELATED PARAMETERS: ENCRYPTION Encrypt part or all of a dump file. Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE. ENCRYPTION_ALGORITHM Specify how encryption should be done. Valid keyword values are: [AES128], AES192 and AES256. […]

Cluster parameter in oracle RAC database

In a RAC database, if you are taking export with the parallel option and the datapump directory is not shared between the nodes, then export might fail with below error Because the remote node is unable to write the dumps to that directory. ORA-31617: unable to open dump file “/dumparea/TEST/asset_75.dmp” for write ORA-19505: failed to […]

Datapump export to ASM diskgroup (EXPDP)

It is tvery common to export dump file to the standard file system(Mount points). However, we can export the dump files to ASM diskgroup also. In this article, we will show the steps for this . DEMO 1.Create a directory pointing to ASM diskgroup( for dump files)

2.Create a directory pointing to normal filesystem […]

How to do import using network_link in oracle

By using network_link parameter, we can directly import the data from source database to target database without generating the dump file. This will need a database link between source and target db . Lets’ see the below example. DEMO: Objective – Export table dbatest.EMP_TAB from SOURCEDB and import in TARGETDB using network link. 1. Add […]

How to run expdp in pluggable database(PDB)

In the multitenant database, For getting an export dump from the pluggable database, we need a follow a bit different process. DEMO: PLUGGABLE DATABASE(PDB) – PRE1 SCHEMA_NAME – DBACLASS ( this schema/user is present in PDB PRE1) 1. Make sure PDB service is registered in the listener.

2. Add the entry in tnsnames.ora file […]

Duplicate a database from RMAN TAPE BACKUP

In this article, we will duplicate the database(with new db_name) from tape backup, without connecting to source database. We will only connect to the recovery catalog and auxiliary instance. SOURCE DB – PRODDB AUXILIARY DB – TESTDB CATALOG DB – ORACAT I.e we will duplicat the database with db_name TESTDB from rman tape backup of […]

How to change flash recovery area location

Below are the steps for changing the flash recovery area(FRA destination). 1. Check the current FRA destination.

2. Change db_recovery_file_dest parameter.

From onwards new files will be created in this new location. However, the flashback logs will be created under the old location. For the FLASHBACK logfiles to be able to pick up […]

Page 1 of 41234
Skip to toolbar