hi, guys,
tanz for the help. Just to make sure i run the rite script, here is
the sql, how i found out non-sys user created a tables in SYSTEM
tablespace. In fact, this dev_user have been assigened a default
tablespace which is dev_user Tablespace. I wonder, how can the table
created by them is in system schema? Pls let me know, if u have the
answer.
1 select owner, TABLESPACE_NAMe, table_name
2 from dba_tables
3 where TABLESPACE_NAMe='SYSTEM'
4 AND OWNER='DEV_USER'
SQL> /
OWNER TABLESPACE_NAME
TABLE_NAME
------------------------------ ------------------------------
--------------------------
DEV_USER SYSTEM
CES_DETAIL
DEV_USER SYSTEM CES_MAIN
DEV_USER SYSTEM
CHARGES_LOOKUP
DEV_USER SYSTEM
CHECK_AGING
DEV_USER SYSTEM
CHECK_CREDITOR
Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0406030639.4b7b69c7_at_posting.google.com>...
> "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 - 21:25:25 CDT
