Installing SQLcl

SQLcl is the new command line tool from Oracle, more specifically from the SQL Developer team. It is currently an Early Adopter (EA) release and you can download it from: http://www.oracle.com/technet Source: Installing SQLcl  

List all running jobs

SET PAUSE ON SET PAUSE ‘Press Return to Continue’ SET PAGESIZE 60 SET LINESIZE 300 SET VERIFY OFF SELECT a.job “Job”, a.sid, a.failures “Failures”, Substr(To_Char(a.last_date,’DD-Mon-YYYY HH24:MI:SS’),1,20) “Last Date”, Substr(To_Char(a.this_date,’DD-Mon-YYYY HH24:MI:SS’),1,20) “This Date” FROM dba_jobs_running a /

Grab stats for partitions

Useful scripts to grab stats for partitions: set serveroutput on DECLARE LV_SQL VARCHAR2(1000); CURSOR C1 IS SELECT TABLE_NAME T , PARTITION_NAME P , LAST_ANALYZED L  FROM USER_TAB_PARTITIONS WHERE LAST_ANALYZED is null AND TABLE_NAME = ‘&TABLE_NAME’ and partition_name like (‘&PART_NAME’); BEGIN   FOR I IN C1 LOOP    dbms_output.put_line(‘Gathering stats for :’||I.P);    LV_SQL:= ‘BEGIN  ‘;…

Get the blocking sessions

select s1.username || ‘@’ || s1.machine     || ‘ ( SID=’ || s1.sid || ‘ )  is blocking ‘     || s2.username || ‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status     from v$lock l1, v$session s1, v$lock l2, v$session s2     where s1.sid=l1.sid and s2.sid=l2.sid     and…

Where is the TEMP space used?

The following statements should give us more indication about the TEMP usage and what is using it: col username format a10 col osuser format a8 col SID_SERIAL format a8 col PROCESS format 9999999 col tablespace format a7 col status format a9 col size_mb format 9999999999999 set lin 300 SELECT   b.TABLESPACE        –, b.segfile#        –,…

OPTIMAL UNDO size

Let’s determine the optimal size for UNDO: col “ACTUAL UNDO SIZE [MByte]” format 999999 col “UNDO RETENTION [Sec]” format 99999 col “OPTIMAL UNDO RETENTION [Sec]” format 999999 SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]”,        SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]”,        ROUND((d.undo_size / (to_number(f.value) *        g.undo_block_per_sec))) “OPTIMAL UNDO RETENTION [Sec]”   FROM (        SELECT SUM(a.bytes)…