dbms_metadata.get_ddl

--GET_DDL: Return the metadata for a single object as DDL.
-- This interface is meant for casual browsing one object's ddl
-- PARAMETERS:  object_type - The type of object to be retrieved.     name - Name of the object.    schema - Schema containing the object. Defaults to  the caller's schema.   version - The version of the objects' metadata.
 model - The object model for the metadata.
transform. - XSL-T transform. to be applied.
-- RETURNS: Metadata for the object transformed to DDL as a CLOB.
FUNCTION get_ddl ( object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform. IN VARCHAR2 DEFAULT 'DDL') RETURN CLOB;
1.To get one table or index's ddl
SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','PK_DEPT','SCOTT') from dual;
2.To get all tables, indexes and procedures' ddl which are in the same schema
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');
3.To get all tablespaces' ddl
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;
4. To get all users' creating ddls
SELECT DBMS_METADATA.GET_DDL('USER',U.username)
FROM DBA_USERS U;
5. To get one view's ddl
SELECT dbms_metadata.get_ddl(object_type => 'VIEW', name => 'view_name' , schema => 'user' ) FROM dual;