RE: How to find out the segments (whether it is table or index) at the end of each datafile in a tablespace.

  • From: Herring Dave - dherri <Dave.Herring@xxxxxxxxxx>
  • To: "anuragdba@xxxxxxxxx" <anuragdba@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 27 Nov 2011 04:29:37 +0000


Fortunately for you the answer doesn't depend on which version of Oracle you're 
running, which I assume is why you didn't include it in your post.

DBA_EXTENTS is your friend in this situation.  You can a very simple cursor to 
run against this view to list out segments whose extents are holding blocks 
with the highest BLOCK_ID value per datafile.  I ran into a few situations 
where I wanted to see a little more detail, including size of pockets of free 
space, so I made that simple query rather complex, so if it helps I've listed 
it below.  Just call it in sqlplus passing the TABLESPACE_NAME and FILE_ID of 
the datafile you're interested in and this will produce an extent map, with the 
HWM listed first descending to the "start" of the datafile.

The code is a bit more complex as I've rolled up consecutive extents into 
chunks, including listing "free space" the same way, so that you get a better 
idea of the impact of moving segments, plus I found it easier to read.  One 
more thing - the code assumes a 16 KB block size.  I was too lazy to join to 
DBA_TABLESPACES to get the block size.



   v_prior_file_name    VARCHAR2(513);
   v_prior_owner        VARCHAR2(30);
   v_prior_segment      VARCHAR2(30);
   v_segment_kb         NUMBER := 0;
   v_start_kb           NUMBER := 0;
   DBMS_OUTPUT.PUT_LINE(RPAD('.', 60, ' ') || '   Start Pos    Length');
   DBMS_OUTPUT.PUT_LINE(RPAD('Owner and Segment Name', 60) || '        (KB)     
   DBMS_OUTPUT.PUT_LINE(RPAD('-', 60, '-') || ' ----------- ---------');
   FOR c_extents IN (SELECT e.*, d.file_name
                       FROM dba_data_files d
                          , (SELECT owner, segment_name, file_id, (block_id * 
16) start_kb
                                  , ((block_id + blocks - 1) * 16) end_kb
                               FROM dba_extents
                              WHERE tablespace_name = '&TABLESPACE'
                                AND file_id = &FILE_ID
                                    UNION ALL
                             SELECT '*****Free', 'Space*****', file_id, 
(block_id * 16) start_kb
                                  , ((block_id + blocks - 1) * 16) end_kb
                               FROM dba_free_space
                              WHERE tablespace_name = '&TABLESPACE'
                                AND file_id = &FILE_ID) e
                      WHERE d.file_id = e.file_id
                      ORDER BY start_kb DESC)
       * If the segment is the same as the previous, continue adding it's space.
      IF c_extents.owner = v_prior_owner AND c_extents.segment_name = 
         v_segment_kb := v_segment_kb + (c_extents.end_kb - c_extents.start_kb);
          * If v_prior_owner IS NULL, then this is the first row read.
         IF v_prior_owner IS NOT NULL
            DBMS_OUTPUT.PUT_LINE(RPAD(v_prior_owner || '.' ||v_prior_segment, 
60) || ' ' ||
                                 TO_CHAR(v_start_kb, '9999999999') || ' ' ||
                                 TO_CHAR(v_segment_kb, '99999999'));
         END IF;
         v_start_kb := c_extents.start_kb;
         v_prior_file_name := c_extents.file_name;
         v_prior_owner := c_extents.owner;
         v_prior_segment := c_extents.segment_name;
         v_segment_kb := (c_extents.end_kb - c_extents.start_kb);
      END IF;
   DBMS_OUTPUT.PUT_LINE(RPAD(v_prior_owner || '.' ||v_prior_segment, 60) || ' ' 
                             TO_CHAR(v_start_kb, '9999999999') || ' ' ||
                             TO_CHAR(v_segment_kb, '99999999'));

Acxiom Corporation
EML   dave.herring@xxxxxxxxxx
TEL    630.944.4762
MBL   630.430.5988 
1501 Opus Pl, Downers Grove, IL 60515, USA

The information contained in this communication is confidential, is intended 
only for the use of the recipient named above, and may be legally privileged. 
If the reader of this message is not the intended recipient, you are hereby 
notified that any dissemination, distribution or copying of this communication 
is strictly prohibited. If you have received this communication in error, 
please resend this communication to the sender and delete the original message 
or any copy of it from your computer system. Thank you.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Anurag Verma
Sent: Saturday, November 26, 2011 9:49 PM
Subject: How to find out the segments (whether it is table or index) at the end 
of each datafile in a tablespace.

how to find out the segments (whether it is table or index) at the end of
each datafile in a tablespace.
I am trying to reduce the sizes of several datafiles, but getting the below
error message.

alter database datafile '/u01/oradata/testdb/users01.dbf' resize 12415139840

ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

If I can find out the segments, I can move them to another tablespace.

I know, in OEM, we can use tablespace map and find out different segments.

Unfortunately, for this database I have only sqlplus to check the data

No Toad or Grid/database control :(


Anurag Verma,
Database Administrator
ERCOT(Electric Reliability Council of Texas),
Texas 76574



Other related posts: