Most Commonly used DBA Scripts:1

Script To Find The Active_Session_Waits

SET LINESIZE 200
SET PAGESIZE 1100
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_timeFROM v$session a, v$process dWHERE a.paddr = d.addrAND a.status = 'ACTIVE'ORDER BY 1,2;

Script To Find Database Usage High Watermark :

COLUMN name FORMAT A40
COLUMN highwater FORMAT 999999999999
COLUMN last_value FORMAT 999999999999
SET PAGESIZE 24SELECT hwm1.name, hwm1.highwater, hwm1.last_valueFROM dba_high_water_mark_statistics hwm1WHERE 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

Display The Value of Dynamically Memory Pools :

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

COLUMN FORMAT DEFAULT


Display Feature Usage Statistics :

COLUMN name FORMAT A50
COLUMN detected_usages FORMAT 999999999999
SELECT u1.name, u1.detected_usagesFROM dba_feature_usage_statistics u1WHERE u1.version = (SELECT MAX(u2.version) FROM dba_feature_usage_statistics u2 WHERE u2.name = u1.name)ORDER BY u1.name;

COLUMN FORMAT DEFAULT

Displays Scheduler Information About Job Classes :

SET LINESIZE 200
COLUMN service FORMAT A20
COLUMN comments FORMAT A40
SELECT job_class_name, resource_consumer_group, service, logging_level, log_history, commentsFROM dba_scheduler_job_classesORDER BY job_class_name;

Displays scheduler information about job programs :
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, commentsFROM dba_scheduler_programsORDER BY owner, program_name;

Displays scheduler information about job schedules :

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, commentsFROM dba_scheduler_schedulesORDER BY owner, schedule_name;

Displays scheduler job information :

SET LINESIZE 200
COLUMN owner FORMAT A20
COLUMN next_run_date FORMAT A35
SELECT owner, job_name, enabled, job_class, next_run_dateFROM dba_scheduler_jobsORDER BY owner, job_name;

Displays scheduler information for running jobs :

SET LINESIZE 200
COLUMN owner FORMAT A20
SELECT owner, job_name, running_instance, elapsed_timeFROM dba_scheduler_running_jobsORDER BY owner, job_name;

Displays information on all database sessions with the username column displayed as a heirarchy if locks are present :

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_timeFROM v$session s CONNECT BY PRIOR s.sid = s.blocking_session START WITH s.blocking_session IS NULL;

SET PAGESIZE 14

Displays segment advice for the specified segment :

SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 200
SET VERIFY OFF
DECLARE l_object_id NUMBER;
l_task_name VARCHAR2(32767) := 'SEGMENT_ADVISOR_TASK';
l_object_type VARCHAR2(32767) := UPPER('&1');
l_attr1 VARCHAR2(32767) := UPPER('&2');
l_attr2 VARCHAR2(32767) := UPPER('&3');
BEGIN IF l_attr2 = 'NULL' THEN l_attr2 := NULL;
END IF;

DBMS_ADVISOR.create_task ( advisor_name => 'Segment Advisor', task_name => l_task_name);

DBMS_ADVISOR.create_object ( task_name => l_task_name, object_type => l_object_type, attr1 => l_attr1, attr2 => l_attr2, attr3 => NULL, attr4 => 'null', attr5 => NULL, object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter ( task_name => l_task_name, parameter => 'RECOMMEND_ALL', value => 'TRUE');

DBMS_ADVISOR.execute_task(task_name => l_task_name);FOR cur_rec IN (SELECT f.impact, o.type, o.attr1, o.attr2, f.message, f.more_info FROM dba_advisor_findings f JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name WHERE f.task_name = l_task_name ORDER BY f.impact DESC) LOOP DBMS_OUTPUT.put_line('..');

DBMS_OUTPUT.put_line('Type : ' cur_rec.type); DBMS_OUTPUT.put_line('Attr1 : ' cur_rec.attr1);
DBMS_OUTPUT.put_line('Attr2 : ' cur_rec.attr2); DBMS_OUTPUT.put_line('Message : ' cur_rec.message);
DBMS_OUTPUT.put_line('More info : ' cur_rec.more_info);
END LOOP;
DBMS_ADVISOR.delete_task(task_name => l_task_name);
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('Error : ' DBMS_UTILITY.format_error_backtrace);
DBMS_ADVISOR.delete_task(task_name => l_task_name);
END;

Displays information about database services :
SET LINESIZE 200
COLUMN name FORMAT A30
COLUMN network_name FORMAT A50
SELECT name, network_nameFROM dba_servicesORDER BY name;

Displays information on all database session waits :

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 sWHERE s.sid = sw.sid ORDER BY sw.seconds_in_wait DESC;

Displays scheduler information about window groups :

SET LINESIZE 250
COLUMN comments FORMAT A40
SELECT window_group_name, enabled, number_of_windows, commentsFROM dba_scheduler_window_groupsORDER BY window_group_name;SELECT window_group_name, window_nameFROM dba_scheduler_wingroup_membersORDER BY window_group_name, window_name;

Displays scheduler information about windows :

SET LINESIZE 250
COLUMN comments FORMAT A40
SELECT window_name, resource_plan, enabled, active, commentsFROM dba_scheduler_windowsORDER BY window_name;