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 /
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 of the default block size of the default tablespace. All I had to do is to enable the 16K block size and to create a new tablespace using the 16k block size.
SQL> ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "TBS_DEFAULT";
ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "TBS_DEFAULT"
*
ERROR at line 1:
ORA-01450: maximum key length (3118) exceeded
SQL> show parameter db_16
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
SQL> alter system set db_16k_cache_size=500M;
System altered.
SQL> CREATE TABLESPACE "NEW_TBS" DATAFILE ...... SIZE 100M BLOCKSIZE 16K;
Tablespace created.
SQL> ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "NEW_TBS";
Table altered.
SQL>
To find that, I did the following:
[oracle@dbserver 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 15.5 5.8 10736632 5832076 ? Rs May28 352:31 oracleDBINST (LOCAL=NO) oracle 8356 15.1 5.5 10736640 5491732 ? Rs May28 364:28 oracleDBINST (LOCAL=NO) oracle 5592 15.0 0.1 10731024 152936 ? Ss 13:22 0:00 ora_j000_DBINST oracle 8358 14.9 5.6 10736632 5635028 ? Rs May28 358:14 oracleDBINST (LOCAL=NO) oracle 8350 14.8 6.1 10736644 6055308 ? Rs May28 355:42 oracleDBINST (LOCAL=NO) oracle 2266 13.5 5.8 10736632 5779248 ? Rs May28 306:35 oracleDBINST (LOCAL=NO) oracle 8342 13.3 5.9 10736636 5858864 ? Rs May28 319:50 oracleDBINST (LOCAL=NO)
And then on the sqlplus:
SYS @ DBINST > select s.sid, s.serial#, s.username,
to_char(s.logon_time,'DD-MON HH24:MI:SS') logon_time,
p.pid oraclepid, p.spid "ServerPID", s.process "ClientPID",
s.program clientprogram, s.module, s.machine, s.osuser,
s.status, s.last_call_et
from gv$session s, gv$process p
where p.spid=nvl('&unix_process',' ')
and s.paddr=p.addr
order by s.sid 2 3 4 5 6 7 8 9
10 ;
Enter value for unix_process: 28986
SID SERIAL# USERNAME LOGON_TIME ORACLEPID ServerPID ClientPID
------------ ------------ ------------------------------ ------------------------ ------------ ------------------------ ------------------------
CLIENTPROGRAM MODULE MACHINE
------------------------------------------------ ---------------------------------------------------------------- ----------------------------------------------------------------
OSUSER STATUS LAST_CALL_ET
------------------------------ -------- ------------
3027 1 24-JAN 08:54:00 40 28986 28986
oracle@dbserver (Q002) Streams dbserver
oracle ACTIVE 10902893
Elapsed: 00:00:00.01
SYS @ DBINST >
So it seems that the STREAMS are very busy 🙂
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; /
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 ';
LV_SQL:= LV_SQL ||'dbms_stats.gather_table_stats (''OWNER'','||':1'||',partname=>'||':2'||',granularity=>''partition'',estimate_percent => 1);' ;
LV_SQL:= LV_SQL ||' END ;';
EXECUTE IMMEDIATE LV_SQL USING I.T,I.P;
END LOOP;
END;
/
Or we can put it in a shell script:
#!/bin/bash
export ORACLE_SID=DB_NAME
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3
export PATH=$PATH:$ORACLE_HOME/bin
cd /u01/app/oracle/admin/
sqlplus -S /nolog << EOF > gather_part_stats.log 2>> gather_part_stats.log
conn user/passwd
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 or LAST_ANALYZED < sysdate -7) AND
order by LAST_ANALYZED;
BEGIN
FOR I IN C1 LOOP
dbms_output.put_line('Gathering stats for :'||I.P);
BEGIN
LV_SQL:= 'BEGIN ';
LV_SQL:= LV_SQL ||'dbms_stats.gather_table_stats (''OWNER'','||':1'||',partname=>'||':2'||',granularity=>''partition'',estimate_percent => 1,CASCADE => TRUE, method_opt => ''FOR ALL COLUMNS SIZE 1'');' ;
LV_SQL:= LV_SQL ||' END ;';
EXECUTE IMMEDIATE LV_SQL USING I.T,I.P;
END;
END LOOP;
END;
/
exit
EOF
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 l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ; SELECT sid, lmode acquired, request, blocking_session, SQL_TEXT FROM v$lock l JOIN v$session s USING (sid) LEFT JOIN v$sqlarea USING (sql_id) WHERE block = 1 OR request > 0 ;
Tree like:
WITH sessions_info
AS
(SELECT sid, blocking_session, row_wait_obj#, sql_id FROM v$session)
SELECT DECODE(LEVEL, 1, 'Root Blocker') STATUS, LPAD (' ', LEVEL) || sid sid,
object_name,
SUBSTR (sql_text, 1, 50) sql_text
FROM sessions_info s
LEFT OUTER JOIN dba_objects ON (object_id = row_wait_obj#)
LEFT OUTER JOIN v$sql USING (sql_id)
WHERE sid IN (SELECT blocking_session FROM sessions_info)
OR blocking_session IS NOT NULL
CONNECT BY PRIOR sid = blocking_session
START WITH blocking_session IS NULL;
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request /
SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;
select * from dba_waiters;
select * from dba_blockers;
If the DBA_WAITERS and DBA_BLOCKERS doesn’t exist, you can execute the script below and create them:
@?/rdbms/admin/catblock.sql
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# --, b.segblk , ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb , a.SID , a.serial# , a.username , a.sql_id , a.osuser , a.program , a.status FROM v$session a , v$sort_usage b , v$process c , v$parameter p WHERE p.NAME = 'db_block_size' AND a.saddr = b.session_addr AND a.paddr = c.addr AND a.status='ACTIVE' AND to_number(ROUND(((b.blocks * 8192)/1024/1024),2))>100 ORDER BY size_mb , b.TABLESPACE , b.segfile# , b.segblk# , b.blocks; col username format a10 col osuser format a8 col SID_SERIAL format a8 col PROCESS format 9999999 col tablespace format a5 col status format a9 col space format 9999999999999 set lin 300 SELECT a.username, a.osuser, a.sid||','||a.serial# SID_SERIAL, c.spid Process,b.tablespace tablespace, a.status, sum(b.extents)* 1024*1024 space FROM v$session a,v$sort_usage b, v$process c, dba_tablespaces d WHERE a.saddr = b.session_addr AND a.paddr = c.addr AND b.tablespace=d.tablespace_name AND d.tablespace_name='&temp_tbs' AND a.status='ACTIVE' group by a.username, a.osuser, a.sid||','||a.serial#, c.spid,b.tablespace, a.status order by space;
