This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jul 22, 2013 9:33 AM by Hussein Sawwan-Oracle RSS

APPS_TS_TX_DATA tablespace filled Fastly.

Kumar_Jaga Explorer
Currently Being Moderated

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 Employee ACE
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Employee ACE
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Employee ACE
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Employee ACE
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Employee ACE
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points