1 2 Previous Next 17 Replies Latest reply: Nov 16, 2012 9:29 AM by j_DBA_sourav RSS

    Not able to reduce the size of Datafile

    j_DBA_sourav
      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....
          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
            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
              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
                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
                  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....
                    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
                      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-Oracle
                        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
                          :-P sorry for the spelling mistake.. :(
                          • 10. Re: Not able to reduce the size of Datafile
                            j_DBA_sourav
                            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....
                              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
                                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
                                  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
                                    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