Question:
I am getting an ORA-25141: invalide EXTENT MANAGMENT clause error
when trying to create the system tablespace's extent management
dictionary using the following:
create database
logfile
group 1
('/u01/app/oradata/anand/redo1a.log') size 100M,
group 2
('/u01/app/oradata/anand/redo2a.log') size 100M,
group 3
('/u01/app/oradata/anand/redo3a.log') size 100M
datafile
'/u01/app/oradata/anand/system.dbf' size 400M extent management
dictionary
sysaux datafile '/u01/app/oradata/anand/sysaux.dbf'
size 300M default temporary tablespace temp tempfile
'/u01/app/oradata/anand/temp.dbf' size 50M
Running this code results in the ORA-25141: invalid EXTENT
MANAGEMENT clause. How can I create the system tablespace's
extent management directory without getting the ORA-25141?
Answer:
The oerr information on the ORA-25141 says the following:
ORA-25141: invalid EXTENT
MANAGEMENT clause
Cause: An invalid option appears for EXTENT
MANAGEMENT clause
Action: Specify one of the valid options:
UNIFORM SIZE, AUTOALLOCATE
FYI, nobody uses dictionary managed tablespaces much anymore,
they can cause bottlenecks in the data dictionary as tablespaces
grow! Instead, check out
locally managed tablespace (LMT).
There are some working examples of the CREATE DATABASE command
HERE.
Further review of the documentation reveals the following points
on the use of extent_management_clause for creating a
locally managed SYSTEM tablespace. Omitting this clause will
result in the SYSTEM tablespace being dictionary managed.
Caution: A locally managed SYSTEM tablespace cannot be
changed to be dictionary managed, nor can you create any other
dictionary-managed tablespaces in this database.
- If this clause is specified, the database must have a
default temporary tablespace, because a locally managed SYSTEM
tablespace cannot store temporary segments.
- If EXTENT MANAGEMENT LOCAL is specified but you do not
specify the DATAFILE clause, the default_temp_tablespace
clause can be omitted. Oracle Database will create a default
temporary tablespace called TEMP with one datafile of size 10M
with autoextend disabled.
- If both EXTENT MANAGEMENT LOCAL and the DATAFILE clause are
specified, then the default_temp_tablespace clause must
be used and datafile explicitly specified for that tablespace.
If the instance has been opened in automatic undo mode,
similar requirements exist for the database undo tablespace:
- If EXTENT MANAGEMENT LOCAL is specified but the
DATAFILE clause is not, the undo_tablespace clause can
be omitted. Oracle Database will create an undo tablespace named
SYS_UNDOTBS.
- If both EXTENT MANAGEMENT LOCAL and the DATAFILE clause are
specified, then the undo_tablespace clause must be used
and a datafile for that tablespace explicitly specified.
