Scripts for Oralce DBA

-- Description : Displays information on the current wait states for all active database sessions.
-- Requirements : Access to the V$ views.
--------
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

########################################################
-- Description : Displays high water mark statistics.
-- Requirements : Access to the DBA views.
-----------------------------------------------------------------
COLUMN name FORMAT A40
COLUMN highwater FORMAT 999999999999
COLUMN last_value FORMAT 999999999999
SET PAGESIZE 24

SELECT hwm1.name, hwm1.highwater, hwm1.last_value FROM dba_high_water_mark_statistics hwm1 WHERE hwm1.version = (SELECT MAX(hwm2.version) FROM dba_high_water_mark_statistics hwm2 WHERE hwm2.name = hwm1.name) ORDER BY hwm1.name;

COLUMN FORMAT DEFAULT

########################################################
- Description : Displays the values of the dynamically memory pools.
-- Requirements : Access to the V$ views.
-- -----------------------------------------------------------------------------------
COLUMN name FORMAT A40
COLUMN value FORMAT A40

SELECT name, value FROM v$parameter WHERE SUBSTR(name, 1, 1) = '_' ORDER BY name;

########################################################
-- Description : Displays feature usage statistics.
-- Requirements : Access to the DBA views.
-- -----------------------------------------------------------------------------------
COLUMN name FORMAT A50
COLUMN detected_usages FORMAT 999999999999
SELECT u1.name, u1.detected_usages FROM dba_feature_usage_statistics u1 WHERE u1.version = (SELECT MAX(u2.version) FROM dba_feature_usage_statistics u2 WHERE u2.name = u1.name) ORDER BY u1.name;

COLUMN FORMAT DEFAULT

########################################################
-- Description : Displays scheduler information about job classes.
-- Requirements : Access to the DBA views.
-- -----------------------------------------------------------------------------------
SET LINESIZE 200
COLUMN service FORMAT A20
COLUMN comments FORMAT A40

SELECT job_class_name, resource_consumer_group, service, logging_level, log_history, comments FROM dba_scheduler_job_classes ORDER BY job_class_name;

########################################################
-- Description : Displays scheduler information about job programs.
-- Requirements : Access to the DBA views.
-- -----------------------------------------------------------------------------------
SET LINESIZE 250
COLUMN owner FORMAT A20
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A50
COLUMN comments FORMAT A40

SELECT owner, program_name, program_type, program_action, number_of_arguments, enabled, comments
FROM dba_scheduler_programs ORDER BY owner, program_name;

########################################################
-- Description : Displays scheduler information about job schedules.
-- Requirements : Access to the DBA views.
-- -----------------------------------------------------------------------------------
SET LINESIZE 250
COLUMN owner FORMAT A20
COLUMN schedule_name FORMAT A30
COLUMN start_date FORMAT A35
COLUMN repeat_interval FORMAT A50
COLUMN end_date FORMAT A35
COLUMN comments FORMAT A40

SELECT owner, schedule_name, start_date, repeat_interval, end_date, comments FROM dba_scheduler_schedules ORDER BY owner, schedule_name;

########################################################
-- Description : Displays scheduler information for running jobs.
-- Requirements : Access to the DBA views.
-- -----------------------------------------------------------------------------------
SET LINESIZE 200
COLUMN owner FORMAT A20

SELECT owner, job_name, running_instance, elapsed_time FROM dba_scheduler_running_jobs ORDER BY owner, job_name;

########################################################
-- 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.
-- -----------------------------------------------------------------------------------
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

########################################################
-- Description : Displays information about database services.
-- Requirements : Access to the DBA views.
-- -----------------------------------------------------------------------------------
SET LINESIZE 200
COLUMN name FORMAT A30
COLUMN network_name FORMAT A50

SELECT name, network_name FROM dba_services ORDER BY name;

########################################################
-- Description : Displays information on all database session waits.
-- Requirements : Access to the V$ views.
-- -----------------------------------------------------------------------------------
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15

SELECT NVL(s.username, '(oracle)') AS username, s.sid, s.serial#, sw.event, sw.wait_class, sw.wait_time, sw.seconds_in_wait, sw.state FROM v$session_wait sw, v$session s WHERE s.sid = sw.sid ORDER BY sw.seconds_in_wait DESC;

########################################################
-- 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.
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL c:\temp.sql

SELECT 'ANALYZE TABLE "' || table_name || '" COMPUTE STATISTICS;' FROM all_tables WHERE owner = Upper('&1') ORDER BY 1;

SPOOL OFF

@c:\temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

########################################################
-- Description : Compiles all invalid triggers for specified schema, or all schema.
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TRIGGER ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a WHERE a.object_type = 'TRIGGER' AND a.status = 'INVALID' AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF

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

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

########################################################
-- Description : Lists all objects being accessed in the schema.
-- Requirements : Access to the v$views.
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET VERIFY OFF

SELECT Substr(a.object,1,30) object, a.type, a.sid, b.username, b.osuser, b.program FROM v$access a, v$session b WHERE a.sid = b.sid AND a.owner = Upper('&1');

PROMPT
SET PAGESIZE 18
########################################################