RE: Novice Question

> -----Original Message-----
> From: Kishore [mailto:mcgoracle_at_yahoo.com]
>
> I am in a position to export a user and drop it then
> create again and import it. Why I am doing this is
> because I want to change the default tablespace of the
> user to a different one and also its tabel are out of
> extents.
>
> Now , my question ; may be very stupid though, Would
> it break anything like indexes and views depends on
> it. Once imported back everything would be same as it
> was before.
>
> Is there a way I know how many views , indexes and
> every other objects depend on this user before even I
> export.

The views and source code will need to be recompiled after the import. alter view view_name compile ;
alter procedure procedure_name compile ; alter function function_name compile ;
alter package package_name compile body ;

Objects owned by the user:
select object_type, count (*)
from dba_objects
where owner = 'USER_NAME' ;

dependencies to objects owned by that user: (this will show views and source code)
select owner, name, type
from dba_dependencies
where referenced_owner = 'USER_NAME'

synonyms to objects owned by that user:
select owner, synonym_name
from dba_synonyms
where table_owner = 'USER_NAME' ;

indexes on tables owned by that user:
select owner, index_name
from dba_indexes
where table_owner = 'USER_NAME' ;

Jacques R. Kilchoër
x8816> Received on Wed Aug 29 2001 - 14:13:23 CDT