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');
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment