Re: recovering the create string for index or table?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 17 Jan 2000 08:41:57 -0500
Message-ID: <9v668s85s6uvgisktk72ubt1jbjgdab2c3@4ax.com>

A copy of this was sent to "ferhat haydar" <ferhat_at_sorry.com> (if that email address didn't require changing) On Mon, 17 Jan 2000 18:40:16 +1100, you wrote:

>Does anybody know what is the documented way to
>recover the original SQL statement which was used to create a table or
>index?
>
>Say I know that there is an index called DUMMY.
>How can I programatically recover the string that created it?
>
>Thanks in advance.
>
>

I use imp/exp to get that when I need to. for example:

ops$tkyte_at_8i> create table t ( x int )
  2 /
Table created.

ops$tkyte_at_8i> create index dummy on T(x)   2 /
Index created.

Now, I export the table with the index I'm interested in:

$ exp userid=/ tables=T rows=n

Export: Release 8.1.5.0.0 - Production on Mon Jan 17 08:43:52 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...

. . exporting table                              T

Export terminated successfully without warnings.

and then import will show me the create statement:

$ imp userid=/ full=y show=y

Import: Release 8.1.5.0.0 - Production on Mon Jan 17 08:44:08 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

Export file created by EXPORT:V08.01.05 via conventional path import done in US7ASCII character set and US7ASCII NCHAR character set . importing OPS$TKYTE's objects into OPS$TKYTE

 "CREATE TABLE "T" ("X" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRA"
 "NS 255 LOGGING STORAGE(INITIAL 524288) TABLESPACE "UTILS""
 "CREATE INDEX "DUMMY" ON "T" ("X" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STOR"
 "AGE(INITIAL 524288) TABLESPACE "UTILS" LOGGING"

Import terminated successfully without warnings.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jan 17 2000 - 07:41:57 CST