DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: resize datafile

  1. #1
    Join Date
    Mar 2002
    Posts
    303

    resize datafile

    I try to resize the datafile,but it give me an error :

    ora-03297:

    But, in fact, it shows enough space in the "used space section"

    Please could you give a further explanation

  2. #2
    Join Date
    Mar 2001
    Posts
    144
    Do a tablespace map and that will tell you where the data is stored within the datafiles.

    You may see enough space but is it contiguous space? I think that is what happening - you're trying to shrink the file but the data is fragmented throughout it.

    Try running this.

    ______________________________
    drop table contg_spc;
    create global temporary table contg_spc(
    tablespace_name CHAR(30),
    contiguous_bytes NUMBER)
    on commit preserve rows;

    DECLARE
    tsname char(30);

    cursor cur1 is
    select *
    from dba_free_space
    order by tablespace_name, block_id;

    cur1rec cur1%rowtype;
    next1rec cur1%rowtype;
    total number:=0;

    BEGIN
    open cur1;

    FETCH cur1 into cur1rec;
    total := total + cur1rec.bytes;

    LOOP
    FETCH cur1 into next1rec;
    exit when cur1%notfound;
    tsname := cur1rec.tablespace_name;

    if next1rec.block_id = cur1rec.block_id + cur1rec.blocks
    and next1rec.tablespace_name = tsname
    then
    total := total + next1rec.bytes;
    else
    insert into contg_spc values (cur1rec.tablespace_name, total);
    total := next1rec.bytes;
    end if;

    cur1rec := next1rec;
    END LOOP;

    INSERT into contg_spc values (cur1rec.tablespace_name, total);
    END;
    /

    set pagesize 60 echo off lines 80;

    ttitle center 'Contiguous Free Space Report' skip 1 center new_today skip 3;

    column tsname format a15 heading "Tablespace Name";
    column contg_bytes format 9,999,999,999 heading "Contigious Bytes";
    column byte_cnt format 99999999 heading "Bytes Count";
    column tot_bytes format 9,999,999,999 heading "Total #|Of Bytes";
    column todays_date noprint new_value new_today format a1;

    break on tsname skip;

    spool contigfree.lis

    select tablespace_name tsname,contiguous_bytes contg_bytes,count (*) byte_cnt,
    contiguous_bytes*count(*) tot_bytes,
    to_char(sysdate,'FMMonth DD, YYYY') todays_date
    from contg_spc
    group by tablespace_name, contiguous_bytes
    order by tablespace_name, contiguous_bytes desc;

    spool off;

    clear breaks;
    clear columns;
    ttitle off;
    ________________________________________

    It may help you in determining where within the datafile the data sits and how much contiguous space you have.

    If you are using OEM then you can run the tablespace map from the tools menu to tell you more precisely, visually, the fragmentation (ie, segment size, object, datafile, extent id, etc.). From there you can determine what you have to do or how far you can shrink your datafile.
    Last edited by ramaral; 12-10-2002 at 10:01 AM.

  3. #3
    Join Date
    Aug 2002
    Location
    Bangalore
    Posts
    52
    Your datafile may contain data beyond the RESIZE value.Try using a higher RESIZE value.It will work.



    Check DBA_FREE_SPACE_COALESED view for extent fragmentation.



    Joe

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Hi,
    Use this query to determine up to where you can resize (downwards):

    Code:
    column file_name format a40; 
    column highwater format 9999999999; 
    set pagesize 9999  
    select a.file_name,round((b.maximum+c.blocks-1)*d.db_block_size/(1024*1024)) highwaterMB
    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 
    /
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

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