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
1 select dbms_metadata.get_ddl('TABLE','MY_TABLE')
2* from dual
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)
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:
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%';
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.