【作者简介】：
本人医疗行业DBA,从业7年，精通oracle底层；会文件、触发器加密的勒索恢复、asm磁盘组不能mount及各种灾难恢复，后面将利用空闲时间将自己工作中遇到的问题及整理的资料分享给更多愿意学习和提升自己的dba。
【表空间提取介绍】：
在生产环境搭建测试库、做恢复过程中，我们可能需要大量的使用dmp的备份来进行恢复动作，在不同的恢复场景下使用提取表空间的方式是不同的；
场景1：生产库完全损坏，无法mount的情况，只有逻辑备份，如何生成表空间创建语句？
处理方法：利用逻辑备份产生的dmp来生成表空间创建语句；
缺点：生成的表空间等创建语句格式有问题，还需要单独处理；
imp + show=y+log参数
场景2：生产库完全损坏，无法mount的情况，只有数据泵备份，如何生成表空间创建语句？
处理方法：利用数据泵备份产生的dmp来生成表空间创建语句impdp+sqlfile参数
缺点：无，生成的脚本是规范整洁的，将sqlfile的log拿出来编辑，然后修改为实际的路径在目标库创建即可；
场景3：生产库损坏或者跨平台搭建测试库情况，生产库可以mount或者open，如何生成表空间创建语句？
--1）mount状态过后就可以使用。
select * from
(select 'create tablespace "'|| t1.NAME || '" datafile ' ||chr(39)||t.name ||chr(39) || ' size '
|| 100 || 'M ' || 'autoextend on;'
from V$DATAFILE t,v$tablespace t1 where t.TS# = t1.TS# order by t.TS#
)
union all
select * from
(select 'create tablespace "'|| t1.NAME || '" datafile ' ||chr(39)||t.name ||chr(39) || ' size '
|| 100 || 'M ' || 'autoextend on;'
from v$tempfile t,v$tablespace t1 where t.TS# = t1.TS# order by t.TS#);
--2）open状态过后就可以使用。
Select *
From (Select 'CREATE TABLESPACE ' ||'"' || Tablespace_Name ||'"' ||' datafile ''' ||
File_Name || ''' SIZE ' || 100 || 'M AUTOEXTEND ON NEXT 10M ;'
From Dba_Data_Files
Order By Tablespace_Name, File_Id)
Union All
Select *
From (Select 'CREATE TEMPORARY TABLESPACE ' ||'"' ||Tablespace_Name ||'"' ||
' tempfile ''' || File_Name || ''' SIZE ' || 100 || 'M AUTOEXTEND ON NEXT 10M ;'
From Dba_Temp_Files
Order By Tablespace_Name, File_Id)
--3）open状态过后就可以使用。过程方法
DECLARE
CURSOR c_ddf IS
SELECT tablespace_name,
file_name,
bytes,
maxbytes,
increment_by,
AUTOEXTENSIBLE
FROM dba_data_files
where tablespace_name not in ('SYSTEM', 'SYSAUX', 'USERS','UNDOTBS1')
ORDER BY tablespace_name;
v_tbname_temp1 DBA_DATA_FILES.TABLESPACE_NAME%type := 'a';
CURSOR c_dtf IS
SELECT tablespace_name,
file_name,
bytes,
maxbytes,
increment_by,
autoextensible
FROM dba_temp_files
where tablespace_name not in ('TEMP')
ORDER BY tablespace_name;
v_tbname_temp2 dba_temp_files.TABLESPACE_NAME%type := 'a';
--在这里修改新环境的路径，old为正式库，newname为新环境
oldname varchar2(4000):= 'E:\ORADATA\ORCL1\';
newname varchar2(4000):= 'E:\app\wang\oradata\orcl\';
begin
for i in c_ddf loop
IF v_tbname_temp1 <> i.tablespace_name THEN
DBMS_OUTPUT.PUT_LINE('create tablespace "' || i.tablespace_name || '" datafile ' || chr(39) || replace(i.file_name,oldname,newname) || chr(39) || ' size ' || 100 ||'M autoextend on;');
ELSE
DBMS_OUTPUT.PUT_LINE('alter tablespace "' || i.tablespace_name || '" add datafile ' || chr(39) || replace(i.file_name,oldname,newname) || chr(39) || ' size ' || 100 ||'M autoextend on;');
END IF;
v_tbname_temp1 := i.tablespace_name;
end loop;
for j in c_dtf loop
IF v_tbname_temp2 <> j.tablespace_name THEN
DBMS_OUTPUT.PUT_LINE('create temporary tablespace "' || j.tablespace_name || '" tempfile ' || chr(39) || replace(j.file_name,oldname,newname) || chr(39) || ' size ' || 100 ||'M autoextend on;');
ELSE
DBMS_OUTPUT.PUT_LINE('alter tablespace "' || j.tablespace_name || '" add tempfile ' || chr(39) || replace(j.file_name,oldname,newname) || chr(39) || ' size ' || 100 ||'M autoextend on;');
END IF;
v_tbname_temp2 := j.tablespace_name;
end loop;
end;
第二版：
begin dbms_output.enable(10000000); end; --10g前
begin dbms_output.enable(buffer_size=>null); end;
/
DECLARE
CURSOR c_ddf IS
SELECT file_id,tablespace_name,
file_name,
bytes,
maxbytes,
increment_by,
AUTOEXTENSIBLE
FROM dba_data_files
where tablespace_name not in ('SYSTEM', 'SYSAUX', 'USERS','UNDOTBS1')
ORDER BY tablespace_name;
v_tbname_temp1 DBA_DATA_FILES.TABLESPACE_NAME%type := 'a';
CURSOR c_dtf IS
SELECT file_id,tablespace_name,
file_name,
bytes,
maxbytes,
increment_by,
autoextensible
FROM dba_temp_files
where tablespace_name not in ('TEMP')
ORDER BY tablespace_name;
v_tbname_temp2 dba_temp_files.TABLESPACE_NAME%type := 'a';
--在这里修改新环境的路径，old为正式库，newname为新环境
--new path
newname varchar2(4000):= '/u01/app/oracle/oradata/orcl/';
begin
for i in c_ddf loop
IF v_tbname_temp1 <> i.tablespace_name THEN
DBMS_OUTPUT.PUT_LINE('create tablespace "' || i.tablespace_name || '" datafile ' || chr(39) ||newname|| lower(i.tablespace_name)||i.file_id||'.dbf'|| chr(39) || ' size ' || i.bytes ||' autoextend on;');
ELSE
DBMS_OUTPUT.PUT_LINE('alter tablespace "' || i.tablespace_name || '" add datafile ' || chr(39) ||newname|| lower( i.tablespace_name)||i.file_id||'.dbf'|| chr(39) || ' size ' || i.bytes ||' autoextend on;');
END IF;
v_tbname_temp1 := i.tablespace_name;
end loop;
for j in c_dtf loop
IF v_tbname_temp2 <> j.tablespace_name THEN
DBMS_OUTPUT.PUT_LINE('create temporary tablespace "' || j.tablespace_name || '" tempfile ' || chr(39) ||newname|| lower(j.tablespace_name)||j.file_id||'.dbf'|| chr(39) || ' size ' || j.bytes ||' autoextend on;');
ELSE
DBMS_OUTPUT.PUT_LINE('alter tablespace "' || j.tablespace_name || '" add tempfile ' || chr(39) ||newname|| lower(j.tablespace_name)||j.file_id||'.dbf'|| chr(39) || ' size ' || j.bytes ||' autoextend on;');
END IF;
v_tbname_temp2 := j.tablespace_name;
end loop;
end;
