DBMS_Metadata – Schema extractions part II

Previously, I blogged about how DBMS_METADATA, why no doubt powerful, seemed a little less than user-friendly in its implementation and documentation.

I also believed that I would get to the bottom of how to reverse engineer some database creation scripts using very few calls to DBMS_METADATA without doing any looping around data dictionary tables.

Unfortunately, no good progress to report so far.

I was even hoping to cheat by tracing a EXPDP DATAPUMP export and seeing how that called DBMS_METADATA but it didn’t produce the goods. Yep, not surprised – thought it sounded too simple. So for now this is just adrift sinking further down my to-do list.

DBMS_METADATA – Schema extractions – Unintuitive

Today, I’d like to reverse engineer some vanilla schema and object creation scripts for the database that I’m working on so that I can whack them in source control.

It’s always a nice to have – although over time you often get to a position where you don’t need them / would never use them – but best practice and all that….

First up, I know that I could the imp/exp utility to extract an indexfile and then I could edit that, take out the comments, reformat it and separate it out into relevant scripts.

I could also use the more recent souped up imp/exp – DATAPUMP using impdp/expdp. This would be preferable to imp/exp because it’s better, faster and because notices regarding the de-support of imp/exp have been issued. You could do that like this:


expdp directory=exp_dir content=metadata_only full=y dumpfile=expdat.dmp logfile=export.log

But, I want readable, editable scripts. So, I could then use impdp to create the file using the sqlfile argument (seems a little weird that impdp creates the indexfile equivalent not expdp):


impdp directory=exp_dir dumpfile=expdat.dmp logfile=import.log sqlfile=import.sql

But I would like to use the DBMS_METADATA package because it seems made for the job.

I’ve read and re-read the Oracle documentation and is it just me or is it getting less and less clear?

The simplest use of the DBMS_METADATA package to extract the DDL for a single named object is pretty straightforward. For example:


SQL>set lines 120
SQL>set pages 9999
SQL>set long 10000
SQL>l
1 select dbms_metadata.get_ddl('TABLE','MY_TABLE')
2* from dual
SQL>/


DBMS_METADATA.GET_DDL('TABLE','MY_TABLE')
--------------------------------------------------------------------------------
CREATE TABLE "NOL_DBA"."MY_TABLE"
( "COL1" NUMBER
) 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 "NOL_DBA_TS"

However, stray from that usage and you’re in a world of poorly documented pain.

It’s interesting how DBMS_METADATA has been implemented.
It returns XML and to get the DDL format it applies XSLT.

You can see some information about this here:


SELECT *
FROM sys.metaxsl$

Actually, I should take a step back.

As mentioned above, the reason why I’m looking at these things is that I want to figure out how to extract all the necessary DDL to recreate all the bespoke schemas in my database.

I tried using the examples in the in the documentation but they’re not doing exactly what I want, which is fair enough.
But using these as a starting point, I’m finding it tough going.
My current implementation is extracting everything including far too many SYS objects, etc. And as a result, it’s taking forever. I don’t know why it’s doing this but that’s what I’m trying to figure out.

(And I will post again later when/if that’s done).

It’s not working, but I’m tracing the session using event 10046 to find some details about what Oracle is doing behind the scenes of the DBMS_METADATA package.

And that’s where I got to SYS.METAXSL$ , although I already knew from the documentation that it was capable of returning XML and doing transformations, etc.

The other useful sys object that I’m going to be looking at is SYS.METANAMETRANS$
Although I’m sure I will be having a look at the rest of them:


select * from dba_tables
where owner = 'SYS'
and table_name like 'META%';


TABLE_NAME
------------------------------
METAXSLPARAM$
METAXSL$
METAVIEW$
METASTYLESHEET
METASCRIPTFILTER$
METASCRIPT$
METAPATHMAP$
METANAMETRANS$
METAFILTER$

What’s interesting about SYS.METANAMETRANS$ , is that it is tying in nicely with the log that I was looking at from EXPDP. EXPDP makes use of DBMS_METADATA to do it’s business. The log of the above EXPDP command reveals entries like this:


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_FULL_02": /******** AS SYSDBA full=y content=metadata_only directory=exp_dir
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
......

So that was by starting point with DBMS_METADATA, fiddling with DBMS_METADATA.OPEN, DBMS_METADATA.SET_FILTER, DBMS_METADATA.ADD_TRANSFORM, DBMS_METADATA.FETCH_CLOB and DBMS_METADATA.CLOSE.

And the interesting thing is that these log entries above, tally with the entries in SYS.METANAMETRANS$.

So, I think I’m making progress, albeit slow (almost as slow as DBMS_METADATA itself – how come DATAPUMP gets it to move so quickly? Is it parallelism and/or queues?)

When (not if, please don’t let it be if) I’ve figured this out, I’ll share it. I have looked hard at the documentation and done numerous Google searches but to no obvious avail.

It looks like something that has been over-engineered whilst losing usability due to ease of use or lack thereof.

Follow

Get every new post delivered to your Inbox.

Join 62 other followers