OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » data pump error (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

data pump error [message #620821]

Wed, 06 August 2014 07:39

andrewscharles89
Messages: 25
Registered: July 2014

Junior Member

ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-04030: out of process memory when trying to allocate 4088 bytes (PLS CGA hp,pdzgM64_New_Link) Failing sql is:
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   NV VARCHAR2(1);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';  stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:1
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-04030: out of process memory when trying to allocate 4088 bytes (PLS CGA hp,pdzgM64_New_Link) Failing sql is:
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   NV VARCHAR2(1);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';  stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:1

the error seems to be with PGA memory i guess.the question is how to reimport only the errored out data.how to gather index stats alone for complete schema

Report message to a moderator

Re: data pump error [message #620822 is a reply to message #620821]

Wed, 06 August 2014 07:49

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

Most likely you are running into known bug when trying to import large statistics. Workaround would be EXCLUDE=STATISTICS.

SY.

Report message to a moderator

Re: data pump error [message #620823 is a reply to message #620821]

Wed, 06 August 2014 07:51

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use dbms_gather_stats package.
Or, if you really want to use data pump, use INCLUDE=INDEX_STATISTICS and other INCLUDE's to list all the indexes that were in error.

[Updated on: Wed, 06 August 2014 08:10]

Report message to a moderator

Re: data pump error [message #620825 is a reply to message #620823]

Wed, 06 August 2014 08:04

andrewscharles89
Messages: 25
Registered: July 2014

Junior Member

why to import again?import was already done.only statistics needs to be reimported.If we exclude statistics,then we will only be duplicating

Report message to a moderator

Re: data pump error [message #620826 is a reply to message #620825]

Wed, 06 August 2014 08:09

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

See my post, just before yours.

Report message to a moderator

Re: data pump error [message #620827 is a reply to message #620826]

Wed, 06 August 2014 08:24

andrewscharles89
Messages: 25
Registered: July 2014

Junior Member

thanks.how do we know which indexes failed so we can include them in the parameter,impdp_stats table is a temp table created by impdp

[Updated on: Wed, 06 August 2014 08:51]

Report message to a moderator

Re: data pump error [message #620835 is a reply to message #620827]

Wed, 06 August 2014 09:43

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In the log you posted an abstract.
You can also query the dictionary, DBA_INDEXES, with NULL or not the current date in LAST_ANALYZED column.

Report message to a moderator

Re: data pump error [message #620837 is a reply to message #620835]

Wed, 06 August 2014 09:55

andrewscharles89
Messages: 25
Registered: July 2014

Junior Member

How to gather index stats alone for complete schema

Report message to a moderator

Re: data pump error [message #620840 is a reply to message #620837]

Wed, 06 August 2014 10:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.oracle.com/pls/db121/search?word=DBMS_Stats

Report message to a moderator

Re: data pump error [message #620841 is a reply to message #620837]

Wed, 06 August 2014 10:11

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Michel already told you.

Michel Cadot wrote on Wed, 06 August 2014 18:21

Use dbms_gather_stats package.
Or, if you really want to use data pump, use INCLUDE=INDEX_STATISTICS and other INCLUDE's to list all the indexes that were in error.

Report message to a moderator

Re: data pump error [message #620970 is a reply to message #620841]

Thu, 07 August 2014 10:06

andrewscharles89
Messages: 25
Registered: July 2014

Junior Member

How to compare index statistics between two databases?

[Updated on: Thu, 07 August 2014 10:07]

Report message to a moderator

Re: data pump error [message #620979 is a reply to message #620970]

Thu, 07 August 2014 10:35

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

For instance:

SQL> desc DBA_IND_STATISTICS
 Name                               
 -----------------------------------
 OWNER                              
 INDEX_NAME                         
 TABLE_OWNER                        
 TABLE_NAME                         
 PARTITION_NAME                     
 PARTITION_POSITION                 
 SUBPARTITION_NAME                  
 SUBPARTITION_POSITION              
 OBJECT_TYPE                        
 BLEVEL                             
 LEAF_BLOCKS                        
 DISTINCT_KEYS                      
 AVG_LEAF_BLOCKS_PER_KEY            
 AVG_DATA_BLOCKS_PER_KEY            
 CLUSTERING_FACTOR                  
 NUM_ROWS                           
 AVG_CACHED_BLOCKS                  
 AVG_CACHE_HIT_RATIO                
 SAMPLE_SIZE                        
 LAST_ANALYZED                      
 GLOBAL_STATS                       
 USER_STATS                         
 STATTYPE_LOCKED                    
 STALE_STATS

Report message to a moderator

Re: data pump error [message #620981 is a reply to message #620979]

Thu, 07 August 2014 10:39

andrewscharles89
Messages: 25
Registered: July 2014

Junior Member

recently did a expdp/imdp from prod to test including statistics.Now i would like to compare the statistics(index) between prod and test.so that the stats are correct

select index_name,index_type,num_rows,distinct_keys,avg_leaf_blocks_per_key,clustering_factor ,sample_size,last_analyzed from dba_indexes where owner=' ' AND INDEX_NAME=' ';

do it need to compare individually for all indexes this way or any other solution

Report message to a moderator