Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: table creation

Re: table creation

From: Ray Ontko <rayo_at_ONTKO.COM>
Date: Sun, 19 Mar 1995 15:54:04 -0500
Message-Id: <>

> I know that it's possible to find out how a view was created by looking
> at the text field of table user_views. I was wondering if there is a
> similar way to determine how tables were created, that is, is the CREATE
> TABLE command stored in any data dictionary tables? If not, how would I
> find this info?


CREATE TABLE command is not stored. You have two basic approaches to the problem: 1) use the dictionary information to piece together the command, or, 2) use EXP to export a table (without rows, say) which writes a CREATE TABLE command as part of the output file. It partly depends on what you're trying to do. If you're looking for columns and datatype (but not for clustered tables, storage clauses, constraints, default values, etc), you should be able to hack together a simple script that will do the job (with SQL*Plus even). If you want all the bells and whistles, you might ask if anyone has one out on the net. A recent conversation in this listserv suggests that the EXP approach may be the easiest.

> As long as I have your attention, is there any way to do an export on
> only a users data dictionary tables? Using exp it seems that one can
> only export specified tables OR a whole user, the latter automatically
> exporting the data dictionary tables for you. In other words, it looks
> like I have to export the entire user in order to get the user's data
> dictionary tables. Is there any way to get just the dd tables?

Sort of. A user doesn't really have any "dictionary tables." The user's dictionary (i.e., USER_TABLES, etc) is a collection of view that reference the system dictionary tables. If you're looking to get the contents of specific view (like USER_TABLES) exported, you might try CREATE MY_TABLES AS SELECT * FROM USER_TABLES, and exporting the MY_TABLES table. If you're looking for a way to save the structure without the data, EXP with ROWS=N (check the manual for syntax) will do the trick.

Best wishes,


Ray Ontko "Ask me about Database Illustrator(tm)" Ray Ontko & Co (anon ftp) Received on Sun Mar 19 1995 - 16:22:49 CST

Original text of this message