Skip to Main Content
  • Questions
  • why dba_extents has no answer but dba_segments has results

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: June 30, 2020 - 6:30 am UTC

Answered by: Chris Saxon - Last updated: June 30, 2020 - 10:23 am UTC

Category: Database Administration - Version: 10.2.0.1

Viewed 100+ times

You Asked

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?



and we said...

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


More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database