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

[tracy]

hi,
i need advise and guidance from experts.
Today, I discovered about 1464 tables created in SYSTEM tablespace and
the owner is dev_user.

As, i read from ORACLE 101 DBA's it said :'System tablespace is a
tablespace user to stored objects belong to user SYS only. if, we do
not ensure this, the SYSTEM tablespace will :
• Tend to get very fragmented
• Have potentially severe contention against it and affect performance
•
we cannot alter SYSTEM tablespace offline, there is no way to rebuild
this tablespace without rebuilding the whole database and this can be
very time consuming.'

Then, wat should i do? please advise.
regards,
tracykim

[x]

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.

[Mark D Powell]

"x" <[e-mail]> wrote in message news:<c9**********@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 --

[tracy]

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







Ma*********@eds.com (Mark D Powell) wrote in message news:<26**************************@posting.google. com>...

"x" <[e-mail]> wrote in message news:<c9**********@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 --

[x]

I wonder, how can the table
created by them is in system schema? Pls let me know, if u have the
answer.

create table my_table (x varchar2(1)) tablespace system

Easy as that. If your dev_user has right priviledges he can create table
where ever he wants.

[tracy]

hi, x
dev_user only have priviledge to create table under his own tablespace
which is dev_user only. he only granted the priviledge 'create table'
not 'create any table'
"x" <[e-mail]> wrote in message news:<c9**********@ls219.htnet.hr>...

I wonder, how can the table
created by them is in system schema? Pls let me know, if u have the
answer.

create table my_table (x varchar2(1)) tablespace system

Easy as that. If your dev_user has right priviledges he can create table
where ever he wants.

[Frank van Bortel]

tracy wrote:

hi, x
dev_user only have priviledge to create table under his own tablespace
which is dev_user only. he only granted the priviledge 'create table'
not 'create any table'
"x" <[e-mail]> wrote in message news:<c9**********@ls219.htnet.hr>...

I wonder, how can the table
created by them is in system schema? Pls let me know, if u have the
answer.

create table my_table (x varchar2(1)) tablespace system

Easy as that. If your dev_user has right priviledges he can create table
where ever he wants.

Unless granted the resource role, which offers quota unlimited on
any tablespace. Unless you specifically do
alter user quota 0 on system;
your dev_user can still create objects in the system tablespace
--

Regards,
Frank van Bortel

[Mark D Powell]

Frank van Bortel <fv********@netscape.net> wrote in message news:<c9**********@news1.tilbu1.nb.home.nl>...

tracy wrote:

hi, x
dev_user only have priviledge to create table under his own tablespace
which is dev_user only. he only granted the priviledge 'create table'
not 'create any table'
"x" <[e-mail]> wrote in message news:<c9**********@ls219.htnet.hr>...

I wonder, how can the table
created by them is in system schema? Pls let me know, if u have the
answer.
create table my_table (x varchar2(1)) tablespace system

Easy as that. If your dev_user has right priviledges he can create table
where ever he wants.

Unless granted the resource role, which offers quota unlimited on
any tablespace. Unless you specifically do
alter user quota 0 on system;
your dev_user can still create objects in the system tablespace

Frank provided the information on how to lock the user out if it has
the resource role. Privileges should be aligned to what the ID has to
be able to do and no more.

Depending on the age of the objects it is also possible that they were
created before the default tablespace of the user was changed and were
never relocated.

Once you review and modify the privileges as necessary you still need
to clean up the system tablespace.

-- Mark D Powell --