drop tablespace TEST including contents and datafiles;

SQL> select t2.file#,t1.name tablespacename,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;       FILE# TABLESPACENAME  NAME---------- ------------------------------ -------------------------------------------------------------------------------- 1 SYSTEM  /u01/oracle/oradata/db01/system01.dbf 2 UNDOTBS1  /u01/oracle/oradata/db01/undotbs01.dbf 3 SYSAUX  /u01/oracle/oradata/db01/sysaux01.dbf 4 USERS  /u01/oracle/oradata/db01/users01.dbf 5 TS_16K  /tmp/test_block_size.dbf 6 FRANKS  /u01/oracle/oradata/db01/ts_test_me/frankts01.dbf 9 FRANKS  /u01/oracle/oradata/db01/ts_test_me/frankts2.dbf 7 DEMOTS  /u01/oracle/oradata/db01/ts_test_me/demots01.dbf 8 UNDOTS  /u01/oracle/oradata/db01/ts_test_me/undots01.dbf10 BIGTS  /u01/oracle/oradata/db01/ts_test_me/bigts01.dbf11 DTFILETS  /u01/oracle/oradata/db01/ts_test_me/dtfilets01.dbf     FILE# TABLESPACENAME  NAME---------- ------------------------------ --------------------------------------------------------------------------------12 DTFILETS  /u01/oracle/oradata/db01/ts_test_me/dtfilets2.dbf13 LOCAL  /tmp/local.dbf<span style="color:#ff0000;">18 TEST_TBS  /u01/test.dbf20 TEST   /u01/oracle/11G/dbs/f:test.dbf</span>15 rows selected.

<pre name="code" class="sql">SQL> col name format a50;SQL> select ts# ,FILE#,STATUS,name from v$datafile;       TS#FILE# STATUS  NAME---------- ---------- ------- -------------------------------------------------- 0    1 SYSTEM  /u01/oracle/oradata/db01/system01.dbf 1    2 ONLINE  /u01/oracle/oradata/db01/undotbs01.dbf 2    3 ONLINE  /u01/oracle/oradata/db01/sysaux01.dbf 4    4 ONLINE  /u01/oracle/oradata/db01/users01.dbf 5    5 RECOVER /tmp/test_block_size.dbf 6    6 ONLINE  /u01/oracle/oradata/db01/ts_test_me/frankts01.dbf 7    7 ONLINE  /u01/oracle/oradata/db01/ts_test_me/demots01.dbf10    8 ONLINE  /u01/oracle/oradata/db01/ts_test_me/undots01.dbf 6    9 ONLINE  /u01/oracle/oradata/db01/ts_test_me/frankts2.dbf12   10 OFFLINE /u01/oracle/oradata/db01/ts_test_me/bigts01.dbf13   11 ONLINE  /u01/oracle/oradata/db01/ts_test_me/dtfilets01.dbf       TS#FILE# STATUS  NAME---------- ---------- ------- --------------------------------------------------13   12 RECOVER /u01/oracle/oradata/db01/ts_test_me/dtfilets2.dbf14   13 RECOVER /tmp/local.dbf<span style="color:#ff0000;">19   18 OFFLINE /u01/test.dbf23   20 OFFLINE /u01/oracle/11G/dbs/f:test.dbf</span>15 rows selected.SQL> --貌似offline 才可以drop 表空间的哦 SQL> SQL> drop tablespace TEST including contents and datafiles;Tablespace dropped.

<span style="font-family: Arial, Helvetica, sans-serif;">SQL> select t2.file#,t1.name datafilename,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;  </span>

     FILE# DATAFILENAME NAME---------- -------------------- -------------------------------------------------------------------------------- 1 SYSTEM/u01/oracle/oradata/db01/system01.dbf 2 UNDOTBS1/u01/oracle/oradata/db01/undotbs01.dbf 3 SYSAUX/u01/oracle/oradata/db01/sysaux01.dbf 4 USERS/u01/oracle/oradata/db01/users01.dbf 5 TS_16K/tmp/test_block_size.dbf 6 FRANKS/u01/oracle/oradata/db01/ts_test_me/frankts01.dbf 9 FRANKS/u01/oracle/oradata/db01/ts_test_me/frankts2.dbf 7 DEMOTS/u01/oracle/oradata/db01/ts_test_me/demots01.dbf 8 UNDOTS/u01/oracle/oradata/db01/ts_test_me/undots01.dbf10 BIGTS/u01/oracle/oradata/db01/ts_test_me/bigts01.dbf11 DTFILETS/u01/oracle/oradata/db01/ts_test_me/dtfilets01.dbf     FILE# DATAFILENAME NAME---------- -------------------- --------------------------------------------------------------------------------12 DTFILETS/u01/oracle/oradata/db01/ts_test_me/dtfilets2.dbf13 LOCAL/tmp/local.dbf<span style="color:#ff0000;">18 TEST_TBS/u01/test.dbf</span>14 rows selected.SQL> select t2.file#,t1.name datafilename,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;       FILE# DATAFILENAME NAME---------- -------------------- -------------------------------------------------------------------------------- 1 SYSTEM/u01/oracle/oradata/db01/system01.dbf 2 UNDOTBS1/u01/oracle/oradata/db01/undotbs01.dbf 3 SYSAUX/u01/oracle/oradata/db01/sysaux01.dbf 4 USERS/u01/oracle/oradata/db01/users01.dbf 5 TS_16K/tmp/test_block_size.dbf 6 FRANKS/u01/oracle/oradata/db01/ts_test_me/frankts01.dbf 9 FRANKS/u01/oracle/oradata/db01/ts_test_me/frankts2.dbf 7 DEMOTS/u01/oracle/oradata/db01/ts_test_me/demots01.dbf 8 UNDOTS/u01/oracle/oradata/db01/ts_test_me/undots01.dbf10 BIGTS/u01/oracle/oradata/db01/ts_test_me/bigts01.dbf11 DTFILETS/u01/oracle/oradata/db01/ts_test_me/dtfilets01.dbf     FILE# DATAFILENAME NAME---------- -------------------- --------------------------------------------------------------------------------12 DTFILETS/u01/oracle/oradata/db01/ts_test_me/dtfilets2.dbf13 LOCAL/tmp/local.dbf<span style="color:#ff0000;">18 TEST_TBS/u01/test.dbf</span>14 rows selected.SQL><span style="color:#ff0000;"> 只查看数据文件也是被删除的</span>

