Online DBA

<<Back to Oracle DATAPUMP Main Page DataPump Export (EXPDP) Error ORA-39095 Dump File Space Has Been Exhausted ORA - 39095 : " dump file space has been exhausted. Unable to allocate 8192 bytes" job system.sys_export_full_02 stops due to fatal error. cause: Not enough number of dump files defined expdp can create maximum 99 dumpfiles so if you are exporting very large database ensure to define filesize big enough so the total export can fit in <=99 dumpfiles The issue can also occur if you specify the dumpfiles manually for example if your parfile looks  like this full=Y directory=<DIRECTORY_NAME> dumpfile=full_1.dmp,full2_2.dmp,full_3.dmp filesize=10 if export did not finish in 3 dumpfiles and need to created 4th one you will receive the error ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes Job "SYSTEM"."sys_export_full_02" stopped due to fatal error at Wed Oct 7 21:13:55 2020 elapsed 0 10:09:02 Job sys_export_fu...

<<Back to Oracle DATAPUMP Main Page How to find if the export job is running or Hanging  SET PAUSE ON SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300 col MODULE for a30 col EVENT for a50 col SQL_ID for a15 col STATUS for a10 SELECT   v.status,   v.SID,   v.serial#,   io.block_changes,   event, module,   v.sql_id FROM   v$sess_io io,   v$session v WHERE   io.SID=v.SID AND   v.saddr IN (SELECT saddr FROM dba_datapump_sessions)  ORDER BY   io.BLOCK_CHANGES / Observe the BLOCK_CHANGES column if the value keeps increasing , your  job is running and not hanging

<<Back to Oracle DATAPUMP Main Page Is there any limit on number of files expdp can create Yes there is a limit.  The total number of dump files that can be created by expdp is  99. so if you specify dumpfile parameter like below and your export did not finish within 99 dumpfiles export will stop therefore if you are exporting big database specify bigger filesize  dumpfile=exp_full_%U.dmp 

<<Back to Oracle DATAPUMP Main Page Converting Partition Table to non Partitioned using impdp PARTITION_OPTIONS is an impdp parameter used to convert partitioned tables in non-partitioned one while importing.  Allowed values for PARTITION_OPTIONS parameter are NONE – This is the default value. Table imported as its in export. DEPARTITION – Promotes each partition or subpartition to a new individual table. The default name of the new table will be the concatenation of the table and partition name or the table and subpartition name, as appropriate. MERGE – Combines all partitions and subpartitions into one table. $ cat imp.par DIRECTORY=EXP DUMPFILE=EXP.DMP LOGFILE=IMP.LOG PARTITION_OPTIONS=MERGE $impdp system@PDB02 parfile=imp.par

<<Back to Oracle DATAPUMP Main Page expdp fails with ORA-01690: sort area size too small expdp As per Metalink note Doc ID 1911968.1 issue has been fixed in 12.1 and above however I had the issue in 12.1.0.2 version while performing full database export using expdp utility. Tried to increase the pga_aggregate_target from 2G to 10G but no luck.  Solution1: Recommended  Changed the parameter file to have schema based export instead of full. Before userid= system directory=dump dumpfile=dump_%U logfile=exp.log full=y parallel=3 flashback_scn=291638761 AFTER userid= system directory=dump dumpfile=dump_%U logfile=exp.log schemas=TEST1, TEST2 parallel=3 flashback_scn=291638761 Solution2: use traditional exp utility instead of expdp

<<Back to Oracle DATAPUMP Main Page While performing import encountering error  ORA-39142 ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39142: incompatible version number 4.2 in dump file "/u01/app/oracle/dump/TEST.dmp"  Cause: The issue is caused due to bug as mentioned in Metalink Doc ID 2422236.1. The source database is on higher patch level that target Workaround : Use parameter VERSION=12.1 while performing the export.

<<Back to Oracle DATAPUMP Main Page How to export schema or table statistics in oracle Step1> create an staging table to hold the stats BEGIN   DBMS_STATS.CREATE_STAT_TABLE (     ownname => 'TEST' ,   stattab => 'SCHEMA_STAT_TEST' ,   tblspace => 'USERS' ); END; / PL/SQL procedure successfully completed. Step2> Verify the Staging Table Details SQL> select owner,table_name from dba_tables where table_name='SCHEMA_STAT_TEST' OWNER                          TABLE_NAME ------------------------------ ---------------------------------------- TEST                       SCHEMA_STAT_TEST 1 row selected.  SQL> select count(*) from TEST.SCHEMA_STAT_TEST;   COUNT(*) ----------...

<<Back to Oracle DATAPUMP Main Page How to Export Database from 12c for 11g Version Using EXPDP VERSION Parameter Syntax and Description VERSION=[COMPATIBLE | LATEST | version_string] Specifies the version of database objects to be exported. Only database objects and attributes that are compatible with the specified release will be exported. This can be used to create a dump file set that is compatible with a previous release of Oracle Database. • COMPATIBLE - This is the default value. The version of the metadata corresponds to the database compatibility level as specified on the COMPATIBLE initialization parameter. Database compatibility must be set to 9.2 or later. • LATEST - The version of the metadata and resulting SQL DDL corresponds to the database release regardless of its compatibility level. • version_string - A specific database release (for example, 11.2.0). In Oracle Database 11g, this value cannot be lower than 9.2. $ cat exp.par DIRECTORY=EXP DUMPFILE=EX...

<<Back to Oracle DATAPUMP Main Page How to Export Tablespace using expdp TABLESPACES=<comma seprated list of tablespace> $ cat exp.par DIRECTORY=EXP DUMPFILE=EXP.DMP LOGFILE=EXP.LOG TABLESPACES=TEST REUSE_DUMPFILES=Y FLASHBACK_TIME=SYSTIMESTAMP $ expdp test@PDB01 parfile=exp.par How to Import Individual Tablespace using impdp  $ cat imp.par DIRECTORY=EXP DUMPFILE=EXP.DMP LOGFILE=IMP.LOG TABLESPACES=TEST $impdp system@PDB02 parfile=imp.par During the following import situations, Data Pump automatically creates the tablespaces into which the data will be imported: • The import is being done in FULL or TRANSPORT_TABLESPACES mode • The import is being done in table mode with TRANSPORTABLE=ALWAYS In all other cases, the tablespaces for the selected objects must already exist.

<<Back to Oracle DATAPUMP Main Page ORA-39173: Encrypted data has been stored unencrypted in dump file set $ expdp test@PDB01 parfile=exp.par Export: Release 12.2.0.1.0 - Production on Wed Nov 28 11:30:14 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "TEST"."SYS_EXPORT_TABLESPACE_01":  test/********@PDB01 parfile=exp.par Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT . . exported "T...

<<Back to Oracle DATAPUMP Main Page EXPDP SOURCE_EDITION Default : the default database edition on the system Purpose Specifies the database edition from which objects will be exported. Syntax and Description SOURCE_EDITION=edition_name This parameter is only useful if there are two or more versions of the same versionable objects in the database. Check Out here  Edition Based Redefinition in Oracle Database 11g Release 2 for an overview of Editioning in oracle Environment Prepration for expdp source_edition demonstration SQL>  grant create any edition to test; Grant succeeded. SQL>select USERNAME,EDITIONS_ENABLED from dba_users where USERNAME='TEST'; USERNAME             E -------------------- - TEST                 N SQL>  create edition my_exp_edition ; Edition created. SQL> create edition my_exp_e...

<<Back to Oracle DATAPUMP Main Page Using EXPDP SAMPLE Parameter Purpose Allows you to specify a percentage of the data rows to be sampled and unloaded from the database. Syntax and Description SAMPLE=[[schema_name.]table_name:]sample_percent This parameter allows you to export subsets of data by specifying the percentage of data to be sampled and exported. If you specify a schema, then you must also specify a table. However, you can specify a table without specifying a schema; the current user will be assumed. If no table is specified, then the sample_percent value applies to the entire export job. Examples: In the following example, 50% of the TEST.EMP table will be exported together with all other objects of TEST SCHEMA $ cat exp.par DIRECTORY=EXP DUMPFILE=EXP.DMP LOGFILE=EXP.LOG SAMPLE=TEST.EMP:50 REUSE_DUMPFILES=Y $ expdp test@PDB01 parfile=exp.par Export: Release 12.2.0.1.0 - Production on Tue Nov 13 09:31:51 2018 Copyright (c) 1982, 2017, Oracle and/or its aff...

<<Back to Oracle DATAPUMP Main Page Using Query Parameter with Datapump expdp or impdp Purpose Allows you to specify a query clause that is used to filter the data that gets exported or imported Syntax and Description QUERY = [schema.][table_name:] query_clause If the NETWORK_LINK parameter is specified along with the QUERY parameter, then any objects specified in the query_clause that are on the remote (source) node must be explicitly qualified with the NETWORK_LINK value. Otherwise, Data Pump assumes that the object is on the local (target) node. If it is not, then an error is returned and the import of the table from the remote (source) system fails. For Example QUERY=(hr.employees:"WHERE last_name IN(SELECT last_name FROM hr.employees@ dblink1 )") The QUERY parameter cannot be used with the following parameters: – CONTENT=METADATA_ONLY – ESTIMATE_ONLY – TRANSPORT_TABLESPACES Expdp QUERY $ cat exp.par DIRECTORY=EXP DUMPFILE=EXP.DMP LOGFILE=EXP.LOG TABLES...

<<Back to Oracle DATAPUMP Main Page How to Use EXPDP/IMPDP NETWORK_LINK Parameter SOURCE : TEST1T HOST : test1.oracle.com TARGET : TEST2T HOST : test2.oracle.com Objective : Trigger the Export of Database schema TEST_EXP@TEST1T from test2.oracle.com and write the export dumps on test2.oracle.com Preparation: Step1 : Add the source database TNS entry in target database tnsnames.ora TEST1T =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = test1.oracle.com )(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = TEST1T)     )   ) Step2 : Create private database link in target database pointing to sourced database SQL>create database link exp_test connect to TEST_EXP identified by xxxx using 'TEST1T'; Step3 : Create directories and grant privilege to the user (supposed to initiate export) in target database SQL>create directo...