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

Thread: CREATE Database Command

  1. #1
    Join Date
    Nov 2000
    Posts
    172

    CREATE Database Command

    HI,

    Oracle 9.2.0.3
    UNIX AIX 5.2

    I am having trouble with the 9i create database command.

    I want to use the new locally managed tbsp for the system tablespace. I have tried several ways and still getting errors.

    I want:
    Local in all tbsp, no dictionary
    uniform sizing
    no auto extend

    See below.

    create database "$db"
    maxinstances 2
    maxlogfiles 32
    maxlogmembers 4
    maxloghistory 500
    maxdatafiles 32000
    datafile '/u09/oradata/ORACLE/prdgis02/system01.dbf' size 200M
    extent management LOCAL uniform size 128k
    character set UTF8
    national character set UTF8
    logfile group 1 ('/u10/oradata/ORACLE/prdgis02/redo01.log') size 250M,
    group 2 ('/u10/oradata/ORACLE/prdgis02/redo02.log') size 250M,
    group 3 ('/u10/oradata/ORACLE/prdgis02/redo03.log') size 250M
    default temporary tablespace TEMP
    tempfile '/u12/oradata/ORACLE/prdgis02/temp01.dbf' size 500M,
    '/u12/oradata/ORACLE/prdgis02/temp02.dbf' size 500M,
    '/u12/oradata/ORACLE/prdgis02/temp03.dbf' size 500M
    extent management LOCAL uniform size 1024k
    undo tablespace undotbs01
    datafile '/u13/oradata/ORACLE/prdgis02/undotbs01_1.dbf' size 500M extent amangement LOCAL uniform size 1024k;
    Last edited by kburrows; 12-19-2005 at 10:48 AM.

  2. #2
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Post the error!
    "What is past is PROLOGUE"

  3. #3
    Join Date
    Nov 2000
    Posts
    172
    create database "prdgis02"
    *
    ERROR at line 1:
    ORA-25146: EXTENT MANAGEMENT option already specified

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    here is one that works, change it to your needs
    Code:
    CREATE DATABASE testdb
    MAXINSTANCES 8
    MAXLOGHISTORY 0
    MAXLOGFILES 80
    MAXLOGMEMBERS 5
    MAXDATAFILES 1024
    DATAFILE '/u02/oradata/testdb/system01.dbf' SIZE 1024M REUSE
    EXTENT MANAGEMENT LOCAL
    DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u03/oradata/testdb/temp01.dbf' SIZE 200M REUSE
    UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u03/oradata/testdb/undotbs01.dbf' SIZE 300M REUSE
    CHARACTER SET AL32UTF8
    NATIONAL CHARACTER SET UTF8
    LOGFILE GROUP 1 ('/u04/oradata/testdb/redo01a_1.rdo', '/u05/oradata/testdb/redo01b_1.rdo') SIZE 500M REUSE,
    GROUP 2 ('/u04/oradata/testdb/redo02a_1.rdo', '/u05/oradata/ltestdb/redo02b_1.rdo') SIZE 500M REUSE,
    GROUP 3 ('/u04/oradata/testdb/redo03a_1.rdo', '/u05/oradata/testdb/redo03b_1.rdo') SIZE 500M REUSE,
    GROUP 4 ('/u04/oradata/testdb/redo04a_1.rdo', '/u05/oradata/testdb/redo04b_1.rdo') SIZE 500M REUSE;
    spool off
    exit;
    probably got something to do with you specifying the extent size for the system tablespace

  5. #5
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by kburrows
    create database "prdgis02"
    *
    ERROR at line 1:
    ORA-25146: EXTENT MANAGEMENT option already specified
    check the script, you have specified EXTENT MANAGEMENT LOCAL more than once says the error.
    "What is past is PROLOGUE"

  6. #6
    Join Date
    Nov 2000
    Posts
    172
    Thanks, I tried your copy and not I am getting another error. This one I got before when I took all the uniform sizing off. Any Ideas?

    create database "prdgis02"
    *
    ERROR at line 1:
    ORA-01092: ORACLE instance terminated. Disconnection forced

    Here is the new copy:

    create database "$db"
    maxinstances 2
    maxlogfiles 32
    maxlogmembers 4
    maxloghistory 500
    maxdatafiles 32000
    datafile '/u09/oradata/ORACLE/prdgis02/system01.dbf' size 200M reuse
    extent management LOCAL
    character set UTF8
    national character set UTF8
    logfile group 1 ('/u10/oradata/ORACLE/prdgis02/redo01a.log', '/u11/oradata/ORACLE/prdgis02/redo01b.log') size 250M reuse,
    group 2 ('/u10/oradata/ORACLE/prdgis02/redo02a.log', '/u11/oradata/ORACLE/prdgis02/redo02b.log') size 250M reuse,
    group 3 ('/u10/oradata/ORACLE/prdgis02/redo03a.log', '/u11/oradata/ORACLE/prdgis02/redo03b.log') size 250M reuse
    default temporary tablespace TEMP
    tempfile '/u12/oradata/ORACLE/prdgis02/temp01.dbf' size 500M,
    '/u12/oradata/ORACLE/prdgis02/temp02.dbf' size 500M,
    '/u12/oradata/ORACLE/prdgis02/temp03.dbf' size 500M
    undo tablespace UNDOTBS1
    datafile '/u13/oradata/ORACLE/prdgis02/undotbs1_01.dbf' size 500M;

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    check the alert log

  8. #8
    Join Date
    Nov 2000
    Posts
    172
    Yep, alert log showed, I forgot to use reuse.

    It works, I wound up with the following. I will have to check into some of the defaults. I did nto specify any uniform extents, but I did get some in TEMP. Thanks for your Help.

    TABLESPACE_NAME EXTENT_MAN ALLOCATIO
    ------------------------------ ---------- ---------
    SYSTEM LOCAL SYSTEM
    UNDOTBS1 LOCAL SYSTEM
    TEMP LOCAL UNIFORM
    USERS LOCAL UNIFORM
    TOOLS LOCAL UNIFORM

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