Trace enabled Concurrent programs list

select a.CONCURRENT_PROGRAM_ID, b.USER_CONCURRENT_PROGRAM_NAME "Program_Name",a.ENABLE_TRACE, a.CONCURRENT_PROGRAM_NAME "Short_Name", a.APPLICATION_ID
from apps.fnd_concurrent_programs a, apps.fnd_concurrent_programs_tl b
where a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID and a.ENABLE_TRACE='Y';

FInd the Row chaining and Row migration in table

Step1-- Analyze  the table.

Step 2

select table_name,chain_cnt,last_analyzed tablespace_namefrom from user_tables where chain_cnt >0;

or
---------
SELECT table_name,chain_cnt,round(chain_cnt/num_rows*100,2) pct_chained,
       avg_row_len, pct_free , pct_used
  FROM user_tables where chain_cnt>0;

__________________


Row chaining and row migration for apps user or hole database:


Step1-- Analyze  the table.
1.Row chaining check


Step :1

>@?/rdbms/admin/utlchain.sql

step :2

spool table.sql
select 'ANALYZE TABLE '||owner||'.'||segment_name||' LIST CHAINED ROWS INTO chained_rows;'
from   sys.dba_segments where segment_type='TABLE' and owner='APPS'
spool off
@table.sql


step 3

select table_name,chain_cnt,last_analyzed,tablespace_name from user_tables where chain_cnt >0;

or
---------
SELECT table_name,chain_cnt,round(chain_cnt/num_rows*100,2) pct_chained,
       avg_row_len, pct_free , pct_used
  FROM user_tables where chain_cnt>0;


example:

>@?/rdbms/admin/utlchain.sql
>analyze table emp list chained rows;
>select count(*) from chained_rows where table_name=’EMP’;
Output:
Count =0 no problem
Count > 0 means problem

Index Fragmentation

set heading off
set feedback off
spool index_analyze.sql
select 'analyze index '|| object_name ||' validate structure; ' || chr(13) || chr(10) || 
'insert into custom_user.index_stat_bk(NAME,LF_ROWS,DEL_LF_ROWS) select NAME,LF_ROWS,DEL_LF_ROWS from index_stats;' from dba_objects where object_type='INDEX' and owner='APPS';
spool off
set heading on
set feedback on
@index_analyze.sql




select name,(DEL_LF_ROWS/LF_ROWS)*100  from index_stat_bk where DEL_LF_ROWS >0 and LF_ROWS>0;