Re: HELP NEWBIE: listing of users objects with sql

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: Sat, 11 Apr 1998 01:46:19 GMT
Message-ID: <6gmi25$9kk@bgtnsc03.worldnet.att.net>

On Wed, 08 Apr 1998 13:15:49 -0600, dgc_at_usa.net wrote:

>Trying to do the following:
> Create a script that reports on all objects owned by a specific user.
>The username is a parameter. The report includes object_type, object_name
>, when it was created and status.
>
>I would be really thankful if someone could help me.

Look at the DBA_OBJECTS view. Grant yourself the DBA role in order to see it. Below is an example, though you would need to beautify it a bit.

SQL> describe sys.dba_objects;

 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                                    VARCHAR2(30)
 OBJECT_NAME                              VARCHAR2(128)
 SUBOBJECT_NAME                           VARCHAR2(30)
 OBJECT_ID                                NUMBER
 DATA_OBJECT_ID                           NUMBER
 OBJECT_TYPE                              VARCHAR2(15)
 CREATED                                  DATE
 LAST_DDL_TIME                            DATE
 TIMESTAMP                                VARCHAR2(19)
 STATUS                                   VARCHAR2(7)
 TEMPORARY                                VARCHAR2(1)
 GENERATED                                VARCHAR2(1)

SQL> select object_name, object_type, created, status   2 from sys.dba_objects
  3 where owner='JONATHAN';

OBJECT_NAME

---

OBJECT_TYPE CREATED STATUS
--------------- --------- -------
TEST
TABLE 10-APR-98 VALID TEST2
TABLE 10-APR-98 VALID TEST2_PK_SEQ
SEQUENCE 10-APR-98 VALID TEST2_SET_KEY
TRIGGER 10-APR-98 VALID TEST3
VIEW 10-APR-98 VALID SQL> Received on Fri Apr 10 1998 - 20:46:19 CDT