<pre name="code" class="sql">SQL> select t2.file#,t2.name from v$datafile t2;      FILE# NAME---------- -------------------------------------------------------------------------------- 1 /u01/oracle/oradata/db01/system01.dbf 2 /u01/oracle/oradata/db01/undotbs01.dbf 3 /u01/oracle/oradata/db01/sysaux01.dbf 4 /u01/oracle/oradata/db01/users01.dbf 5 /tmp/test_block_size.dbf 6 /u01/oracle/oradata/db01/ts_test_me/frankts01.dbf 7 /u01/oracle/oradata/db01/ts_test_me/demots01.dbf 8 /u01/oracle/oradata/db01/ts_test_me/undots01.dbf 9 /u01/oracle/oradata/db01/ts_test_me/frankts2.dbf10 /u01/oracle/oradata/db01/ts_test_me/bigts01.dbf11 /u01/oracle/oradata/db01/ts_test_me/dtfilets01.dbf     FILE# NAME---------- --------------------------------------------------------------------------------12 /u01/oracle/oradata/db01/ts_test_me/dtfilets2.dbf13 /tmp/local.dbf<span style="color:#ff0000;">18 /u01/test.dbf</span>14 rows selected.SQL> 同时物理文件也被删除了

<pre name="code" class="sql">[oracle@MasterHadoop50 u01]$ cd -/u01/oracle/11G/dbs[oracle@MasterHadoop50 dbs]$ ls -ltr *.dbf

ls: *.dbf: No such file or directory

----------------------------看看没有 and datafiles的情况--------------------------------------

<pre name="code" class="sql"><pre name="code" class="sql">SQL> drop tablespace TEST_TBS including contents;Tablespace dropped.SQL>

SQL> select t2.file#,t2.name from v$datafile t2;      FILE# NAME---------- -------------------------------------------------------------------------------- 1 /u01/oracle/oradata/db01/system01.dbf 2 /u01/oracle/oradata/db01/undotbs01.dbf 3 /u01/oracle/oradata/db01/sysaux01.dbf 4 /u01/oracle/oradata/db01/users01.dbf 5 /tmp/test_block_size.dbf 6 /u01/oracle/oradata/db01/ts_test_me/frankts01.dbf 7 /u01/oracle/oradata/db01/ts_test_me/demots01.dbf 8 /u01/oracle/oradata/db01/ts_test_me/undots01.dbf 9 /u01/oracle/oradata/db01/ts_test_me/frankts2.dbf10 /u01/oracle/oradata/db01/ts_test_me/bigts01.dbf11 /u01/oracle/oradata/db01/ts_test_me/dtfilets01.dbf     FILE# NAME---------- --------------------------------------------------------------------------------12 /u01/oracle/oradata/db01/ts_test_me/dtfilets2.dbf<span style="color:#ff0000;">13 /tmp/local.dbf</span>13 rows selected.SQL> 

SQL> select t2.file#,t1.name datafilename,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;       FILE# DATAFILENAME NAME---------- -------------------- -------------------------------------------------------------------------------- 1 SYSTEM/u01/oracle/oradata/db01/system01.dbf 2 UNDOTBS1/u01/oracle/oradata/db01/undotbs01.dbf 3 SYSAUX/u01/oracle/oradata/db01/sysaux01.dbf 4 USERS/u01/oracle/oradata/db01/users01.dbf 5 TS_16K/tmp/test_block_size.dbf 6 FRANKS/u01/oracle/oradata/db01/ts_test_me/frankts01.dbf 9 FRANKS/u01/oracle/oradata/db01/ts_test_me/frankts2.dbf 7 DEMOTS/u01/oracle/oradata/db01/ts_test_me/demots01.dbf 8 UNDOTS/u01/oracle/oradata/db01/ts_test_me/undots01.dbf10 BIGTS/u01/oracle/oradata/db01/ts_test_me/bigts01.dbf11 DTFILETS/u01/oracle/oradata/db01/ts_test_me/dtfilets01.dbf     FILE# DATAFILENAME NAME---------- -------------------- --------------------------------------------------------------------------------12 DTFILETS/u01/oracle/oradata/db01/ts_test_me/dtfilets2.dbf<span style="color:#ff0000;">13 LOCAL/tmp/local.dbf</span>13 rows selected.SQL> 

再一看物理文件并没有被删除

[oracle@MasterHadoop50 u01]$ ls -ltr *.dbf-rw-r----- 1 oracle dba 12591104 Jan 29 21:24 test.dbf[oracle@MasterHadoop50 u01]$ pwd/u01[oracle@MasterHadoop50 u01]$ 

综上所述 drop tablespace TEST including contents and datafiles;

<span style="color:#ff0000;">         既删除数据文件 表空间 等表或视图的记录又删除了物理文件</span>

<span style="color:#ff0000;">         drop tablespace TEST_TBS including contents;         </span><pre name="code" class="sql"><span style="color:#ff0000;"><span style="white-space:pre"></span> 只删除数据文件 表空间 等表或视图的记录并没有删除物理文件。</span><span style="color:#3333ff;">貌似可以reuse........</span>