System tablespace 1

[barbb]

I have a customer who is using an Oracle database for our product. We use an ODBC connection. This customer is having major performance issues with our product that our other customers using MS SQL Server are not experiencing. Looking at their Oracle database I noticed that our tables and indexes are in the System tablespace. I was wondering if anybody could tell me if this is correct or if this could be causing them performance issues. And, if so, then how would one go about moving the tables and indexes to a different tablespace? Any help will be greatly appreciated.

 

[Georgdba]

Hi -

this failure is very common but help is near -
please give a little more details about OS-system and Oracle Server version.
Best way should be recreate the DB-Objects in the right Tablespace by the SchemaOwner (not by DBA).
Helpful would be the total size of tables, indexes ..

greetings
Georgdba

 

[liyakat]

hello barb

u can create a new tablespace by using the command run as sys user
sql> create tablespace users datafile '<path>\<filename>' size 25m autoextend on;

then u can move ur tables from system tablespace to this tablespace by the command below.

run as the table owner.
sql>alter table <tablename> move tablespace users;

onething is that ur indexes will have to be recreated.

regards

Liyakat Parkar

 

[sem]

In most cases you shouldn't create users object in SYSTEM tablespace. As I suppose the owner has no DEFAULT (and possibly TEMPORARY) tablespace defind. In this case all its objects are created in SYSTEM tablespaces unless other is specified explicitly. Moreover, without TEMPORARY tablespace SYSTEM is also used for sorting and some other operaions.

To prevent from future faults
ALTER USER <user> DEFAULT TABLESPACE <tablespace> TEMPORARY TABLESPACE <temporary tablespace>

You may also move the tables by

ALTER TABLE <table> MOVE TABLESPACE <tablespace>