Toad World® Forums

nav 6.7 incorrectly reverses global temp table

grumble I just found out you can’t paste from nav into this crazy window on the new site. (how stupid!)

[:@]

I reversed a global temporary table in nav 6.7.

CREATE GLOBAL TEMPORARY TABLE myschema.temp_alert

(alert_id NUMBER(12,0),

context_id NUMBER(12,0))

ON COMMIT PRESERVE ROWS

–SEGMENT CREATION IMMEDIATE

NOPARALLEL

/

The problem is that nav puts the segment creation immediate at the end of the table - but this is a temporary table - so there should not be any thing created.

please correct the syntax when reversing a global temporary table.

Hi Henry,

Thank you for reporting this issue. I have raised PT#53052251 for it, once we fix will let you know.

You said you can’t paste from Nav to this site, sounds awful. However it works well for me. Did you use normal simple reply input or did you use rich formatting? And do you think it’s related to the explorer too?

Thanks,

Shirly

Hi Henry,

Thanks for your feedback.

According to Oracle 11.2 docs, global temporary tables cannot use segment creation deferred option. (docs.oracle.com/…/statements_7002.htm You cannot defer segment creation for the following types of tables: index-organized tables, clustered tables, global temporary tables, session-specific temporary tables, internal tables, typed tables, AQ tables, external tables, and tables owned by SYS, SYSTEM, PUBLIC, OUTLN, or XDB.)

We add SEGMENT CREATION IMMEDIATE to the DDL when the result of the query below return YES:

SELECT segment_created

FROM sys.user_tables

WHERE table_name LIKE :tablename

(docs.oracle.com/…/statviews_2117.htm SEGMENT_CREATED VARCHAR2(3) Indicates whether the table segment is created (YES) or not (NO) )

When user creates a global temporary table in 11gr2, no matter SEGMENT CREATION IMMEDIATE is specified or not, We always see YES in the result above. We understand there is no segment created for temporary table, but the DDL we generate for it reflects the truth of the table information Oracle stores.

If you still have any concern regarding this option, please let us know.

Thanks,

Vincent