====== 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/';
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;
/