Dba_segment and dba_extend query results-CodePudding

Environment: oracle10.2.0.1 64 - bit
Problem description:
Test environment has a table space named ANNEX, corresponding to the file size is 331.8 GB, because the space of the data has been derived, the table also let developers removed,

By using the following SQL statements show table space utilization & lt; 1%

SELECT a.tablespace_name "表空间名称", total/(1024 * 1024) "表空间大小(M)", free/(1024 * 1024) "表空间剩余大小(M)", (total - free)/(1024 * 1024 ) "表空间使用大小(M)", total/(1024 * 1024 * 1024) "表空间大小(G)", free/(1024 * 1024 * 1024) "表空间剩余大小(G)", (total - free)/(1024 * 1024 * 1024) "表空间使用大小(G)", round((total - free)/total, 4) * 100 "使用率 %" FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name=b.tablespace_name

Now needs to be released this table space capacity, therefore, perform the following command:

The ALTER DATABASE DATAFILE '/oradata hbmisdb/ANNEX. DBF' RESIZE 247921216 k

Results suggest that Failed to commit: ORA - 03297: file contains 2 data beyond requested the RESIZE the value

Attempt by dba_extents and dba_segments query what are in this table space object, the query results are as follows:

 SQL> The 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 24 9437184 $qRP3thbxAC7gU6wQAQIALg==$0 INDEX

And through dba_segments query result is:

 SQL> The select segment_name, extent_id file_id, bytes from dba_extents where tablespace_name='ANNEX'; 

SEGMENT_NAME EXTENT_ID FILE_ID BYTES 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

The query result is empty,

Here would like to ask is:

1. Why do not check dba_extents content while dba_segments can find some objects?

Segment is composed of multiple among, why dba_extents but not any content?

2. Why is this table space utilization & lt; 1% is unable to shrink?

CodePudding user response:

1. Why do not check dba_extents content while dba_segments can find some objects?

BIN $at the beginning of section, table is deleted, currently stored in the recycle BIN, can use the purge command to remove them,

2. Why is this table space utilization & lt; 1% is unable to shrink?

Can only shrink to the last extents location,

CodePudding user response:

Thank you moderator to solve, there are still two points is not clear to
1. From the point of the query results of dba_segments, except in the recycle bin's index, and lobsegment lobindex type section, but why not any content from dba_extents?

2. I want to by dba_extents query the location of the last extents, but dba_extents query result is empty,
Further queries the size of the existing period, the results are as follows:

 SQL> Select segment_name, segment_type, extents, initial_extent MB_of_initial_extent/1024/1024, 1024/1024 next_extent/MB_of_next_extent, max_extents/1024/1024 MB_of_max_extents, bytes/1024/1024/1024 GB from dba_segments where tablespace_name='ANNEX'; 

SEGMENT_NAME SEGMENT_TYPE EXTENTS MB_OF_INITIAL_EXTENT MB_OF_NEXT_EXTENT MB_OF_MAX_EXTENTS GB 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
SYS_IL0000445585C00006 $$17 LOBINDEX 0.0625 2047.99999713898 0.00195312 
SYS_LOB0000445585C00006 $$LOBSEGMENT 210 0.0625 2047.99999713898 1.63085937 
BIN $qRP3thbzAC7gU6wQAQIALg==$0 TABLE 83 0.0625 2047.99999713898 0.09375 
BIN 22 $qRP3thbyAC7gU6wQAQIALg==$0 INDEX 0.0625 2047.99999713898 0.00683593 
BIN $24 0.0625 2047.99999713898 0.00878906 qRP3thbxAC7gU6wQAQIALg==$0 INDEX

But from the results don't see the location of the last extents, there are other ways?

CodePudding user response:

In the recycle bin, to empty the recycle bin, and then have a look

CodePudding user response:

refer to the second floor hanfeier2012 response:

thank moderator solutions, there are two still don't quite understand
1. From the point of the query results of dba_segments, except in the recycle bin's index, and lobsegment lobindex type section, but why not any content from dba_extents?

2. I want to by dba_extents query the location of the last extents, but dba_extents query result is empty,
Further queries the size of the existing period, the results are as follows:
 SQL> nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull           