Metadata of Table/Index

1. Using the import/export is one method of getting the metadata where while exporting row=n will be defined and while importing show=y should be provide but we need to lot of editing to get formated metadata but most easiest and quick one is using DMBS_METADATA

Export/Import
c:\>exp file=motoexp.dmp log=motoexp.log rows=n buffer=1000000
c:\>imp file=motoexp.dmp log=motoim.log show=y rows=n indexfile=moto.sql buffer=1000000

DBMS_METADATA
SELECT DBMS_METADATA.get_ddl('object_type','object_name','owner') from dual;

SQL> set serveroutput on
SQL> set long 10000
SQL> set pages 0
SQL> select DBMS_METADATA.get_ddl ('TABLE','JOBS','HR') from dual;

CREATE TABLE "HR"."JOBS" ( "JOB_ID" VARCHAR2(10),
"JOB_TITLE" VARCHAR2(35) CONSTRAINT "JOB_TITLE_NN" NOT NULL ENABLE, "MIN_SALARY" NUMBER(6,0),
"MAX_SALARY" NUMBER(6,0),
CONSTRAINT "JOB_ID_PK" PRIMARY KEY ("JOB_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE" 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 "EXAMPLE"

Find all indexes on a particular table using DBMS_METADATA

SQL> select DBMS_METADATA.get_dependent_ddl('INDEX','JOBS','HR') from dual;

CREATE UNIQUE INDEX "HR"."JOB_ID_PK" ON "HR"."JOBS" ("JOB_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE"

Find all referencial constraint on a table

SQL> select DBMS_METADATA.get_dependent_dd ('REF_CONSTRAINT','EMPLOYEES','HR') from dual;

ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE

Find granted privileges for a particual schema using DBMS_METADATA

SQL> select DBMS_METADATA.get_granted_ddl('SYSTEM_GRANT','HR') from dual;
GRANT UNLIMITED TABLESPACE TO "HR"

SQL> select DBMS_METADATA.get_granted_ddl('ROLE_GRANT','HR') from dual;
GRANT "CONNECT" TO "HR"
GRANT "RESOURCE" TO "HR"

SQL> select DBMS_METADATA.get_granted_ddl('OBJECT_GRANT','HR') from dual;
GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "HR"