Prepare create database link script :

Prepare create database link script :



SQL> SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
  2    3  ||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
  4  '''||L.HOST||''''
  5  ||chr(10)||';' TEXT
  6  FROM SYS.LINK$ L, SYS.USER$ U
  7  WHERE L.OWNER# = U.USER#;

************************************************************************

how to compile invalid all type of objects using below script :

select 'alter'||' '||OBJECT_TYPE||' '||OBJECT_NAME||' '||
DECODE(OBJECT_TYPE,'PACKAGE BODY','compile body;','compile;') from user_objects where status='INVALID';

(or)

select 'alter'||' '||OBJECT_TYPE||' '||OBJECT_NAME||' '||
DECODE(OBJECT_TYPE,'PACKAGE BODY','compile body;','compile;') from dba_objects where status='INVALID' and OWNER='&username';

(or)

select 'alter'||' '||OBJECT_TYPE||' '||OBJECT_NAME||' '||
DECODE(OBJECT_TYPE,'PACKAGE BODY','compile body;','compile;') from dba_objects where status='INVALID' and OWNER='&username' and object_type='&object_type';

*****************************************************************************

How to kill active sessions:

select DISTINCT 'alter system kill session '||''''||s1.sid||','||s1.serial#||''';'  from v$session s1 where s1.status='ACTIVE' and s1.type NOT like 'BACKGROUND%';

(or)

select DISTINCT 'alter system kill session '||''''||s1.sid||','||s1.serial#||''' immediate ; ' from v$session s1 where s1.machine  like 'qpass-prod-monapp-1.slc1.qpass.net' and s1.status='ACTIVE' and s1.type NOT like 'BACKGROUND%';

(or)

(HOW TO KILL ROWLOCKED ACTIVE SESSION'S)
-----------------------------------------------------------------

select 'alter system kill session ''' ||ss.sid||','||ss.serial# || ''';'
FROM
v$session ss, v$sqlarea sa
where
        ss.username is not null
    and           ss.sql_hash_value = sa.hash_value (+)
    and ss.status='ACTIVE'
    and ss.event like 'enq: TX - row lock contention'
and (
      (ss.machine like '%tmo-prod-asydcm-%qpass.net%' )     OR
      (ss.machine like '%tmo-prod-wsndcm-%qpass.net%' and  upper(sa.sql_text) like  '%INSERT%ACTIVITY_STATE%' ) OR
      (ss.machine like '%tmo-prod-asydcm-%qpass.net%' and  upper(sa.sql_text) like  '%UPDATE%ACTIVITY_HEADER%' )
    );


**************************************************************************

Find number of execution and elapsed time of a query :

SQL> set lines 132 pages 1000 colsep |
 SELECT SQL_ID,PLAN_HASH_VALUE,SUM(ROUND (BUFFER_GETS_TOTAL, 0))  BUFFER_GETS_TOTAL , SUM(EXECUTIONS_TOTAL) EXECUTIONS_TOTAL,
SUM(ELAPSED_TIME_DELTA)/1000000/decode(sum(executions_delta),0,null,sum(executions_delta)) ELAPSED_TIME,
    SUM(ROUND (BUFFER_GETS_TOTAL, 0))/SUM(EXECUTIONS_TOTAL) BUFFER_GETS_EXEC ,
SUM(ROUND (ROWS_PROCESSED_TOTAL,0))/SUM(EXECUTIONS_TOTAL) ROWS_PRO_EXEC
    from dba_hist_sqlstat A
    where sql_id in ('&sql_id')
    and SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
        WHERE BEGIN_INTERVAL_TIME>=sysdate-1
    AND END_INTERVAL_TIME<=sysdate)
    GROUP BY SQL_ID,PLAN_HASH_VALUE
   ORDER BY SQL_ID,BUFFER_GETS_EXEC DESC;


Output:
----------------

SQL_ID       |PLAN_HASH_VALUE|BUFFER_GETS_TOTAL|EXECUTIONS_TOTAL|ELAPSED_TIME|BUFFER_GETS_EXEC|ROWS_PRO_EXEC
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
fs7hs88js9348|      597161504|       6512664175|              96|  12810.1492|      67840251.8|   923102.635




(or)


SELECT SQL_ID,PLAN_HASH_VALUE,SUM(ROUND (BUFFER_GETS_TOTAL, 0)) BUFFER_GETS_TOTAL ,
SUM(EXECUTIONS_TOTAL) EXECUTIONS_TOTAL,
 SUM(ELAPSED_TIME_DELTA)/1000000/decode(sum(executions_delta),0,null,sum(executions_delta)) ELAPSED_TIME,
    SUM(ROUND (BUFFER_GETS_TOTAL, 0))/SUM(EXECUTIONS_TOTAL) BUFFER_GETS_EXEC ,
SUM(ROUND (ROWS_PROCESSED_TOTAL, 0))/SUM(EXECUTIONS_TOTAL) ROWS_PRO_EXEC
    from dba_hist_sqlstat A
    where sql_id in ('&sql_area')
    and SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
        WHERE trim(to_char(BEGIN_INTERVAL_TIME,'DD-MON-YY HH24:MI:SS')) >= ('28-MAY-13 15.15.00')
        AND
        trim(to_char(END_INTERVAL_TIME,'DD-MON-YY HH24:MI:SS')) <= ('28-MAY-13 16.15.00'))
        GROUP BY SQL_ID,PLAN_HASH_VALUE
   ORDER BY SQL_ID,BUFFER_GETS_EXEC DESC;



SQL_ID        PLAN_HASH_VALUE BUFFER_GETS_TOTAL EXECUTIONS_TOTAL ELAPSED_TIME BUFFER_GETS_EXEC ROWS_PRO_EXEC
------------- --------------- ----------------- ---------------- ------------ ---------------- -------------
3yz8tktbv8fx1       389256908         105375661          4657034    .60243953       22.6272046    .999998926

*****************************************************************************