Re: GLOBAL TEMPORARY TABLE in Oracle 9.2.0.4

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 29 Apr 2004 18:51:13 +0100
Message-ID: <007201c42e12$9219cd30$7102a8c0@Primary>

There are a couple of bugs where Oracle fails to drop global temporary tables (Possibly because it gets their names wrong on the drop). Another example where SYS will accumulate GTTs is you drop and recreate bitmap join indexes.

I think these GTTs come from the option to generate histograms - Oracle extracts a data sample, and then generates histograms on the sample if the cost of doing so seems sensible. (But that's working from memory, it might be stats on FBIs).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland http://www.index.is/oracleday.php June 2004 UK - Optimising Oracle Seminar July 2004 USA West Coast, Optimising Oracle Seminar August 2004 Charlotte NC, Optimising Oracle Seminar September 2004 USA East Coast, Optimising Oracle Seminar September2004 UK - Optimising Oracle Seminar

Original Message ----- From: "Arnon, Yuval" <Yuval.Arnon_at_webloyalty.com> To: <oracle-l_at_freelists.org> Sent: Thursday, April 29, 2004 4:37 PM Subject: GLOBAL TEMPORARY TABLE in Oracle 9.2.0.4

Hi,

I have been seeing a new GLOBAL TEMPORARY TABLE in our production database every day for the last few weeks. This table is owned by SYS and has the name as ORA_TEMP_1_DS_xxxxx where xxxxx is a number. The table structure is as follows
CREATE GLOBAL TEMPORARY TABLE ora_temp_1_ds_18765

    (full_db_name                   VARCHAR2(30),
    stats_created_dt               DATE,
    owner                          VARCHAR2(30) NOT NULL,
    table_name                     VARCHAR2(30) NOT NULL)

ON COMMIT PRESERVE ROWS
PARALLEL (DEGREE 4)
CACHE
/

So I know it has something to do with stats. What I do not understand is why those tables are not removed at the end of the process. All the tables have not data.

Thanks

Yuval.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Thu Apr 29 2004 - 12:51:02 CDT