11 Replies Latest reply: Nov 8, 2005 9:27 AM by Laurent Schneider RSS

    How to Shrink Tablespace / Datafile.

    442663
      Hi

      I have a very heavy table in a datafile, I drop table but datafile don’t release extra disk space.

      Please advise how to Shrink tablespace/ datafile.

      Wishes
      J a w a d
        • 1. Re: How to Shrink Tablespace / Datafile.
          Billy~Verreynne
          I do not believe this is the right forum for your question. It does not deal with either SQL or PL/SQL - which are the subjects of this forum.

          You also neglected to provide any additional information such as Oracle version and whether or not you're using LMTs, auto extending datafiles, and so on.

          The very basic reason for a datafile not to shrink is that there are allocated datablocks at the end of the datafile (e.g. 10GB of unused space followed by 1 8KB data block marked in used).

          However, the correct answer could just as well be that you will never see that "freed space" on the o/s filesystem as you are mistaken in how Oracle space management works and what Oracle freelists are.
          • 2. Re: How to Shrink Tablespace / Datafile.
            almity
            ALTER DATABASE
            DATAFILE 'disk1:db1.dat' RESIZE 10 M;


            Use the above SQL.

            Replace with the file name of your datafile and the size of datafile

            Rgds,
            Manohar
            • 3. Re: How to Shrink Tablespace / Datafile.
              442663
              Thanks Manohar

              Will you please also tell me how to see the objects within a datafile + there size (to get the min size to RESIZE ).

              Wishes
              Jawad
              • 4. Re: How to Shrink Tablespace / Datafile.
                445055
                First anylise the object and use user_objects or dba_objects to get size.
                • 5. Re: How to Shrink Tablespace / Datafile.
                  Michel SALAIS
                  Take a look to dba_extents (not user_extents) and all is there! As an alternative, you can look at dba_free_space.
                  • 6. Re: How to Shrink Tablespace / Datafile.
                    USER101
                    Jawad,

                    can you tell the following ?

                    1. What tablespaces do you use ? Local or dictionary managed
                    2. What version of Oracle do you use ?

                    If you are on 10g, you could use

                    Alter table table_name shrink or compact based on the load on the database. But unless you furnish complete information, it would be very difficult to answer.

                    Thanks.
                    • 7. Re: How to Shrink Tablespace / Datafile.
                      396018
                      here is an example.
                      SQL> set linesize 150
                      SQL> column file_name format a50
                      SQL> column tablespace_name format a10
                      SQL> column free_space format 9999.9999
                      SQL>
                      SQL> select ddf.file_name
                        2        ,ddf.tablespace_name
                        3        ,sum(dfs.bytes)/1024/1024 free_space
                        4  from dba_data_files ddf, dba_free_space dfs
                        5  where ddf.file_id = dfs.file_id
                        6    and ddf.tablespace_name like 'USERS'
                        7  group by ddf.file_name,ddf.tablespace_name
                        8  /

                      FILE_NAME                                          TABLESPACE FREE_SPACE                                                                             
                      -------------------------------------------------- ---------- ----------                                                                             
                      D:\DEV9IDB\ORADATA\DEV9I\USERS01.DBF               USERS         23.4375                                                                             
                      • 8. Re: How to Shrink Tablespace / Datafile.
                        Michel SALAIS
                        Hi Raghu,

                        what is important for the poster of this thread is not the sum of free space. Rather, it is the last free extent if there is no used extents behind! Only then he can truncate the file using the found value.
                        • 9. Re: How to Shrink Tablespace / Datafile.
                          396018
                          Michel, excuse me.
                          My mistake.
                          pls ignore my post.
                          • 10. Re: How to Shrink Tablespace / Datafile.
                            442663
                            Thanks to all

                            I have resized my datafiles. I found OEM’s GUI the best way to find free space.

                            But

                            Some file get resize very gently from following statement

                            ALTER DATABASE
                            DATAFILE 'disk1:db1.dat' RESIZE 10 M;

                            Others are not ready to loss a single M space. (Give error like “got more data ….” Sorry don’t remember the exact wordings)

                            I got some ambiguity but I personal feel that some files have free space at end and got resize easily other have free spaces in middle and not got resize.

                            If this is the case tell me how to deal with it. If this is not the case can any expert tell me the reasons why file not got resized?

                            Wishes
                            Jawad
                            • 11. Re: How to Shrink Tablespace / Datafile.
                              Laurent Schneider
                              well, resize would not released space below the high water mark. To really gain space, you should move all your tables to a new tablespace, drop the original tablespace and rename the new tablespace to the original name.