Get DDL of all tables and indexes in a Schema

DBMS_METADATA package can be conveniently used to get the DDL of all tables and indexes in a schema. The sample syntax is as follows:

set pagesize 0
set long 90000
set feedback off
set echo off 
spool ddl.txt 
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name, 'MY_SCHEMA') || '/'
     FROM dba_tables u
  where owner='MY_SCHEMA';


SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name, 'MY_SCHEMA') || '/'
     FROM dba_INDEXES u
    where owner='MY_SCHEMA';
spool off;

SQL> set pagesize 200
SQL> set long 1000
SQL> select dbms_metadata.get_ddl('TABLE','EMPLOYEES') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES')
----------------------------------------------------------------------------

  CREATE TABLE "STORE"."EMPLOYEES"
   (    "EMPLOYEE_ID" NUMBER(*,0),
        "MANAGER_ID" NUMBER(*,0),
        "FIRST_NAME" VARCHAR2(10) NOT NULL ENABLE,
        "LAST_NAME" VARCHAR2(10) NOT NULL ENABLE,
        "TITLE" VARCHAR2(20),
        "SALARY" NUMBER(6,0),
         CONSTRAINT "EMPLOYEES_PK" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) 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"