Home » RDBMS Server » Server Administration » ORA-01667: cannot add any more tablespaces: limit of exceeded (windows oracle 9208)
ORA-01667: cannot add any more tablespaces: limit of exceeded [message #336099] Thu, 24 July 2008 17:09 Go to next message
evoradba
Messages: 104
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
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 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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
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 Go to previous messageGo to next message
evoradba
Messages: 104
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...
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 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Please read your Private Message...
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 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
I suggest opening a Service Request with Oracle on Metalink.
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 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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
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 Go to previous messageGo to next message
evoradba
Messages: 104
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
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 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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.
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 Go to previous message
evoradba
Messages: 104
Registered: April 2005
Location: Canada
Senior Member
thank you so much the database is now open
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:
  


Current Time: Sun Dec 04 14:58:26 CST 2016

Total time taken to generate the page: 0.08179 seconds