I have a test environment which has oracle 10.2.0.1 installed, today I want to resize a tablespace named ANNEX which has a size:347921216KB because there
is no usable data in it.the usage of the datafile Corresponding this tablespace is 0.7%,
I resized the tablespace ANNEX with the following command:
ALTER DATABASE DATAFILE '/test/ANNEX.dbf' RESIZE 247921216K
but resize failed with following messages:
Failed to commit: ORA-03297: file contains used data beyond requested RESIZE value
so I queryed the segments and extends, and got the following results:
SQL> select segment_name,segment_type,extents,bytes from dba_segments where tablespace_name='ANNEX';
SEGMENT_NAME SEGMENT_TYPE EXTENTS BYTES
------------------------------------- -------------- --------- ----------
SYS_IL0000445585C00006$$ LOBINDEX 17 2097152
SYS_LOB0000445585C00006$$ LOBSEGMENT 210 1751121920
BIN$qRP3thbzAC7gU6wQAQIALg==$0 TABLE 83 100663296
BIN$qRP3thbyAC7gU6wQAQIALg==$0 INDEX 22 7340032
BIN$qRP3thbxAC7gU6wQAQIALg==$0 INDEX 24 9437184
SQL> select segment_name,extent_id,file_id,bytes from dba_extents where tablespace_name='ANNEX';
SEGMENT_NAME EXTENT_ID FILE_ID BYTES
-------------------------------------------------------------------------------- ---------- ---------- ----------
SQL>
would you please tell me why? from dba_segments I queryed some segment,but from the dba_extents I queryed nothing?
segments are made of some extents,but why i cann't query any extents?
Notice how many of the objects begin BIN$?
This means they're in the recyclebin. The extents view exclude these dropped objects. I suspect the LOBs were in this dropped table.
create tablespace tblsp datafile 'tblsp.dbf' size 10m;
create table t tablespace tblsp as
select level c1, sysdate c2, rpad ( 'x', 100, 'x' ) c3 from dual
connect by level <= 1000;
drop table t;
select segment_name, bytes from dba_extents
where tablespace_name = 'TBLSP';
no rows selected
select segment_name, bytes from user_segments
where tablespace_name = 'TBLSP';
SEGMENT_NAME BYTES
BIN$qUtmPEW0WdDgUwQAAArguA==$0 196608
select * from recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
BIN$qUtmPEW0WdDgUwQAAArguA==$0 T DROP TABLE TBLSP 2020-06-30:10:21:32 2020-06-30:10:21:33 8990240 <null> YES YES 82897 82897 82897 24
To recover the space, purge the recyclebin:
purge recyclebin;
select * from recyclebin;
no rows selected
select * from user_segments
where tablespace_name = 'TBLSP';
no rows selected