Tag Archives: create tablespace locally

Check the tablespace is locally and data dictionary managed in Oracle

Check the tablespace is locally and data dictionary managed in Oracle

Check tablespace is locally or Dictionary Managed

SQL> SELECT tablespace_name,extent_management,segment_space_management from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN SEGMEN
------------------------------ ---------- ------
SYSTEM                         DICTIONARY MANUAL
SYSAUX                         LOCAL      AUTO
UNDOTBS1                       LOCAL      MANUAL
TEMP                           LOCAL      MANUAL
USERS                          LOCAL      AUTO

Dictionary Managed
Dictionary managed tablespace inwhich oracle use data dictionary to keep track the allocated and free extents in tablespace. Free space tracked with SYS.FET$ table and used space tracked with SYS.UET$ tablespace. It will increase overhead of Oracle Server to maintained entries in these tables.

CREATE TABLESPACE data_orcl DATAFILE 'D:\oradata\orcl\dataorcl_01.dbf' SIZE 50M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

Locally Managed
Each tablespace manages it’s own free and used space within a bitmap structure stored in one of the tablespace’s data files. Each bit corresponds to a database block or group of blocks.

CREATE TABLESPACE data_orcl DATAFILE 'D:\oradata\orcl\dataorcl_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;