Thursday, May 28, 2009

Useful Queries


-- Command to move the tables

select 'alter table ' table_name ' move tablespace ' tablespace_name ';' from dba_tables where table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

-- Command to rebuild the indexes in the moved tables

select 'alter index ' owner '.' index_name ' rebuild tablespace ' tablespace_name ';' from dba_indexes where index_type='NORMAL' and table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

-- Check the status of the indexes

select status from dba_indexes where index_type='NORMAL' and table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

select status,index_name from dba_indexes where table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

select 'alter table ' owner '.' table_name ' move tablespace ' tablespace_name ';' from dba_tables where table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

select * from fnd_concurrent_programs_vl where user_concurrent_program_name like 'Purge%Workflow%';

-- Command to move LOB's

select 'alter table ' owner '.'table_name ' move tablespace hyp_ss_tst STORAGE(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0) lob ('column_name ')
store as ' segment_name ' (TABLESPACE hyp_ss_tst STORAGE (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0));'
from all_lobs where owner='HYP_SS_TST'

-- Command to make a tablespace as autoextend

select 'alter database datafile ' file_name ' ' ' autoextend on;' from dba_data_files where tablespace_name='MDCCDATA'

-- Gather stats for table

select 'analyze table ' owner '.' table_name ' estimate statistics;' from dba_tables where table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

-- Move Queue Tables

select 'exec move_aqt.move('''''a.owner''''','''''a.queue_table''''');' from
dba_queue_tables a, dba_segments b where a.queue_table =
b.segment_name and a.owner = b.owner and b.tablespace_name='APPS_TS_QUEUES' and a.owner not in
('SYS','SYSTEM');

No comments: