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 /
ORA-01450 maximum key length (3118) exceeded
All of a sudden, deploying a new version of the apps, I got this error. The fix is simple. The error is related to the block size of the tablespace where you want to deploy your object. In my case it was about the creation of an index an the key was exceeding the size…
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…
ORA-20000: ORU-10027: buffer overflow
Recently I got this error when I tried to execute a script with lots of dbms_output lines in it and the solution was to alter the script and to add the following: SET LINESIZE 999 SET PAGES 500 SET TIMING ON SET SERVEROUTPUT ON size UNLIMITED ……………………. BEGIN begin dbms_output.enable(NULL); end; ……………………….. END; /
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# –,…
Check default temp tablespace
col PROPERTY_NAME format a50 col PROPERTY_VALUE format a50 col DESCRIPTION format a50 set lin 300 SELECT PROPERTY_NAME,PROPERTY_VALUE,DESCRIPTION FROM DATABASE_PROPERTIES where PROPERTY_NAME=’DEFAULT_TEMP_TABLESPACE’;
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)…
