USER OBJECTS

[Upeshp]

Hi,

I have hundreads of users crated in the database, are not active (locked) and want to drop all those users. My question is,

1). What query i should write so it will get output as bleow username, status (locked) and objects, subobjects of those users.

Thanks in advance.

Upesh

[alapps]

Upeshp,

Use the view DBA_OBJECTS. It contains:

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

[stecal]

Create a spool file to capture the SQL statements created by the following SQL statement:

select 'drop user '||username||' cascade;' from dba_users
where account_status != 'OPEN';

or

select 'drop user '||username||' cascade;' from dba_users
where account_status = 'LOCKED';


[Edited by stecal on 07-11-2002 at 12:37 PM]

[Upeshp]

Thanks for reply,

I think both of you did not understand my question,

I already have list of Locked users from dba_objects but now i want their object names and sub object names. Meaning Locked users' object name.

Please can anyone give me query for the same.

[stecal]

If you are dropping the users, why do you care what their objects were?

"I have hundreads of users crated in the database, are not active (locked) and want to drop all those users."

select username, object_name from dba_objects a, dba_users b where b.account_status = 'LOCKED'
and a.owner=b.username
order by b.username;

[Upeshp]

Thanks stecal for prompt reply.


My developer wants to make sure that there should not be any object which is in use now. B'cos in the list of LOCKED users, there are some earlier DBAs also who worked and their objects might be in use by application.

Again Thanks,