10 Replies Latest reply on Aug 21, 2019 7:47 AM by Just_UD

    Reduce / shrink database in Oracle EBS

    Just_UD

      Hello,

       

      Hope I post in the correct forum. I have some questions regarding my case. I have an EBS 12.1.3 instance with Oracle Database 11.2.0.4.0. I read, and tried myself, an empty data full install Oracle EBS only consume about 80 GB space. The problem is, my database instance is about 400 GB, it's only about 4 years old and with not many transactions. So I think it shouldn't be that big. None knows why it grows as big as that. I'm thinking whether there's a safe way to reduce / shrink the database and also find the cause of it to grow that big? Thanks,

        • 1. Re: Reduce / shrink database in Oracle EBS
          John_K

          400GB isn't really that big for an E-Business Suite instance. I presume you have looked at table sizes to identify any large tables? Do you have a lot of attachments (LOB's) being uploaded?

          • 2. Re: Reduce / shrink database in Oracle EBS
            Just_UD

            Not big if there are some transactions. The problem is this instance does not have many transactions so 400 GB is quite a lot. I should also inform that the modules being use are only finance, purchasing and inventory.

            • 3. Re: Reduce / shrink database in Oracle EBS
              John_K

              Please post output of the following queries:

               

              select sum(bytes) / 1024 / 1024 size_in_mb

                from dba_data_files;

               

               

              select sum(bytes) / 1024 / 1024 size_in_mb

                from dba_segments;

               

              select * from (

                select owner, segment_name, sum(bytes) / 1024 / 1024 size_mb

                  from dba_segments

              group by owner, segment_name

              order by size_mb desc) where rownum <=10;

              • 4. Re: Reduce / shrink database in Oracle EBS
                Kanda-Oracle

                Hi

                 

                As mentioned above, just find out the big tables (top 20) . Focus them to purge and defragment to reduce the size.

                 

                From my experience, most of the time FND_LOBS grown big. Please explore whether FND_LOBS tables can be required to be purged and defragmented subsequently.

                 

                Refer the below note for more details.

                    Note.829235.1  FAQ - Performance considerations for FND_LOBS

                 

                Please check Note 752322.1 as well to purge other tables.

                 

                Thanks!

                 

                N Kandasamy

                • 5. Re: Reduce / shrink database in Oracle EBS
                  Just_UD

                  Sorry for late reply. I'm currently not feeling well so can't gather the data yet. Will update ASAP. Thanks

                  • 6. Re: Reduce / shrink database in Oracle EBS
                    Just_UD

                    Query:

                    select sum(bytes) / 1024 / 1024 size_in_mb

                     

                      from dba_data_files;

                     

                    Result: size_in_mb - 66607.125

                     

                    -------------------------------------------------------------

                    Query:

                    select sum(bytes) / 1024 / 1024 size_in_mb

                     

                      from dba_segments;

                     

                    Result : size_in_mb - 49543.375

                    -------------------------------------------------------------

                    Query:
                    select * from (

                      select owner, segment_name, sum(bytes) / 1024 / 1024 size_mb

                        from dba_segments

                    group by owner, segment_name

                    order by size_mb desc) where rownum <=10;

                     

                    Result:
                    Owner         Segment Name                                          Size MB

                    SYS             _SYSSMU10_1912268817$                       3411.125

                    SYS             SOURCE$                                                   3286.7578125

                    SYS             WRH$_ACTIVE_SESSION_HISTORY          2061.5

                    SYS             IDL_UB1$                                                    1766.25

                    APPLSYS    DR$FND_LOBS_CTX$I                                1741.125

                    APPLSYS    FND_CONCURRENT_REQUESTS               1430

                    SYS             I_SOURCE1                                                 1374.8203125

                    SYS             _SYSSMU19_1227766224$                         1220.125

                    APPLSYS    SYS_LOB0000057442C00004$$                 1081.375

                    XDO            SYS_LOB0000216935C00010$$                  985.625

                    • 7. Re: Reduce / shrink database in Oracle EBS
                      John_K

                      So the datafiles are only taking ~60G. Where is it you are seeing a size of 400G?

                      • 8. Re: Reduce / shrink database in Oracle EBS
                        Just_UD

                        Guess I'm not hallucinating then when I said it can't be 400 GB. My team was giving me a screenshot of the backup and the dbf files.

                        • 9. Re: Reduce / shrink database in Oracle EBS
                          John_K

                          Have you checked the instance server using something like the du or df commands?

                          • 10. Re: Reduce / shrink database in Oracle EBS
                            Just_UD

                            Not yet. I don't have direct access to the server now but will do it. Problem is, the one install the instances seems put the folders scattered if I'm reading the screenshot correctly.