OraFAQ Forum: Server Administration » ORA-01667: cannot add any more tablespaces: limit of exceeded

Home » RDBMS Server » Server Administration » ORA-01667: cannot add any more tablespaces: limit of exceeded (windows oracle 9208)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01667: cannot add any more tablespaces: limit of exceeded [message #336099]

Thu, 24 July 2008 17:09

evoradba
Messages: 144
Registered: April 2005
Location: Canada

Senior Member

hello

The server crahesd now i cant start oracle i keep getting
ORA-01667: cannot add any more tablespaces: limit of exceeded

how can i fix it? i know i can edit the control file but i cant even bring oracle up

thank you
Maria

Report message to a moderator

Re: ORA-01667: cannot add any more tablespaces: limit of exceeded [message #336100 is a reply to message #336099]

Thu, 24 July 2008 17:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Please provide more details as to how you got into this situation.

Use CUT & PASTE to show us what you are doing & how Oracle responds

Report message to a moderator

Re: ORA-01667: cannot add any more tablespaces: limit of exceeded [message #336102 is a reply to message #336100]

Thu, 24 July 2008 17:39

evoradba
Messages: 144
Registered: April 2005
Location: Canada

Senior Member

here we go

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Jul 24 18:40:21 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1318135840 bytes
Fixed Size 456736 bytes
Variable Size 587202560 bytes
Database Buffers 729808896 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\test\SYSTEM01.DBF'

SQL>
SQL>
SQL> recover datafile 'D:\ORACLE\ORADATA\test\SYSTEM01.DBF''
ORA-01756: quoted string not properly terminated

SQL> recover datafile 'D:\ORACLE\ORADATA\test\SYSTEM01.DBF';
Media recovery complete.
SQL> alter database open
2 ;
alter database open
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\test\UNDOTBS01.DBF'

SQL> recover datafile 'D:\ORACLE\ORADATA\test\UNDOTBS01.DBF';
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: 'D:\ORACLE\ORADATAtest\USERS01.DBF'

SQL> recover datafile 'D:\ORACLE\ORADATA\test\USERS01.DBF';
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

Thu Jul 24 18:07:28 2008
Errors in file d:\oracle\admin\test\bdump\test_ckpt_2384.trc:
ORA-01667: cannot add any more tablespaces: limit of exceeded

Thu Jul 24 18:07:29 2008
Errors in file d:\oracle\admin\test\bdump\ctest_lgwr_3484.trc:
ORA-01667: cannot add any more tablespaces: limit of exceeded

Instance terminated by USER, pid = 1596
ORA-1092 signalled during: alter database open...

Report message to a moderator

Re: ORA-01667: cannot add any more tablespaces: limit of exceeded [message #336103 is a reply to message #336099]

Thu, 24 July 2008 17:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read your Private Message...

Report message to a moderator

Re: ORA-01667: cannot add any more tablespaces: limit of exceeded [message #336104 is a reply to message #336099]

Thu, 24 July 2008 17:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I suggest opening a Service Request with Oracle on Metalink.

Report message to a moderator

Re: ORA-01667: cannot add any more tablespaces: limit of exceeded [message #336105 is a reply to message #336099]

Thu, 24 July 2008 17:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Using CUT & PASTE show the results of the following:
sqlplus
/ as sysdba
shutdown abort
startup mount
alter database backup controlfile to trace;
shutdown immediate
exit

Report message to a moderator

Re: ORA-01667: cannot add any more tablespaces: limit of exceeded [message #336107 is a reply to message #336105]

Thu, 24 July 2008 18:11

evoradba
Messages: 144
Registered: April 2005
Location: Canada

Senior Member

done

SQL> startup mount
ORACLE instance started.

Total System Global Area 1318135840 bytes
Fixed Size 456736 bytes
Variable Size 587202560 bytes
Database Buffers 729808896 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter database backup controlfile to trace;

Database altered.

SQL>

now i can see the backup in the udump
how can i start oracle now using the

Report message to a moderator

Re: ORA-01667: cannot add any more tablespaces: limit of exceeded [message #336108 is a reply to message #336099]

Thu, 24 July 2008 18:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Edit the file.
Remove all line above the following:

--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA1DEV" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 5840

You will have your own ORACLE_SID in place of "ORA1DEV"
Change the value associated with MAXDATAFILES
Remove all lines after the following:

-- End of tempfile additions.
-- 
--     Set #2. RESETLOGS case

save as restart.sql

sqlplus
/ as sysdba
shutdown abort
@restart.sql
exit

Ideally you will now have an open & open database.

Report message to a moderator

Re: ORA-01667: cannot add any more tablespaces: limit of exceeded [message #336111 is a reply to message #336108]

Thu, 24 July 2008 18:32

evoradba
Messages: 144
Registered: April 2005
Location: Canada

Senior Member

thank you so much the database is now open

Report message to a moderator

Previous Topic:	How to setup a job to identify any trace file which contains "ORA-" in it
Next Topic:	unable to drop user get ORA-00600

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 12 11:33:16 CDT 2026