From oracle-l-bounce@freelists.org Thu Dec 8 05:45:36 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id jB8Bja1X025358 for ; Thu, 8 Dec 2005 05:45:36 -0600 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 jB8BjaAX025351 for ; Thu, 8 Dec 2005 05:45:36 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0B97124ED72; Thu, 8 Dec 2005 06:45:29 -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 10376-04; Thu, 8 Dec 2005 06:45:28 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6EC2A24ED52; Thu, 8 Dec 2005 06:45:28 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 08 Dec 2005 06:45:28 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 15BAC24ED73 for ; Thu, 8 Dec 2005 06:45:28 -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 10366-07 for ; Thu, 8 Dec 2005 06:45:28 -0500 (EST) Received: from server102.tchmachines.com (server102.tchmachines.com [72.9.248.66]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D920624ED52 for ; Thu, 8 Dec 2005 06:45:27 -0500 (EST) Received: from server102.tchmachines.com ([72.9.248.66] helo=www.timothyhopkins.net) by server102.tchmachines.com with esmtp (Exim 4.50) id 1EkKDD-0006dW-Ox; Thu, 08 Dec 2005 06:45:23 -0500 Received: from 192.165.213.18 (SquirrelMail authenticated user oracle-l@timothyhopkins.net); by www.timothyhopkins.net with HTTP; Thu, 8 Dec 2005 11:45:23 -0000 (GMT) Message-ID: <62622.192.165.213.18.1134042323.squirrel@192.165.213.18> In-Reply-To: <45970.192.165.213.18.1134038531.squirrel@192.165.213.18> References: <18b4f18e0512080005s6438f501w@mail.gmail.com> <45970.192.165.213.18.1134038531.squirrel@192.165.213.18> Date: Thu, 8 Dec 2005 11:45:23 -0000 (GMT) Subject: Re: system tablespace's extend From: "Tim Hopkins" To: oracle-l@timothyhopkins.net Cc: mr.fishyu@gmail.com, oracle-l@freelists.org User-Agent: SquirrelMail/1.4.3a MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - server102.tchmachines.com X-AntiAbuse: Original Domain - freelists.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - timothyhopkins.net X-Source: X-Source-Args: X-Source-Dir: X-archive-position: 29026 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: oracle-l@timothyhopkins.net Precedence: normal Reply-To: oracle-l@timothyhopkins.net 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: Not scanned: please contact your Internet E-Mail Service Provider for details X-MailScanner-From: oracle-l-bounce@freelists.org Hi, The idea of the SQL was to show the most recently created objects. Basically, look at the time difference between the rows to see if objects are being created often and/or frequently. Also, if the names appear to be system or application generated (eg, contain numbers or non-alpha characters) then this would be a further clue that you have an automated process generating a lot of new code. For example, if you saw something like this: OWNER OBJECT_NAME OBJECT_ID CREATED SCOTT V99999 45645 08/12/2005 06:35:48 SCOTT V99998 42000 08/12/2005 06:34:48 SCOTT V99997 41326 08/12/2005 06:33:48 SCOTT V99996 40546 08/12/2005 06:32:48 Then you can see code is automatically creating views within the SCOTT schema every minute. If this was the case then you'd have to track down the code which is creating these objects. In this case, something like the following: select * from dba_source where upper(text) like '%CREATE%VIEW%'; might reveal the offending code if they've been nice enough to put the 'CREATE' and 'VIEW' on the same line. Given the state of your DB this query would probably take quite a while. Hope that helps. Cheers, Tim -------------------------------------------------------------------------------- From: Yu Fish [mailto:mr.fishyu@gmail.com] Sent: 08 December 2005 11:31 To: oracle-l@timothyhopkins.net Subject: Re: system tablespace's extend Hi, I hava got lots of objects via your sql.and how i can know which object is the culprit? > Hi, > > This table is used to store PL/SQL and view source code. By chance, > does your system include a process to dynamically create views or > PL/SQL objects? > > A query like the following may help track down the culprit: > > SELECT * > FROM (SELECT owner > , object_name > , object_id > , created > FROM dba_objects, (SELECT DISTINCT obj# > FROM SYS.idl_ub1$) > WHERE obj# = object_id > ORDER BY created DESC) > WHERE ROWNUM < 100; > > Cheers, > Tim > -- http://www.freelists.org/webpage/oracle-l