oracle objects export

FAQ

Memberlist

Usergroups

Profile

Preferences

· nixdoc.net

· man pages

· Linux HOWTOs

· FreeBSD Tips

· Forums

Forum index » Databases » Oracle

oracle objects export

Page 1 of 1 [5 Posts]

View previous topic :: View next topic

Author

Message

prasath.rao@gmail.com
*nix forums beginner

Joined: 16 Nov 2005
Posts: 19

Posted: Mon Jul 17, 2006 9:21 am Post subject: Note

oracle objects export

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

sybrandb
*nix forums beginner

Joined: 13 Jul 2006
Posts: 22

Posted: Mon Jul 17, 2006 10:12 am Post subject: Note

Re: oracle objects export

Prasath wrote:

Quote:

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

<g>@nirgendwo
*nix forums beginner

Joined: 05 Jul 2006
Posts: 7

Posted: Mon Jul 17, 2006 11:20 am Post subject: Note

Re: oracle objects export

"sybrandb" <sybrandb@gmail.com> wrote in message
news:1153131132.179793.294890@35g2000cwc.googlegroups.com...

Quote:

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

or toad

Charles Hooper
*nix forums addict

Joined: 09 Jul 2006
Posts: 51

Posted: Mon Jul 17, 2006 12:44 pm Post subject: Note

Re: oracle objects export

sybrandb wrote:

Quote:

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.

Brian Peasland
*nix forums Guru

Joined: 04 Apr 2006
Posts: 301

Posted: Mon Jul 17, 2006 12:54 pm Post subject: Note

Re: oracle objects export

Prasath wrote:

Quote:

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

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@nospam.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

Google

Display posts from previous:

Page 1 of 1 [5 Posts]

View previous topic :: View next topic

The time now is Mon Dec 01, 2008 11:05 pm | All times are GMT

Forum index » Databases » Oracle

Jump to:

Topic		Author	Forum	Replies	Last Post
Similar Topics
	User Environment - export PATH variable	paalepu	AIX	0	Tue Sep 12, 2006 8:12 pm
	Move Oracle 10g database to another location	Selt	Server	0	Fri Jul 21, 2006 2:14 pm
	Oracle runtime	Spitfire	Server	0	Fri Jul 21, 2006 1:18 pm
	Oracle Text Score Computation	jatinder.1975@gmail.com	Server	0	Fri Jul 21, 2006 1:00 pm
	Can a trigger insert records into another oracle server	Tauqir	Server	2	Fri Jul 21, 2006 12:27 am

Car Loans | Mortgage Calculator | Personal Loans | Property in Spain | McDonalds

Other DeniX Solutions sites: Unix/Linux blog | electronics forum | medicine forum | science forum |

[ Time: 0.3037s ][ Queries: 16 (0.1666s) ][ GZIP on - Debug on ]