It is quite difficult to get metadata out of Oracle 8i and earlier versions. The two most popular methods are:
- Querying the data dictionary. In many cases you need more than one select statement to get the required information.
- Exporting your schema with ROWS=N and then import with SHOW=Y. This creates a text file, which you then edit to get the metadata. Both methods take a considerable amount of time.
However, in Oracle 9i, a new package was introduced: DBMS_METADATA. It allows users to get metadata information about any object (tables, indexes, procedures, etc) from a data dictionary with a single command.
DBMS_METADATA provides two types of retrieval interfaces:
- For browsing: GET_XML and GET_DDL return metadata for a single object.
- For programmatic use:
OPEN, SET_FILTER, SET_COUNT, GET_QUERY, SET_PARSE_ITEM, ADD_TRANSFORM, SET_TRANSFORM_PARAM, FETCH_XXX, CLOSE
These allow for flexible selection criteria and extraction of groups of objects.
Example 1 - To get the metadata of Table EMP of SCOTT user, execute the following statement.
set pagesize 0 set long 90000 SELECT DBMS_METADATA.GET_DDL( 'TABLE','EMP','SCOTT') FROM DUAL;In this SQL statement, you can ignore the schema if you are extracting from the current schema. The output would be:
CREATE TABLE "SCOTT"."EMP" ( "COLUMN1" VARCHAR2(20), "COLUMN2" NUMBER(10,2),
"COLUMN3" DATE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA"From the above statement, we can see that it also returns storage clauses. To specify that storage clauses are not to be returned in the SQL DDL, use the following:
execute DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);To reset the session-level parameters to the default, use the following command:
execute DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');Example 2 - This example fetches the DDL for all "complete" tables in the current schema, filtering out nested tables and overflow segments.
set pagesize 0 set long 90000 execute DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_ALL_TABLES u WHERE u.nested='NO' AND (u.iot_type is null or u.iot_type='IOT'); execute DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');You can use spool to get all DDL into a file.
Example 3 - Fetch the DDL for all object grants on SCOTT.EMP:
SET PAGESIZE 0 SET LONG 90000 SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT', 'EMP', 'SCOTT') FROM DUAL;
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.
This was first published in July 2002