Saturday, September 17, 2011

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 :)


No comments:

Post a Comment