oracle冷备----删除非系统表空间

1.备份文件

define fil=/home/oracle/cdbk.sql
define log=/home/oracle/cdbk.log
spool &fil
prompt connet sys/sysdba@asmdemo as sysdba;
prompt shutdown immediate;
select 'host cp ' || file_name|| '/u01/asmdemo' || substr(file_name,instr(file_name,'/',-1,1)) from dba_data_fi
les;
prompt startup;
spool off
spool &log
@&fil;
spool off

 

2.创建用户

create user test identified by test default tablespace ts_test;

create table t1 ase select * from dba_objects where rownum = 1

3.删除非系统表空间

select file_name,file_id from dba_data_files;

/u01/app/oracle/oradata/demo/users01.dbf           1

/u01/app/oracle/oradata/demo/sysaux01.dbf          2
/u01/app/oracle/oradata/demo/undotbs01.dbf        3

/u01/app/oracle/oradata/demo/system01.dbf          4
/u01/app/oracle/oradata/demo/ts_test01.dbf         5

rm -f  /u01/app/oracle/oradata/demo/ts_test01.dbf   

4.查询t1表报错

select * from t1

 

5.恢复数据文件online

alter tablespace ts_test online

 

6.恢复数据文件

recover datafile 5