Oracle Scripts

Hi, Guy's,

It's very easy to make a script by using the 'CONCATENATE' function in SQL to simplify our DB tasks.


 Killing Sessions:

SQL> select 'alter system kill session ''' || sid ||','|| serial# ||',@'|| inst_id ||''''||'immediate;' FROM gv$session WHERE status ='INACTIVE' and username like 'DR%';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||',@'||INST_ID||''''||'IMMEDIATE;'

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

alter system kill session '69,60037,@1'immediate;

alter system kill session '137,54957,@1'immediate;

alter system kill session '140,25457,@1'immediate;

alter system kill session '200,63197,@1'immediate;

alter system kill session '266,21920,@1'immediate;

alter system kill session '333,13739,@1'immediate;

alter system kill session '774,50855,@1'immediate;

alter system kill session '903,20285,@1'immediate;

alter system kill session '1158,53245,@1'immediate;

alter system kill session '1226,50285,@1'immediate;

alter system kill session '1352,52379,@1'immediate;

alter system kill session '1418,27389,@1'immediate;

alter system kill session '1483,55652,@1'immediate;

alter system kill session '331,21769,@2'immediate;

alter system kill session '332,63307,@2'immediate;

alter system kill session '451,45973,@2'immediate;

alter system kill session '455,53137,@2'immediate;

alter system kill session '521,53017,@2'immediate;

 Index Rebuild:


SQL> select DISTINCT 'alter index '||index_name||' rebuild ONLINE;' from user_ind_columns where table_name='OTOCURNC';

'ALTERINDEX'||INDEX_NAME||'REBUILDONLINE;'
--------------------------------------------------------------------------------
alter index    ACTIVE_MED               rebuild ONLINE;
alter index    IDX_OCU_MCC            rebuild ONLINE;
alter index    PK_OCURNC_PK         rebuild ONLINE;
alter index    IDX_OCU1_MCC          rebuild ONLINE;
alter index    IND_CRDFLR                rebuild ONLINE;
alter index    INX_AWRABOCU         rebuild ONLINE;
alter index    OTOCURNC_MED        rebuild ONLINE;
alter index    INDX_APPNMBR           rebuild ONLINE;

8 rows selected.


 Index Rebuild  for a Schema : 

SQL> set head off

           set pagesize 20000

           set linesize 20000

           select 'alter index '||index_name||' rebuild online;' from user_indexes where table_owner='KMIS';

alter index SYS_IL0000103129C00027$$ rebuild online;

alter index MRMSCANNERBUTTONS_PK rebuild online;

alter index PK_PROVCD rebuild online;

alter index PK_SKQUALIF rebuild online;

alter index PK_SKRACCOD rebuild online;

alter index PK_SKREFRAL rebuild online;

alter index PK_SKRELIGN rebuild online;

alter index PK_SKRLTCOD rebuild online;

alter index PK_SKSERCOD rebuild online;

alter index PK_SKSEXCOD rebuild online;

 Index Move in New Tablespace : 

SQL> set head off
           set pagesize 20000
           set linesize 20000
          select 'alter index '||index_name||' rebuild online tablespace new_tablespace;' from 
          user_indexes where table_owner='HMIS';

alter index SYS_IL0000103129C00027$$ rebuild online tablespace new_tablespace;
alter index OBSKEY rebuild online tablespace new_tablespace;
alter index RTINSREQ_IDX rebuild online tablespace new_tablespace;
alter index PK_EXTSUP rebuild online tablespace new_tablespace;
alter index PK_EXTANT rebuild online tablespace new_tablespace;
alter index K_CLNCSSUP rebuild online tablespace new_tablespace;
alter index CLN_K rebuild online tablespace new_tablespace;
alter index RTAPROVL_PK rebuild online tablespace new_tablespace;
alter index SSSS rebuild online tablespace new_tablespace;
alter index PK_PAYTRN rebuild online tablespace new_tablespace;
alter index PK_DOCSTS rebuild online tablespace new_tablespace;
alter index PK_UN rebuild online tablespace new_tablespace;
alter index RNRNLSCD_C_X rebuild online tablespace new_tablespace;
alter index RMROMTYP_X rebuild online tablespace new_tablespace;
alter index PK_ROMSPC rebuild online tablespace new_tablespace;
alter index JTN rebuild online tablespace new_tablespace;
alter index HFGHYHJ rebuild online tablespace new_tablespace;





Happy Learning.