Oracle Data pump import stuck Processing object type DATABASE_EXPORT/SCHEMA

You might come across issues when importing data using DataPump. I have faced the issue several times while importing Domain Index and other objects. You can encounter DOMAIN INDEX issue  related to CTXSYS schema. Note that data import may complete quickly but import gets stuck when creating DOMAIN INDEXES or INDEX and it may still run even after few days. 

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/CODE_BASE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX

Pls note that domain or normal index rebuild may take time based upon the index size during the import and also other reasons may effect the operation. Here are few reasons that you need to look …

1. Did you gather Stats before the import – If yes, exclude STATS and manually gather stats after import completed.

2. Make sure all tablespaces have enough space – Sometimes imports get stuck due to not having enough space

3. Make sure you have enough STREAMS_POOL_SIZE - The value should be at least 512M or more

If you are still facing the issue then you need to run below commands to identify the SQL Statement A Data Pump Process Is Executing

a) Find out the the datapump import job running or not using below SQL

SQL> select owner_name, job_name, operation, job_mode, from dba_datapump_jobs where state='EXECUTING' ;

You can run below command to identify the session used by datapump job.

SQL> select owner_name, job_name, session_type from dba_datapump_sessions;

b)  If the job is still running on step 4 then Identify The Current SQL Statement A Data Pump Process Is Executing (refer Oracle support Doc ID 1528301.1) and identify the object

After you have tried all the options if the index creating is still taking time then the last option to EXCLUDE the object taking time and manually create after import operation is successful…

You can get the object DDL command using below query

SQL> select dbms_metadata.get_ddl('INDEX','<Index_Name>','<Schema_Name>') from dual;

or 

SQL> select dbms_metadata.get_ddl('TABLE','<Table_Name>','<Schema_Name') from dual;

You can exclude the index by adding below clause in the import command…

Exclude= INDEX:"LIKE ‘Index_name _that got stuck_%'"

After import is successful you can create the object manually in my case it is index…

To monitor Data Pump jobs query views DBA_DATAPUMP_JOBS AND DBA_DATAPUMP_SESSIONS. You can also query V$SESSION_LONGOPS to see the progress of data pump job.

The below script very useful to identify database role, version, registry status, patch level and Invalid objects. I would highly suggest to run this script for any maintenance activity you perform on a Database.

SET PAGESIZE 2000
SET LINESIZE 500
COL OBJECT_NAME FORMAT A30
COL OBJECT_TYPE FORMAT A30
COL COMP_ID FORMAT A10

COL COMP_NAME FORMAT A45  
COL OWNER FORMAT A15
COL STATUS FORMAT A10

COL VERSION FORMAT A10  
/* Database Role and Version */
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;
select * from V$version where banner like 'Oracle Database%';
/* Database Component Registry status */
select comp_id, comp_name, status, version from dba_registry;
/* Database patch Level*/
select * from dba_registry_history;
/* INVALID objects in the DB count, by type & in detail */
select count(*) "INVALID Objects Count" from dba_objects where status !='VALID';
select owner, object_type, count(*) from dba_objects where status !='VALID' group by owner, object_type order by owner, object_type;
select owner, object_type, object_name, status from dba_objects where status !='VALID' order by owner, object_type, object_name;

Hope this helps

Regards,

http://oracleracexpert.com, Oracle ACE