It is quite difficult to get metadata out of Oracle 8i and earlier versions. The two most popular methods are:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
- 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.