datapump

=========================
procedure   export  par schema
=========================CREATE OR REPLACE PROCEDURE SYS.P_dtpump (sourceSchema IN VARCHAR2)
IS
   handle        NUMBER;
   MESSAGE       VARCHAR2 (2000);
   logfileName   VARCHAR2 (200);
   vschema       VARCHAR2 (200);
   dName         VARCHAR2 (200);
   v_scn     number;
   jobState      user_datapump_jobs.state%TYPE;
BEGIN
   handle := DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'SCHEMA');
   DBMS_OUTPUT.put_line (sourceSchema);
   SELECT UPPER (sourceSchema) INTO vschema FROM DUAL;
  select CURRENT_SCN into v_scn from v$database;
 
 
   DBMS_OUTPUT.put_line (vschema);
   DBMS_OUTPUT.put_line ('set logfile parameter');
   SELECT    instance_name
          || '_Sche_datapump_'
          || TO_CHAR (SYSDATE, 'YYYYMMDDMISS')
          || '.log'
     INTO logfileName
     FROM V$instance;
   DBMS_DATAPUMP.add_file (
      handle      => handle,
      filename    => logfileName,
      directory   => 'EXPDIR',
      filetype    => DBMS_DATAPUMP.ku$_file_type_log_file);

   SELECT    instance_name
          || '_Sche_datapump_'
          || TO_CHAR (SYSDATE, 'YYYYMMDDMISS')
          || '.dmp'  --fichier  *.dmp
     INTO dName
     FROM v$instance;
 --dbms_datapump.set_parallel(handle => handle, degree => 3); -- pas supporte  en standard edition
 dbms_datapump.set_parameter(handle => handle, name => 'FLASHBACK_SCN', value => v_scn);
   DBMS_DATAPUMP.ADD_FILE (
      handle,
      dName,
      'EXPDIR',
      filetype   => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);

   --   DBMS_OUTPUT.put_line ('define SCHEMA_LIST');
   DBMS_DATAPUMP.metadata_filter (handle   => handle,
                                  name     => 'SCHEMA_EXPR',
                                  -- name     => 'SCHEMA_LIST',
                                  --VALUE    => 'IN(''' || sourceSchema || ''')'
                                  VALUE    => 'IN(' || vschema || ')');
   DBMS_OUTPUT.put_line ('start datapump job');
   DBMS_DATAPUMP.start_job (handle);
   DBMS_OUTPUT.put_line ('wait for job to finish');
   DBMS_DATAPUMP.wait_for_job (handle, jobState);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
         '-------------------------------------------------------------------------------------');
      DBMS_OUTPUT.put_line (
         '-- Error Backtrace ------------------------------------------------------------------');
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace ());
      DBMS_OUTPUT.put_line (
         '-- Call Stack -----------------------------------------------------------------------');
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack ());
      DBMS_OUTPUT.put_line (
         '-------------------------------------------------------------------------------------');
      DBMS_DATAPUMP.stop_job (handle);
      RAISE;
END;
/
 
  pour  lancer :                                                        
 exec sys.p_dtpump('''tx'',''mo'',''dbtanos'''); 
======================
Proc  datapump  full
=====================
 CREATE OR REPLACE PROCEDURE SYS.P_dtpump_FULL
 IS
    handle        NUMBER;
    MESSAGE       VARCHAR2 (2000);
    -- flashbackSCN            NUMBER;
    logfileName   VARCHAR2 (200);
    dumpName1      VARCHAR2 (200);
   -- dumpName2      VARCHAR2 (200);
    --dumpName3      VARCHAR2 (200);
    v_scn  number;
    ---destinationSchemaExists NUMBER;
    jobState      user_datapump_jobs.state%TYPE;
 BEGIN
    handle := DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'FULL');

    --IF debug = TRUE THEN
    DBMS_OUTPUT.put_line ('set logfile parameter');

     select CURRENT_SCN into v_scn from v$database;
     dbms_datapump.set_parallel(handle => handle, degree => 3);  --pas supporte en standard edition
     --select  TO_char(SYSDATE,'Mon_Dy_DD@HH24:MI:SS')   from  dual;  pour  ADOPT

    SELECT   
           'LOG'
           ||instance_name
           || TO_CHAR (SYSDATE, 'Mon_Dy_DD')||'_'||to_char(sysdate,'HH24:MI:SS')
           || '.log'
      INTO logfileName
      FROM v$instance;

    DBMS_DATAPUMP.add_file (
       handle      => handle,
       filename    => logfileName,
       directory   => 'EXPDIR',
       filetype    => DBMS_DATAPUMP.ku$_file_type_log_file);

    -- END IF;
    SELECT  'full'
           ||instance_name
           || TO_CHAR (SYSDATE, 'Mon_Dy_DD')||'_'||to_char(sysdate,'HH24:MI:SS')
            || '_1.dmp'    --- fichier  *.dmp
      INTO dumpName1
      FROM V$instance;
     
         /*SELECT  'full'
           ||instance_name
           || TO_CHAR (SYSDATE, 'Mon_Dy_DD')||'_'||to_char(sysdate,'HH24:MI:SS')
            || '_1.dmp'    --- fichier  *.dmp
      INTO dumpName2
      FROM V$instance;
         SELECT  'full'
           ||instance_name
           || TO_CHAR (SYSDATE, 'Mon_Dy_DD')||'_'||to_char(sysdate,'HH24:MI:SS')
            || '_1.dmp'    --- fichier  *.dmp
      INTO dumpName3
      FROM V$instance;*/
  dbms_datapump.set_parameter(handle => handle, name => 'FLASHBACK_SCN', value => v_scn);
    DBMS_DATAPUMP.ADD_FILE (handle, dumpName1, 'EXPDIR',filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
    --DBMS_DATAPUMP.ADD_FILE (handle, dumpName2, 'EXPDIR',filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
    --DBMS_DATAPUMP.ADD_FILE (handle, dumpName3, 'EXPDIR',filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);


    DBMS_OUTPUT.put_line ('start datapump job');
    DBMS_DATAPUMP.start_job (handle);
    DBMS_OUTPUT.put_line ('wait for job to finish');
    DBMS_DATAPUMP.wait_for_job (handle, jobState);
 EXCEPTION
    WHEN OTHERS
    THEN
       DBMS_OUTPUT.put_line (
          '-------------------------------------------------------------------------------------');
       DBMS_OUTPUT.put_line (
          '-- Error Backtrace ------------------------------------------------------------------');
       DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace ());
       DBMS_OUTPUT.put_line (
          '-- Call Stack -----------------------------------------------------------------------');
       DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack ());
       DBMS_OUTPUT.put_line (
          '-------------------------------------------------------------------------------------');
       DBMS_DATAPUMP.stop_job (handle);
       RAISE;
 END;
 /