DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

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

  1. #1

    Question 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
    ------------------------------------------
    Shyami.S.Seelan, OCP 8i, 9i DBA
    Dublin, Ireland.
    http://www.geocities.com/shyamiseelan/OCP
    ------------------------------------------

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Can't figure out in which version you are working..
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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;
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    What about this?
    Code:
    SQL> create tablespace test
      2  datafile 'D:\ORACLE\ORADATA\TEST10G\test00003.dbf' size 10M
      3  extent management local;
    
    Tablespace created.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  5. #5
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Code:
    SQL> create temporary tablespace test_tmp
      2  tempfile 'D:\ORACLE\ORADATA\TEST10G\test00005.dbf' size 10M
      3  extent management local;
    
    Tablespace created.
    Last edited by Thomasps; 11-09-2004 at 10:03 AM.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  6. #6
    Join Date
    Jul 2002
    Posts
    335
    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

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    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.
    Last edited by Thomasps; 11-09-2004 at 10:03 AM.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    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
    ------------------------------------------
    Shyami.S.Seelan, OCP 8i, 9i DBA
    Dublin, Ireland.
    http://www.geocities.com/shyamiseelan/OCP
    ------------------------------------------

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width