建站
咨詢
售后
建站咨詢
新聞中心
本篇文章重點(diǎn)為大家講解一下Oracle刪除表空間時(shí)遇到的一些問(wèn)題以及解決方法，有需要的小伙伴可以參考一下。
成都做網(wǎng)站、網(wǎng)站設(shè)計(jì)、外貿(mào)營(yíng)銷網(wǎng)站建設(shè)介紹好的網(wǎng)站是理念、設(shè)計(jì)和技術(shù)的結(jié)合。創(chuàng)新互聯(lián)擁有的網(wǎng)站設(shè)計(jì)理念、多方位的設(shè)計(jì)風(fēng)格、經(jīng)驗(yàn)豐富的設(shè)計(jì)團(tuán)隊(duì)。提供PC端+手機(jī)端網(wǎng)站建設(shè)，用營(yíng)銷思維進(jìn)行網(wǎng)站設(shè)計(jì)、采用先進(jìn)技術(shù)開(kāi)源代碼、注重用戶體驗(yàn)與SEO基礎(chǔ)，將技術(shù)與創(chuàng)意整合到網(wǎng)站之中，以契合客戶的方式做到創(chuàng)意性的視覺(jué)化效果。
問(wèn)題1：刪除表空間期間遭遇報(bào)錯(cuò) ORA-29857
刪除表空間語(yǔ)句：DROP TABLESPACE SAC INCLUDING CONTENTS AND DATAFILES; 根據(jù)MOS文檔： How To Resolve ORA-29857 During a Drop Tablespace although No Domain Index exists in This Tablespace (文檔 ID 1610456.1) 對(duì)于ORA-29857這個(gè)錯(cuò)誤，文檔說(shuō)的很清楚：
現(xiàn)象：
刪除表空間時(shí)，遇到報(bào)錯(cuò)ORA-29857，例如： SQL> drop tablespace SAC including contents and datafiles
drop tablespace SAC including contents and datafiles
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace
然而，你并未在這個(gè)表空間中發(fā)現(xiàn)域索引：
SQL> SELECT OWNER,INDEX_NAME, TABLE_OWNER, TABLE_NAME
FROM DBA_INDEXES WHERE INDEX_TYPE='DOMAIN'
AND TABLESPACE_NAME ='SAC';
no rows selected
原因：
“
The table which is in the tablespace to be dropped has a domain index which needs to be dropped before dropping the tablespace. Domain indexes cannot be created in a specific tablespace and the TABLESPACE_NAME column in DBA_INDEXES is always null for domain indexes.
”
要?jiǎng)h除的表空間中的表有一個(gè)域索引，這個(gè)域索引在刪除表空間前需要被刪除掉。 域索引不能被創(chuàng)建在指定的表空間，對(duì)于域索引，DBA_INDEXES中的TABLESPACE_NAME列值總是空值。
解決方法：
You need to identify and drop the secondary objects: 你需要找出并刪除二級(jí)對(duì)象：
1.The domain index associated with a table in the tablespace to be dropped can be identified from the following query: 要?jiǎng)h除的與在這個(gè)表空間中的表相關(guān)的域索引可以通過(guò)下面的查詢找出來(lái)：
SQL> SELECT INDEX_NAME,I.TABLE_NAME FROM DBA_INDEXES I, DBA_TABLES T
WHERE T.TABLE_NAME=I.TABLE_NAME
AND T.OWNER=I.OWNER
AND I.INDEX_TYPE='DOMAIN'
and t.TABLESPACE_NAME='&TABLESPACE_NAME';
2.Secondary objects associated with domain indexes, can be identified from the following query: 與域索引相關(guān)的二級(jí)對(duì)象，可以通過(guò)下面的查詢找出來(lái)：
SQL> SELECT SECONDARY_OBJECT_OWNER,SECONDARY_OBJECT_NAME,SECONDARY_OBJDATA_TYPE FROM DBA_SECONDARY_OBJECTS WHERE INDEX_NAME='INDEX_NAME_From_Previous_Query';
Once you identify the secondary objects, you can drop those and then drop the tablespace. 一旦你找出這些二級(jí)對(duì)象，你就可以刪除它們?nèi)缓笤賱h除表空間。
Please see the following example: 請(qǐng)看下面的例子：
SQL> CREATE TABLESPACE SAC DATAFILE 'C:\SAC.DBF' SIZE 50M;
Tablespace created.
SQL> CREATE TABLE SAC TABLESPACE SAC AS SELECT * FROM ALL_OBJECTS;
Table created.
SQL> begin
ctx_ddl.create_preference('SUBSTRING_PREF','BASIC_WORDLIST');
ctx_ddl.set_attribute('SUBSTRING_PREF', 'SUBSTRING_INDEX','TRUE');
end;
/
PL/SQL procedure successfully completed.
-- Trying to create the domain index in specific tablespace fails with ORA-29850:
SQL> CREATE INDEX SAC_INDX ON SAC(OBJECT_TYPE) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('WORDLIST SUBSTRING_PREF MEMORY 50M') TABLESPACE SAC;
CREATE INDEX SAC_INDX ON SAC(OBJECT_TYPE) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('WORDLIST SUBSTRING_PREF MEMORY 50M') TABLESPACE SAC
*
ERROR at line 1:
ORA-29850: invalid option for creation of domain indexes
SQL> CREATE INDEX SAC_INDX ON SAC(OBJECT_TYPE) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('WORDLIST SUBSTRING_PREF MEMORY 50M');
Index created.
SQL> drop tablespace sac including contents and datafiles;
drop tablespace sac including contents and datafiles
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace
-- Trying to find the domain index in this tablespace:
SQL> SELECT OWNER,INDEX_NAME, TABLE_OWNER, TABLE_NAME
FROM DBA_INDEXES WHERE INDEX_TYPE='DOMAIN'
AND TABLESPACE_NAME ='SAC';
no rows selected
--Trying to find segments created in this newly created tablespace:
SQL> SELECT SEGMENT_NAME,SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SAC';
SEGMENT_NAME SEGMENT_TYPE
-------------------- ------------------
SAC TABLE
-- Trying to find the segment for index SAC_INDX :
SQL> SELECT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE SEGMENT_NAME='SAC_INDX';
no rows selected
-- Trying to find the tablespace for index SAC_INDX from DBA_INDEXES :
SQL> set null null
SQL> select INDEX_TYPE,TABLE_TYPE,DOMIDX_STATUS,DOMIDX_OPSTATUS,SEGMENT_CREATED,TABLESPACE_NAME from DBA_INDEXES where INDEX_NAME='SAC_INDX';
INDEX_TYPE TABLE_TYPE DOMIDX_STATU DOMIDX SEG TABLESPACE_NAME
--------------------------- ----------- ------------ ------ --- ------------------------------
DOMAIN TABLE VALID VALID YES null
--To find the indexes that are causing ORA-29857 , please use the following query :
SQL> col TABLE_NAME for a30
SQL> col INDEX_NAME for a30
SQL> SELECT INDEX_NAME,I.TABLE_NAME FROM DBA_INDEXES I, DBA_TABLES T
WHERE T.TABLE_NAME=I.TABLE_NAME
AND T.OWNER=I.OWNER
AND I.INDEX_TYPE='DOMAIN'
and t.TABLESPACE_NAME='SAC';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
SAC_INDX SAC
SQL> DROP INDEX SAC_INDX;
Index dropped.
--confirm that no secondary objects associated with domain index still exist:
SQL> SELECT SECONDARY_OBJECT_OWNER,SECONDARY_OBJECT_NAME,SECONDARY_OBJDATA_TYPE FROM DBA_SECONDARY_OBJECTS WHERE INDEX_NAME='SAC_INDX';
no rows selected
SQL> DROP TABLESPACE SAC INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
問(wèn)題2：刪除表空間期間遭遇 ORA-02429
對(duì)于ORA-02429這個(gè)錯(cuò)誤，MOS文檔的描述也很清楚： Drop Tablespace Failed with ORA-02429: cannot drop index used for enforcement of unique/primary key (文檔 ID 1918060.1)
現(xiàn)象：
刪除表空間失敗，伴隨下面的錯(cuò)誤：
SQL> DROP TABLESPACE REP_DATA INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE REP_DATA INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
解決方法：
Find the constraint name for the unique/primary key, disable the constraint and drop the tablespace again. 找到那些惟一/主鍵約束名，禁用這些約束然后再次刪除表空間。
Steps:
=====
1) Execute below query to find the constraint name:
執(zhí)行下面的查詢來(lái)找到約束名：
SQL> select owner, constraint_name,table_name,index_owner,index_name
from dba_constraints
where (index_owner,index_name) in (select owner,index_name from dba_indexes
where tablespace_name=' ' );
2) Disable the constraint:
禁用約束：
SQL> ALTER TABLE
DISABLE CONSTRAINT
; 3) Drop the tablespace: 刪除表空間： SQL> DROP TABLESPACE
INCLUDING CONTENTS AND DATAFILES;
問(wèn)題3：表空間刪除完畢，主機(jī)磁盤空間不釋放
如果等待很長(zhǎng)時(shí)間都沒(méi)有釋放，那么可參考：http://www.linuxidc.com/Linux/2016-04/130312.htm
“
建議的操作方法如下： 1、下載一個(gè)lsof軟件裝上，google上可以搜到 2、找到正在用被刪文件的進(jìn)程 lsof | grep deleted 3、kill掉相應(yīng)的進(jìn)程空間就釋放了
”
一般這種情況，并不建議重啟數(shù)據(jù)庫(kù)或主機(jī)。
網(wǎng)站名稱：Oracle刪除表空間時(shí)遇到的一些問(wèn)題以及解決方法
分享網(wǎng)址：http://www.5511xx.com/article/coddjji.html
