FAQ
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

Search Discussions

  • Herring Dave - dherri at Nov 27, 2011 at 4:30 am
    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@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@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
  • Jonathan Lewis at Nov 27, 2011 at 9:59 am
    Anurag,

    Dave Herring has already supplied a suitable example of a script.
    If you want some extra thoughts I wrote about the problem at the following URL:

    http://jonathanlewis.wordpress.com/2010/02/06/shrink-tablespace/

    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com


    ----- Original Message -----
    From: "Anurag Verma" <anuragdba@gmail.com>
    To: "ORACLE-L" <oracle-l@freelists.org>
    Sent: Sunday, November 27, 2011 3:48 AM
    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.

    --
    http://www.freelists.org/webpage/oracle-l
  • Denis at Nov 28, 2011 at 2:33 am
    Recently I have a need to reorganize a 7T database. We estimated we can save about 2-3 T after reorg. I used several scripts to guide the reorg, including the one to find the segments at the end of each datafile. The query was obtained from an Oracle-L member originally ( I forgot from who). the idea is to list the segment order by the max(block_id), the segment with high max(block_id) should be at the end of the datafile.  I blogged my experience and  scripts here: http://oracle-study-notes.blogspot.com/2011/11/reorganize-table-and-index.html. The reorg is still going on since we need several downtime to finally release the space at OS level.  Hope this helps.

    Denis


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Anurag Verma
    Sent: Saturday, November 26, 2011 10: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
    --
    http://www.freelists.org/webpage/oracle-l
  • Holvoet, Jo at Nov 28, 2011 at 8:47 am
    Something else to remember that I don't think anyone has mentioned yet : the recyclebin, if turned on, can also get in your way in these situations.

    Table sys.recyclebin$ will help you there (unless there's a better way ?) :

    select file# f,
    max(block# + space - 1) hwm
    from sys.recyclebin$
    group by file#


    Of course if you're reorganizing, just purging the recyclebin shouldn't be an issue.


    mvg / regards,
    Jo Holvoet


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Denis
    Sent: maandag 28 november 2011 3:28
    To: oracle-l@freelists.org; Anurag Verma
    Subject: Re: How to find out the segments (whether it is table or index) at the end of each datafile in a tablespace.

    Recently I have a need to reorganize a 7T database. We estimated we can save about 2-3 T after reorg. I used several scripts to guide the reorg, including the one to find the segments at the end of each datafile. The query was obtained from an Oracle-L member originally ( I forgot from who). the idea is to list the segment order by the max(block_id), the segment with high max(block_id) should be at the end of the datafile.  I blogged my experience and  scripts here: http://oracle-study-notes.blogspot.com/2011/11/reorganize-table-and-index.html. The reorg is still going on since we need several downtime to finally release the space at OS level.  Hope this helps.

    Denis


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Anurag Verma
    Sent: Saturday, November 26, 2011 10: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
    --
    http://www.freelists.org/webpage/oracle-l


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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 27, '11 at 3:49a
activeNov 28, '11 at 8:47a
posts5
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase