This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Nov 16, 2012 7:29 AM by j_DBA_sourav RSS

Not able to reduce the size of Datafile

j_DBA_sourav Newbie
Currently Being Moderated
Hello all,
I am using Oracle Database in 11.2.0.2 and in ASM instance. Today I noticed that disk usage is almost full in my disc groups. I then thought to reduce the size of big Tablespace datafiles's size.

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name='FRARDTA9T';

FILE_NAME BYTES/1024/1024
------------------------------------------------------------------------- -------------------------------------
+DATAJDFSWM/t1erp90d/datafile/frardta9t01.dbf                81000

alter database datafile '+DATAJDFSWM/t1erp90d/datafile/frardta9t01.dbf' resize 40000M;

I am getting the following error.


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

Here is the output for DBA_FREE_SPACE


SQL> select * from dba_free_space where tablespace_name='FRARDTA9T';

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
-------------------------------------------- ---------- -------------- ---------- ----------- -----------------
FRARDTA9T 104 97728 5767168 704 1024
FRARDTA9T 104 189016 4521984 552 1024
FRARDTA9T 104 277016 5046272 616 1024
FRARDTA9T 104 277680 655360 80 1024
FRARDTA9T 104 1630336 3288334336 401408 1024
FRARDTA9T 104 2031744 4160749568 507904 1024
FRARDTA9T 104 2539648 4160749568 507904 1024
FRARDTA9T 104 3047552 4160749568 507904 1024
FRARDTA9T 104 3555456 4160749568 507904 1024
FRARDTA9T 104 4063360 4160749568 507904 1024
FRARDTA9T 104 4571264 4160749568 507904 1024
FRARDTA9T 104 5079168 4160749568 507904 1024
FRARDTA9T 104 5587072 1543503872 188416 1024
FRARDTA9T 104 5775616 2616197120 319360 1024
FRARDTA9T 104 6094976 4160749568 507904 1024
FRARDTA9T 104 6637472 2803630080 342240 1024
FRARDTA9T 104 7550488 558694400 68200 1024
FRARDTA9T 104 7618688 4160749568 507904 1024
FRARDTA9T 104 8126592 4160749568 507904 1024
FRARDTA9T 104 8634496 4160749568 507904 1024
FRARDTA9T 104 9142400 4160749568 507904 1024
FRARDTA9T 104 9650304 4160749568 507904 1024
FRARDTA9T 104 10223520 786432 96 1024

Please suggest me how to fix this issue.. Is fragmentation the culprit for not letting me freeing up space?

-Saha
  • 1. Re: Not able to reduce the size of Datafile
    Aman.... Oracle ACE
    Currently Being Moderated
    I would suggest that you run this script from Tom Kyte mentioned by Uwe,
    set verify off
    column file_name format a50 word_wrapped
    column smallest format 999,990 heading "Smallest|Size|Poss."
    column currsize format 999,990 heading "Current|Size"
    column savings  format 999,990 heading "Poss.|Savings"
    break on report
    compute sum of savings on report
     
    column value new_val blksize
    select value from v$parameter where name = 'db_block_size'
    / 
     
    select file_name,
           ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
           ceil( blocks*&&blksize/1024/1024) currsize,
           ceil( blocks*&&blksize/1024/1024) -
           ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
    from dba_data_files a,
         ( select file_id, max(block_id+blocks-1) hwm
             from dba_extents
            group by file_id ) b
    where a.file_id = b.file_id(+)
    / 
     
    column cmd format a75 word_wrapped
     
    select 'alter database datafile '''||file_name||''' resize ' ||
           ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
    from dba_data_files a,
         ( select file_id, max(block_id+blocks-1) hwm
             from dba_extents
            group by file_id ) b
    where a.file_id = b.file_id(+)
      and ceil( blocks*&&blksize/1024/1024) -
          ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
    This would tell you that whether you really can resize the datafile of yours or not! The datafile can't be resized lesser than the HWM of it and that's the message you are getting too.

    Aman....

    Source of the file:- Re: recover space
  • 2. Re: Not able to reduce the size of Datafile
    Oviwan Pro
    Currently Being Moderated
    as the tablespace is maybe fragmented you have to defragment it to release some space. there are several features to do this more or less efficient.

    -alter table/index move
    -exp/imp
    -shrink space
    -dbms_redefinition
    -ctas

    each technologie has pros and cons...
    i suggest you to start with shrink space, but don't start with the biggest objects...

    hth
  • 3. Re: Not able to reduce the size of Datafile
    KrishnaAgnihotri Explorer
    Currently Being Moderated
    Hi
    Kindly refer the below mention doc and links. how this may help you.

    Note: 115586.1 How to Deallocate Unused Space from a Table, Index
    Note: 77635.1 How to Determine Real Space used by a Table

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/schema003.htm
    http://www.oracle-base.com/articles/misc/ReclaimingUnusedSpace.php
    http://www.dba-oracle.com/t_reclaiming_disk_space.htm



    Thanks
    Krishna
  • 4. Re: Not able to reduce the size of Datafile
    j_DBA_sourav Newbie
    Currently Being Moderated
    thnx everyone..
    I will try this definitely tomorrw and let you know if any of these works..

    -regards,
    saha
  • 5. Re: Not able to reduce the size of Datafile
    j_DBA_sourav Newbie
    Currently Being Moderated
    Aman,

    I got the list of the datafiles, those were afforded to be resized but the tablespace I mentioned above is not there in the list. Actually that tablespace went through a heavy truncate and loading of data. Mainly it is used for archiving tool. This tablespace is still not reduced and huge amount space is lost. When Application truncates table why HMW is not getting reduced? What could be the reason behind not letting me to re-size the datafile.

    Out of 90Gb of space only 19 GB is now utilized.

    -Regards,
    Saha
  • 6. Re: Not able to reduce the size of Datafile
    Aman.... Oracle ACE
    Currently Being Moderated
    If you have already touched the HWM(High Water Mark) of the used space,you won't be allowed to resize teh datafile beyond that size. The space allocated to the objects within the datafile is not in one go so even if you would delete, it won't ensure that you would get the space given back to you and I think that's what you are seeing right now as well.

    Aman....
  • 7. Re: Not able to reduce the size of Datafile
    j_DBA_sourav Newbie
    Currently Being Moderated
    Hi,

    Thank you for your help.

    I tried shrink space;

    first I enable row movement. Then I shrink table space;

    Alter table MYSCHEMA.MY_TAB ENABLE ROW MOVEMENT ;

    alter table MYSCHEMA.MY_TAB shrink space casacde;

    for few tables I got error.

    ERROR at line 1:
    ORA-10630: Illegal syntax specified with SHRINK clause

    *Cause: An illegal option was specified with the SHRINK clause
    *Action: Verify the SQL Reference Manual and reissue the command


    can you help I am not getting the reason for this.
  • 8. Re: Not able to reduce the size of Datafile
    UweHesse Expert
    Currently Being Moderated
    Instead of casacde it should be cascade

    Kind regards
    Uwe Hesse

    http://uhesse.wordpress.com
  • 9. Re: Not able to reduce the size of Datafile
    j_DBA_sourav Newbie
    Currently Being Moderated
    :-P sorry for the spelling mistake.. :(
  • 10. Re: Not able to reduce the size of Datafile
    j_DBA_sourav Newbie
    Currently Being Moderated
    Hello Aman,

    Thank you for your previous reply.

    In another database (for BI) I saw a strange incident. There was a table where 2.5 TB of space was allocated. After so much of huge size we saw no data present there. I understand that could be due to HWM. After I truncated the table I could shrink the segment and by enabling row movement and shrinking the space.

    SQL>select bytes from dba_segments where owner='SOME_SCHEMA' and segment_name='SPACEGUILT_TABLE'

    bytes
    ---------
    65536

    The tablespace size 2,975,500M and your script is giving me the result like below:

    FILE_NAME Poss. Size Savings
    -------------------------------------------------- -------- -------- --------

    +file_name                           ######## ########    8,682

    alter database datafile 'file_name' resize 2966818m;

    I saw the main space guilt table is not eating size now. How this amount of size being empty I can't resize my tablespace.? I have space constraint on that server

    I should have the elegibility of gaining more than 80% space here.

    Neither I can rebuild the table...
    getting error:
    ORA-01658: unable to create INITIAL extent for segment in tablespace TBS_ODS_ARCHI2T

    Please suggest ASAP.

    -Saha.
  • 11. Re: Not able to reduce the size of Datafile
    Aman.... Oracle ACE
    Currently Being Moderated
    For ASAP, please raise a Serv1 SR to oracle support. This is a discussion forum and all the threads are of equal importance here.

    For your issue, it seems that Seems like that you have touched the limit of file space usage. Use the Segment Advisor and see what other objects can be defragmented to give you some more space.

    Aman....
  • 12. Re: Not able to reduce the size of Datafile
    j_DBA_sourav Newbie
    Currently Being Moderated
    Hi Aman,

    First of all sorry for writing ASAP :(

    Now, coming to your point, I did not get it .. Can you please elaborate it a little? Tablespace usage is 13% now as I described before.. Now, what could be the reason, after shrinking all the segments also I am not able to reduce the tablesapce nor I am able to do alter table <> move ; command.

    Please help and would you mind to give me an example please?

    -Saha
  • 13. Re: Not able to reduce the size of Datafile
    sb92075 Guru
    Currently Being Moderated
    j_DBA_saha wrote:

    SQL> select * from dba_free_space where tablespace_name='FRARDTA9T';
    SELECT owner,
           segment_name
    FROM   dba_extents
    WHERE  tablespace_name = 'FRARDTA9T'
           AND block_id = (SELECT MAX(block_id)
                           FROM   dba_extents
                           WHERE  tablespace_name = 'FRARDTA9T');  
    post results from SQL above
  • 14. Re: Not able to reduce the size of Datafile
    j_DBA_sourav Newbie
    Currently Being Moderated
    Hello sb92075 ,

    The select * from dba_free_space where tablespace_name='MY TABLESPACE' is retruning almost 5.5K records.. as given below.

    select count(*) from dba_free_space where tablespace_name='TABLESPACE_NAME'

    count(*)
    ----------------
    5456

    SQL> SELECT owner,segment_name FROM dba_extents WHERE tablespace_name = TABLESPACE_NAME' AND block_id (SELECT MAX(block_id) FROM dba_extents WHERE tablespace_name = 'TABLESPACE_NAME');

    OWNER SEGMENT_NAME
    ------------------------------ ---------------------------------------------------------------------------------
    USER1 ODS_JDE_F0902_BKP

    -Regards,
    Saha
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points