Metadaten aus der DB extrahieren und einsammeln

Mit dem PL/SQL Package DBMS_METADATA lassen sich Informationen über Datenbank Objekte einfach aus der DB heraus ermitteln.
zuvor ein Directory anlegen

create or replace directory SOURCECODE as '/backuptest/flash_recovery_area/MCNGDB/';

getDBMetaData.sql

CREATE OR REPLACE PROCEDURE getDBMetaData
AS
   v_lob                   clob;
   v_doc				   clob;
   v_file_handle           UTL_FILE.file_type;
   v_length                PLS_INTEGER;
   v_buffer                VARCHAR2( 32767 );
   v_buffer_size          CONSTANT BINARY_INTEGER     := 32767;
   v_amount               BINARY_INTEGER;
   v_offset               NUMBER( 38 );
   v_meta_handle          NUMBER;         -- handle returned by 'OPEN'
   v_meta_handle_trans          NUMBER;         -- handle returned by 'OPEN'
 
BEGIN
 
 -- Specify the object type.
  v_meta_handle := DBMS_METADATA.OPEN('TABLESPACE');
  v_meta_handle_trans:=DBMS_METADATA.ADD_TRANSFORM(v_meta_handle,'DDL');
 
 
  LOOP
    v_doc   := DBMS_METADATA.FETCH_CLOB(v_meta_handle);
	EXIT WHEN v_doc IS NULL;
	v_lob   := v_lob ||v_doc;
    v_lob   := v_lob || '/';
    -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
   END LOOP;
   v_lob                      := v_lob || '
';
 
 -- Specify the object type.
  v_meta_handle := DBMS_METADATA.OPEN('USER');
  v_meta_handle_trans:=DBMS_METADATA.ADD_TRANSFORM(v_meta_handle,'DDL');
 
 LOOP
    v_doc   := DBMS_METADATA.FETCH_CLOB(v_meta_handle);
	EXIT WHEN v_doc IS NULL;
	v_lob   := v_lob ||v_doc;
    v_lob   := v_lob || '/';
    -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
   END LOOP;
   v_lob                      := v_lob || '
';
 
 
 -- Specify the object type.
  v_meta_handle := DBMS_METADATA.OPEN('DB_LINK');
  v_meta_handle_trans:=DBMS_METADATA.ADD_TRANSFORM(v_meta_handle,'DDL');
 
 LOOP
    v_doc   := DBMS_METADATA.FETCH_CLOB(v_meta_handle);
	EXIT WHEN v_doc IS NULL;
	v_lob   := v_lob ||v_doc;
    v_lob   := v_lob || '/';
    -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
   END LOOP;
   v_lob                      := v_lob || '
';
 
  -- Specify the object type.
  v_meta_handle := DBMS_METADATA.OPEN('ROLLBACK_SEGMENT');
  v_meta_handle_trans:=DBMS_METADATA.ADD_TRANSFORM(v_meta_handle,'DDL');
 
 LOOP
    v_doc   := DBMS_METADATA.FETCH_CLOB(v_meta_handle);
	EXIT WHEN v_doc IS NULL;
	v_lob   := v_lob ||v_doc;
    v_lob   := v_lob || '/';
    -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
   END LOOP;
   v_lob                      := v_lob || '
';
 
 
 
   v_lob                      :=  REPLACE( v_lob, '  CREATE ', 'CREATE ' );
   v_lob                      :=  REPLACE( v_lob, ' /', '/' );
   v_length                  :=  DBMS_LOB.getlength( v_lob );
-- write the file
   v_file_handle              :=
      UTL_FILE.fopen( location          => 'SOURCECODE'
                    , filename          => 'MY_TABLESPACE.sql'
                    , open_mode         => 'w'
                    , max_linesize      => v_buffer_size
                    );
   v_amount                 := v_buffer_size;
   v_offset                   := 1;
 
   WHILE v_amount >= v_buffer_size
   LOOP
      DBMS_LOB.READ( lob_loc => v_lob, amount => v_amount, offset => v_offset, buffer => v_buffer );
      v_offset                   := v_offset + v_amount;
      UTL_FILE.put( file => v_file_handle, buffer => v_buffer );
      UTL_FILE.fflush( file => v_file_handle );
   END LOOP;
 
   UTL_FILE.fclose( file => v_file_handle );
 
END;
/