Re: ORA-01654 Fix?

The reason that "you are being told to create another tablespace" is that user segments should not be stored in the system tablespace.

Your real problems far exceed the error that you post.

Lets just assume for the duration of this thread that you're treating the Oracle database as a toy black box and that you're a developer and not familiar with administering an Oracle database. You haven't read the Concepts Manual for the release that you're working with and certainly have not read the Administration Guide or "2 Day DBA" guides.

Someone (possibly you) created an application schema named "SYSADM" and failed to assign its default tablespace to something other than "SYSTEM. THAT is the root cause of this particular issue (the real root cause being a lack of training, expertise, knowledge regarding administering Oracle databases).

Judging by the database name (poor use of OFA exhibited in the datafile names) this is not a single user database residing on your laptop and the data inside this database may have some importance. Before you do anything - including reading any further in this post - back that sucker up. Seriously.

Your real solution starts with creating a tablespace and assign that to be the default tablespace for the app schemas that reside in this database. that will prevent new segments from being created in the system tablespace. It will not move any existing segments out of that tablespace, nor will it prevent those existing segments from growning (adding extents).

The second part of this solution includes altering tables to have their segments stored in the new app-specific tablespace, followed by rebuilding their indexes into either the same tablespace - or possibly even into an additional tablespace, if you choose to do so.

Since you have not posted the version and edition of the oracle database server software that you have installed - I cannot futher recommend any specific actions regarding what type of tablespace you should create (dictionary or locally managed, uniform extent or auto allocate extent management) nor if you should attempt online reorganization of such segments (depends upon edition of Oracle).

you really do need to do some serious RTFMing or sub this out to someone who has.

It is possible on certain version of oracle on certain operations for autoextending datafiles to hit certain sizes whereby the tablespace is corrupted. If that tablespace is the system tablespace, that database would be in very very very very serious trouble.

good luck.

-bdbafh Received on Wed Dec 21 2005 - 14:33:59 CST