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.

About these ads

6 Responses to DBMS_METADATA – Schema extractions – Unintuitive

  1. Jeff Hunter says:

    Well, dbms_metadata has it’s warts, that’s for sure. There’s a bug in 9.2.0.5 that makes it excruciatingly slow, but on 9.2.0.8 it’s OK. And returning a LONG isn’t the easiest thing to work with either.

    Yet, dbms_metadata is a nice tool. It is very flexible and you can basically make it do anything you want by passing it the right arguments. Definitely something nice to have in your toolbox if you’re into automation.

  2. dombrooks says:

    Cheers Jeff.

    Earlier in the week, I noted the frustrations that you had come across, presumably the ones you mention in 9.2.0.5

    http://marist89.blogspot.com/2005/10/giving-up-on-dbmsmetadata.html

    It does seem like a flexible, powerful tool but I do think the documentation is pants and figuring out all the arguments that you could pass in is the issue. And it does have a whiff of overengineering about it.

  3. Pingback: DBMS_Metadata - Schema extractions part II « OraStory

  4. Pingback: Change Management : Oracle Musings

  5. Pingback: Three months since my last confession… « OraStory

  6. transform=segment_attributes:[N/Y]: says:

    This is the answer

    http://www.rampant-books.com/art_nanda_datapump.htm

    Segment Transforms
    This is also a common problem in creating a test database from production. The test database may be very small compared to the production volume. Therefore, when you export and import the tables, they may fail if the initial extents are defined too large to fit in the test database. Instead of creating the table manually prior to importing, you will find Data Pump doing that work for you. You can remove the storage clauses of the tables, MVs etc. using a simple parameter TRANSFORM to impdp. The parameter has the following format:

    transform=segment_attributes:[N/Y]:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 70 other followers

%d bloggers like this: