Concatenation for Oracle Queries

Rebuild Partition

select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||';' from USER_IND_PARTITIONS;

Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from USER_IND_PARTITIONS;

Disable Constraint 

SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' '||' DISABLE CONSTRAINT '||CONSTRAINT_NAME||' '||';' FROM DBA_CONSTRAINTS WHERE R_CONSTRAINT_NAME='PKD_RETURN';

Alter Profile

Select 'ALTER user '|| username ||' profile ' || 'NEW_SAP_1' ||';' from dba_users where username like 'SAP%'

Alert Sequence

select 'alter sequence '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||' cache 100 ;' from dba_SEQUENCEs where SEQUENCE_OWNER in ('GST','OEM_REPORT','EFS','TDS','CAGAUDIT','ITGM','GCPUSER','PRS','ITDBA','PRL');

Table Initrans

select 'alter table '||owner||'.'||table_name||' INITRANS 40 MAXTRANS 255 ;' from dba_tables where owner in ('GST','OEM_REPORT','EFS','TDS','CAGAUDIT','ITGM','GCPUSER','PRS','ITDBA');

select 'alter index '||owner||'.'||index_name||' INITRANS 50 MAXTRANS 255 ;' from dba_indexes where owner in ('GST','OEM_REPORT','EFS','TDS','CAGAUDIT','ITGM','GCPUSER','PRS','ITDBA','PRL');

select 'alter table '||owner||'.'||table_name||' INITRANS 40 MAXTRANS 255 ;' from dba_tables where owner not in 

('SYS','SYSTEM','DBSNMP','WMSYS','EXFSYS','MDSYS','CTXSYS','OLAPSYS','SYSMAN','OUTLN','TSMSYS',

'DMSYS','XDB','ORDSYS','PERFSTAT','SCOTT') 

and ini_trans < 40;

select 'alter index '||owner||'.'||index_name||' INITRANS 50 MAXTRANS 255 ;' from dba_indexes where owner not in 

('SYS','SYSTEM','DBSNMP','WMSYS','EXFSYS','MDSYS','CTXSYS','OLAPSYS','SYSMAN','OUTLN','TSMSYS',

'DMSYS','XDB','ORDSYS','PERFSTAT','SCOTT') 

and ini_trans < 50;

select 'alter sequence '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||' cache 100 ;' from dba_SEQUENCEs where SEQUENCE_OWNER not in 

('SYS','SYSTEM','DBSNMP','WMSYS','EXFSYS','MDSYS','CTXSYS','OLAPSYS','SYSMAN','OUTLN','TSMSYS',

'DMSYS','XDB','ORDSYS','PERFSTAT','SCOTT') 

and cache_size  < 100;

Kill PID

select 'kill -9 '||a.spid from v$process a

where a.addr in (select b.paddr from v$session b where b.sid in (select sid from v$access where object='T_PRCSD_RFD')) 

select 'kill -9 '||spid from gv$process p where addr in (select paddr from gv$session  where USERNAME like '%U%'  AND  PROGRAM like 'sqlplus%' and inst_id=2) and p.inst_id=2

Session Kill

SELECT 'ALTER SYSTEM KILL SESSION '||''''||SID||','||SERIAL#||''''||' IMMEDIATE;' FROM 

V$SESSION where event like  '%TX - index contention%';

SELECT 'ALTER SYSTEM KILL SESSION '||''''||SID||','||SERIAL#||''''||' IMMEDIATE;' FROM 

V$SESSION where event like  '%SQ - contention%';

SELECT 'ALTER SYSTEM KILL SESSION '||''''||SID||','||SERIAL#||''''||' IMMEDIATE;' FROM

V$SESSION where event like  '%US - contention%';

SELECT 'ALTER SYSTEM KILL SESSION '||''''||SID||','||SERIAL#||''''||' IMMEDIATE;' FROM

V$SESSION where event like  '%gc buffer busy%';

SELECT 'ALTER SYSTEM KILL SESSION '||''''||SID||','||SERIAL#||''''||' IMMEDIATE;' FROM

V$SESSION where event like  '%latch: library cache%';

select 'kill -9 '|| p.spid from v$process p,v$session s where s.paddr=p.addr and s.status='SNIPED';

select distinct '!kill -9 '||p.spid||chr(10)||

'alter system kill session '''||s.sid||','||s.serial#||''';'

from v\$session_wait w,v\$session s,v\$sql q,v\$process p

where s.sql_id in ('6u9aahua9ad58','6uxttdyjw2rd5', '3g248qzga3j74','0cbhup0dvy4wr','bkzdczudq2275','fa8wjv7f1h6ma','d93f0z9jwcqmh','9ujfywfw67qmm','6nv1y6sydkrrj')

and w.sid=s.sid

and p.addr=s.paddr

and s.SQL_HASH_VALUE=q.HASH_VALUE

and s.status='ACTIVE'

and s.username is not null;

Gather status

SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS('''|| owner ||''','||''''|| Segment_name||''''||',CASCADE=>TRUE,DEGREE=>10,ESTIMATE_PERCENT=>100,METHOD_OPT=>''FOR ALL COLUMNS SIZE 1'');' FROM ( 

select a.owner,segment_name,sum(bytes) abc from dba_segments a,dba_tables b where 

a.segment_name = b.table_name 

and a.owner = b.owner 

and a.SEGMENT_TYPE  in ( 

'TABLE SUBPARTITION', 

'TABLE PARTITION', 

'TABLE') 

and a.owner not in ('SYS','OUTLN','SYSTEM','TSMSYS','DBSNMP','WMSYS','EXFSYS','XDB','OLAPSYS','MDSYS', 

'CAGAUDIT','SYSMAN','DMSYS','CTXSYS','ORDSYS','SCOTT','PERFSTAT') and b.table_name not in ('T_AUDIT_LOG','T_AUDIT_VALUES') 

 and b.last_analyzed is null  group by segment_name,a.owner) 

A ORDER BY ABC;

SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS('''|| owner ||''','||''''|| Segment_name||''''||',CASCADE=>TRUE,DEGREE=>10,ESTIMATE_PERCENT=>100,METHOD_OPT=>''FOR ALL COLUMNS SIZE 1'');' FROM ( 

select a.owner,segment_name,sum(bytes) abc from dba_segments a,dba_tables b where 

a.segment_name = b.table_name 

and a.owner = b.owner 

and a.SEGMENT_TYPE  in ( 

'TABLE SUBPARTITION', 

'TABLE PARTITION', 

'TABLE') 

and a.owner not in ('SYS','OUTLN','SYSTEM','TSMSYS','DBSNMP','WMSYS','EXFSYS','XDB','OLAPSYS','MDSYS', 

'CAGAUDIT','SYSMAN','DMSYS','CTXSYS','ORDSYS','SCOTT','PERFSTAT') and b.table_name not in ('T_AUDIT_LOG','T_AUDIT_VALUES') 

 and b.last_analyzed < sysdate - 4  group by segment_name,a.owner) 

A ORDER BY ABC 

SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS('''||OWNER||''','''||TABLE_NAME||''','||'CASCADE=>TRUE, DEGREE=>10, ESTIMATE_PERCENT=>100,METHOD_OPT=>'||'''FOR ALL COLUMNS SIZE 1'''||');' 

FROM DBA_TABLES WHERE  owner in('FMS','IPAN','ITDMIG','IRL','EFS') and TEMPORARY='N' AND TRUNC(LAST_ANALYZED) < TRUNC(SYSDATE-5);