-------------------------------------------------- -------------------------------- CREATE TABLE --------------- -------------------------------------------------- ------------------ Create Table Bak_DBA_TABLESAPCE (DDL_TXT VARCHAR2 (2000)); --------------------- -------------------------------------------------- ------------ Procedure ------------------------------------- -------------------------------------------- Create or Replace Procedure get_tabspace_ddl as type r_curdf is ref cursor; v_tpname varchar2 (30); cursor v_curtp is select * from dba_tablespaces; v_curdf r_curdf; v_ddl varchar2 (2000); v_txt varchar2 (2000); v_tp dba_tablespaces% rowtype; v_df dba_data_files% rowtype; v_count number; begin OPEN v_CURTP; LOOP FETCH v_curtp INTO v_tp; EXIT WHEN v_CURtp% NOTFOUND; V_TPNAME: = v_TP.tablespace_name; IF v_tp.CONTENTS = 'tEMPORARY' THEN --- temporary table space --DBMS_OUTPUT.PUT_LINE ( 'CREATE tEMPORARY tABLESPACE' | | v_tp.tablespace_name || 'DataFile'); v_txt: = 'Create Temporary TableSpace' || V_tp.tablespace _name || 'DATAFILE'; insert into bak_dba_tablesapce (ddl_txt) values (v_txt); SELECT COUNT (*) INTO v_count --- the number of data files to obtain a temporary current cursor pointing v_curtp tablespace contains FROM DBA_TEMP_FILES WHERE tablespace_name = v_tp.tablespace_name ; Elsif v_tp.contents = 'undo' Then --- Retreat table space - dbms_output.put_line ('create undo tablespace' || v_tp.tablespace_name || 'datafile'); v_txt: = 'create undo tablespace' || v_tp.tablespace_name || 'DATAFILE'; insert into bak_dba_tablesapce (ddl_txt) values (v_txt); SELECT COUNT (*) INTO v_count --- obtain the number of data points to the current file cursor v_curtp tablespace contains FROM DBA_DATA_FILES WHERE tablespace_name = v_tp. TableSpace_name; Elsif v_tp.contents = 'permanent'
Normal THEN --- tablespace v_txt: = 'CREATE TABLESPACE' || v_tp.tablespace_name || 'DATAFILE'; insert into bak_dba_tablesapce (ddl_txt) values (v_txt); END IF; if v_tp.CONTENTS = 'TEMPORARY' THEN - - temporary data files OPEN v_CURDF for select * from dba_temp_files where tablespace_name = v_tpname; else OPEN v_CURDF for select * from dba_data_files where tablespace_name = v_tpname; end if; LOOP FETCH v_curdf INTO v_df; --- get DATAFILE defined EXIT WHEN v_CURdf% NOTFOUND ; If v_df.autoextensible = 'yes'; Else V_DDL: = 'OFF'; end if; if; if v_curdf% rowcount = v_count kiln v_txt: = '' '|| v_df.file_name ||' '' || 'size' || || 'm Autoextend' || V_DDL; INSERT INTO BAK_DBA_TABLESAPCE (DDL_TXT) VALUES (v_txt); Else V_txt: = '' || v_df.file_name || '' '' || 'size' || (v_df.blocks * 8/1024) || 'm Autoextend' || V_DDL || ','; Insert INTO BAK_DBA_TABLESAPCE (DDL_TXT) VALUES (V_txt) ; End if; end loop; close v_curdf; if v_tp.contents = 'undo' THEN --- Return table Space Storage Parameters INSERT INTO BAK_DBA_TABLESAPCE (DDL_TXT) VALUES (v_tp.status); Else --- Ordinary Table Space, temporary Table space storage parameter i F v_tp.CONTENTS = 'PERMANENT' THEN --- common parameter table storage space insert into bak_dba_tablesapce (ddl_txt) values (V_TP.LOGGING); insert into bak_dba_tablesapce (ddl_txt) values (V_TP.STATUS); insert into bak_dba_tablesapce (ddl_txt) values ('Permanent'); end if; if v_tp.allocation_type = 'uniform' Then ---- Uniform partition size v_txt: = 'extent management' || v_tp.extent_management || 'uniform size' || v_tp.initial_extent / 1024 * 1024) || 'm'; INSERT INTO BAK_DBA_TABLESAPCE (DDL_TXT) VALUES (V_txt); Elsif v_tp.allocation_type =
