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;
/
Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
dba/collect_metainfo.txt · Zuletzt geändert: 2010/09/23 14:03 von gpipperr

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki