From oracle-l-bounce@freelists.org Mon Aug 8 10:10:49 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j78FAn2Y013196 for ; Mon, 8 Aug 2005 10:10:49 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j78FAjIP013173 for ; Mon, 8 Aug 2005 10:10:45 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B4FE51DD4A7; Mon, 8 Aug 2005 10:10:42 -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 23519-10; Mon, 8 Aug 2005 10:10:42 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EDFC31DD582; Mon, 8 Aug 2005 10:10:41 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: REFERENCEing a system table Date: Mon, 8 Aug 2005 11:08:50 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: REFERENCEing a system table Thread-Index: AcWcJoLM77d61iGbSaWi7q4HMWVrlAAA94Yg From: "Mercadante, Thomas F (LABOR)" To: , X-OriginalArrivalTime: 08 Aug 2005 15:08:50.0597 (UTC) FILETIME=[155E8550:01C59C2B] X-archive-position: 23549 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Thomas.Mercadante@labor.state.ny.us Precedence: normal Reply-To: Thomas.Mercadante@labor.state.ny.us X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-3.1 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 Leandro, In my opinion, it is a very bad idea to create local objects and relate them to sys objects. A very basic rule of thumb is that Oracle can and will change anything in the SYS schema at their own pace and time. This leaves us with the opportunity to constantly update any objects that we create that may reference these SYS objects. So I would say that your idea is a bad one. And yes, Oracle does generally not put constraints on their internal tables. These things are tuned in their own way. So Oracle does not feel the need to follow the rules that they tell us to follow. FYI - the TS$ tables *does* have a unique index on the NAME column (at least in 9.2). Hope this helps. Tom -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Leandro Guimaraes Faria C. Dutra Sent: Monday, August 08, 2005 10:30 AM To: oracle-l@freelists.org Subject: REFERENCEing a system table I just mentioned it in another message, but I thought it would be nice to ask if there is a way out -- until now I've been assumed it is just an arbitrary restriction coupled to an Oracle modelling defect and have let the issue to rest. All of you are familiar with DBA_TABLESPACES, and know that it is based on SYS.TS$. I want to store historical data from DBA_TABLESPACE_USAGE_METRICS, and I would like to avoid letting garbage in it by a CONSTRAINT REFERENCES on TABLESPACE_NAME. The problem is that view constraints can't be enabled, and a CONSTRAINT REFERENCES needs an enabled constraint to be itself enabled; and on the other hand TS$ doesn't have a primary key, and I don't really want to ALTER it, it being internal. Should I just forget my CONSTRAINT REFERENCES ENABLE? It will be quite harmless to leave it DISABLEd anyway, I guess... By the way, it seems quite strange to me that Oracle itself doesn't declare PRIMARY KEYs on its internal tables. -- Leandro Guimarães Faria Corcete DUTRA Administrador de Bases de Dados +55 (11) 4390 5383 Toyota do Brasil Ltda ldutra@toyota.com.br São Bernardo do Campo, SP BRASIL This message (including any attachments) is confidential and may be privileged and intended solely for the use of the person/entity to whom it is addressed. If you have received it by mistake please notify the sender by returning via e-mail as well as delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is prohibited. Please note that e-mails are susceptible to change. TOYOTA DO BRASIL LTDA (including its group companies) shall not be liable for the improper or incomplete transmission of the information contained in this communication, neither for personal, nonbusiness related information nor opinion sent through this email or even for any delay in its receipt or damage to your system. TOYOTA DO BRASIL LTDA (or its group companies) does not guarantee that the integrity of this communication has been kept nor that this communication is free of viruses, interceptions or interference. -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l