Schema level DBMS_DATAPUMP extract with some excludes

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;


0 comments:

Post a Comment