Re: oracle objects export

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 17 Jul 2006 05:44:00 -0700
Message-ID: <1153140240.418059.31730@35g2000cwc.googlegroups.com>

sybrandb wrote:
> Prasath wrote:
> > Is it possible to export the table structures(no data), packages,
> > triggers from a schema to a flat file?
>
> exp rows=n full=y compress=n
> --
> Sybrand Bakker
> Senior Oracle DBA

Nice example - output can be used to examine the DDL for the objects.

If the above does not produce a flat file that meets your needs, such as the need to have the data available in a spreadsheet, or something similar, you can extract the information with various SQL statements. SQL statements that I use when I need a view of the data dictionary: Tables and their columns:
SELECT
  DT.OWNER,
  DT.TABLE_NAME,

  DTC.COLUMN_NAME,
  DTC.DATA_TYPE,
  DTC.DATA_LENGTH,
  DTC.DATA_PRECISION,
  DTC.DATA_SCALE,
  DTC.NULLABLE,
  DTC.COLUMN_ID,

  DT.TABLESPACE_NAME,
  DTCC.COMMENTS TABLE_COMMENTS,
  SUBSTR(DCC.COMMENTS,1,255) COLUMN_COMMENTS FROM

  DBA_TABLES DT,
  DBA_TAB_COLUMNS DTC,
  DBA_TAB_COMMENTS DTCC,
  DBA_COL_COMMENTS DCC

WHERE
  DT.OWNER=DTC.OWNER

  AND DT.TABLE_NAME=DTC.TABLE_NAME
  AND DT.OWNER=DTCC.OWNER(+)
  AND DT.TABLE_NAME=DTCC.TABLE_NAME(+)
  AND DTC.OWNER=DCC.OWNER(+)

  AND DTC.TABLE_NAME=DCC.TABLE_NAME(+)
  AND DTC.COLUMN_NAME=DCC.COLUMN_NAME(+) ORDER BY
  DT.OWNER,
  DT.TABLE_NAME,
  DTC.COLUMN_ID; Indexes:
SELECT

  DI.OWNER,
  DI.INDEX_NAME,
  DI.INDEX_TYPE,
  DI.TABLE_OWNER,
  DI.TABLE_NAME,
  DIC.COLUMN_NAME,

  DIC.COLUMN_POSITION,
  DIC.DESCEND,

  DI.TABLE_TYPE,
  DI.UNIQUENESS,
  DI.COMPRESSION,
  DI.PREFIX_LENGTH,
  DI.TABLESPACE_NAME

FROM
  DBA_INDEXES DI,
  DBA_IND_COLUMNS DIC
WHERE
  DI.OWNER=DIC.INDEX_OWNER
  AND DI.INDEX_NAME=DIC.INDEX_NAME
ORDER BY

  DI.OWNER,
  DI.TABLE_OWNER,
  DI.TABLE_NAME,

  DIC.TABLE_NAME,
  DIC.COLUMN_POSITION; Triggers (1):
SELECT

  DT.OWNER,
  DT.TRIGGER_NAME,
  DT.TRIGGER_TYPE,
  DT.TRIGGERING_EVENT,
  DT.TABLE_OWNER,
  DT.TABLE_NAME,
  DT.REFERENCING_NAMES,
  DT.WHEN_CLAUSE,
  DT.STATUS,
  DT.DESCRIPTION,
  DT.TRIGGER_BODY

FROM
  DBA_TRIGGERS DT
ORDER BY

  DT.OWNER,
  DT.TABLE_OWNER,
  DT.TABLE_NAME,
  DT.TRIGGER_NAME;

Triggers (2):
SELECT
  TRIGGER_OWNER,
  TRIGGER_NAME,
  TABLE_OWNER,
  TABLE_NAME,

  COLUMN_LIST,
  COLUMN_USAGE,
  COLUMN_NAME

FROM
  DBA_TRIGGER_COLS
ORDER BY
  TRIGGER_OWNER,
  TABLE_NAME,
  TRIGGER_NAME,
  COLUMN_NAME; Code:
SELECT
  TYPE,
  OWNER,
  NAME PROCEDURE_NAME,
  LINE LINE_NO,
  TEXT SOURCE_CODE
FROM
  DBA_SOURCE
WHERE
  OWNER<>'SYS'
ORDER BY
  OWNER,
  NAME,
  LINE; Constraints:
SELECT

  DC.OWNER,
  DC.CONSTRAINT_NAME,
  DC.CONSTRAINT_TYPE,
  DC.TABLE_NAME,

  DCC.COLUMN_NAME,
  DCC.POSITION,

  DC.R_OWNER,
  DC.R_CONSTRAINT_NAME,
  DC.DELETE_RULE,
  DC.STATUS,
  DC.DEFERRABLE,
  DC.DEFERRED,
  DC.VALIDATED,
  DC.LAST_CHANGE

FROM
  SYS.DBA_CONSTRAINTS DC,
  SYS.DBA_CONS_COLUMNS DCC
WHERE
  DC.OWNER=DCC.OWNER
  AND DC.TABLE_NAME=DCC.TABLE_NAME
  AND DC.CONSTRAINT_NAME=DCC.CONSTRAINT_NAME ORDER BY

  DC.OWNER,
  DC.TABLE_NAME,
  DC.CONSTRAINT_NAME,

  DCC.POSITION; Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Jul 17 2006 - 07:44:00 CDT