DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: ORA-03297: file contains used data beyond requested RESIZE value

  1. #1
    Join Date
    Nov 2000
    Posts
    440
    I need space on my disk, and i have a datafile that takes 6 gig. it contain only 3gig of data.
    I want to resize and it gives me:
    ORA-03297: file contains used data beyond requested RESIZE value

    I did an alter table move tablespace

    On all my table in the same tablespace.
    I did a rebuild on all my index.

    NOTE: I CANT DO A EXPORT/IMPORT because 24/24 database.


    1. What oracle table or view can tell me wich object is beyond resize value?

    2. I have also package, sequence, function and procedure, do i have to recreate them?

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    dba_extents

  3. #3
    Join Date
    Nov 2000
    Posts
    440
    WICH FIELD?

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    block_id?

  5. #5
    Join Date
    May 2002
    Posts
    193
    Would you mind giving the querry please..

    Regards,

    K.Diwakar

    Originally posted by pando
    block_id?

  6. #6
    Join Date
    Jul 2002
    Posts
    132
    column file_name format a50;
    column tablespace_name format a15;
    column highwater format 9999999999;
    set pagesize 9999

    select a.tablespace_name
    ,a.file_name
    ,(b.maximum+c.blocks-1)*d.db_block_size highwater
    from dba_data_files a
    ,(select file_id,max(block_id) maximum
    from dba_extents
    group by file_id) b
    ,dba_extents c
    ,(select value db_block_size
    from v$parameter
    where name='db_block_size') d
    where a.file_id = b.file_id
    and c.file_id = b.file_id
    and c.block_id = b.maximum
    order by a.tablespace_name,a.file_name
    /

    The query gives the high water mark for each of the file beyond which they cannot be reduced by size.

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    You can see all the objects and allocated extents in that datafile by using this query.
    Code:
    SELECT SEGMENT_NAME, SEGMENT_TYPE, EXTENT_ID, BLOCK_ID
    FROM DBA_EXTENTS 
    WHERE FILE_ID = n;
    BLOCK_ID is the starting block_id for the extent.

    Sanjay


    [Edited by SANJAY_G on 07-19-2002 at 05:44 AM]

  8. #8
    Join Date
    Jul 2002
    Posts
    132
    From Metalink

    Script to find database object locations for a given datafile.

    -----------CUT-----------------CUT-----------------CUT--------
    REM findext.sql
    REM
    REM This script prompts user for a datafile ID number, and
    REM then lists all the segments contained in that datafile,
    REM the blockid where it starts, and how many blocks the
    REM segment contains. It shows the owner, segment name, and
    REM segment type.
    REM
    REM Janet Robinson Stern April 2, 1997
    REM variation on Cary Millsap's script
    REM

    SET ECHO OFF
    ttitle -
    center 'Segment Extent Summary' skip 2

    col ownr format a8 heading 'Owner' justify c
    col type format a8 heading 'Type' justify c trunc
    col name format a28 heading 'Segment Name' justify c
    col exid format 990 heading 'Extent#' justify c
    col fiid format 9990 heading 'File#' justify c
    col blid format 99990 heading 'Block#' justify c
    col blks format 999,990 heading 'Blocks' justify c

    select
    owner ownr,
    segment_name name,
    segment_type type,
    extent_id exid,
    file_id fiid,
    block_id blid,
    blocks blks
    from
    dba_extents
    where
    file_id = &file_id
    order by
    block_id
    /

  9. #9
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Code:
    SELECT SEGMENT_NAME, SEGMENT_TYPE, BLOCK_ID
    FROM DBA_EXTENTS
    WHERE FILE_ID=n AND
    BLOCK_ID = (SELECT MAX(BLOCK_ID) FROM DBA_EXTENTS
    WHERE FILE_ID=n);
    Sanjay

  10. #10
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    May be DBMS_SPACE would be helpful.

    Write a small PL/SQL block using DBMS_SPACE package and get a list of tables having more than enough unused blocks.
    Then recover that space using ALTER TABLE ... DEALLOCATE UNUSED.


    Sameer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width