Re: system tablespace's extend

From: Tim Hopkins <oracle-l_at_timothyhopkins.net>
Date: Thu, 8 Dec 2005 11:45:23 -0000 (GMT)
Message-ID: <62622.192.165.213.18.1134042323.squirrel@192.165.213.18>

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_at_gmail.com] Sent: 08 December 2005 11:31
To: oracle-l_at_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

Received on Thu Dec 08 2005 - 05:45:36 CST