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; /
dba/collect_metainfo.txt · Zuletzt geändert: 2010/09/23 14:03 von gpipperr