一、概述
系統環境，rhel5.8 X64 Oracle11g RAC （oracle11.2.0.3）
同事在做一個常規檢查，檢查表空間時報錯，如下

SQL> set pagesize 9999

SQL> set linesize 132

SQL> col TABLESPACE_NAME for a25

SQL> select

2 f.tablespace_name,

3 a.total,

4 f.free,

5 round((f.free/a.total)*100) "% Free"

6 from

7 (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,

8 (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f

9 WHERE a.tablespace_name = f.tablespace_name(+)

10 order by "% Free"

11 /

(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,

*

ERROR at line 7:

ORA-01157: cannot identify/lock data file 102 - see DBWR trace file

ORA-01110: data file 102: '/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/E:TEST_TABLESPACEdata.DBF'

檢視節點1目錄，沒有該檔案，在第二節點執行正常，該資料檔案存在於第二節點。也就是當時有人使用命令建立了個表空間，所指路徑不對，造成現在這個情況。

二、處理過程
檢視與該資料檔案、使用者相關的object，透過檢視該資料檔案所屬表空間就一個資料檔案，也就是建在本地磁碟上的這個資料檔案。

SQL> select owner,segment_name,bytes/1024/1024,segment_type from dba_segments where tablespace_name='TEST';

no rows selected

SQL> select table_name,tablespace_name from dba_tables where owner='FIRSOUL';

no rows selected

SQL>

由以上得知，該表空間、資料檔案並無資料，聯絡業務人員，確認後，決定刪除該使用者及所屬表空間
刪除使用者

SQL> drop user FIRSOUL cascade;

drop user FIRSOUL cascade

*

ERROR at line 1:

ORA-01940: 無法刪除當前連線的使用者

查出該使用者所佔用session，並執行kill，再次執行drop user，刪除成功。

SQL> select 'alter system kill session '''||sid||','||serial#||''';' username,status from v$session where username='FIRSOUL';

USERNAME STATUS

---------------------------------------------------------------- --------

alter system kill session '148,46393'; INACTIVE

alter system kill session '197,34073'; INACTIVE

alter system kill session '262,33941'; INACTIVE

alter system kill session '727,13125'; INACTIVE

alter system kill session '774,64545'; INACTIVE

alter system kill session '821,42635'; INACTIVE

alter system kill session '976,21047'; INACTIVE

alter system kill session '1026,6429'; INACTIVE

alter system kill session '1260,33987'; INACTIVE

刪除tablespace，出現以下錯誤，說明有index佔用了該表空間

SQL> drop tablespace TEST including contents and datafiles cascade constraints;

drop tablespace TEST including contents and datafiles cascade constraints

*

ERROR at line 1:

ORA-00604: 遞迴 SQL 級別 1 出現錯誤

ORA-02429: 無法刪除用於強制唯一/主鍵的索引

再次檢視，沒有

SQL> select owner,segment_name,bytes/1024/1024,segment_type from dba_segments where tablespace_name='TEST';

no rows selected

檢視索引dba_indexes，發現有一索引所使用表空間還是TEST，檢視dba_tables無記錄

SQL> select table_name,index_name,owner,tablespace_name from dba_indexes where tablespace_name='TEST';

TABLE_NAME INDEX_NAME OWNER TABLESPACE_NAME

---------------------- ------------------------- ---------- ------------------------------

T_MODEL_RTU_SINGLE MODEL_RTU_SINGLE_PK SDPW TEST

經跟業務人員協商，修改索引

SQL> alter index SDPW.MODEL_RTU_SINGLE_PK rebuild tablespace SDPWDATA online;

Index altered.

再次刪除表空間

SQL> drop tablespace TEST including contents and datafiles cascade constraints;

Tablespace dropped.

至此，已完成表空間的清理工作，有一個奇怪的現象是，開始我透過以下命令檢視時，該表空間有以下object

SQL> col SEGMENT_NAME for a30

SQL> select owner,segment_name,bytes/1024/1024,segment_type from dba_segments where tablespace_name='TEST';

OWNER SEGMENT_NAME BYTES/1024/1024 SEGMENT_TYPE

------------------------------ ------------------------------ --------------- ------------------

FIRSOUL T_MODEL_POINTTEMPLATE .0625 TABLE

FIRSOUL T_MODEL_POINTTEMPLATE_PK .0625 INDEX

FIRSOUL T_MODEL_POINT_INFO .0625 TABLE

FIRSOUL MODEL_POINT_INFO_PK .0625 INDEX

FIRSOUL MODEL_SIM_PK .0625 INDEX

FIRSOUL T_MODEL_RTU .0625 TABLE

FIRSOUL MODEL_RTU_PK .0625 INDEX

FIRSOUL T_MODEL_SIM .0625 TABLE

以上是上午發現，下午跟業務人員溝通後再次檢視並無記錄。但還有一個索引使用了TEST表空間，在dba_segments沒有記錄。透過檢視關聯表發現，其表沒有記錄，是一個空表，所屬其他使用者，也就是透過create…as..方式建立。
透過檢視官網文件，得知，oracle11g新特性，create table 延遲建立segment，如果只建立一個空表，不佔用segment空間，insert後才會佔用建立segment，即使再刪除也會使用segment。如下所示

SQL> truncate table t;

Table truncated.

SQL> commit;

Commit complete.

SQL> select segment_name,segment_type,owner,tablespace_name from dba_segments where tablespace_name='TEST';

SEGMENT_NAME SEGMENT_TYPE OWNER TABLESPACE_NAME

-------------------- ------------------ ------------------------------ ------------------------------

T TABLE TEST TEST

SQL> select count(*) from t;

COUNT(*)

----------

0

SQL> insert into t values(20);

1 row created.

SQL> commit;

Commit complete.

SQL> create table t1 as select * from t where 1=0;

Table created.

SQL> commit;

Commit complete.

SQL> select segment_name,segment_type,owner,tablespace_name from dba_segments where tablespace_name='TEST';

SEGMENT_NAME SEGMENT_TYPE OWNER TABLESPACE_NAME

-------------------- ------------------ ------------------------------ ------------------------------

T TABLE TEST TEST

SQL> select count(*) from t1;

COUNT(*)

----------

0

官網部分介紹：

The initial segment creation for nonpartitioned tables and indexes can be delayed until data is first inserted into an object.

Several prepackaged applications are delivered with large schemas containing many tables and indexes. Depending on the module usage, only a subset of these objects are really being used. With delayed segment creation, empty database objects do not consume any space, reducing the installation footprint and speeding up the installation.

文件E26088-01有詳細說明

Oracle? Database SQL Language Reference
11g Release 2 (11.2)
Part Number E26088-01

三、總結
以上問題屢見不鮮，在RAC環境中將資料檔案建立在本地，當然多數情況是由於對資料庫不熟悉造成，這跟公司及人員管理有關係。我們便在很多時候成了救火隊員，然而在處理過程中，忽略了Oracle11g新特性，造成在處理上用時過多。由此得到，也在此提醒自己，一個新版本，我們應該認真的去了解一下它的新特性，以便更好的處理問題。處理類似問題，順便提醒相關業務人員運算元據庫時要謹慎一下，有條件可以培訓依稀相關人員，以免造成資料庫其他問題，也方便自己。

【Oracle11g】記Oracle11g RAC一次清理使用者與表空間

相關文章