Scripts
Create a DBMS_JOB
–schedules this job to run immediately and then every hour after that DECLARE JobNo dba_jobs.job%TYPE; BEGIN DBMS_JOB.SUBMIT ( job => JobNo, what => ‘begin SESSIONS_RM(minutes=>180); end;’, next_date => SYSDATE, interval => ‘SYSDATE + 1/24’); COMMIT; END; /
view_fra.sql
set lines 185 col df format 999,999,999 heading “Datafile Size (in MB)” col tf format 999,999,999 heading “Tempfile Size (in MB)” col rd format 999,999,999 heading “Redo Logfile Size (in MB)” col nm format a50 heading “Location” col sl format 999,999,999 heading “Size (in MB)” col su format 999,999,999 heading “Used (in MB)” col sr […]
show_users.sql
column mu format a40 heading “Machine – User” select machine||’ – ‘||nvl(username,'[BKGD PROCESS]’) mu, count(*) from v$session group by machine||’ – ‘||nvl(username,'[BKGD PROCESS]’); select count(*) “Total Count of Sessions” from v$session;
Show DBMS_SCHEDULER job information.
To show details on job run: select log_date , job_name , status , req_start_date , actual_start_date , run_duration from dba_scheduler_job_run_details To show running jobs: select job_name , session_id , running_instance , elapsed_time , cpu_used from dba_scheduler_running_jobs; To show job history: select log_date , job_name , status from dba_scheduler_job_log; show all schedules: select schedule_name, schedule_type, start_date, […]
Gathering session and SQL information without actually turning on Oracle auditing.
01,15,30,59 * * * * /dba/inser_tp.ksh >> /dba/third_party_tools_usage.txt 2>&1 #!/bin/ksh . /dba/env $ORACLE_HOME/bin/sqlplus “/ as sysdba”
gen_index_reb_script.sql
select ‘alter index ‘||owner||’.’||segment_name||’ rebuild online;’ from sys.dba_extents where owner like ‘BB%’ and segment_type = ‘INDEX’ group by owner, segment_name order by sum(bytes/1024/1024) /
check_index_extents.sql
col owner format a15 col segment_name format a40 select owner, segment_name, sum(bytes/1024/1024) “Size (MB)” from sys.dba_extents where owner like ‘BB%’ and segment_type = ‘INDEX’ group by owner, segment_name order by sum(bytes/1024/1024) /
SQL Column Formats
set termout ON define ON feed ON verify ON trim on trims on set pause off timing off clear col clear break clear compute ttitle off btitle off repf off reph off undefine undefine name ALTER SESSION SET nls_language= ‘AMERICAN’ nls_territory= ‘AMERICA’ nls_currency= ‘$’ nls_iso_currency= ‘AMERICA’ nls_numeric_characters= ‘.,’ nls_calendar= ‘GREGORIAN’ nls_date_format= ‘DD/mm/RRRR:HH/MI/SS PM’ nls_date_language= ‘AMERICAN’ […]
Check hot backup mode
Small query to list Oracle tablespaces, that are in backup mode: select d.tablespace_name, b.time from dba_data_files d, v$backup b where d.file_id = b.FILE# and b.STATUS = ‘ACTIVE’ ;
PL/SQL example using format, break and skip
col c1 heading ‘Begin|Interval|time’ format a20 col c2 heading ‘SQL|ID’ format a13 col c3 heading ‘Executions|Delta’ format 9,999 col c4 heading ‘Buffer|Gets|Delta’ format 9,999 col c5 heading ‘Disk|Reads|Delta’ format 9,999 col c6 heading ‘IO Wait|Delta’ format 9,999 col c7 heading ‘Application|Wait|Delta’ format 9,999 col c8 heading ‘Concurrency|Wait|Delta’ format 9,999 break on c1 skip 2 break […]