CREATE Database Command

[kburrows]

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;

[dbasan]

Post the error!

[kburrows]

create database "prdgis02"
*
ERROR at line 1:
ORA-25146: EXTENT MANAGEMENT option already specified

[davey23uk]

here is one that works, change it to your needs

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

[dbasan]

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.

[kburrows]

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;

[davey23uk]

check the alert log

[kburrows]

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