1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Important Oracle DBA Scripts

Oracle DBA Scripts:

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 24
SELECT 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, valueFROM v$parameterWHERE 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 500SET PAGESIZE 1000
COLUMN username FORMAT A15COLUMN machine FORMAT A25COLUMN 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 sCONNECT BY PRIOR s.sid = s.blocking_sessionSTART WITH s.blocking_session IS NULL;
SET PAGESIZE 14

Displays segment advice for the specified segment :

SET SERVEROUTPUT ON SIZE 1000000SET LINESIZE 200SET 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 200COLUMN name FORMAT A30COLUMN network_name FORMAT A50
SELECT name, network_nameFROM dba_servicesORDER BY name;

Displays information on all database session waits :

SET LINESIZE 200SET PAGESIZE 1000
COLUMN username FORMAT A20COLUMN event FORMAT A30COLUMN 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.stateFROM v$session_wait sw, v$session sWHERE s.sid = sw.sidORDER 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;

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy