Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
====== 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\\
<code>
create or replace directory SOURCECODE as '/backuptest/flash_recovery_area/MCNGDB/';
</code>


<code plsql 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;
/
</code>