Get table Space DDL statements in Oracle

oracle| Statement-----------------------------------------------------------------------------------
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---Get the number of temporary data files contained in the current tablespace that the cursor V_CURTP points to
From Dba_temp_files
WHERE Tablespace_name=v_tp.tablespace_name;

Elsif V_TP. contents= ' UNDO ' THEN---fallback 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---Get the number of data files contained in the current table space that the cursor V_CURTP points to
From Dba_data_files
WHERE Tablespace_name=v_tp.tablespace_name;

Elsif V_TP. Contents= ' permanent ' THEN---plain table space
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 file
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 definition
EXIT when V_curdf%notfound;

IF V_DF. Autoextensible= ' YES ' THEN
V_ddl:= ' on ';
ELSE
v_ddl:= ' off ';
End IF;

IF V_curdf%rowcount=v_count THEN
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);

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---Fallback table space storage parameters
Insert into BAK_DBA_TABLESAPCE (ddl_txt) VALUES (V_TP. STATUS);

ELSE---Normal tablespace, temporary table space storage parameters
IF V_TP. Contents= ' permanent ' THEN---Normal table space storage parameters
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= ' system ' THEN----automatic management of partition dimensions
v_txt:= ' EXTENT MANAGEMENT ' | | V_tp. extent_management| | ' Autoallocate ';
Insert into BAK_DBA_TABLESAPCE (ddl_txt) values (v_txt);
End IF;

IF V_TP. segment_space_management= ' auto ' THEN----system automatically manage segment space
Insert into BAK_DBA_TABLESAPCE (ddl_txt) VALUES (' SEGMENT spaces MANAGEMENT AUTO ');
End IF;

End IF;
v_txt:= ' BLOCKSIZE ' | | (V_TP. block_size/1024) | | ' K ';
Insert into BAK_DBA_TABLESAPCE (ddl_txt) values (v_txt);
Insert into BAK_DBA_TABLESAPCE (ddl_txt) VALUES ('/');
Insert into BAK_DBA_TABLESAPCE (ddl_txt) values (');
Commit
End LOOP;
Close V_CURTP;

EXCEPTION
When others THEN
If V_curtp%isopen Then
Close V_CURTP;
If V_curdf%isopen Then
Close V_CURDF;
End If;
End If;
RAISE;
End GET_TABSPACE_DDL;
---------------------------------------------------------------------
get_tabspace_dll.sh
DDL for crontab scheduled backup of database tablespace
---------------------------------------------------------------------
#!/bin/ksh
Table Space DDL statements #生成 the bill database
#每天执行
#获取环境变量
. /oracle/.profile
Username=sys
Password=aaa123

########
Sqlplus username/password<<eof
---declare var here
Begin
GET_TABSPACE_DDL;
End
/
Exit
/
Eof
If [$?-ne 0];then
echo "error! Execute procedure failed! Please check it "
#mail ...
Exit 1
Fi
Sqlplus Username/password <<!
Set pages 0;
Set serveroutput on size 1000000;
Set heading off;
Set feedback off;
Set echo off;

Spool/ora_backup/orasysbak/bill_tabspace_ddl.sql
Select Ddl_txt from Bak_dba_tablesapce;
Spool off;
Exit
!
If [$?-ne 0];then
echo "error! Generate Tabspace DDL failed! Please check it "
#mail ...
Exit 1
Fi