DBMS_METADATA is a useful package to generate the DDL of an existing user in the database, it can also be used to generate the DDL of any object and its dependencies.

Get user DDL with DBMS_METADATA.GET_DDL

set feedback off pages 0 long 90000 serveroutput on

accept USERNAME prompt "Enter username :"

--This line add a semicolon at the end of each statement
execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

-- This will generate the DDL for the user and add his objects,system and role grants
SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username='&username'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',grantee)as script from DBA_SYS_PRIVS where grantee='&username' and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',grantee)as script from DBA_ROLE_PRIVS where grantee='&username' and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',grantee)as script from DBA_TAB_PRIVS where grantee='&username' and rownum=1;

Get object DDL and dependents objects DDL

If you want to generate the DDL for an object you can use the following command :

SET LONG 2000000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') FROM DUAL;

If you want the grants for the object as well you can use the GET_DEPENDENT_DDL procedure :

SET LONG 2000000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','EMP','SCOTT') FROM DUAL;

First argument is the object_type, second one the object_name and the last one represents the schema.

Now, suppose you want to get the DDL for all tables in the schema SCOTT, you can use this code :

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('TABLE');

 -- Use filters to specify the schema.
 DBMS_METADATA.SET_FILTER(h,'SCHEMA','SCOTT');

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

--add semicolon at the end of the statement
 DBMS_METADATA.SET_TRANSFORM_PARAM(th,'SQLTERMINATOR',true);

 -- 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;
/

EXEC get_schema_md;
SELECT * FROM my_metadata;

With this method you can also generate the DDL for a whole schema, you just have to change the object_type from ‘TABLE’ to ‘SCHEMA_EXPORT’ in the OPEN procedure.

This procedure is from the oracle documentation, you can consult it at the following address: DBMS_METADATA documentation