From oracle-l-bounce@freelists.org Wed May 5 16:16:58 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i45LGhC12270 for ; Wed, 5 May 2004 16:16:53 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i45LGX612230 for ; Wed, 5 May 2004 16:16:43 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B4C5D72E00A; Wed, 5 May 2004 16:06:36 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 14051-93; Wed, 5 May 2004 16:06:36 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 56A6172DFD6; Wed, 5 May 2004 16:06:35 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 05 May 2004 16:05:13 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F404872D85F for ; Wed, 5 May 2004 16:04:35 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13053-96 for ; Wed, 5 May 2004 16:04:35 -0500 (EST) Received: from agminet04.oracle.com (agminet04.oracle.com [141.146.126.231]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 45B2672DC7F for ; Wed, 5 May 2004 16:04:33 -0500 (EST) Received: from agminet04.oracle.com (localhost [127.0.0.1]) by agminet04.oracle.com (Switch-3.1.4/Switch-3.1.0) with ESMTP id i45LIMFm002241 for ; Wed, 5 May 2004 14:18:23 -0700 Received: from rgmgw3.us.oracle.com (rgmgw3.us.oracle.com [138.1.191.12]) by agminet04.oracle.com (Switch-3.1.4/Switch-3.1.0) with ESMTP id i45LGXPI032490 for ; Wed, 5 May 2004 14:17:02 -0700 Received: from localhost (localhost [127.0.0.1]) by rgmgw3.us.oracle.com (Switch-3.1.4/Switch-3.1.0) with SMTP id i45LGWFr012158 for ; Wed, 5 May 2004 15:16:33 -0600 Received: from au.oracle.com (dhcp-amer-vpn-gw2-east-141-144-66-164.vpn.oracle.com [141.144.66.164]) by rgmgw3.us.oracle.com (Switch-3.1.4/Switch-3.1.0) with ESMTP id i45LGMgH011710; Wed, 5 May 2004 15:16:25 -0600 Message-Id: <200405052116.i45LGMgH011710@rgmgw3.us.oracle.com> From: Pete Sharman To: oracle-l@freelists.org Cc: Peter Ross Sharman Subject: RE: PLAN_TABLE gotcha in 10g Date: Thu, 6 May 2004 07:16:22 +1000 X-Sent-Folder-Path: Sent Items X-Mailer: Oracle Connector for Outlook 9.0.4 60130 (10.0.4712) MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 4520 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: peter.sharman@oracle.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Um, in the catplan.sql script? :) = Pete = "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook = "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] = On Behalf Of Daniel Fink Sent: Thursday, 6 May 2004 7:08 AM To: oracle-l@freelists.org Subject: PLAN_TABLE gotcha in 10g I've been working with some sql in a new 10g db doing the typical 'explain plan for ' syntax. Today, I decided to preserve some of the plans, so I set statement_ids and commited them. Lo and behold, they were not there when I went back later in the day. In 10g, a PLAN_TABLE$ table is created and given the PUBLIC synonym PLAN_TABLE. Convenient, now I don't have to run utlxplan for every user. Just be disciplined and use distinct statement_ids. Unfortunately, the PLAN_TABLE$ is a global temporary table, so my records are deleted when the session ends! ARGH! FYI, this table is created with the catplan.sql script that is called by catalog.sql. = Okay, Pete. Where's the doc on this one? ;) Daniel ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 -----------------------------------------------------------------