Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:collect_metainfo

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;
/
Cookies helfen bei der Bereitstellung von Inhalten. Diese Website verwendet Cookies. Mit der Nutzung der Website erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Computer gespeichert werden. Außerdem bestätigen Sie, dass Sie unsere Datenschutzerklärung gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website. Weitere Information
"Autor: Gunther Pipperr"
dba/collect_metainfo.txt · Zuletzt geändert: 2010/09/23 14:03 von gpipperr