Is it possible to export the table structures(no data), packages,
triggers from a schema to a flat file?

Prasath wrote:
exp rows=n full=y compress=n
--
Sybrand Bakker
Senior Oracle DBA

"sybrandb" <sybrandb (AT) gmail (DOT) com> wrote

or toad

sybrandb wrote:
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.

Prasath wrote:
No version was indicated, so I'm going to assume that my solution works
in your version (Oracle 9i or 10g):

Use the DBMS_METADATA package to get this sort of information. In
SQL*Plus, do the following:

spool ddl.txt
SELECT dbms_metadata.get_ddl(object_type,object_name,user )
FROM user_objects;
spool off



HTH,
Brian


--
================================================== =================

Brian Peasland
dba (AT) nospam (DOT) peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown