SQL Developer Data Modeler 3.1

New version SQL Developer Data Modeler 3.1 is available. Also SQL Developer 3.1 published.

XMLType from no rows

I used DBMS_XMLGEN package in my post constraint name generated. There I used GETXML function and got problems when the schema constraints were all named. So the query result was empty - no rows.

SELECT XMLTYPE(
         DBMS_XMLGEN.GETXML('SELECT 1 FROM dual WHERE 1=0')
       ) AS xml
  FROM dual
;

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

There is a built in DBMS_XMLGEN.GETXMLTYPE function to get the XMLType. This is how you get rid of the ORA-06502 problem.

SELECT DBMS_XMLGEN.GETXMLTYPE('SELECT 1 FROM dual WHERE 1=0') AS xml
  FROM dual
;

A single serializable session throwing ORA-08177

We have a newly created table in a 11.2.0.3 Enterprise edition instance. A single session is throwing ORA-08177 can't serialize access for this transaction. The whole service level is marked for trace.

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('servicename')

No one else is updating the tables involved. There comes out only two trace files. One having only the select 1 from dual connection pool check. And in the another trace there may be found.

ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE
END OF STMT

Yes we wanted transactions to be serialized. Couple of inserts and internal staements. And an update to seg$ table thowing ORA-08177.

PARSING IN CURSOR #47594977567928 len=314 dep=1 uid=0 oct=6 lid=0 tim=1327068762208173 hv=3096556448 ad='de83deb8' sqlid='0kkhhb2w93cx0'
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL
, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where t
s#=:1 and file#=:2 and block#=:3
END OF STMT
PARSE #47594977567928:c=0,e=673,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1327068762208172
EXEC #47594977567928:c=1999,e=1411,p=0,cr=3,cu=1,mis=1,r=1,dep=1,og=4,plh=2170058777,tim=1327068762209667
STAT #47594977567928 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  SEG$ (cr=3 pr=0 pw=0 time=116 us)'
STAT #47594977567928 id=2 cnt=1 pid=1 pos=1 obj=14 op='TABLE ACCESS CLUSTER SEG$ (cr=3 pr=0 pw=0 time=16 us cost=2 size=68 card=1)'
STAT #47594977567928 id=3 cnt=1 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=6 us cost=1 size=0 card=1)'
CLOSE #47594977567928:c=0,e=2,dep=1,type=3,tim=1327068762209778
EXEC #47594977567928:c=0,e=125,p=0,cr=3,cu=1,mis=0,r=1,dep=1,og=4,plh=2170058777,tim=1327068762209931
CLOSE #47594977567928:c=0,e=1,dep=1,type=3,tim=1327068762209972
EXEC #47594977567928:c=0,e=129,p=0,cr=4,cu=1,mis=0,r=1,dep=1,og=4,plh=2170058777,tim=1327068762210127
CLOSE #47594977567928:c=0,e=1,dep=1,type=3,tim=1327068762210167
EXEC #47594984047704:c=50992,e=50764,p=7,cr=576,cu=241,mis=1,r=0,dep=0,og=1,plh=0,tim=1327068762211409
ERROR #47594984047704:err=8177 tim=1327068762211441
STAT #47594984047704 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=1 us)'

*** 2012-01-20 16:12:42.223
WAIT #47594984047704: nam='log file sync' ela= 11537 buffer#=84 sync scn=604674242 p3=0 obj#=-1 tim=1327068762223055
WAIT #47594984047704: nam='SQL*Net break/reset to client' ela= 21 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=1327068762223170
WAIT #47594984047704: nam='SQL*Net break/reset to client' ela= 3715 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=1327068762226909

After awhile the problem vanished... No solutions in this post. Just wondering why...

Oracle instance memory usage

The right place to look for memory usage of a process in at operating system level. Interpreting the shared memory usage might be misleading. The top command seems to add the shared part to each process. And thou makes it hard to get right numbers. Here is a blog post trying to do that. Thou you could ask the numbers also from Oracle. Parameters influencing Oracle instance memory usage limits. 11g instance might have memory target set or sga and pga set separately.

select name,display_value 
  from v$parameter 
 where name like 'mem%target' 
    or name like 'pga%' 
    or name like 'sga%'
;

The actual PGA usage might hit and go above those limits. The current allocation can be queried.

select round(sum(bytes)/1024/1024/1024,3) SGA_G 
  from v$sgastat;

select round(value/1024/1024/1024,3) PGA_G
  from v$pgastat 
 where name = 'total PGA allocated';

