5 Replies Latest reply: Apr 29, 2013 1:08 AM by JP88 RSS

    DBMS_DATAPUMP exclude

    JP88
      Hi ,

      How to exclude the index,synomys,grants,statistics while doing import using DBMS_DATAPUMP.

      Could anyone pls give me the syntax to exculde those things .

      Thanks
      JP
        • 1. Re: DBMS_DATAPUMP exclude
          Dean Gagne-Oracle
          dbms_datapump.METADATA_FILTER(
          handle = 93,
          name = EXCLUDE_PATH_LIST,
          value = 'INDEX','SYNONYMS','GRANTS','STATISTICS',
          object_path = NULL,
          object_type = NULL);

          Fill in your handle.

          Dean
          • 2. Re: DBMS_DATAPUMP exclude
            DK2010
            Hi,

            Or you can directly add the exclude command in the expdp

            like
            expdp \'/ as sysdba\' directory=<DIR_NAME> dumpfile=<dumpfile_name>.dmp logfile=<logfile_name>.log exclude=GRANT,INDEX,..
            Hi

            i missed the OP comment DBMS_DATAPUMP and i post it, not able to remove, please ignore this ..

            Edited by: DK2010 on Apr 25, 2013 5:49 AM
            • 3. Re: DBMS_DATAPUMP exclude
              JP88
              Hi Dean,

              Before adding "dbms_datapump.METADATA_FILTER" import ran sucessfully but after adding dbms_datapump.METADATA_FILTER.

              I am receving follwing error while using.Could please help me to overcome this error

              ORA-39001: invalid argument value
              ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
              ORA-06512: at "SYS.DBMS_DATAPUMP", line 3507
              ORA-06512: at "SYS.DBMS_DATAPUMP", line 4825
              ORA-06512: at line 36

              Thanks
              JP
              • 4. Re: DBMS_DATAPUMP exclude
                Richard Harrison .
                Hi JP,
                The problem is the single quotes - try this example (just update your schema name as appropriate etc):
                DECLARE
                  l_dp_handle      NUMBER;
                  l_last_job_state VARCHAR2(30) := 'UNDEFINED';
                  l_job_state      VARCHAR2(30) := 'UNDEFINED';
                  l_sts            KU$_STATUS;
                  v_job_state      varchar2(4000);
                BEGIN
                  l_dp_handle := DBMS_DATAPUMP.open(operation   => 'EXPORT',
                                                    job_mode    => 'SCHEMA',
                                                    remote_link => NULL,
                                                    version     => 'LATEST');
                  DBMS_DATAPUMP.add_file(handle    => l_dp_handle,
                                         filename  => 'test.dmp',
                                         directory => 'DATA_PUMP_DIR',
                                         reusefile => 1);
                  DBMS_DATAPUMP.add_file(handle    => l_dp_handle,
                                         filename  => 'test.log',
                                         directory => 'DATA_PUMP_DIR',
                                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,
                                         reusefile => 1);
                  DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle, 'SCHEMA_LIST', '''ALIGNE''');
                  DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle,
                                                'EXCLUDE_PATH_EXPR',
                                                'IN (''INDEX'', ''SYNONYMS'',''GRANTS'',''STATISTICS'')');
                  DBMS_DATAPUMP.start_job(l_dp_handle);
                  DBMS_DATAPUMP.WAIT_FOR_JOB(l_dp_handle, v_job_state);
                  DBMS_OUTPUT.PUT_LINE(v_job_state);
                END;
                Cheers,
                Harry
                • 5. Re: DBMS_DATAPUMP exclude
                  JP88
                  Thanks Harry .

                  Error has been fixed .

                  Thanks
                  JP