1 2 Previous Next 16 Replies Latest reply: Jul 22, 2013 11:33 AM by Hussein Sawwan-Oracle RSS

    APPS_TS_TX_DATA tablespace filled Fastly.

    Kumar_Jaga

      Hi,

       

      I am maintaining the R12 applications

      DB-11.1.07

      apps-12.1.3

       

      Am facing issue with APPS_TS_TX_DATA tablespace filled too fast.

      Please suggest me to solve this issue.

      am working in client environment. please help me to solve this issue ASAP.

       

       

      Regards

      Kumar

        • 1. Re: APPS_TS_TX_DATA tablespace filled Fastly.
          Hussein Sawwan-Oracle

          Kumar,

           

          This topic was discussed many times in the forum before, please see previous threads for details -- Make sure you run purge programs on regular basis and query DBA_SEGMENTS for details about the database objects that make this tablespace get filled too fast.

           

          https://forums.oracle.com/community/developer/search.jspa?q=APPS_TS_TX_DATA+AND+DBA_SEGMENTS

          https://forums.oracle.com/community/developer/search.jspa?q=Purging

           

          Thanks,

          Hussein

          • 2. Re: APPS_TS_TX_DATA tablespace filled Fastly.
            Kumar_Jaga

            Hi Hussein,

             

            thanks for your suggestion

            I go through the above mentioned link, but unfortunately we unable to find the similar issue which we facing right now

            kindly guide me to fix the issue.

             

            Awaiting for your reply

             

            Regards

            Kumar

            • 3. Re: APPS_TS_TX_DATA tablespace filled Fastly.
              Hussein Sawwan-Oracle

              Kumar_Jaga wrote:

               

              Hi Hussein,

               

              thanks for your suggestion

              I go through the above mentioned link, but unfortunately we unable to find the similar issue which we facing right now

              kindly guide me to fix the issue.

               

              Awaiting for your reply

               

              Regards

              Kumar

               

              What do you mean by "but unfortunately we unable to find the similar issue which we facing right now"?

               

              Do you run purging concurrent programs on regular basis?

               

              Please run one of the queries from the first link referenced above for details about the database objects.

               

              Thanks,

              Hussein

              • 4. Re: APPS_TS_TX_DATA tablespace filled Fastly.
                Kumar_Jaga

                Hi Hussein,

                 

                We didn't schedule any programs for purging concurrent request,

                kindly let me know which request need to be run for fix that issue

                Awaiting for your valuable suggestion

                 

                 

                Regards

                Kumar

                • 5. Re: APPS_TS_TX_DATA tablespace filled Fastly.
                  Hussein Sawwan-Oracle

                  What Are Concurrent Reports That Should Be Scheduled Periodically [ID 1066117.1]

                  Reducing Your Oracle E-Business Suite Data Footprint using Archiving, Purging, and Information Lifecycle Management [ID 752322.1]

                  Purging Strategy for eBusiness Suite 11i [ID 732713.1]

                   

                  Again, please go through the links referenced above.

                   

                  Thanks,

                  Hussein

                  • 6. Re: APPS_TS_TX_DATA tablespace filled Fastly.
                    Kumar_Jaga

                    Hi Hussein,

                     

                    Thanks for your update!!!

                    FYKI, we frequently adding the datafile for APPS_TS_TX_DATA tablespace

                    For past 5 hrs we added the more than 8GB for the particular tablespace

                    So please suggest me to fix the issue ASAP.

                    simultaneously we scheduled request for purging the data

                    Awaiting for your reply...

                     

                    Regards

                    Kumar

                    • 7. Re: APPS_TS_TX_DATA tablespace filled Fastly.
                      Hussein Sawwan-Oracle

                      For past 5 hrs we added the more than 8GB for the particular tablespace

                      So please suggest me to fix the issue ASAP.

                       

                      Query DBA_SEGMENTS as I mentioned twice (so far).

                       

                      Please also log a SR whenever it's production issue.

                       

                      simultaneously we scheduled request for purging the data

                       

                      What are the program you have scheduled to run so far? How frequent?

                       

                      Awaiting for your reply...

                       

                      I have nothing more to add and you need to find out which objects are growing fast. Once you find out we can start working on them and try to stop this unnecessary growth of data.

                       

                      Thanks,

                      Hussein

                      • 8. Re: APPS_TS_TX_DATA tablespace filled Fastly.
                        Kumar_Jaga

                        Hi Hussein,

                         

                        1) we have run the below query to find the segment_name and its values

                        "select segment_name, sum(bytes) from dba_segments where tablespace_name='APPS_TS_TX_DATA' group by segment_name order by 2 desc"

                        Here I have listed the top 30 segment_name values for your reference, kindly go through it and suggest me to move forward

                         

                        SEGMENT_NAMESUM(BYTES)
                        SYS_LOB0003209711C00015$$9,509,404,672
                        FND_LOG_MESSAGES2,191,654,912
                        XLA_DISTRIBUTION_LINKS2,109,472,768
                        WF_ITEM_ATTRIBUTE_VALUES1,864,237,056
                        SYS_LOB0001677220C00004$$1,778,647,040
                        SYS_LOB0002009392C00004$$1,617,821,696
                        SYS_LOB0002008435C00004$$1,590,820,864
                        SYS_LOB0000714994C00010$$1,518,075,904
                        XLA_AE_LINES1,501,954,048
                        GL_BALANCES1,115,815,936
                        SYS_LOB0002007579C00004$$922,091,520
                        SYS_LOB0002014132C00004$$912,916,480
                        SYS_LOB0001711884C00004$$844,234,752
                        PAY_RUN_RESULT_VALUES738,066,432
                        GL_JE_LINES731,643,904
                        MSC_NET_RESOURCE_AVAIL680,263,680
                        MTL_MATERIAL_TRANSACTIONS612,630,528
                        WF_NOTIFICATION_ATTRIBUTES604,504,064
                        XLA_AE_HEADERS589,168,640
                        AD_PATCH_RUN_BUG_ACTIONS578,945,024
                        ZX_LINES_DET_FACTORS578,813,952
                        PAY_ACTION_INFORMATION565,313,536
                        GL_IMPORT_REFERENCES537,788,416
                        DR$FND_LOBS_CTX$I522,846,208
                        CN_PROCESS_AUDIT_LINES_ALL520,880,128
                        MTL_TRANSACTION_ACCOUNTS519,831,552
                        SYS_LOB0000065579C00003$$457,834,496
                        ZX_LINES451,805,184
                        GCS_ENTRY_LINES449,183,744

                         

                        2)FYR, we have scheduled the gather schema request for all schema.

                        Kindly suggest me to fix the issue

                         

                        Regards

                        Kumar

                        • 9. Re: APPS_TS_TX_DATA tablespace filled Fastly.
                          Hussein Sawwan-Oracle

                          Please see the following docs.

                           

                          SYS_LOB Objects Use A Lot Of Disk Space, What Are They? [ID 409022.1]

                          How to Purge MSD.AW$ LOB Segments [ID 418248.1]

                          FND_LOBS Table Growing Rapidly Due To Purchase Orders Attachments. How to deal with this. [ID 1410946.1]

                          Useful SQL Statements to Check What Makes FND_LOBS Grow? [ID 1245974.1]

                          How to Purge/Troubleshoot the FND_LOG_MESSAGES table [ID 735138.1]

                          Any Available Method of Purging XLA Tables? [ID 1111390.1]

                          Check current volume of SLA Data and Manually Archive/Purge Data from XLA/SLA Tables in R12 [ID 1491255.1]

                          Speeding Up And Purging Workflow [ID 132254.1]

                          Troubleshooting Workflow Data Growth Issues [ID 298550.1]

                          Payroll Purge [ID 155106.1]

                           

                          You need to schedule purge concurrent programs as well, and the gather schema stats has nothing to do with the space issue you have.

                           

                          Thanks,

                          Hussein

                          • 10. Re: APPS_TS_TX_DATA tablespace filled Fastly.
                            Ishita

                            Hi Kumar,

                             

                            Have you tried to find out which are the tables in which you are having high DMLs , are those tables getting reorged regularly ??

                             

                            Thanks,

                            Ishita.

                            • 11. Re: APPS_TS_TX_DATA tablespace filled Fastly.
                              Kumar_Jaga

                              Hi,

                               

                              Am working on this issue.

                              Do you have any specific query to find log enabled for all level.

                               

                              Regards

                              Kumar

                              • 12. Re: APPS_TS_TX_DATA tablespace filled Fastly.
                                Asif Muhammad

                                Hi Kumar,

                                 

                                Did you go through the suggested note by Hussein's earlier post which probably should address your issue.

                                 

                                SYS_LOB Objects Use A Lot Of Disk Space, What Are They? [ID 409022.1]

                                How to Purge MSD.AW$ LOB Segments [ID 418248.1]

                                FND_LOBS Table Growing Rapidly Due To Purchase Orders Attachments. How to deal with this. [ID 1410946.1]

                                Useful SQL Statements to Check What Makes FND_LOBS Grow? [ID 1245974.1]

                                How to Purge/Troubleshoot the FND_LOG_MESSAGES table [ID 735138.1]

                                Any Available Method of Purging XLA Tables? [ID 1111390.1]

                                Check current volume of SLA Data and Manually Archive/Purge Data from XLA/SLA Tables in R12 [ID 1491255.1]

                                Speeding Up And Purging Workflow [ID 132254.1]

                                Troubleshooting Workflow Data Growth Issues [ID 298550.1]

                                Payroll Purge [ID 155106.1]

                                 

                                You need to schedule purge concurrent programs as well, and the gather schema stats has nothing to do with the space issue you have.

                                 

                                Thanks,

                                Hussein

                                 

                                Please analyze the following segments:

                                 

                                SEGMENT_NAMESUM(BYTES)
                                SYS_LOB0003209711C00015$$9,509,404,672
                                FND_LOG_MESSAGES2,191,654,912
                                XLA_DISTRIBUTION_LINKS2,109,472,768
                                WF_ITEM_ATTRIBUTE_VALUES1,864,237,056
                                SYS_LOB0001677220C00004$$1,778,647,040
                                SYS_LOB0002009392C00004$$1,617,821,696
                                SYS_LOB0002008435C00004$$1,590,820,864
                                SYS_LOB0000714994C00010$$1,518,075,904

                                 

                                Also refer the following link:

                                DBA Tips Archive for Oracle

                                 

                                 

                                Hope this helps!

                                Best Regards

                                • 13. Re: APPS_TS_TX_DATA tablespace filled Fastly.
                                  Kumar_Jaga

                                  Hi,

                                   

                                  Thanks for the update.

                                  Now i have disabled the debug log for all level in applications. using below query to check log enabled or not for all level.

                                   

                                  SELECT

                                  po.user_profile_option_name,

                                  po.profile_option_name "NAME" ,

                                  DECODE (TO_CHAR (pov.level_id), '10001', 'SITE' , '10002', 'APP', '10003',

                                  'RESP', '10004', 'USER', '???') "LEV",

                                  DECODE (TO_CHAR (pov.level_id) , '10001', '', '10002',

                                  app.application_short_name , '10003', rsp.responsibility_key, '10004',

                                  usr.user_name, '???') "CONTEXT" ,

                                  pov.profile_option_value "VALUE"

                                  FROM

                                  fnd_profile_options_vl po,

                                  fnd_profile_option_values pov ,

                                  fnd_user usr,

                                  fnd_application app,

                                  fnd_responsibility rsp

                                  WHERE

                                  (

                                  po.user_profile_option_name LIKE '%Debug%'

                                  )

                                  AND pov.application_id = po.application_id

                                  AND pov.profile_option_id = po.profile_option_id

                                  AND usr.user_id(+) = pov.level_value

                                  AND rsp.application_id(+) = pov.level_value_application_id

                                  AND rsp.responsibility_id(+) = pov.level_value

                                  AND app.application_id(+) = pov.level_value

                                  ORDER BY

                                  "NAME",

                                  pov.level_id,

                                  "VALUE";

                                   

                                   

                                  And ran the below concurrent request with completed normal.

                                  1) Purge concurrent Request and/or Manager Data.

                                  2) Purge Logs and closed system alerts.

                                  3) purge obsolete generic file manager data

                                  4) purge obsolete workflow Runtime data

                                  5) Purge web services Audit Data

                                  6) Gather schema Statistics.

                                   

                                  Now the tablespace growth has been controlled.

                                   

                                  Is it possible to make free space in tablespace which has been increased past two days because of this issue.

                                   

                                  Regards

                                  Kumar

                                  • 14. Re: APPS_TS_TX_DATA tablespace filled Fastly.
                                    Asif Muhammad

                                    Hi Kumar,

                                    Is it possible to make free space in tablespace which has been increased past two days because of this issue.

                                     

                                    If the datas in the tablespace are freed, then you can reclaim your unused free space using "ALTER TABLE .. shrink" command

                                     

                                     

                                    Hope this helps!

                                    Regards

                                    1 2 Previous Next