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

Thread: resize datafile

  1. #1
    Join Date
    Mar 2002

    resize datafile

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


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

    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;

    open cur1;

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

    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
    total := total + next1rec.bytes;
    insert into contg_spc values (cur1rec.tablespace_name, total);
    total := next1rec.bytes;
    end if;

    cur1rec := next1rec;

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

    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
    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.


  4. #4
    Join Date
    Jun 2001
    Helsinki. Finland
    Use this query to determine up to where you can resize (downwards):

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