常用Oracle表数据删除有如下两种方式:
1、delete from tablename;
2、trancate table tablename;
这两种方式都能达到删除数据的效果,但在Oracle空间回收的机制中,这两种方式有着不同的区别;
使用drop方式删除表中数据,能够回收数据所占用的数据块,但使用delete方式删除表中数据,
是无法回收数据块,数据块仍然占用着磁盘空间。我们可以通过如下方式查看表所占用的数据块:
新建表并插入数据:create table T as select * from dba_objects;
select count(*) from user_extents where segment_name='T';如果如下:
COUNT(*)
----------
23
一共23个数据块,我们使用delete删除数据后再看结果:
COUNT(*)
----------
23
通过结果可以看出,数据块并没有被回收掉,我们再通过执行alter table T move看看结果:
COUNT(*)
----------
1
这时,数据块被回收了。
但这样就会引入索引失败的潜在问题,我们来模拟这种情况的发生:
SQL>CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
SQL>CREATE INDEX T_IDX ON T(OBJECT_ID);
我们先看索引未失效时的状态:
SQL>select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='T_IDX';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
T_IDX NORMAL SYSTEM TABLE VALID
使索引失效:
SQL>ALTER TABLE T MOVE;
SQL>select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='T_IDX';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
T_IDX NORMAL SYSTEM TABLE UNUSABLE
SQL>INSERT INTO T VALUES(VALUE);
这时,索引状态变成了UNUSABLE,插入数据导致Oracle抛出了ORA-01502错误。
从上面的MOVE操作到导致ORA-01502错误,个人理解如下:
MOVE操作类似磁盘碎片整理,操作后,将断断续续的无有的碎片空间回收,并对整个磁盘文件空间系统重新整理,这样肯定
会涉及到数据块的移动,但并未同步将表关联的索引对象所引用的数据块做更新,这就意味着此时这个索引对应的数据块其实已经出错了。
所以我们需要重建索引,使用如下命令修复:
SQL>alter session set skip_unusable_indexes=false;
SQL>alter index T_IDX rebuild;
如果数据文件满了也会导致这个错误。
另,Oracle中还有很多地方需要用到rebuild,sync,像对象分析,数据压缩等等。
1、delete from tablename;
2、trancate table tablename;
这两种方式都能达到删除数据的效果,但在Oracle空间回收的机制中,这两种方式有着不同的区别;
使用drop方式删除表中数据,能够回收数据所占用的数据块,但使用delete方式删除表中数据,
是无法回收数据块,数据块仍然占用着磁盘空间。我们可以通过如下方式查看表所占用的数据块:
新建表并插入数据:create table T as select * from dba_objects;
select count(*) from user_extents where segment_name='T';如果如下:
COUNT(*)
----------
23
一共23个数据块,我们使用delete删除数据后再看结果:
COUNT(*)
----------
23
通过结果可以看出,数据块并没有被回收掉,我们再通过执行alter table T move看看结果:
COUNT(*)
----------
1
这时,数据块被回收了。
但这样就会引入索引失败的潜在问题,我们来模拟这种情况的发生:
SQL>CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
SQL>CREATE INDEX T_IDX ON T(OBJECT_ID);
我们先看索引未失效时的状态:
SQL>select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='T_IDX';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
T_IDX NORMAL SYSTEM TABLE VALID
使索引失效:
SQL>ALTER TABLE T MOVE;
SQL>select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='T_IDX';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
T_IDX NORMAL SYSTEM TABLE UNUSABLE
SQL>INSERT INTO T VALUES(VALUE);
这时,索引状态变成了UNUSABLE,插入数据导致Oracle抛出了ORA-01502错误。
从上面的MOVE操作到导致ORA-01502错误,个人理解如下:
MOVE操作类似磁盘碎片整理,操作后,将断断续续的无有的碎片空间回收,并对整个磁盘文件空间系统重新整理,这样肯定
会涉及到数据块的移动,但并未同步将表关联的索引对象所引用的数据块做更新,这就意味着此时这个索引对应的数据块其实已经出错了。
所以我们需要重建索引,使用如下命令修复:
SQL>alter session set skip_unusable_indexes=false;
SQL>alter index T_IDX rebuild;
如果数据文件满了也会导致这个错误。
另,Oracle中还有很多地方需要用到rebuild,sync,像对象分析,数据压缩等等。