Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2004
    Posts
    31

    Unanswered: ora-03297 can'tresize system01.dbf

    Hi ya!

    I tried to do the following because my db does no longer contain as much data as it once did... (see my previous post)

    alter database datafile 'c:\oracle\oradata\myDB\SYSTEM01.DBF' resize 1000M

    but what I got was this:
    ora-03297 file contain data that is beeing used. ouside interval for resize value.

    the system01.dbf is bigger than 5GB wich it definetly shouldn't be...

    any ideas?
    thanks

  2. #2
    Join Date
    Mar 2004
    Posts
    205
    Yes, I too got this error :

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

    Please anyone advise on this.

    Thanks,
    Sam

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The highwater mark on the file is higher then what you want to resize it to. For example, say you had 5 gig in your system tablespace. You then removed a number of tables that contained 2 gig. That doesn't mean that you can get rid of 2 gig of storage, it means that you can get rid of storage to where the highest extent for a table/index resides in the datafile. If an extent is left at the 4.8 gig mark, then you can only go done to 4.8 gig. That being said, You must NEVER use the system schema for storage of user data. Never, Never, Never!!!!. The system schema should grow very slowely as new data objects are built in the database. It should contain no active rollback segments. If you need to make non system datafiles, you must make a new tablespace!!!!
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Mar 2004
    Posts
    205
    Hi,

    I created a new tablespace and I altered my database to this tablespace, but when I compiled my Oracle PL/SQL file , it gives the following error :

    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01653: unable to extend table SYS.IDL_UB1$ by 16 in tablespace SYSTEM

    Please advise,

    Thanks,
    Sam

  5. #5
    Join Date
    Apr 2004
    Posts
    31
    allright... screw it then... I gues I can live without that few gigs... however... you said that the system tablespace will grow slowly... will it continue to grow or will the new data kinda obsorb within the already large file...

    thanks for, as always, a goog reply beilswidth... or what ever the hell your name is...

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What do you mean that you "altered your database to this tablespace" Your System database is for use by System, SYS and other system processes. Your new tablespace would be for use by new users. For example.

    create user samcute identified by password default tablespace my_new_tablespace temporary tablespace my_temp_tablespace;
    grant connect to samcute;
    Last edited by beilstwh; 08-30-04 at 13:05.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by Zcumbag
    allright... screw it then... I gues I can live without that few gigs... however... you said that the system tablespace will grow slowly... will it continue to grow or will the new data kinda obsorb within the already large file...

    thanks for, as always, a goog reply beilswidth... or what ever the hell your name is...
    My name is Bill (beilstwh is a mixture of my first/middle/last name). The released extents will be reused by the system dataspace before it tries to grow larger.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Oct 2009
    Posts
    5
    Hi folks. Hope that you have already resolved the issue, but anyway. I can recommend trying the sql server data file secondary restore program that automatically eliminates dbf errors. It is a good solution, if you experience difficulties with dbx files. Hope this helps

  9. #9
    Join Date
    Aug 2009
    Posts
    262
    sql server ??? lol

    okey

    The following query will give you the minimum file size (in MB) you can

    resize to for the datafiles of the SYSTEM tablespace.
    This may not solve your problem but at least you can stop the

    trial-and-error method.

    select a.file_id, max((a.block_id + a.blocks) * b.value) / (1024 * 1024)
    from dba_extents a, v$parameter b
    where a.tablespace_name = 'SYSTEM'
    and b.name = 'db_block_size'
    group by a.file_id;


    This is the excerpt from Oracle's Error Messages
    manual:

    ORA-03297 file contains used data beyond requested
    RESIZE value

    Cause: Some portion of the file in the region to be
    trimmed is currently in use by a database object.

    Action: Drop or move segments containing extents in
    this region prior to resizing the file, or choose a
    resize value such that only free space is in the
    trimmed.

    Basically, what it means is that even you only use
    55MB of 2.5GB space now, the SYSTEM tablespace had
    once used more than the new size you tried to resize
    (maybe due to the update to that user table).

    So, you can try to resize again with a larger
    value. If the new value is still too big to you and
    you really want to make it smaller, then the only way
    I can think of is to use Export/Import the SYSTEM
    tablespace (not the user table) which will compress
    the extents by default.

  10. #10
    Join Date
    Aug 2009
    Posts
    262
    ORA-03297 on empty datafile
    In Oracle 10gR2 (10.2.0.3) I play this test case . i did few things and when resizing I was getting ORA-03297: file contains used data beyond requested RESIZE value while DBA_EXTENTS show no rows for that file. I've discovered that there are objects in Recycle Bin that were located on that file. After purging recycle bin file was shirked successfully.
    Let me make an example:

    SQL> create tablespace tb_test
    2 datafile 'c:\temp\tb_test.dbf' size 100 K autoextend on;
    Tablespace created.
    SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
    COUNT(*)
    ----------
    0
    SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
    BYTES
    ----------
    106496
    SQL> create table t_big tablespace tb_test as
    2 select a.* from all_objects a, dba_users for_cartesian;
    Table created.
    SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
    COUNT(*)
    ----------
    34
    SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
    BYTES
    ----------
    19963904

    Now we have datafile quite big. Let's drop table and shrink file:

    SQL> drop table t_big;
    Table dropped.
    SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
    COUNT(*)
    ----------
    0
    SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
    alter database datafile 'c:\temp\tb_test.dbf' resize 100 K
    *
    ERROR at line 1:
    ORA-03297: file contains used data beyond requested RESIZE value

    So. even there is nothing reported in DBA_EXTENT, file cannot be shinked. Let's purge recyclebin and try again:

    SQL> purge recyclebin;
    Recyclebin purged.
    SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
    Database altered.



    SQL> drop tablespace tb_test including contents and datafiles;

  11. #11
    Join Date
    Aug 2009
    Posts
    262
    incase you cannot do imp/export due to a 24/7 OLTP database .

    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


    ===========

    You can see all the objects and allocated extents in that datafile by using this query.

    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);


    BLOCK_ID is the starting block_id for the extent.


    ===================================

    From oracle Metalink

    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
    /


    ===================

    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.

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Is there a reason someone suddenly started to write to a 5 year old thread?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  13. #13
    Join Date
    Oct 2009
    Posts
    5
    but it is not closed yet?

  14. #14
    Join Date
    Aug 2009
    Posts
    262
    08-30-04, 12:34
    Zcumbag Zcumbag is offline
    Registered User

    speachless:O

Posting Permissions

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