Generate DDL for all Indexes in a Schema

set pagesize 0
set linesize 1000
set long 500000
set head off
set trimspool on
set feedback off
set echo off
set verify off

column theClob format a400

accept schemaName prompt "Enter Schema Name: "
accept outputFile prompt "Enter Output File: "

spool '&outputFile'

EXEC dbms_metadata.SET_TRANSFORM_PARAM (dbms_metadata.SESSION_TRANSFORM, 'PRETTY', true);

SELECT DBMS_METADATA.GET_DDL('INDEX', dba_indexes.index_name, '&schemaName') as theClob from dba_indexes where owner = '&schemaName';

spool off

Generate DDL for all Tables in a Schema


set pagesize 0
set linesize 1000
set long 500000
set head off
set trimspool on
set feedback off
set echo off
set verify off

column theClob format a400

accept schemaName prompt "Enter Schema Name: "
accept outputFile prompt "Enter Output File: "

spool '&outputFile'

EXEC dbms_metadata.SET_TRANSFORM_PARAM (dbms_metadata.SESSION_TRANSFORM, 'PRETTY', true);

SELECT DBMS_METADATA.GET_DDL('TABLE', dba_tables.table_name, '&schemaName') as theClob from dba_tables where owner = '&schemaName';

spool off



Show columns in all indexes given the table name

column indexName    format a30  heading 'Index Name'
column columnName   format a30  heading 'Column Name'
column indexType    format a12  heading 'Index|Type'

set linesize 80
set verify off

accept tableName prompt "Enter Table Name: "

select  DBA_IND_COLUMNS.index_name  indexName,
        DBA_IND_COLUMNS.column_name columnName,
        DBA_INDEXES.index_type      indexType
from    DBA_IND_COLUMNS,
        DBA_INDEXES
where   DBA_INDEXES.table_name = '&tableName'
  and   DBA_INDEXES.table_name = DBA_IND_COLUMNS.table_name;

Example:

                                                              Index
Index Name                     Column Name                    Type
------------------------------ ------------------------------ ------------
PRODUCTS_PROD_STATUS_BIX       PROD_STATUS                    NORMAL
PRODUCTS_PK                    PROD_ID                        NORMAL
PRODUCTS_PROD_SUBCAT_IX        PROD_SUBCATEGORY               NORMAL
PRODUCTS_PROD_CAT_IX           PROD_CATEGORY                  NORMAL
PRODUCTS_PROD_STATUS_BIX       PROD_STATUS                    NORMAL
PRODUCTS_PK                    PROD_ID                        NORMAL
PRODUCTS_PROD_SUBCAT_IX        PROD_SUBCATEGORY               NORMAL
PRODUCTS_PROD_CAT_IX           PROD_CATEGORY                  NORMAL
PRODUCTS_PROD_STATUS_BIX       PROD_STATUS                    NORMAL
PRODUCTS_PK                    PROD_ID                        NORMAL
PRODUCTS_PROD_SUBCAT_IX        PROD_SUBCATEGORY               NORMAL
PRODUCTS_PROD_CAT_IX           PROD_CATEGORY                  NORMAL
PRODUCTS_PROD_STATUS_BIX       PROD_STATUS                    BITMAP
PRODUCTS_PK                    PROD_ID                        BITMAP
PRODUCTS_PROD_SUBCAT_IX        PROD_SUBCATEGORY               BITMAP
PRODUCTS_PROD_CAT_IX           PROD_CATEGORY                  BITMAP