一、概述
系统环境，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 |
三、 总结
以上问题屡见不鲜，在RAC环境中将数据文件创建在本地，当然多数情况是由于对数据库不熟悉造成，这跟公司及人员管理有关系。我们便在很多时候成了救火队员，然而在处理过程中，忽略了Oracle11g新特性，造成在处理上用时过多。由此得到，也在此提醒自己，一个新版本，我们应该认真的去了解一下它的新特性，以便更好的处理问题。处理类似问题，顺便提醒相关业务人员操作数据库时要谨慎一下，有条件可以培训依稀相关人员，以免造成数据库其他问题，也方便自己。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29487349/viewspace-1591974/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29487349/viewspace-1591974/
3578
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
