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;

Post the error!

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

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

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.

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;

check the alert log

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