Oracle Scripts

Some Oracle Scripts maybe will be useful for all of us :

 1-Active_session_wait.sql

 — ———————————————————————————–
— File Name    : http://osamamustafa.blogspot.com
— Author       : Osama Mustafa
— Description  : Displays information on the current wait states for all active database sessions.
— Requirements : Access to the V$ views.
— Call Syntax  : @active_session_waits
— Last Modified: 02/05/2011
— ———————————————————————————–
SET LINESIZE 250
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20

SELECT NVL(a.username, ‘(oracle)’) AS username,
       a.osuser,
       a.sid,
       a.serial#,
       d.spid AS process_id,
       a.wait_class,
       a.seconds_in_wait,
       a.state,
       a.blocking_session,
       a.blocking_session_status,
       a.module,
       TO_CHAR(a.logon_Time,’DD-MON-YYYY HH24:MI:SS’) AS logon_time
FROM   v$session a,
       v$process d
WHERE  a.paddr  = d.addr
AND    a.status = ‘ACTIVE’
ORDER BY 1,2;

SET PAGESIZE 14

 2-Analys_All .sql

— ———————————————————————————–
— File Name    : http://osamamustafa.blogspot.com
— Author       : Osama Mustafa
— Description  : Outdated script to analyze all tables for the specified schema.
— Comment      : Use DBMS_UTILITY.ANALYZE_SCHEMA or DBMS_STATS.GATHER_SCHEMA_STATS if your server allows it.
— Call Syntax  : @ananlyze_all (schema-name)
— Last Modified: 02/11/2010
— ———————————————————————————–
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT ‘ANALYZE TABLE “‘ || table_name || ‘” COMPUTE STATISTICS;’
FROM   all_tables
WHERE  owner = Upper(‘&1’)
ORDER BY 1;

SPOOL OFF

— Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON




3-Lock_tree.sql

— ———————————————————————————–
— File Name    : http://osamamustafa.blogspot.com
— Author       : Osama Mustafa
— Description  : Displays information on all database sessions with the username
—                column displayed as a heirarchy if locks are present.
— Requirements : Access to the V$ views.
— Call Syntax  : @lock_tree
— Last Modified: 03/05/2011
— ———————————————————————————–
SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT LPAD(‘ ‘, (level-1)*2, ‘ ‘) || NVL(s.username, ‘(oracle)’) AS username,
       s.osuser,
       s.sid,
       s.serial#,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,’DD-MON-YYYY HH24:MI:SS’) AS logon_time
FROM   v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;

SET PAGESIZE 14

I will upload more Scripts Just copy & Paste Save it as SQL .. Enjoy 🙂