Home » RDBMS Server » Server Utilities » data pump error (11g)
data pump error [message #620821] Wed, 06 August 2014 07:39 Go to next message
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

Re: data pump error [message #620822 is a reply to message #620821] Wed, 06 August 2014 07:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3000
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.
Re: data pump error [message #620823 is a reply to message #620821] Wed, 06 August 2014 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 67464
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Re: data pump error [message #620826 is a reply to message #620825] Wed, 06 August 2014 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67464
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

See my post, just before yours.

Re: data pump error [message #620827 is a reply to message #620826] Wed, 06 August 2014 08:24 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67464
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: data pump error [message #620837 is a reply to message #620835] Wed, 06 August 2014 09:55 Go to previous messageGo to next message
andrewscharles89
Messages: 25
Registered: July 2014
Junior Member
How to gather index stats alone for complete schema
Re: data pump error [message #620840 is a reply to message #620837] Wed, 06 August 2014 10:10 Go to previous messageGo to next message
BlackSwan
Messages: 26747
Registered: January 2009
Location: SoCal
Senior Member
http://www.oracle.com/pls/db121/search?word=DBMS_Stats
Re: data pump error [message #620841 is a reply to message #620837] Wed, 06 August 2014 10:11 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
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.


Re: data pump error [message #620970 is a reply to message #620841] Thu, 07 August 2014 10:06 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67464
Registered: March 2007
Location: Nanterre, France, http://...
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                        

Re: data pump error [message #620981 is a reply to message #620979] Thu, 07 August 2014 10:39 Go to previous messageGo to next message
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
Re: data pump error [message #620997 is a reply to message #620981] Thu, 07 August 2014 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 67464
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html
Scroll down to "Comparing the Contents of Two Tables" section.

Re: data pump error [message #621105 is a reply to message #620997] Fri, 08 August 2014 22:19 Go to previous message
andrewscharles89
Messages: 25
Registered: July 2014
Junior Member
use dblinks to compare the stats across database.
Previous Topic: Expdp taking more time to dump the file
Next Topic: how to access sqlldr on network
Goto Forum:
  


Current Time: Sun Oct 25 06:34:55 CDT 2020