Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:datapump_oracle_export_plsql

Wie kann eine Datapump Export über SQL*Plus gestartet werden?


DB:11g
Beispiel:

-- Export GPIDB Database
SET SERVEROUTPUT ON
 
ACCEPT export_dir  CHAR PROMPT 'Enter the name for the directory for the export of the database:'
 
DECLARE
  CURSOR c_dir (P_DIRNAME VARCHAR2)
  IS
    SELECT DIRECTORY_PATH
      FROM dba_directories
     WHERE DIRECTORY_NAME = P_DIRNAME;
 
  v_dir   dba_directories.DIRECTORY_PATH%TYPE;
BEGIN
  DBMS_OUTPUT.put_line ('check for directory GPIDB_EXPORT');
 
  OPEN c_dir ('GPIDB_EXPORT');
 
  FETCH c_dir INTO v_dir;
 
  IF SQL%NOTFOUND
  THEN
    DBMS_OUTPUT.put_line ('create directory GPIDB_EXPORT');
 
    EXECUTE IMMEDIATE 'create directory GPIDB_export as ''/orabackup''';
  ELSE
    IF v_dir NOT LIKE '&&export_dir'
    THEN
      DBMS_OUTPUT.put_line ('relink directory GPIDB_EXPORT');
 
      EXECUTE IMMEDIATE 'drop directory GPIDB_export';
 
      EXECUTE IMMEDIATE 'create directory GPIDB_export as ''/orabackup''';
    END IF;
  END IF;
 
  CLOSE c_dir;
END;
/
 
SELECT DIRECTORY_PATH
  FROM dba_directories
 WHERE DIRECTORY_NAME = 'GPIDB_EXPORT';
 
--- Start datapump to export the database
 
CREATE OR REPLACE PROCEDURE db_export_GPIDB
IS
  v_dp_handle   NUMBER;
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  -- Create Data Pump Handle - "TABLE EXPORT" in this case
  v_dp_handle :=
    DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 'GPIDB_EXPORT2' || TO_CHAR (SYSDATE, 'DD_HH24'));
 
  DBMS_DATAPUMP.set_parallel (handle => v_dp_handle, degree => 4);
 
  -- Export the complete schema
  DBMS_DATAPUMP.metadata_filter (handle => v_dp_handle, name => 'SCHEMA_EXPR', VALUE => 'IN (''GPIDB'')');
 
  -- Specify target file - make it unique with a timestamp
  DBMS_DATAPUMP.add_file (handle      => v_dp_handle
                         ,filename    => 'GPIDB_' || TO_CHAR (SYSDATE, 'YYYYMMDD-HH24MISS') || '%U.dmp'
                         ,directory   => 'GPIDB_EXPORT'
                         ,reusefile   => 1                                                             -- overwrite existing files
                         ,filesize    => '50000M');
 
  -- Logfile
  DBMS_DATAPUMP.add_file (handle      => v_dp_handle
                         ,filename    => 'GPIDB_' || TO_CHAR (SYSDATE, 'YYYYMMDD-HH24MISS') || '.log'
                         ,filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
                         ,directory   => 'GPIDB_EXPORT'
                         ,reusefile   => 1                                                             -- overwrite existing files
                         ,filesize    => '10000M');
 
  --    MERGE => that each partitioned table is re-created in the target database as an unpartitioned table
  -- DBMS_DATAPUMP.set_parameter (handle => v_dp_handle, name => 'PARTITION_OPTIONS', VALUE => 'MERGE');
 
  -- Do it!
  DBMS_DATAPUMP.start_job (handle => v_dp_handle);
 
  COMMIT;
--    DBMS_DATAPUMP.detach (handle => v_dp_handle);
END;
/
 
BEGIN
  DBMS_OUTPUT.put_line (' create export at ' || TO_CHAR (SYSDATE, 'dd.mm HH24:MI'));
  db_export_GPIDB;
END;
/
 
PROMPT "to attach to the job please use:"
 
SELECT 'expdp "''/ as sysdba''" attach=GPIDB_EXPORT2' || TO_CHAR (SYSDATE, 'DD_HH24') FROM DUAL;
 
TTITLE OFF
Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
dba/datapump_oracle_export_plsql.txt · Zuletzt geändert: 2012/03/22 18:12 von Gunther Pippèrr