Re: tablespace SYSTEM used by others user, not sys. Help!

"x" <x_at_x.hr> wrote in message news:<c9mrg5$6t5$1_at_ls219.htnet.hr>...
> You should move all tables and rebuild all indexes to different tablespace.
>
> something like:
>
> alter table my_table move tablespace users;
>
> alter index my_index rebuild tablespace users;
>
> Use dba_tables and dba_indexes to find out which tables and indexes are in
> system tablespace. You could use it like this:
>
>
> select 'alter table ' || table_name || ' move tablespace users;'
> from dba_tables
> where tablespace_name='SYSTEM' and owner not in('SYS','SYSTEM')
>
> Output of this query is a script which you should run to move all the
> tables. Do the same for indexes.

I think the first step should be to alter the user dev_user to have a default tablespace other than SYSTEM. If dev_user is not a DBA I would then alter the user's quota/privilege depending on exactly how this ID is to be used to prevent it from using SYSTEM. This will stop any new non-SYS objects from being created in the SYSTEM tablespace.

Moving or dropping the objects is the next step, and depending on object usage may take a while.

HTH -- Mark D Powell -- Received on Thu Jun 03 2004 - 16:39:04 CEST