Question

如何从表空间中“删除”数据文件

Answer 1

1.查看数据文件：
SQL> select name from v$datafile;
NAME
+DATA/orcl/system01.dbf
+DATA/orcl/undotbs01.dbf
+DATA/orcl/sysaux01.dbf
+DATA/orcl/users01.dbf
+DATA/orcl/undotbs02.dbf
/oracle/u01/app/oracle/oradata/orcl/SMS.dbf
/oracle/u01/app/oracle/oradata/orcl/WEBDB.dbf
7 rows selected.
2.查看数据文件状态

SQL> select file#,status,name from v$datafile where name like '%SMS%';
FILE# STATUS
NAME
6 ONLINE
/oracle/u01/app/oracle/oradata/orcl/SMS.dbf
1 rows selected.
3.确认操作系统上SMS数据文件已经不存在，u01文件夹都没有

[oracle@db1 ~]$ ll /oracle/
总计 6778872
drwxr-xr-x 2 oracle oinstall 4096 12-16 17:07 bak
-rw-rw-r-- 1 oracle oinstall 170 12-19 15:41 IMPORT.LOG
4.此时查看数据文件状态，状态没有异常
sec@ora10g> select file#,status,name from v$datafile where name like '%SMS%';
FILE# STATUS NAME
6 ONLINE /oracle/u01/app/oracle/oradata/orcl/SMS.dbf
5.手工将误删除的文件offline drop，成功

SQL> alter database datafile '/oracle/u01/app/oracle/oradata/orcl/SMS.dbf' offline drop;
Database altered.
6.此时，该文件的状态为“RECOVER”
SQL> select file#,status,name from v$datafile where name like '%SMS%';
FILE# STATUS NAME
6 RECOVER /oracle/u01/app/oracle/oradata/orcl/SMS.dbf
7.演示删除表空间，成功。

SQL> alter database datafile '/oracle/u01/app/oracle/oradata/orcl/SMS.dbf' offline drop;

Database altered.
8.最后确认，删除得很彻底。
SQL>select file#,status,name from v$datafile where name like '%SMS%';
no rows selected
SQL> select name from v$datafile;
NAME
+DATA/orcl/system01.dbf
+DATA/orcl/undotbs01.dbf
+DATA/orcl/sysaux01.dbf
+DATA/orcl/users01.dbf
+DATA/orcl/undotbs02.dbf
/oracle/u01/app/oracle/oradata/orcl/WEBDB.dbf
6 rows selected.

Answer 2

本节视频我们来聊一聊如何删除数据表的行。Treelab 官网（免费注册体验）：www.treelab.com.cn