From oracle-l-bounce@freelists.org Wed Jun 23 13:53:18 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5NIr3R18751 for ; Wed, 23 Jun 2004 13:53:13 -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 i5NIqq618719 for ; Wed, 23 Jun 2004 13:53:02 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5779372CE4F; Wed, 23 Jun 2004 13:36:22 -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 11515-33; Wed, 23 Jun 2004 13:36:22 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9ACFA72CE40; Wed, 23 Jun 2004 13:36:21 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 23 Jun 2004 13:34:54 -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 D953472CCF2 for ; Wed, 23 Jun 2004 13:34:53 -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 10465-46 for ; Wed, 23 Jun 2004 13:34:53 -0500 (EST) Received: from [12.106.87.70] (unknown [12.106.87.70]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1F63772CD14 for ; Wed, 23 Jun 2004 13:34:52 -0500 (EST) Received: from [12.106.87.70] by [12.106.87.70] via smtpd (for freelists-180.iquest.net [206.53.239.180]) with ESMTP; Wed, 23 Jun 2004 11:56:29 -0700 Received: from irvmbxw02.prod.quest.corp ([10.1.2.203]) by irvbhxw03.prod.quest.corp with Microsoft SMTPSVC(6.0.3790.0); Wed, 23 Jun 2004 11:56:28 -0700 X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain Subject: RE: what is obj$.type#=10? Date: Wed, 23 Jun 2004 11:56:28 -0700 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: what is obj$.type#=10? Thread-Index: AcRY+KPw994fciO4SiWECgnGD/llXwAVvxpgAACS2jAAAGI84A== From: "Jacques Kilchoer" To: X-OriginalArrivalTime: 23 Jun 2004 18:56:28.0610 (UTC) FILETIME=[CA66AA20:01C45953] X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 3480 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Jacques.Kilchoer@quest.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org OK, but why was the non-existent object created in the first place? Is it because when I drop a public synonym Oracle thinks that there might be dependencies on it? -----Original Message----- Bobak, Mark Because unused NON-EXISTANT objects are cleaned up by SMON every 12 hours. -----Original Message----- Jacques Kilchoer -----Original Message----- Jonathan Lewis -- I agree. Specifically, you get lots of these when -- you create a permanent object that depends on -- public synonym. At some point, you may create -- a local object that "conceals" the public synonym, -- at which point the thing that depends on the synonym -- has to become invalid and depend on the local object. -- To allow this to happen, you NEED a 'non-existent' -- object of the same name to exist, so that the permanent -- object can depend on its non-existence, and notice when -- it ceases to be non-existent. Once again I learn something from the list. (I wrote a sample script below to show what Mr. Lewis is talking about.) I have a question though. In an Oracle 9.2 database, I create public synonym X for some_table. Then I drop public synonym X. No one has ever used public synonym X, there are no dependencies on it, so why does a row remain in SYS.OBJ$ for X with type# 10? That row will remain until I restart the database. -- showing how an procedure depending on a public synonym will cause the -- the creation of a "non-existent" object with the same name as the -- public synonym, in the procedure's schema SQL> select 2 b.name as owner, a.name as object_name, a.type#, 3 decode (a.type#, 2, 'TABLE', 5, 'SYNONYM', 7, 'PROCEDURE') as object_type 4 from 5 sys.obj$ a, sys.user$ b 6 where 7 a.name in ('COUNTRY', 'COUNTRY_PROC') 8 and a.owner# = b.user# ; aucune ligne sélectionnée SQL> create table beta.country 2 (country_code varchar2 (10), country_name varchar2 (40)) ; Table créée. SQL> create public synonym country for beta.country ; Synonyme créé. SQL> grant select on beta.country to alpha ; Autorisation de privilèges (GRANT) acceptée. SQL> create procedure alpha.country_proc 2 as 3 x country.country_code%type ; 4 begin 5 null ; 6 end ; 7 / Procédure créée. SQL> select 2 b.name as owner, a.name as object_name, a.type#, 3 decode (a.type#, 2, 'TABLE', 5, 'SYNONYM', 7, 'PROCEDURE') as object_type 4 from 5 sys.obj$ a, sys.user$ b 6 where 7 a.name in ('COUNTRY', 'COUNTRY_PROC') 8 and a.owner# = b.user# ; OWNER OBJECT_NAME TYPE# OBJECT_TY ------------------------------ ------------------------------ --------- --------- PUBLIC COUNTRY 5 SYNONYM ALPHA COUNTRY 10 ALPHA COUNTRY_PROC 7 PROCEDURE BETA COUNTRY 2 TABLE SQL> create table alpha.country 2 (country_code varchar2 (15), country_name varchar2 (40)) ; Table créée. SQL> select 2 b.name as owner, a.name as object_name, a.type#, 3 decode (a.type#, 2, 'TABLE', 5, 'SYNONYM', 7, 'PROCEDURE') as object_type 4 from 5 sys.obj$ a, sys.user$ b 6 where 7 a.name in ('COUNTRY', 'COUNTRY_PROC') 8 and a.owner# = b.user# ; OWNER OBJECT_NAME TYPE# OBJECT_TY ------------------------------ ------------------------------ --------- --------- PUBLIC COUNTRY 5 SYNONYM ALPHA COUNTRY 2 TABLE ALPHA COUNTRY_PROC 7 PROCEDURE BETA COUNTRY 2 TABLE ---------------------------------------------------------------- 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 -----------------------------------------------------------------