Search code examples
oracledictionarylocaltablespace

how to change system tablespace's extent_management in oracle


i used oracle 19c.

I'm trying to see the difference between a dictionary of tablespaces, local management.

When the first database was created, it was confirmed that the management of the SYSTEM tablespace was local.

The following query was performed when creating a new tablespace.

CREATE TABLESPACE TABLESPACE1
DATAFILE
    '/TS_DATAFILE1.dbf'
        SIZE 1024000
        AUTOEXTEND OFF
NOLOGGING
DEFAULT NOCOMPRESS STORAGE (
    INITIAL 10240
    NEXT 10240
    MINEXTENTS 2
    MAXEXTENTS 50
    PCTINCREASE 50
)
ONLINE
EXTENT MANAGEMENT
    DICTIONARY
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK OFF;

But the following error occurred.

ORA 12913- Cannot create dictionary managed tablespace

I know that the 19c version is created as local by default, and it seems to be necessary to change the SYSTEM tablespace to a dictionary in order to solve the above error.

But I don't know how to change it. Please advise.


Solution

  • When your system tablespace is already locally managed, you cannot create a tablespace that is dictionary managed.

    Also check this info from oracle.

    • Cause: Attempt to create dictionary managed tablespace in database which has system tablespace as locally managed.
    • Action: Create a locally managed tablespace.

    The extents of a locally managed tablespace are managed efficiently in the tablespace by the Oracle database server itself. Dictionary-managed option is provided only for backward compatibility.