Re: What's special about this create table statement?

From: HansF <News.Hans_at_telus.net>
Date: Thu, 05 Jan 2006 05:27:30 GMT
Message-Id: <pan.2006.01.05.05.27.29.958866@telus.net>

On Wed, 04 Jan 2006 21:08:19 -0800, dean wrote:

> I got it from the EM console. I right-clicked on the table and then on
> Show Object DDL...
>
> If this is coming up wrong, could you tell me if there is another way
> to get the create table statement? Originally this was a global temp
> table, and perhaps it still is, I am just confused why its not showing
> up this way in the ddl.
>
> Thanks for so far!
>
> Dean

So much for GUI ... never trust a GUI unless you can duplicate it via command line.

(GUI assumes
a) the developer did the right thing, and b) you *want* to work & think the same way as the developer which are both often not the case once you are moderately skilled.)

And possibly so much for the following, as I assume you have a recent version of Oracle but have not found anything to justify that assumption.

I'll give you the command line demo against 10gR2 (should be valid back to 9i), let you think your way around the rest <g>

SQL> connect hr/hr
Connected.
SQL> CREATE GLOBAL TEMPORARY TABLE lots_objects   2 ON COMMIT PRESERVE ROWS
  3 AS SELECT * FROM all_objects
  4 where object_name like 'A%';

Table created.

SQL> SET LONG 2000000
SQL> SET PAGESIZE 0
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'LOTS_OBJECTS') FROM DUAL;

  CREATE GLOBAL TEMPORARY TABLE "HR"."LOTS_OBJECTS"

   (    "OWNER" VARCHAR2(30) NOT NULL ENABLE,
        "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE NOT NULL ENABLE,
        "LAST_DDL_TIME" DATE NOT NULL ENABLE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)

   ) ON COMMIT PRESERVE ROWS By the way - tables are generally not in the habit of transitioning between global temps, heap and iot without assistance.

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** Top posting [replies] guarantees I won't respond. ***

Received on Wed Jan 04 2006 - 23:27:30 CST