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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.