ORA-00604: error occurred at recursive SQL level 1 followed by ORA-25141:

Hello All,

We are trying to build a database and I getting the following error messages:

ORA-00604: error occurred at recursive SQL level 1
ORA-25141: invalid EXTENT MANAGEMENT clause
Offending statement at line 5478
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u18/oradata/DATAMART/dbf/datamart_temp01.dbf' SIZE 2147450880,
'/u18/oradata/DATAMART/dbf/datamart_temp02.dbf' SIZE 2147450880,
'/u18/oradata/DATAMART/dbf/datamart_temp03.dbf' SIZE 2147450880,
'/u18/oradata/DATAMART/dbf/datamart_temp04.dbf' SIZE 2147450880,
'/u18/oradata/DATAMART/dbf/datamart_temp05.dbf' SIZE 2147450880,
'/u18/oradata/DATAMART/dbf/datamart_temp06.dbf' SIZE 2147450880
EXTENT
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/sql.bsq' near line 5478
ORA-00604: error occurred at recursive SQL level 1
ORA-25141: invalid EXTENT MANAGEMENT clause

The create script seems to be fine. I've pasted a copy of that here as well.

CREATE DATABASE DATAMART
USER sys IDENTIFIED BY admin
USER system IDENTIFIED BY manager
DATAFILE '/u12/oradata/DATAMART/dbf/datamart_system01.dbf' SIZE 1000M
AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u18/oradata/DATAMART/dbf/datamart_temp01.dbf' SIZE 2147450880,
'/u18/oradata/DATAMART/dbf/datamart_temp02.dbf' SIZE 2147450880,
'/u18/oradata/DATAMART/dbf/datamart_temp03.dbf' SIZE 2147450880,
'/u18/oradata/DATAMART/dbf/datamart_temp04.dbf' SIZE 2147450880,
'/u18/oradata/DATAMART/dbf/datamart_temp05.dbf' SIZE 2147450880,
'/u18/oradata/DATAMART/dbf/datamart_temp06.dbf' SIZE 2147450880
EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE datamart_undo
DATAFILE '/u81/oradata/DATAMART/dbf/datamart_undo01.dbf' SIZE 2147450880 REUSE,
'/u82/oradata/DATAMART/dbf/datamart_undo02.dbf' SIZE 2147450880 REUSE,
'/u83/oradata/DATAMART/dbf/datamart_undo03.dbf' SIZE 2147450880 REUSE
AUTOEXTEND OFF
MAXLOGFILES 16
MAXLOGMEMBERS 4
MAXDATAFILES 1022
LOGFILE GROUP 1 ('/u81/oradata/DATAMART/rdo/datamart_redo_01_01.log',
'/u84/oradata/DATAMART/rdo/datamart_redo_01_02.log') SIZE 250M REUSE,
GROUP 2 ('/u82/oradata/DATAMART/rdo/datamart_redo_02_01.log',
'/u85/oradata/DATAMART/rdo/datamart_redo_02_02.log') SIZE 250M REUSE,
GROUP 3 ('/u83/oradata/DATAMART/rdo/datamart_redo_03_01.log',
'/u86/oradata/DATAMART/rdo/datamart_redo_03_02.log') SIZE 250M REUSE
CHARACTER SET WE8ISO8859P1
/

I tried to google for the first error message and I can see that it is a generic message and as for the second message it tells me that I should use a valid EXTENT MANAGEMENT value but LOCAL is valid and it works for all other scripts except this one. No one seems to have got this combination of errors though!

Anyone has seen anything like this before? Any solutions?

Thanks in advance,
-Shyami

Can't figure out in which version you are working..

Your temp tablespace has to be manged with uniform extents.

Code:

---

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/Mr_hankey/porn/animals/temp01.dbf'
SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

---

What about this?

Code:

---


SQL> create tablespace test
  2  datafile 'D:\ORACLE\ORADATA\TEST10G\test00003.dbf' size 10M
  3  extent management local;

Tablespace created.

---

Code:

---

SQL> create temporary tablespace test_tmp
  2  tempfile 'D:\ORACLE\ORADATA\TEST10G\test00005.dbf' size 10M
  3  extent management local;

Tablespace created.

---

Quote:

---

Originally posted by Thomasps
What about this?

Code:

---


SQL> create tablespace test
  2  datafile 'D:\ORACLE\ORADATA\TEST10G\test00003.dbf' size 10M
  3  extent management local;

Tablespace created.

---

---

What? The error is to with create temporary tablespace syntax. Not sure what you're demostrating here.

Either omit the extent management clause altogether for the temp tablespace or do as oracldoc suggests

Quote:

---

Originally posted by Thomasps

Code:

---

SQL> create temporary tablespace test_tmp
  2  tempfile 'D:\ORACLE\ORADATA\TEST10G\test00005.dbf' size 10M
  3  extent management local;

Tablespace created.

---

---

Sorry, forgot to add the version, the version is 9.2.0.5.0 and I should say a similar script seems to work fine for me (without the UNIFORM SIZE). I am creating a series of databases and this is the only one that has given me a problem so far.

Thanks for all your replies guys!

have a nice day
-Shyami