Most often the database load varies over time. For resource planning it is vital to know how the memory usage is changing during night time. There might be going on some batch jobs whose behavior is not seen during day time. If you have diagnostics pack purchased you can also ask what was the situation earlier. Here is a query getting hourly memory usages of an Oracle instance.

select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) tot,trunc(SN.END_INTERVAL_TIME,'mi') time
  from
(select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024/1024,3) allo 
   from DBA_HIST_SGASTAT 
  group by snap_id,INSTANCE_NUMBER) sga
,(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024/1024,3) allo 
    from DBA_HIST_PGASTAT where name = 'total PGA allocated' 
   group by snap_id,INSTANCE_NUMBER) pga
, dba_hist_snapshot sn 
where sn.snap_id=sga.snap_id
  and sn.INSTANCE_NUMBER=sga.INSTANCE_NUMBER
  and sn.snap_id=pga.snap_id
  and sn.INSTANCE_NUMBER=pga.INSTANCE_NUMBER
order by sn.snap_id desc, sn.INSTANCE_NUMBER
;

Longest roman number

It is possible to ask Oracle database the roman number presentation of a number. Just use to_char function with RN format. Just wondering what is the longest roman number.

select rn,len
 from (
  select rn, max(length(rn))over() mlen, length(rn) len
    from (
    select to_char(level,'RN') rn from dual connect by level <= 4000
    )
  )
where mlen=len
;

Well they all are not 15 characters. Need to trim.

select n, rn,len
 from (
  select n, rn, max(length(rn))over() mlen, length(rn) len
    from (
    select level n, trim(to_char(level,'RN')) rn from dual connect by level <= 4000
    )
  )
where mlen=len
;

3888 MMMDCCCLXXXVIII 15
4000 ############### 15

4000 and above are not converted. Also negative numbers and zero are not available. But in between 3888 seems to be "the longest".

Using client identifier to populate a column

Earlier I wrote about missing dbms_session.get_client_identifier. Here is a compound trigger example using session client identifier to populate a column in a table.

drop table t;

create table t(n number, client_id varchar2(30) not null);

create or replace trigger t_iuc for insert or update on t 
  compound trigger
  cli varchar2(30);
 before statement is
  begin
   cli := SYS_CONTEXT('userenv', 'CLIENT_IDENTIFIER');
  end before statement;
 before each row is
  begin
   :new.client_id := cli;
  end before each row;
end;
/

exec dbms_session.set_identifier('rafu')

insert into t(n) select level from dual connect by level < 4;

select * from t;

1 rafu
2 rafu
3 rafu

Constraint name generated

Generated constraint names makes development, testing and production environment error messages vary. And those reported SYS_C006206630 could be more informative. To identify those generated constraint names *_constraints views has the information available at generated column. The constraint search_condition is a long typed column and thou hard to use. Working with long columns document gives tools to convert the search column to varchar2. This query reports generated named constraints in users schema.

WITH xml AS (
          SELECT DBMS_XMLGEN.GETXMLTYPE(
                     'select co.constraint_name
                           , co.table_name
                           , cc.column_name
                           , co.constraint_type
                           , co.r_constraint_name
                           , co.search_condition  
                        from user_constraints co
                        left outer join user_cons_columns cc
                          on co.constraint_name = cc.constraint_name 
                       where co.generated = ''GENERATED NAME''')
                    AS xml
          FROM   dual
          ), concol as (
  SELECT extractValue(xs.object_value, '/ROW/TABLE_NAME')       AS table_name
  ,      extractValue(xs.object_value, '/ROW/COLUMN_NAME')      AS column_name
  ,      extractValue(xs.object_value, '/ROW/CONSTRAINT_TYPE')  AS constraint_type
  ,      extractValue(xs.object_value, '/ROW/CONSTRAINT_NAME')  AS constraint_name
  ,      extractValue(xs.object_value, '/ROW/R_CONSTRAINT_NAME')  AS r_constraint_name
  ,      extractValue(xs.object_value, '/ROW/SEARCH_CONDITION') AS search_condition
  FROM   xml x
  ,      TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
)
select cc.* 
  from concol cc
order by table_name, column_name,constraint_type
;

Give names to the reported constraints to your modeling tool. NOT NULL checks are not first in the list to be named. NOT NULL error messages are informative with the generated names also. But naming also those make the different (DEV,TST,PRD) schemas a bit more similar. SQL Developer Data modeler 3.1 EA3 has the possibility to name those also at relational model. Formerly it was only possible at physical model. If you feel like too much work modeling those at least change the reported names.

ALTER TABLE test RENAME CONSTRAINT SYS_C006206630 TO test_pk;