TO GET THE DDLS

set heading off; set echo off; Set pages 999; set long 90000;

-------TO GET THE DDLS OF A PARTICULAR TABLE OR A INDEX FROM A USER---------
SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','ORACLE9I') FROM DUAL;
CREATE TABLE "ORACLE9I"."EMP" ( "NO" NUMBER, "NAME" VARCHAR2(10) ) 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"
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;


--------------TO GET THE DDLS OF ALL THE TABLES AND INDEXS IN A USER---
select dbms_metadata.get_ddl('TABLE',u.table_name)from user_tables u;
SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME)FROM USER_INDEXES U;


----------TO GET THE DDLS OF A PARTICULAR TABLESPACE-----------------------
select dbms_metadata.get_ddl('TABLESPACE','USERS')FROM DUAL;
CREATE TABLESPACE "USERS" DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS1.DBF' SIZE 5242880 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL
-----TO GET THE DDLS OF A ALL TABLESPACES IN THE DATABASE-------------
select dbms_metadata.get_ddl('TABLESPACE',u.tablespace_name) FROM USER_tablespaces U;