Get Metadata of an Object in Oracle

There are different methods to get the metadata of an object in Oracle, below are the few which i use.

1)Using exp and imp utilities
.Export the table/schema with rows=n (This will not export the data but only the table structure)
.Import it with indexfile=table_metadata.log (indexfile will make imp to write the contens of .dmp file to the mentioned log file. Nothing will be done inside the schema)

To get the metdata of the table emp in schema john:

 $ exp tables=john.emp file=john_emp.dmp rows=n
 $ imp full=y file=john_emp.dmp indexfile=john_emp_metadata.log
 $ cat john_emp_metadata.log
REM  CREATE TABLE "JOHN"."EMP" ("EMPNO" NUMBER, "ENAME" VARCHAR2(20),
REM  "SAL" NUMBER, "DEPTNO" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1
REM  MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1
REM  BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS ;

2)Using DBMS_METADATA.GET_DDL()

To get the metadata of individual objects:

select dbms_metadata.get_ddl(‘<OBJ_TYPE>’,'<OBJ_NAME>’,'<SCHEMA>’) from dual;

SQL> SET PAGES 1000
SQL> SET LONG 1000
SQL> select dbms_metadata.get_ddl('TABLE','EMP','JOHN') from dual;
DBMS_METADATA.GET_DDL('TABLE',
--------------------------------------------------------------------------------
 CREATE TABLE "JOHN"."EMP"
 ("EMPNO" NUMBER,
 "ENAME" VARCHAR2(20),
 "SAL" NUMBER,
 "DEPTNO" NUMBER )
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

To get the metadata of the tables or indexes or materialized views with a ” ; ” at the end of each script use

SET LINE 240
SET ECHO OFF
SET PAGES 0
SET LONG 90000
SET HEADING OFF
SET TRIMSPOOL OFF
SET FEEDBACK OFF
SET LONGCHUNKSIZE 600
SPOOL USER_METADATA.LOG
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);  
SELECT DBMS_METADATA.GET_DDL('TABLE',UT.TABLE_NAME) FROM USER_TABLES UT;
SELECT DBMS_METADATA.GET_DDL('INDEX',IDX.INDEX_NAME) FROM USER_INDEXES IDX;
SELECT DBMS_METADATA.GET_DDL(REPLACE(OBJECT_TYPE,' ','_'),OBJECT_NAME)
FROM USER_OBJECTS
WHERE OBJECT_TYPE IN ('MATERIALIZED VIEW');

SPOOL OFF