--创建测试表空间
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ENMOEDU/system01.dbf
/u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf
/u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf
/u01/app/oracle/oradata/ENMOEDU/users01.dbf
/u01/app/oracle/oradata/ENMOEDU/example01.dbf

SQL> create tablespace ts_test datafile
2   '/u01/app/oracle/oradata/ENMOEDU/test.dbf'
3   size 20m autoextend on;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ENMOEDU/system01.dbf
/u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf
/u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf
/u01/app/oracle/oradata/ENMOEDU/users01.dbf
/u01/app/oracle/oradata/ENMOEDU/example01.dbf
/u01/app/oracle/oradata/ENMOEDU/test.dbf

6 rows selected.

SQL>
----在测试表空间上创建表
SQL> create table t1 tablespace ts_test as select 1 id from dual;

Table created.

SQL>
----删除测试表空间
[oracle@imp ~]$ rm /u01/app/oracle/oradata/ENMOEDU/test.dbf
----再创建表测试表空间是否存在（也是能创建的、因为写在内存）
SQL> create table t4 tablespace ts_test as select 1 id from dual;

Table created.

SQL>
----****注意此时、千万不要关闭数据库

----查看DBWR进程的pid
[oracle@imp ENMOEDU]$ ps -ef|grep dbw|grep -v grep
oracle    4374     1 0 18:16 ?        00:00:00 ora_dbw0_ENMOEDU
[oracle@imp ENMOEDU]$
----找到句柄（一闪一闪）
[oracle@imp ENMOEDU]$ ls -rtl /proc/4374/fd
----将文件拷贝出来
[oracle@imp ENMOEDU]$ cp /proc/4374/fd/264 /u01/app/oracle/oradata/ENMOEDU/test.dbf
[oracle@imp ENMOEDU]$
----再查看数据文件
SQL> select file#,status,name from v$datafile;

     FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
         1 SYSTEM
/u01/app/oracle/oradata/ENMOEDU/system01.dbf

         2 ONLINE
/u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf

         3 ONLINE
/u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf

     FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
         4 ONLINE
/u01/app/oracle/oradata/ENMOEDU/users01.dbf

         5 ONLINE
/u01/app/oracle/oradata/ENMOEDU/example01.dbf

         6 ONLINE
/u01/app/oracle/oradata/ENMOEDU/test.dbf

6 rows selected.

SQL>
----将数据文件offline
SQL> alter database datafile 6 offline;

Database altered.

SQL> select file#,status ,name from v$datafile;

     FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
         1 SYSTEM
/u01/app/oracle/oradata/ENMOEDU/system01.dbf

         2 ONLINE
/u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf

         3 ONLINE
/u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf

     FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
         4 ONLINE
/u01/app/oracle/oradata/ENMOEDU/users01.dbf

         5 ONLINE
/u01/app/oracle/oradata/ENMOEDU/example01.dbf

         6 RECOVER
/u01/app/oracle/oradata/ENMOEDU/test.dbf

6 rows selected.
----恢复数据
SQL> RECOVER datafile 6;
Media recovery complete.
SQL>
----将数据文件online
SQL> alter database datafile 6 online;

Database altered.

SQL>

ITPub博客

oralce数据文件删除之后恢复

最新文章