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 /

Find the Oracle session that’s using a lot of resources on a Linux system

To find that, I did the following: [[email protected] marius]$ ps aux | sort -nrk 3,3 | head -n 10 oracle 28986 75.8 1.6 10739264 1602896 ? Rs Jan24 137897:06 ora_q002_DBINST oracle 23660 63.1 1.2 10730424 1221500 ? Rs Feb12 97718:28 ora_q003_DBINST oracle 8360 15.7 6.0 10734592 5970484 ? Ss May28 377:34 oracleDBINST (LOCAL=NO) oracle 2264…

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)…