Driven by a post from Lewis about “OSDM: Rerverse Engineer A Schema“, I dared my luck to play with Oracle SQL Developer Data Modeler (OSDM), trying to see if it understands the XML database realm off doing things. It doesn’t understands it…at least, yet. I used the early adopter release 1.5.1 (build 518).
I wonder if it is reasonable that I am disappointed; In the long run the “XDB” schema that I used to reverse engineer isn’t relational but (at least) object-relational. Most of the object relational issues, OSDM understands, but not the theory behind XMLType tables. I tried it multiple times, in the end, even the “secondary tables” and “spatial properties” option you can check during the reverse enginering option.
Although in it now also reverse enginered the PATH_TABLE in the XDB schema, it still didn’t “know” what to do with the XMLType tables which where XML schema based and ended up with DDL that was far from complete. An XMLIndex, a domain index with its foundation a PATH_TABLE, is especially designed for the XMLDB realm and apparently is only picked up if you use the “spatial properties” option.
A simple binary XML table, using BASICFILE storage, shows the following generated DDL
CREATE TABLE XM_TAB
OF SYS.XMLTYPE
SUBSTITUTABLE AT ALL LEVELS
OBJECT IDENTIFIER IS SYSTEM GENERATED
PCTFREE 10
PCTUSED 40
MAXTRANS 255
TABLESPACE USERS
LOGGING
STORAGE (
INITIAL 65536
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
MONITORING;
.
It should have been something like the following
SQL> select dbms_metadata.get_ddl('TABLE','XM_TAB')
2* from dual;
DBMS_METADATA.GET_DDL('TABLE','XM_TAB')
-----------------------------------------------------------
CREATE TABLE "XM_TAB" OF "SYS"."XMLTYPE"
OIDINDEX ( PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "USERS" )
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT)
TABLESPACE "USERS"
XMLTYPE COLUMN "SYS_NC_ROWINFO$"
STORE AS BASICFILE BINARY XML (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT)) ALLOW NONSCHEMA DISALLOW ANYSCHEMA
1 row selected.
One of the issues here is that the OSDM generator doesn’t use double quotes, so objects are not created case-sensitive, but maybe I missed a setting that allows to generate double quotes in the DDL scripting anyway.
I did once a test, “Oracle 11g – Reverse Engineering Attempt of the XDB Schema“, with Toad Data Modeler while trying to see what would happen if I used it on the XDB Schema. Although nog yet 11gR compliant, it did a very nice attempt and most of it correct. OSDM doesn’t even come near to this achievement.
Don’t get me wrong, I just hoped for the better, because it is only an early adopter release, who knows and this will be improved. I would really hope so.
If you would use it for a relational schema, as I did trying to reverse engineer the SYSMAN schema that is used for the local DB Console, then it does a remarkable job. With ease it handled hundreds off tables and other objects. At least for that I am very grateful, because we have finally again a worthy successor of the Designer Suite.
Rectifications needed…
Can’t read… I got mail from Oracle that OSDM doesn’t support 11g new features yet. Apparently I also overlooked the following in the “readme” that goes with the current OSDM build.
Support for Oracle 11g
Full support for Oracle 11g needs to be added. The current Oracle 11g implementation is Oracle 10g compatible.
I don’t have a Oracle 10gRx database at hand, so I tried some simple 9i2, 10g syntax to see how OSDM handles this.
I created an XMLType based on CLOB storage and a XMLType table (Object Relational Storage) based on a XML Schema to test old syntax handling. Both of these “XMLType”‘s are available in Oracle 9.2.0.3 and upwards.
SQL> create table xmltab
2 (xdata xmltype)
3 xmltype column "XDATA" store as CLOB
4 nomonitoring
5 ;
Table created.
SQL> select dbms_metadata.get_ddl('TABLE','XMLTAB','TEST') from dual;
DBMS_METADATA.GET_DDL('TABLE','XMLTAB')
---------------------------------------------------------------
CREATE TABLE "TEST"."XMLTAB"
( "XDATA" "SYS"."XMLTYPE"
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
XMLTYPE COLUMN "XDATA" STORE
AS BASICFILE CLOB "NOMONITORING"(
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT))
1 row selected.
OSDM DDL output shows the following:
CREATE TABLE TEST.XMLTAB
(
XDATA XMLTYPE
)
PCTFREE 10
PCTUSED 40
MAXTRANS 255
TABLESPACE USERS
LOGGING
STORAGE (
INITIAL 65536
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
MONITORING
;
I am missing out on some tablespace details but the monitoring part is picked up correctly, in the light that OSDM isn’t 11g new features compatible. NOMONITORING and MONITORING keywords are ignored in 11g (See: Automatically Collecting Statistics on Tables), although dbms_metadata isn’t aware of this yet.
SQL> select table_name, monitoring from user_tables;
TABLE_NAME MON
------------------------------ ---
XMLTAB YES
1 row selected.
OK, what if I create an Object Relational table based on an XML Schema, in this case “root.xsd”
SQL> begin
2 DBMS_XMLSCHEMA.deleteSchema(
3 'http://localhost/public/myschema.xsd', 4);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> purge recyclebin;
Recyclebin purged.
SQL> begin
2 DBMS_XMLSCHEMA.registerSchema(
3 SCHEMAURL => 'http://localhost/public/myschema.xsd',
4 SCHEMADOC => xdbURIType('/public/root.xsd').getClob(),
5 LOCAL => FALSE, -- local
6 GENTYPES => TRUE, -- generate object types
7 GENBEAN => FALSE, -- no java beans
8 GENTABLES => TRUE, -- generate object tables
9 OWNER => USER);
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
INFO182_TAB TABLE
ROOT184_TAB TABLE
2 rows selected.
SQL> select dbms_metadata.get_ddl('TABLE','ROOT184_TAB',user)
from dual;
DBMS_METADATA.GET_DDL('TABLE','ROOT184_TAB',USER)
------------------------------------------------------------
CREATE TABLE "TEST"."ROOT184_TAB" OF "SYS"."XMLTYPE"
XMLSCHEMA "http://localhost/public/myschema.xsd"
ELEMENT "ROOT" ID 3468 PCTFREE 10
PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
VARRAY "XMLEXTRA"."NAMESPACES"
STORE AS BASICFILE LOB "NAMESPACES188_L"
(ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT))
VARRAY "XMLEXTRA"."EXTRADATA"
STORE AS BASICFILE LOB "EXTRADATA187_L"
(ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT))
VARRAY "XMLDATA"."SYS_XDBPD$"
STORE AS BASICFILE LOB "SYS_XDBPD$185_L"
(ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT))
VARRAY "XMLDATA"."INFO"."SYS_XDBPD$"
STORE AS BASICFILE LOB "SYS_XDBPD$186_L"
(ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT))
1 row selected.
The OSDM DDL output after reverse engineering:
CREATE TABLE TEST.ROOT184_TAB
OF SYS.XMLTYPE
SUBSTITUTABLE AT ALL LEVELS
OBJECT IDENTIFIER IS SYSTEM GENERATED
PCTFREE 10
PCTUSED 40
MAXTRANS 255
TABLESPACE USERS
LOGGING
STORAGE (
INITIAL 65536
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
MONITORING
;
The full OSDM table and type listing was
CREATE OR REPLACE TYPE TEST.INFO181_T
;
/
CREATE OR REPLACE TYPE TEST.ROOT183_T
;
/
CREATE OR REPLACE TYPE XDB.XDB$RAW_LIST_T
IS VARRAY ( 2147483647 ) OF RAW (2000)
;
CREATE OR REPLACE TYPE TEST.INFO181_T
AS OBJECT
(
SYS_XDBPD$ XDB$RAW_LIST_T ,
INFO_ID NUMBER (38) ,
INFO_CONTENT VARCHAR2 (4000)
) FINAL
;
/
CREATE OR REPLACE TYPE TEST.ROOT183_T
AS OBJECT
(
SYS_XDBPD$ XDB$RAW_LIST_T ,
ID NUMBER (38) ,
INFO INFO181_T
) FINAL
;
/
CREATE TABLE TEST.INFO182_TAB
OF SYS.XMLTYPE
SUBSTITUTABLE AT ALL LEVELS
OBJECT IDENTIFIER IS SYSTEM GENERATED
PCTFREE 10
PCTUSED 40
MAXTRANS 255
TABLESPACE USERS
LOGGING
STORAGE (
INITIAL 65536
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
MONITORING ;
-- Error - Unique Constraint INFO182_TAB.SYS_C006570 doesn't have columns
CREATE TABLE TEST.ROOT184_TAB
OF SYS.XMLTYPE
SUBSTITUTABLE AT ALL LEVELS
OBJECT IDENTIFIER IS SYSTEM GENERATED
PCTFREE 10
PCTUSED 40
MAXTRANS 255
TABLESPACE USERS
LOGGING
STORAGE (
INITIAL 65536
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
MONITORING ;
-- Error - Unique Constraint ROOT184_TAB.SYS_C006569 doesn't have columns
.
Alternative ways
An alternative way to see what is created is setting a debugging event, this will create a trace file in the udump directory.
SQL> ALTER session SET events = '31098 trace name context forever';
Session altered.
--
-- The content of the trace file gives the following output
--
------------ QMTS Executing SQL ------------
CREATE OR REPLACE TYPE "TEST"."INFO192_T" AS OBJECT
("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T",
"INFO_ID" NUMBER(38),
"INFO_CONTENT" VARCHAR2(4000 CHAR))
FINAL INSTANTIABLE
/
--------------------------------------------
select * from user_errors where name = 'INFO192_T';
------------ QMTS Executing SQL ------------
CREATE OR REPLACE TYPE "TEST"."ROOT194_T" AS OBJECT
("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T",
"ID" NUMBER(38),
"INFO" "INFO192_T")
FINAL INSTANTIABLE
/
--------------------------------------------
select * from user_errors where name = 'ROOT194_T';
------------ QMTS Executing SQL ------------
GRANT EXECUTE ON "TEST"."INFO192_T" TO PUBLIC WITH GRANT OPTION
/
--------------------------------------------
------------ QMTS Executing SQL ------------
GRANT EXECUTE ON "TEST"."ROOT194_T" TO PUBLIC WITH GRANT OPTION
/
--------------------------------------------
------------ QMTS Executing SQL ------------
CREATE TABLE "TEST"."ROOT195_TAB" OF SYS.XMLTYPE
XMLSCHEMA "http://localhost/public/myschema.xsd"
ID 'E6F21F2A25794FEC9ED1B08EE1DD272E'
ELEMENT "ROOT" ID 3474
TYPE "TEST"."ROOT194_T"
VARRAY XMLDATA."SYS_XDBPD$" STORE AS LOB SYS_XDBPD$196_L
VARRAY XMLDATA."INFO"."SYS_XDBPD$" STORE AS LOB SYS_XDBPD$197_L
VARRAY XMLEXTRA.EXTRADATA STORE AS LOB EXTRADATA198_L
VARRAY XMLEXTRA.NAMESPACES STORE AS LOB NAMESPACES199_L
/
--------------------------------------------
*** 2008-10-18 21:51:20.453
------------ QMTS Executing SQL ------------
begin
xdb.dbms_xdbz.enable_hierarchy('"TEST"','"ROOT195_TAB"',DBMS_XDBZ.ENABLE_CONTENTS,TRUE); end;
/
--------------------------------------------
------------ QMTS Executing SQL ------------
CREATE TABLE "TEST"."INFO193_TAB" OF SYS.XMLTYPE
XMLSCHEMA "http://localhost/public/myschema.xsd" ID 'E6F21F2A25794FEC9ED1B08EE1DD272E'
ELEMENT "INFO" ID 3477
TYPE "TEST"."INFO192_T"
VARRAY XMLDATA."SYS_XDBPD$" STORE AS LOB SYS_XDBPD$200_L
VARRAY XMLEXTRA.EXTRADATA STORE AS LOB EXTRADATA201_L
VARRAY XMLEXTRA.NAMESPACES STORE AS LOB NAMESPACES202_L
/
--------------------------------------------
------------ QMTS Executing SQL ------------
begin
xdb.dbms_xdbz.enable_hierarchy('"TEST"','"INFO193_TAB"',DBMS_XDBZ.ENABLE_CONTENTS,TRUE); end;
/
--------------------------------------------
This is what actually is happening under the cover, to create the object relational XMLType table.
Although OSDM shouldn’t understand stuff like “BASICFILE” keywords, it should have noticed the VARRAY’s involved. It got the create types correct in the overall DDL scripting it generates. In Oracle 11g syntax has progressed, but not that much.
I will double check against an 10gR2 database next week, but I guess it still doesn’t give me the correct DDL syntax.
Give me a lever long enough, and I shall move the world.