Wednesday, May 21, 2008

List the contents of a schema - Oracle 9i

Just a couple of steps to list all the objects of a schema.

When you describe the all_objects view you get the following.

SQL> desc all_objects;

Name Null? Type

----------------------------------------- -------- ----------------------------

OWNER NOT NULL VARCHAR2(30)

OBJECT_NAME NOT NULL VARCHAR2(30)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NOT NULL NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(18)

CREATED NOT NULL DATE

LAST_DDL_TIME NOT NULL DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

Now to list the contents of a schema to you will need to fire a SQL statement as follows.

SQL> SELECT object_name,owner,object_type FROM ALL_OBJECTS WHERE lower(owner) LIKE 'temp1';

where,

temp1 is the schema name.


No comments: