oracle中获取表空间ddl语句
2024-08-29 13:29:12
供稿：网友
-----------------------------------------------------------------------------------
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 ---临时表空间
--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 ---获得游标v_curtp指向的当前表空间包含的临时数据文件数
from dba_temp_files
where tablespace_name=v_tp.tablespace_name;
elsif v_tp.contents='undo' then ---回退表空间
-- 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 ---获得游标v_curtp指向的当前表空间包含的数据文件数
from dba_data_files
where tablespace_name=v_tp.tablespace_name;
elsif v_tp.contents='permanent' then ---普通表空间
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 ----临时数据文件
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; ---获取datafile定义
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 ---回退表空间存储参数
insert into bak_dba_tablesapce(ddl_txt) values(v_tp.status);
else ---普通表空间、临时表空间存储参数
if v_tp.contents='permanent' then ---普通表空间存储参数
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 ----统一分区尺寸
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 ----系统自动管理分区尺寸
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 ----系统自动管理段空间
insert into bak_dba_tablesapce(ddl_txt) values('segment space 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
用于crontab 定时备份数据库表空间的ddl
---------------------------------------------------------------------
#!/bin/ksh
#生成 bill数据库的表空间ddl语句
#每天执行
#获取环境变量
. /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
