Oracle Tips #6 [Metadata Info X]

In Oracle ,to get some ddl information and source you can call system tables like user_objects. So with a particular function called DBMS_METADATA.GET_DDL you can have the entire source of an object passing two parameter : first: the object type and the second parameter is the name of the object that you want to know the ddl definition.

I the next example i’ll get all ddl about all trigger in my schema :

   

 select
   U.OBJECT_NAME,U.STATUS ,DBMS_METADATA.GET_DDL(U.object_type,u.object_name) 
 from
   user_objects u
 where
   object_type = 'TRIGGER';
   
   
   
   
SELECT object_type, COUNT(*) FROM USER_OBJECTS
GROUP BY OBJECT_TYPE 

so you can also access to metadata api to export full schema , by creating a table and a procedure you can have full schema inside the new table. I took the example from http://www.di.unipi.it the code :

creating table:

.
  CREATE TABLE my_metadata (md CLOB);

-- Create a table to store the retrieved objects.
CONNECT scott/tiger
DROP TABLE my_metadata;
CREATE TABLE my_metadata (md CLOB);
CREATE OR REPLACE PROCEDURE get_schema_md IS

-- Define local variables.
h       NUMBER;         -- handle returned by OPEN
th      NUMBER;         -- handle returned by ADD_TRANSFORM
doc     CLOB;           -- metadata is returned in a CLOB
BEGIN

-- Specify the object type.
 h := DBMS_METADATA.OPEN('SCHEMA_EXPORT');

 -- Use filters to specify the schema.
 DBMS_METADATA.SET_FILTER(h,'SCHEMA','<### YOUR SCHEMA NAME ####>');

 -- Request that the metadata be transformed into creation DDL.
 th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

 -- Fetch the objects.
 LOOP
   doc := DBMS_METADATA.FETCH_CLOB(h);

   -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
   EXIT WHEN doc IS NULL;

   -- Store the metadata in the table.
   INSERT INTO my_metadata(md) VALUES (doc);
   COMMIT;
 END LOOP;
 
 -- Release resources.
 DBMS_METADATA.CLOSE(h);
END;
/

you only need to replace <### YOUR SCHEMA NAME ####> with your real schema name or add a parameter to your procedure.

after your can call from a sql interface like

.
  exec get_schema_md;

now inside your table “my_metadata” there is all your db schema.

rgds
i