Re: ORA-01654 Fix?

Ah, then this database is more the victim of the defaults set in a starter database - or of the poor practices of a now defunct vendor.

It is helpful to include operating system information, storage subsystem info.

Care to specify the Oracle database server software version including patchsets, and whether this is Enterprise or Standard Edition?
>From sqlplus:

SQL> select * from v$version;
SQL> select * from v$option;

7 years ago - its likely either Oracle v7.3 (7.3.4) or v8 (8.0.5). Both of these versions were long since de-supported. Locally managed tablespaces were not introduced until Oracle 8i - so those won't be an option available to you.

If you're dealing with dictionary managed tablespaces back in Oracle 7.3 - you might want to consider manually coalescing that tablespace (after you have good backups).

If pctincrease has been set to a non-zero value (say 50 %) its entirely possible that the next extent sizes for large segments are rather large. Adjusting these values downward may buy you some time.

Performing the re-org to new tablespaces should be fairly straightforward.
A tool such as TOAD has a nice GUI interface for perfoming re-organizations - you won't even have to write any scripts.

what is the blocksize for this database? (its in the file init.ora).

How are the backups configured for this database? - as you're going to have to be able to rely upon them prior to mucking about.

As the path delimiters indicate I'm guessing that this is either an MS W2K | WinNT 4.0 box. The bug that I referred to did occur on win32 operating systems on the 2 GB mark. It would be worth your while to connect to the Oracle Metalink site https://metalink.oracle.com and see if you have hit this bug.

hth.

-bdbafh Received on Wed Dec 21 2005 - 16:13:22 CST