drop tablespace TEST including contents and datafiles;

98 阅读 0 评论 65 点赞

我是靠谱客的博主精明绿草，最近开发中收集的这篇文章主要介绍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.dbf
	10 BIGTS			  /u01/oracle/oradata/db01/ts_test_me/bigts01.dbf
	11 DTFILETS			  /u01/oracle/oradata/db01/ts_test_me/dtfilets01.dbf

     FILE# TABLESPACENAME		  NAME
---------- ------------------------------ --------------------------------------------------------------------------------
	12 DTFILETS			  /u01/oracle/oradata/db01/ts_test_me/dtfilets2.dbf
	13 LOCAL			  /tmp/local.dbf
	<span style="color:#ff0000;">18 TEST_TBS			  /u01/test.dbf
	20 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.dbf
	10	    8 ONLINE  /u01/oracle/oradata/db01/ts_test_me/undots01.dbf
	 6	    9 ONLINE  /u01/oracle/oradata/db01/ts_test_me/frankts2.dbf
	12	   10 OFFLINE /u01/oracle/oradata/db01/ts_test_me/bigts01.dbf
	13	   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.dbf
	14	   13 RECOVER /tmp/local.dbf
	<span style="color:#ff0000;">19	   18 OFFLINE /u01/test.dbf
	23	   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.dbf
	10 BIGTS		/u01/oracle/oradata/db01/ts_test_me/bigts01.dbf
	11 DTFILETS		/u01/oracle/oradata/db01/ts_test_me/dtfilets01.dbf

     FILE# DATAFILENAME 	NAME
---------- -------------------- --------------------------------------------------------------------------------
	12 DTFILETS		/u01/oracle/oradata/db01/ts_test_me/dtfilets2.dbf
	13 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.dbf
	10 BIGTS		/u01/oracle/oradata/db01/ts_test_me/bigts01.dbf
	11 DTFILETS		/u01/oracle/oradata/db01/ts_test_me/dtfilets01.dbf

     FILE# DATAFILENAME 	NAME
---------- -------------------- --------------------------------------------------------------------------------
	12 DTFILETS		/u01/oracle/oradata/db01/ts_test_me/dtfilets2.dbf
	13 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.dbf
	10 /u01/oracle/oradata/db01/ts_test_me/bigts01.dbf
	11 /u01/oracle/oradata/db01/ts_test_me/dtfilets01.dbf

     FILE# NAME
---------- --------------------------------------------------------------------------------
	12 /u01/oracle/oradata/db01/ts_test_me/dtfilets2.dbf
	13 /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.dbf
	10 /u01/oracle/oradata/db01/ts_test_me/bigts01.dbf
	11 /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.dbf
	10 BIGTS		/u01/oracle/oradata/db01/ts_test_me/bigts01.dbf
	11 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>