13 Replies Latest reply: Jan 10, 2013 3:03 AM by Jonathan Lewis RSS

    Tablespace Fragmentation. specially in SYSTEM tablesapce.

    VIRU
      Hi,

      I am working on Oracle 11gR2 DB and AIX OS.

      An database is having many tablespaces which are allocated to specific schemas. These schemas are functional from a long time. Data comes and goes all the time. The size of the tablespace has gone very high(than expected). We have removed(truncated) most of the unwanted tables and data from the schema and trying to resize the datafile, but we are not able to do it.

      while checking with the below query :-
      SELECT   df.tablespace_name "Tablespace",
             df.bytes / (1024 * 1024) "Size (MB)",
             SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
             Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
             Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
        FROM dba_free_space fs,
             (SELECT tablespace_name,SUM(bytes) bytes
                FROM dba_data_files
               GROUP BY tablespace_name) df
       WHERE fs.tablespace_name (+)  = df.tablespace_name
       GROUP BY df.tablespace_name,df.bytes
      UNION ALL
      SELECT df.tablespace_name tspace,
             fs.bytes / (1024 * 1024),
             SUM(df.bytes_free) / (1024 * 1024),
             Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
             Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
        FROM dba_temp_files fs,
             (SELECT tablespace_name,bytes_free,bytes_used
                FROM v$temp_space_header
               GROUP BY tablespace_name,bytes_free,bytes_used) df
       WHERE fs.tablespace_name (+)  = df.tablespace_name
       GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
       ORDER BY 4 DESC;
       
      The output is :-
      tablespace name         size_MB    free_space_MB     %free  %used
        SYSTEM                34256     28941.375        84            16
         SMD                      7975     2991.1875        38            62
      .................N many other 
      Here if i try to resize the SMD tablespace datafile to (7975-2991.1875) 4983 MB it does not allow me to do it.
      Also the SYSTEM tablespace has gone so big but used space is very less.

      I suspect that fragmentation is the problem here._

      Please guide me what could i do in these case to reduce the space taken by the datafiles.

      Thanks in advance.
        • 1. Re: Tablespace Fragmentation. specially in SYSTEM tablesapce.
          moreajays
          Hi VIRU ,

          Once datafile extents are used it can be re-sized/released by alter database datafile resize command, although you can reclaim the space if you remove all dependency of that tablespace/datafile e.g schema deletion.

          Thanks,
          Ajay More
          http://moreajays.blogspot.com
          • 2. Re: Tablespace Fragmentation. specially in SYSTEM tablesapce.
            Jonathan Lewis
            VIRU wrote:

            I am working on Oracle 11gR2 DB and AIX OS.
            tablespace name         size_MB    free_space_MB     %free  %used
            SYSTEM                34256     28941.375        84            16
            SMD                      7975     2991.1875        38            62
            .................N many other 
            You may have trouble if you're not using locally managed tablespaces, and even if you are the SYSTEM tablespace may give you some problems because of the limitations on moving or rebuilding some of the objects that it contains. Your first step, however, is to find out what's where in the tablespaces. There is a screen on OEM somewhere to help you with this, otherwise there are a couple of items on my blog (article and script) that may help: http://jonathanlewis.wordpress.com/2010/02/06/shrink-tablespace/

            This type of restructuring shouldn't be undertaken casually, though. It can be a lot of work for what turns out to be very little benefit - and it can sometimes have a nasty impact on performance.

            Regards
            Jonathan Lewis
            • 3. Re: Tablespace Fragmentation. specially in SYSTEM tablesapce.
              Niket Kumar
              try for 4000MB may be it will work.
              • 4. Re: Tablespace Fragmentation. specially in SYSTEM tablesapce.
                VIRU
                Thanks for your reply.

                I have used this command :-
                alter database datafile '/data01/oradata/NGPIUT/NGPIUT/system01.dbf' resize 20G
                 
                ORA-03297: file contains used data beyond requested RESIZE value
                but i face error as above.

                When i execute the query to check the free space in an tablespace, I can see much more "MB" space is free.
                                 total_space free_space
                SYSTEM     34256     28941.375     84     16
                • 5. Re: Tablespace Fragmentation. specially in SYSTEM tablesapce.
                  Niket Kumar
                  use this query:

                  column tablespace_name format a10
                  column file_name format a32
                  column file_mb format 9999990
                  column hwm_mb format 9999990
                  column used_mb format 9999990
                  column shrnk_mb format 9999990

                  break on report
                  compute sum of file_mb on report
                  compute sum of hwm_mb on report
                  compute sum of used_mb on report
                  compute sum of shrnk_mb on report

                  select a.*
                  , file_mb-hwm_mb shrnk_mb
                  from (
                  select /*+ rule */
                  a.tablespace_name,
                  a.file_name,
                  a.bytes/1024/1024 file_mb,
                  b.hwm*d.block_size/1024/1024 hwm_mb,
                  b.used*d.block_size/1024/1024 used_mb
                  from
                  dba_data_files a,
                  (select file_id,max(block_id+blocks-1) hwm,sum(blocks) used
                  from dba_extents
                  group by file_id) b,
                  dba_tablespaces d
                  where a.file_id = b.file_id
                  and a.tablespace_name = d.tablespace_name
                  ) a
                  order by a.tablespace_name,a.file_name;


                  it will tell you how much you can shrink your datafiles.

                  Edited by: Niket Kumar on Jan 4, 2013 7:08 PM
                  • 6. Re: Tablespace Fragmentation. specially in SYSTEM tablesapce.
                    sb92075
                    VIRU wrote:

                    I suspect that fragmentation is the problem here._
                    post SQL & results that quantify this "fragmentation"

                    which metric at what value shows that tablespace is fragmented or not?
                    at what value does tablespace go from OK to being fragmented?
                    • 7. Re: Tablespace Fragmentation. specially in SYSTEM tablesapce.
                      Jonathan Lewis
                      Niket Kumar wrote:
                      use this query:

                      it will tell you how much you can shrink your datafiles.
                      Not quite - here's a cut-n-paste:
                      SQL> @ ts_space
                      
                      TABLESPACE_NAME                  FILE_NAME                                         FILE_MB   HWM_MB  USED_MB SHRNK_MB
                      -------------------------------- ------------------------------------------------ -------- -------- -------- --------
                      SYSAUX                           C:\APP\ORACLE\ORADATA\D11G\SYSAUX01.DBF               256      219   214          37
                      SYSTEM                           C:\APP\ORACLE\ORADATA\D11G\SYSTEM01.DBF               636      612   511          24
                      TEST_8K                          C:\APP\ORACLE\ORADATA\D11G\TEST_8K.DBF                500        4     3         496
                      UNDOTBS1                         C:\APP\ORACLE\ORADATA\D11G\UNDOTBS01.DBF              256       48    14         208
                                                                                                        -------- -------- -------- --------
                      sum                                                                                   1648      883   743         765
                      
                      4 rows selected.
                      
                      SQL> alter database datafile 'C:\APP\ORACLE\ORADATA\D11G\SYSAUX01.DBF' resize 16M;
                      alter database datafile 'C:\APP\ORACLE\ORADATA\D11G\SYSAUX01.DBF' resize 16M
                      *
                      ERROR at line 1:
                      ORA-03297: file contains used data beyond requested RESIZE value
                      I should, according to the script, be able to shrink the test_8k datafile to 4M, but I can't even get it down to 16M.
                      Unfortunately your script doesn't allow for the fact that there may be objects in the tablespace that have been dropped (so their extents show up as free) but not yet purged (so that you can't shrink below them.)

                      Regards
                      Jonathan Lewis
                      • 8. Re: Tablespace Fragmentation. specially in SYSTEM tablesapce.
                        Mark Malakanov (user11181920)
                        SYSTEM - 34256
                        I suspect that fragmentation is the problem here.
                        Why it is a problem? Do just 28 free MBs in SYSTEM TS impact your business?
                        If yes, you have to take full export, recreate DB and make full import.

                        Edited by: Mark Malakanov (user11181920) on Jan 4, 2013 10:47 AM

                        Oh, I see, it is 28GB. Which I'd agree may be a problem.
                        It is a good practice not to have user objects in SYSTEM. In this case it does not grows to multiple GB size. If happened, relocate users segments to other TS.
                        And try to relocate system segments which have last extents close to end of datafile, if possible.
                        • 9. Re: Tablespace Fragmentation. specially in SYSTEM tablesapce.
                          Niket Kumar
                          Thanks for notifying......I have a misconception about it...both the queries giving almost same result.
                          but first query give you result at datafile level.
                          SQL> column tablespace_name format a10
                          SQL> column file_name format a32
                          SQL> column file_mb format 9999990
                          SQL> column hwm_mb format 9999990
                          SQL> column used_mb format 9999990
                          SQL> column shrnk_mb format 9999990
                          SQL>
                          SQL> break on report
                          SQL> compute sum of file_mb on report
                          SQL> compute sum of hwm_mb on report
                          SQL> compute sum of used_mb on report
                          SQL> compute sum of shrnk_mb on report
                          SQL>
                          SQL> select a.*
                            2  , file_mb-hwm_mb shrnk_mb
                            3  from (
                            4  select /*+ rule */
                            5  a.tablespace_name,
                            6  a.file_name,
                            7  a.bytes/1024/1024 file_mb,
                            8  b.hwm*d.block_size/1024/1024 hwm_mb,
                            9  b.used*d.block_size/1024/1024 used_mb
                           10  from
                           11  dba_data_files a,
                           12  (select file_id,max(block_id+blocks-1) hwm,sum(blocks) used
                           13  from dba_extents
                           14  group by file_id) b,
                           15  dba_tablespaces d
                           16  where a.file_id = b.file_id
                           17  and a.tablespace_name = d.tablespace_name
                           18  ) a
                           19  order by a.tablespace_name,a.file_name;
                          
                          TABLESPACE FILE_NAME                         FILE_MB   HWM_MB  USED_MB SHRNK_MB
                          ---------- -------------------------------- -------- -------- -------- --------
                          SYSAUX     C:\ORACLEXE\APP\ORACLE\ORADATA\X      710      673      672       37
                                     E\UNDOTBS1.DBF
                          
                          SYSTEM     C:\ORACLEXE\APP\ORACLE\ORADATA\X      360      353      352        7
                                     E\SYSTEM.DBF
                          
                          UNDOTBS1   C:\ORACLEXE\APP\ORACLE\ORADATA\X      260      258      257        2
                                     E\SYSAUX.DBF
                          
                          USERS      C:\ORACLEXE\APP\ORACLE\ORADATA\X     6340     6017     6016      323
                                     E\USERS.DBF
                          
                          TABLESPACE FILE_NAME                         FILE_MB   HWM_MB  USED_MB SHRNK_MB
                          ---------- -------------------------------- -------- -------- -------- --------
                          
                                                                      -------- -------- -------- --------
                          sum                                             7670     7301     7296      369
                          
                          SQL> SELECT /* + RULE */  df.tablespace_name "Tablespace",
                            2         df.bytes / (1024 * 1024) "Size (MB)",
                            3         SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
                            4         Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
                            5         Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
                            6    FROM dba_free_space fs,
                            7         (SELECT tablespace_name,SUM(bytes) bytes
                            8            FROM dba_data_files
                            9           GROUP BY tablespace_name) df
                           10   WHERE fs.tablespace_name (+)  = df.tablespace_name
                           11   GROUP BY df.tablespace_name,df.bytes
                           12  UNION ALL
                           13  SELECT /* + RULE */ df.tablespace_name tspace,
                           14         fs.bytes / (1024 * 1024),
                           15         SUM(df.bytes_free) / (1024 * 1024),
                           16         Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
                           17         Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
                           18    FROM dba_temp_files fs,
                           19         (SELECT tablespace_name,bytes_free,bytes_used
                           20            FROM v$temp_space_header
                           21           GROUP BY tablespace_name,bytes_free,bytes_used) df
                           22   WHERE fs.tablespace_name (+)  = df.tablespace_name
                           23   GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
                           24   ORDER BY 4 DESC;
                          
                          Tablespace                      Size (MB)  Free (MB)     % Free     % Used
                          ------------------------------ ---------- ---------- ---------- ----------
                          TEMP                                   20         17         85         15
                          USERS                                6340   323.3125          5         95
                          SYSAUX                                710      37.25          5         95
                          SYSTEM                                360     7.3125          2         98
                          UNDOTBS1                              260     1.8125          1         99
                          
                          SQL>
                          • 10. Re: Tablespace Fragmentation. specially in SYSTEM tablesapce.
                            VIRU
                            I have made a few attempts but the results are not good :-

                            I execute this query :-
                            select a.*
                            , file_mb-hwm_mb shrnk_mb
                            from (
                            select /*+ rule */
                            a.tablespace_name,
                            a.file_name,
                            a.bytes/1024/1024 file_mb,
                            b.hwm*d.block_size/1024/1024 hwm_mb,
                            b.used*d.block_size/1024/1024 used_mb
                            from
                            dba_data_files a,
                            (select file_id,max(block_id+blocks-1) hwm,sum(blocks) used
                            from dba_extents
                            group by file_id) b,
                            dba_tablespaces d
                            where a.file_id = b.file_id
                            and a.tablespace_name = d.tablespace_name
                            ) a
                            order by a.tablespace_name,a.file_name;
                            Output :-
                                 SYSAUX     /data01/oradata/NGPIUT/NGPIUT/SYSAUX_3.dbf     3016     2572.9921875     2546.25     443.0078125
                            ...........
                              SYSTEM  /data01/oradata/NGPIUT/NGPIUT/system02.dbf  2044  2043.9921875  817.0625  0.0078125
                              SYSTEM  /data01/oradata/NGPIUT/NGPIUT/system01.dbf  25804  25803.9921875  1789  0.0078125
                              SYSTEM  /data01/oradata/NGPIUT/NGPIUT/system03.dbf  1098  1097.9921875  665  0.0078125
                              SYSTEM  /data01/oradata/NGPIUT/NGPIUT/system05.dbf     2048     2047.9921875     619     0.0078125
                                 SYSTEM     /data01/oradata/NGPIUT/NGPIUT/system04.dbf     2048     2047.9921875     605     0.0078125
                                 SYSTEM     /data01/oradata/NGPIUT/NGPIUT/SYSTEM_6.dbf     609     608.9921875     584     0.0078125
                                 SYSTEM     /data01/oradata/NGPIUT/NGPIUT/SYSTEM_10.dbf     65     64.9921875     64     0.0078125
                                 SYSTEM     /data01/oradata/NGPIUT/NGPIUT/SYSTEM_7.dbf     342     341.9921875     341     0.0078125
                                 SYSTEM     /data01/oradata/NGPIUT/NGPIUT/SYSTEM_9.dbf     100     99.9921875     99     0.0078125
                                 SYSTEM     /data01/oradata/NGPIUT/NGPIUT/SYSTEM_8.dbf     98     97.9921875     97     0.0078125
                            .........
                            then i tried :-
                            SQL> alter database datafile '/data01/oradata/NGPIUT/NGPIUT/SYSAUX_3.dbf' resize 444M;
                             
                            alter database datafile '/data01/oradata/NGPIUT/NGPIUT/SYSAUX_3.dbf' resize 444M
                             
                            ORA-03297: file contains used data beyond requested RESIZE value
                            You can also observer.. that in my DB the SYSTEM tablespace has grown very much and on the datafile level its not showing much shrink space.

                            How can i reduce my system tablespace (in this scenario) ?

                            in the tablepace level query (in my first post) it shows that 28 GB free space in system tablespace. I want to recover it.

                            Mark Malakanov ...      ,
                            how can i do this ?
                            try to relocate system segments which have last extents close to end of datafile, if possible.

                            Thanks in advance.
                            • 11. Re: Tablespace Fragmentation. specially in SYSTEM tablesapce.
                              VIRU
                              Can anybody give me solution on this problem ?
                              • 12. Re: Tablespace Fragmentation. specially in SYSTEM tablesapce.
                                moreajays
                                VIRU,

                                Checkout this query , which will atleast guide you to avoid making false attempt to resize datafile which will result in failure
                                Pass specific FS to it, you can reduce particular datafile to (allocated_mb-free_MB) size without any error
                                It may not be the relevant solution to your problem but it should help someway
                                set line 900
                                set pages 900
                                col file_name for a50
                                select distinct dt.file_name,dt.file_id,df.bytes/1024/1024 "free_MB",dt.bytes/1024/1024 "allocated_mb",dt.tablespace_name,dt.autoextensible  
                                from dba_data_files dt, dba_free_space df
                                where  df.FILE_ID=dt.FILE_ID and df.bytes/1024/1024 > 50
                                and dt.file_name like '/data01%';
                                Thanks,
                                Ajay More
                                http://moreajays.blogspot.com
                                • 13. Re: Tablespace Fragmentation. specially in SYSTEM tablesapce.
                                  Jonathan Lewis
                                  Have you read the blog post at the link I supplied a few days ago ?
                                  Did you notice the closing comment about the recyclebin ?
                                  Did you try running the script it linked to against your SYSTEM tablespace ?


                                  I see you have several files in the system tablespace - this makes it harder to juggle with objects that need to be moved, and my script doesn't turn file ids into file names, but knowing WHERE the free space is the first step to solving your problem.

                                  Regards
                                  Jonathan Lewis