3

We are using dbms_datapump to copy data between different servers or to setup new schemas quickly.

However importing statistics usually takes ages and has the additional disadvantage that schema statistics are locked after the import.

So I'm trying to find a way to skip importing the statistics.

According to Metalink Doc ID 1392981.1 this should be possible using:

dbms_datapump.metadata_filter(handle => l_job_handle, 
      name => 'EXCLUDE_PATH_LIST', 
      value => '''STATISTICS''');

However when I try that, I get an "ORA-39001: invalid argument value" error.

I also tried various other formats found in different places:

dbms_datapump.metadata_filter(handle => l_job_handle, 
      name => 'EXCLUDE_PATH_LIST', 
      value => 'STATISTICS');

dbms_datapump.metadata_filter(handle => l_job_handle, 
       name => 'EXCLUDE_PATH_EXPR', 
       value => 'like ''%/TABLE/STATISTICS/TABLE_STATISTICS''');

dbms_datapump.metadata_filter(handle => l_job_handle, 
       name => 'EXCLUDE_PATH_EXPR', 
       value => 'like ''%STATISTICS%''');

But all of them return with an ORA-39001.

The version I'm using is:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Operating system is a Windows Server, but this also occurs on a Linux installation.

Any ideas how I can skip the statistics during import (or export) through the DBMS_DATAPUMP API?

5

You need to use:

DBMS_DATAPUMP.METADATA_FILTER(l_job_handle,
                              'EXCLUDE_PATH_EXPR',
                              'IN (''STATISTICS'')');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.