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_at_acxiom.com>
Date: Sun, 27 Nov 2011 04:29:37 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB4454CD90B_at_LITIGMBCRP02.Corp.Acxiom.net>

Anurag,

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.

SET ECHO off FEEDBACK off HEADING on NUMWIDTH 10

DEFINE TABLESPACE='&1';
DEFINE FILE_ID=&2 DECLARE

   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;

BEGIN

   DBMS_OUTPUT.PUT_LINE(RPAD('.', 60, ' ') || '   Start Pos    Length');
   DBMS_OUTPUT.PUT_LINE(RPAD('Owner and Segment Name', 60) || '        (KB)      (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)
   LOOP
      /*
       * 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_prior_segment
      THEN
         v_segment_kb := v_segment_kb + (c_extents.end_kb - c_extents.start_kb);
      ELSE
         /*
          * If v_prior_owner IS NULL, then this is the first row read.
          */
         IF v_prior_owner IS NOT NULL
         THEN
            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;

   END LOOP;
   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;
/

DAVID HERRING
DBA
Acxiom Corporation

EML   dave.herring_at_acxiom.com
TEL    630.944.4762
MBL   630.430.5988 

1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Anurag Verma Sent: Saturday, November 26, 2011 9:49 PM To: ORACLE-L
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 dictionary.

No Toad or Grid/database control :(

-- 

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


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Sat Nov 26 2011 - 22:29:37 CST