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 :
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
|
1 2 |
. CREATE TABLE my_metadata (md CLOB); |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
-- 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
|
1 2 |
. exec get_schema_md; |
now inside your table “my_metadata” there is all your db schema.
rgds
i
