set feedback off
set verify off
set echo off
prompt This script is to get description of all tables excluding some schemas.
set termout off
set pages 500
set heading off
set linesize 150
spool table_definition.sql
select 'spool table_def_output.log;' from dual;
select 'DESC ' || A.OWNER ||'.'||A.TABLE_NAME DESC_SCRIPT from dba_tables a where
OWNER NOT IN ('SYS','SYSTEM','SYSMAN','MGMT_VIEW','TSMSYS','WMSYS','EXP_DBA','OUTLN','ORACLE_OCM','DBSNMP', 'MDSYS','EXFSYS', 'CTXSYS', 'OLAPSYS');
select 'exit;' from dual;
set termout on
prompt Running Script now to get description
set termout off
@table_definition.sql;
exit
Save it in a file and run that file.
Following is the sample output of above script in my test database.
C:\Documents and Settings\User>sqlplus arju/a
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jun 3 21:53:55 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> set feedback off
SQL> set verify off
SQL> set echo off
SQL> prompt This script is to get description of all tables excluding some schemas.
This script is to get description of all tables excluding some schemas.
SQL> set termout off
SQL> set pages 500
SQL> set heading off
SQL> set linesize 150
SQL> spool table_definition.sql
SQL> select 'spool table_def_output.log;' from dual;
spool table_def_output.log;
SQL> select 'DESC ' || A.OWNER ||'.'||A.TABLE_NAME DESC_SCRIPT from dba_tables a where
2 OWNER NOT IN ('SYS','SYSTEM','SYSMAN','MGMT_VIEW','TSMSYS','WMSYS','EXP_DBA','OUTLN','ORACLE_OCM','DBSNMP', 'MD
DESC DMSYS.DM$P_MODEL
DESC DMSYS.DM$P_MODEL_TABLES
DESC XDB.XDB$ROOT_INFO
DESC XDB.XDB$H_INDEX
DESC XDB.XDB$COLUMN_INFO
DESC XDB.XDB$PATH_INDEX_PARAMS
DESC XDB.XDB$NMSPC_ID
DESC XDB.XDB$QNAME_ID
DESC XDB.XDB$PATH_ID
DESC XDB.XDB$CHECKOUTS
DESC XDB.XDB$DXPTAB
DESC XDB.XDB$DXPATH
DESC XDB.MIGR9202STATUS
DESC ORDSYS.SI_IMAGE_FORMATS_TAB
DESC ORDSYS.SI_FEATURES_TAB
DESC ORDSYS.SI_VALUES_TAB
DESC ORDSYS.ORD_CARTRIDGE_COMPONENTS
DESC JULIA.BONUS
DESC JULIA.SALGRADE
DESC JULIA.DUMMY
DESC JULIA.INSTRUCTOR
DESC JULIA.GRADE
DESC JULIA.GRADE_TYPE
DESC JULIA.GRADE_CONVERSION
DESC JULIA.GRADE_TYPE_WEIGHT
DESC JULIA.SECTION
DESC JULIA.COURSE
DESC JULIA.ENROLLMENT
DESC JULIA.STUDENT
DESC JULIA.ZIPCODE
DESC ARJU.T
DESC ARJU.PRODUCT
DESC ARJU.COMPOSER_DETAILS
DESC ARJU.MUSIC
DESC A.MM_MOVIE_TYPE
DESC A.MM_PAY_TYPE
DESC A.MM_MEMBER
DESC A.MM_MOVIE
DESC A.MM_RENTAL
DESC ARJU.MM_MOVIE_TYPE
DESC ARJU.MM_PAY_TYPE
DESC ARJU.MM_MEMBER
DESC ARJU.MM_MOVIE
DESC ARJU.MM_RENTAL
DESC SCOTT.DEPT
DESC SCOTT.EMP
DESC SCOTT.BONUS
DESC SCOTT.SALGRADE
DESC ARJU.REL_MODULE
DESC ARJU.REL_COMPLEXASSEMBLY
DESC ARJU.REL_BASEASSEMBLY
DESC ARJU.REL_COMPOSITEPART
DESC HR.EMPLOYEES
DESC HR.JOB_HISTORY
DESC OE.CUSTOMERS
DESC OE.INVENTORIES
DESC OE.PRODUCT_INFORMATION
DESC OE.PRODUCT_DESCRIPTIONS
DESC ARJU.SOLOIST_DETAILS
DESC OE.SYS_IOT_OVER_52539
DESC OE.SYS_IOT_OVER_52544
DESC OE.PRODUCT_REF_LIST_NESTEDTAB
DESC OE.SUBCATEGORY_REF_LIST_NESTEDTAB
DESC OO7SUBDB.REL_COMPLEXASSEMBLY
DESC B.REL_MODULE
DESC B.REL_MODULE_COMPOSITEPARTS
DESC OO7SUBDB.REL_MODULE
DESC ARJU.PAYMENT_INFO
DESC OO7SUBDB.REL_BASEASSEMBLY
DESC OO7SUBDB.REL_COMPOSITEPART
DESC OO7SUBDB.REL_COMPONENTSSHAR
DESC B.REL_COMPLEXASSEMBLY
DESC B.REL_COMPLEXASSEMBLY_CPARTS
DESC B.REL_BASEASSEMBLY
DESC B.REL_BASEASSEMBLY_CPARTS
DESC JULIA.EMP
DESC JULIA.DEPT
DESC JULIA.TEST1
DESC JULIA.TEST2
DESC AR.STUDENT
DESC TE.STUDENT
DESC A.EMPLOYEE
DESC A.SALE
DESC JULIA.EMPLOYEE
DESC JULIA.TEST
DESC JULIA.TESTZIP
DESC ARJU.S_DEPT
DESC ARJU.S_EMP
DESC ARJU.S_ITEM
DESC ARJU.PRECIOUS_METAL_PRICE
DESC ARJU.METALS
DESC MUDDY.MV_DBA_CLIENTS
DESC MUDDY.T
DESC MUDDY.DBA_CLIENTS
DESC E.MM_MOVIE_TYPE
DESC E.MM_PAY_TYPE
DESC E.MM_MEMBER
DESC E.MM_MOVIE
DESC E.MM_RENTAL
DESC ARJU.PAYMENT_INFORMATION
DESC B.STAR
DESC B.STAR_MOVIE
DESC C.REL_MODULE
DESC C.REL_MODULE_COMPOSITEPARTS
DESC C.REL_COMPLEXASSEMBLY
DESC C.REL_COMPLEXASSEMBLY_CPARTS
DESC C.REL_BASEASSEMBLY
DESC C.REL_BASEASSEMBLY_CPARTS
DESC C.REL_COMPOSITEPART
DESC C.REL_COMPOSITEPARTS
DESC ARJU.DAS_EXCEPTIONS
DESC ARJU.DAS_LOGON_TRAIL
DESC MUDDY.CLIENT
DESC MUDDY.COURSE
DESC MUDDY.COURSE_ACTIVITY
DESC MUDDY.CORP_EXTRACT1
DESC MUDDY.CORP_EXTRACT2
DESC ARJU.CLIENT
DESC ARJU.CONTRACT_MANAGER
DESC ARJU.STAFF_MEMBER
DESC JULIA.LOB_TAB
DESC ARJU.CUSTORDTOT_TEMP
DESC ARJU.CUSTORD_TEMP
DESC ARJU.G_DEPT
DESC SH.SALES_TRANSACTIONS_EXT
DESC SH.DR$SUP_TEXT_IDX$N
DESC SH.DR$SUP_TEXT_IDX$K
DESC SH.COSTS
DESC SH.SALES
DESC IX.AQ$_STREAMS_QUEUE_TABLE_C
DESC IX.AQ$_STREAMS_QUEUE_TABLE_I
DESC IX.AQ$_STREAMS_QUEUE_TABLE_G
DESC IX.AQ$_STREAMS_QUEUE_TABLE_H
DESC IX.AQ$_STREAMS_QUEUE_TABLE_T
DESC IX.AQ$_ORDERS_QUEUETABLE_I
DESC IX.AQ$_ORDERS_QUEUETABLE_G
DESC IX.AQ$_ORDERS_QUEUETABLE_H
DESC IX.AQ$_ORDERS_QUEUETABLE_T
DESC HR.COUNTRIES
DESC SH.MVIEW$_EXCEPTIONS
DESC IX.AQ$_STREAMS_QUEUE_TABLE_S
DESC PM.ONLINE_MEDIA
DESC SH.TIMES
DESC SH.PRODUCTS
DESC SH.FWEEK_PSCAT_SALES_MV
DESC SH.CHANNELS
DESC PM.PRINT_MEDIA
DESC SH.SUPPLEMENTARY_DEMOGRAPHICS
DESC IX.AQ$_ORDERS_QUEUETABLE_S
DESC SH.COUNTRIES
DESC OE.PROMOTIONS
DESC IX.ORDERS_QUEUETABLE
DESC OE.WAREHOUSES
DESC SH.DR$SUP_TEXT_IDX$R
DESC HR.LOCATIONS
DESC SH.CAL_MONTH_SALES_MV
DESC HR.REGIONS
DESC OE.ORDERS
DESC HR.JOBS
DESC IX.SYS_IOT_OVER_52134
DESC SH.CUSTOMERS
DESC IX.STREAMS_QUEUE_TABLE
DESC SH.PROMOTIONS
DESC HR.DEPARTMENTS
DESC IX.SYS_IOT_OVER_52117
DESC OE.ORDER_ITEMS
DESC SH.DR$SUP_TEXT_IDX$I
SQL> select 'exit;' from dual;
exit;
SQL> set termout on
SQL> prompt Running Script now to get description
Running Script now to get description
SQL> set termout off
SQL> @table_definition.sql;
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
