刪除表空間的操作
一個使用者要刪除資料庫的表空間,首先該使用者要具備drop tablespace的許可權
在當前使用者下執行以下語句來查詢確認
select a2.username,a1.privilege from dba_sys_privs a1 , user_role_privs a2
where a1.privilege = 'DROP TABLESPACE'
and a1.grantee =a2.granted_role
SQL> conn xxx/xxx
已連線。
SQL> select a2.username,a1.privilege from dba_sys_privs a1 , user_role_privs a2
2 where a1.privilege = 'DROP TABLESPACE'
3 and a1.grantee =a2.granted_role;
USERNAME PRIVILEGE
------------------------------ ----------------------------------------
WULW DROP TABLESPACE
如果沒有 drop tablespace,請先用更高階的使用者(如sys)給予授權
SQL> conn as sysdba;
已連線。
SQL> grant drop tablespace to wulw ;
授權成功。
以上前提條件確認完,下面開始實驗.
確認已有的表空間
SQL> select a.TS# ,a.NAME , b.NAME from v$tablespace a,v$datafile b
2 where a.TS# = b.TS#;
TS# NAME NAME
0 SYSTEM L:\ORACLE\ORADATA\IBM\SYSTEM01.DBF
1 UNDOTBS1 L:\ORACLE\ORADATA\IBM\UNDOTBS01.DBF
3 CWMLITE L:\ORACLE\ORADATA\IBM\CWMLITE01.DBF
4 DRSYS L:\ORACLE\ORADATA\IBM\DRSYS01.DBF
5 EXAMPLE L:\ORACLE\ORADATA\IBM\EXAMPLE01.DBF
6 INDX L:\ORACLE\ORADATA\IBM\INDX01.DBF
7 ODM L:\ORACLE\ORADATA\IBM\ODM01.DBF
8 TOOLS L:\ORACLE\ORADATA\IBM\TOOLS01.DBF
9 USERS L:\ORACLE\ORADATA\IBM\USERS01.DBF
10 XDB L:\ORACLE\ORADATA\IBM\XDB01.DBF
12 TDATA_01 L:\ORACLE\ORADATA\IBM\TDATA_01.ORA
14 TINDEX_01 L:\ORACLE\ORADATA\IBM\TINDEX_01.ORA
已選擇12行。(為看起來直觀,上面的顯示作了手工格式化)
建立新的測試表空間:
SQL> create tablespace mytbs01
2 logging
3 datafile 'L:\ORACLE\ORADATA\IBM\mytbs01.dbf' size 10m REUSE ;
表空間已建立。
SQL> CREATE TABLESPACE "MYTBS02"
2 LOGGING
3 DATAFILE 'L:\ORACLE\ORADATA\IBM\MYTBS02.dbf' SIZE 10M REUSE ;
表空間已建立。
如果表空間裡面沒有任何資料物件,可以直接刪除.
SQL> drop tablespace mytbs01;
表空間已丟棄。
這種刪除方式相關的資料檔案仍然存在於磁碟上.
(可以進入資料檔案所在目錄L:\ORACLE\ORADATA\IBM 進行檢視 MYTBS01.DBF )
如果表空間裡面含有資料物件,那麼該表空間就不能像上面那樣做直接刪除了.
SQL> create tablespace mytbs01
2 logging
3 datafile 'L:\ORACLE\ORADATA\IBM\mytbs01.dbf' size 10m REUSE;
表空間已建立。
SQL> create table test(mobile number(13))
2 tablespace mytbs01;
表已建立。
SQL> drop tablespace mytbs01;
drop tablespace mytbs01
*
ERROR 位於第 1 行:
ORA-01549: 表空間非空，請使用 INCLUDING CONTENTS 選項
看到了吧,提示ora-01549錯誤.
如果要刪除該表空間,可加上including contents子句.
如: drop tablespace mytbs01 including contents ;
如果想在刪除表空間的同時也刪除掉對應的資料檔案,那就在上面的語句最後加上 and datafiles
成為 drop tablespace mytbs01
including contents and datafiles;
SQL> drop tablespace mytbs01
2 including contents and datafiles;
表空間已丟棄。
要注意的一點是,如果drop tablespace語句中含有datafiles,那datafiles之前必須有contents關鍵字,不然會提示ora-01911錯誤:
SQL> drop tablespace mytbs02
2 including datafiles;
including datafiles
*
ERROR 位於第 2 行:
ORA-01911: 需要 CONTENTS 關鍵字
接下來的實驗是:
如果表空間A中有一個表ta,表空間B中有一個表tb,而ta與tb有著某種關係,那麼是否可以按上面的方法直接幹掉表空間A和表空間B呢?
看試驗過程:
SQL> create tablespace mytbs01
2 logging
3 datafile 'L:\ORACLE\ORADATA\IBM\mytbs01.dbf' size 10m REUSE;
表空間已建立。
SQL> CREATE TABLESPACE "MYTBS02"
2 LOGGING
3 DATAFILE 'L:\ORACLE\ORADATA\IBM\MYTBS02.dbf' SIZE 10M REUSE ;
CREATE TABLESPACE "MYTBS02"
*
ERROR 位於第 1 行:
ORA-01543: 表空間 'MYTBS02' 已經存在
SQL> create table test(mobile number(13))
2 tablespace mytbs01;
表已建立。
SQL> create table test2(mobile number(13))
2 tablespace mytbs02;
表已建立。
SQL> alter table TEST2
2 add primary key (mobile);
表已更改。
SQL> ALTER TABLE test
2 ADD CONSTRAINT FOREIGN con_mobile REFERENCES test2(mobile);
表已更改。
以上的意思是:在表空間mytbs01上建立表test,在表空間mytbs02上建立表test2,兩個表test和test2以外來鍵相關聯,test2為主表,test為從表.
現在嘗試能否用上面的語句直接幹掉mytbs02:
SQL> drop tablespace mytbs02;
drop tablespace mytbs02
*
ERROR 位於第 1 行:
ORA-01549: 表空間非空，請使用 INCLUDING CONTENTS 選項
SQL> drop tablespace mytbs02
2 including contents;
drop tablespace mytbs02
*
ERROR 位於第 1 行:
ORA-02449: 表中的唯一/主鍵被外部關鍵字引用
SQL> drop tablespace mytbs02
2 including contents and datafiles;
drop tablespace mytbs02
*
ERROR 位於第 1 行:
ORA-02449: 表中的唯一/主鍵被外部關鍵字引用
可見主表所在表空間因為表與其他空間上的表有聯絡,所以沒辦法直接刪掉.
那mytbs01表空間能不能幹掉?請看:
SQL> drop tablespace mytbs01
2 including contents and datafiles;
表空間已丟棄。
SQL> drop tablespace mytbs02
2 including contents and datafiles ;
表空間已丟棄。
嘿嘿,從表test所在的表空間mytbs01能直接幹掉,而且從表的表空間幹掉後,主表test2所在的表空間mytbs02也能幹掉了!
那麼,如果我只想幹掉主表所在的表空間,又不想幹掉從表所在的表空間那怎麼辦?
很簡單,最笨的一招就是想把兩個表的關聯關係給滅了,(在上面的兩個表中,就是把那個外來鍵給刪了)再把主表表所在的表空間刪了.但這種方法可不太現實,如果一個表空間裡有成百上千個物件與別的表空間裡的物件有聯絡,總不能一個一個去"解鈴"吧? 即使你本人就是"系鈴"人,估計你也要"解"到鬱悶死!
一個更簡單的方法當然是級聯刪除了！
drop tablespace mytbs02
including contents and datafiles cascade constraints
試驗如下：
SQL> create tablespace mytbs01
2 logging
3 datafile 'L:\ORACLE\ORADATA\IBM\mytbs01.dbf' size 10m REUSE;
表空間已建立。
SQL> CREATE TABLESPACE "MYTBS02"
2 LOGGING
3 DATAFILE 'L:\ORACLE\ORADATA\IBM\MYTBS02.dbf' SIZE 10M REUSE ;
表空間已建立。
SQL> create table test(mobile number(13))
2 tablespace mytbs01;
表已建立。
SQL> create table test2(mobile number(13))
2 tablespace mytbs02;
表已建立。
SQL> alter table TEST2
2 add primary key (mobile);
表已更改。
SQL> ALTER TABLE test
2 ADD CONSTRAINT FOREIGN con_mobile REFERENCES test2(mobile);
表已更改。
SQL> drop tablespace mytbs02
2 including contents and datafiles cascade constraints;
表空間已丟棄。