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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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