APPS_TS_TX_DATA and APPS_TS_TX_IDX size keep on increasing

APPS_TS_TX_DATA and APPS_TS_TX_IDX size keep on increasing

Solution:-

Step1:
You can truncate these tables for deleting entire data without any back up.The truncate is more faster compare to delete command because data in the table is not write to roll back segments.

1. SQL>truncate table xla.XLA_DIAG_SOURCES;
2. SQL>truncate table xla.XLA_DIAG_EVENTS;


Step2:
Turn OFF the “SLA: Diagnostics Enabled” profile option at all levels.

Step3:
Check the tables, tablespace and archive logs status

R12 directory structure diagram

Get all the Concurrent Program Request Details

Get all the Concurrent Program Request Details

select   request_id,
           parent_request_id,
           fcpt.user_concurrent_program_name Request_Name,
           fcpt.user_concurrent_program_name program_name,
           DECODE(fcr.phase_code,
                        'C','Completed',
                        'I','Incactive',
                        'P','Pending',
                        'R','Running') phase,
           DECODE(fcr.status_code,
                        'D','Cancelled',
                        'U','Disabled',
                        'E','Error',
                        'M','No Manager',
                        'R','Normal',
                        'I','Normal',
                        'C','Normal',
                        'H','On Hold',
                        'W','Paused',
                        'B','Resuming',
                        'P','Scheduled',
                        'Q','Standby',
                        'S','Suspended',
                        'X','Terminated',
                        'T','Terminating',
                        'A','Waiting',
                        'Z','Waiting',
                        'G','Warning','N/A') status,
           round((fcr.actual_completion_date - fcr.actual_start_date),3) * 1440 as Run_Time,
           round(avg(round(to_number(actual_start_date - fcr.requested_start_date),3) *  1440),2) wait_time,
           fu.User_Name Requestor,
           fcr.argument_text parameters,
           to_char (fcr.requested_start_date, 'MM/DD HH24:mi:SS') requested_start,
           to_char(actual_start_date, 'MM/DD/YY HH24:mi:SS') startdate,
           to_char(actual_completion_date, 'MM/DD/YY HH24:mi:SS') completiondate,
           fcr.completion_text
From    fnd_concurrent_requests fcr,
           fnd_concurrent_programs fcp,
           fnd_concurrent_programs_tl fcpt,
           fnd_user fu
Where 1=1
and      fcr.concurrent_program_id = fcp.concurrent_program_id
and      fcp.concurrent_program_id = fcpt.concurrent_program_id
and      fcr.program_application_id = fcp.application_id
and      fcp.application_id = fcpt.application_id
and      fcr.requested_by = fu.user_id
and      fcpt.language = 'US'
and      fcr.actual_start_date like sysdate
GROUP BY  request_id,
                 parent_request_id,
                 fcpt.user_concurrent_program_name,
                 fcr.requested_start_date,
                 fu.User_Name,
                 fcr.argument_text,
                 fcr.actual_completion_date,
                 fcr.actual_start_date,
                 fcr.phase_code,
                 fcr.status_code,
                 fcr.resubmit_interval,
                 fcr.completion_text,
                 fcr.resubmit_interval,
                 fcr.resubmit_interval_unit_code,
                 fcr.description
Order by 1 desc


================

How to change user's password in Oracle 11g and change back it to original

How to change user's password in Oracle 11g and change back it to original

Imp points
(1) Note down current password found in data dictionary.
(2) Modify password
(3) Do required tasks
(4) Reset the password


Get current password :-
SQL> SET LONG 100000
SQL> SELECT dbms_metadata.get_ddl('USER','TEST') FROM dual;

   CREATE USER "TEST" IDENTIFIED BY VALUES 'S:659106CEC6E63EE94597855D276029184257176283D5456B83C1A711ABD8;42CE85A96B6A78FA';
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

Password is in encrypted format.

Now change password temporarily:-
SQL> alter user TEST identified by 1234;

Login to the user with new password:-
SQL> conn test/1234
Connected.

Once the job is done we can change password back to the original :-

SQL> alter user TEST identified by values 'S:659106CEC6E63EE94597855D276029184257176283D5456B83C1A711ABD8;42CE85A96B6A78FA';


DONE