Sunday , October 22 2017
Home / Oracle DBA / LOCAL Vs DICTIONARY managed tablespaces

LOCAL Vs DICTIONARY managed tablespaces

LOCAL Vs DICTIONARY managed tablespaces

DMT

  1. In DMT, free block information is used to maintain in the form of freelist which will be there in data dictionary cache.
  2. Every time DBWR n requires free block, server process will perform an I/O to know free block information from free list. This will happen to all the free blocks. Because more no of I/O’s are being performed, it will degrade the performance of database.

LOGICALLY MANAGED TABLE SPACE

  1. In locally managed tablespace, free block information is maintained in datafile header itself in the form of bitmap blocks
  2. These bitmaps are represented with 0 and 1 where 0 means free and 1 means used.
  3. When a free block is required, server process will search in bitmap block and will inform DBWRn thus it is avoiding I/O which increases database performance.
  4. In 8i default tablespace type is dictionary, but still we can create locally managed tablespace where as in 9i, default is local.
Note: In any version, we can create dictionary managed tablespace only if SYSTEM tablespace is dictionary.
Tablespace creation syntax
create tablespace mytbs
datafile ‘/u02/ora10g/prod/mytbs01.dbf’ size 50m
autoextend on maxsize 200m
extent management local / dictionary
segment space management auto / manual
inittrans 1 maxtrans 255
pctfree 20 pctused 40
initial 1m next 5m
pctincrease / uniform / autoallocate
minextents 1 maxextents 500
logging / nologging
blocksize 8k; – this is optional
Note:Even though we specify a tablespace as NOLOGGING, still all DML transactions will generate redo entries (this is to help in instance recovery). NOLOGGING is applicable in only below situations
  1. create table B as select * from A nologging;
  2. insert into B select * from A nologging;
  3. alter index <index_name> rebuild online nologging;
  4. create index <index_name> on table_name(column_name) nologging;
  5. any DML operations on LOB segments.
COMMANDS
# To create a tablespace
SQL> create tablespace mytbs
datafile ‘/u02/prod/mytbs01.dbf’ size 10m;
# To create a tablespace in 9i
SQL> create tablespace mytbs
datafile ‘/u02/prod/mytbs01.dbf’ size 10m
         segment space management auto;
# To create dictionary managed tablespace
SQL> create tablespace mytbs
datafile ‘/u02/prod/mytbs01.dbf’ size 10m
         extent management dictionary;
# To view tablespace information
SQL> select allocation_type,extent_management,contents from dba_tablespaces where
tablespace_name=’MYDATA’;
# To view datafile information
SQL> select file_name,sum(bytes),autoextensible,sum(maxbytes) from dba_data_files where tablespace_name=’MYDATA’ group by file_name,autoextensible;
# To check the database size
SQL> select sum(bytes/1024/1024/1024) from dba_data_files;
# To enable/disable autoextend
SQL> alter database datafile ‘/u02/prod/mytbs01.dbf’ autoextend on maxsize 100m;
SQL> alter database datafile ‘/u02/prod/mytbs01.dbf’ autoextend off;
# To resize a datafile
SQL> alter database datafile ‘/u02/prod/mytbs01.dbf’ resize 20m;
# To add a datafile
SQL> alter tablespace mytbs add datafile ‘/u02/prod/mytbs02.dbf’ size 10m;
Note: If we have multiple datafiles, extents will be allocated in round robin fashion.
Note: Adding the datafile for tablespace size increment is the best option if we have multiple hard disks.
# To rename a tablespace (10g/11g)
SQL> alter tablespace mytbs rename to mydata;
# To convert DMT to LMT or vice versa
SQL> exec dbms_space_admin.tablespace_migrate_to_local(‘MYDATA’);
SQL> exec dbms_space_admin.tablespace_migrate_from_local(‘MYDATA’);
Note: Local to dictionary conversion is possible only if SYSTEM tablespace is not local.
# To rename or relocate a datafile
SQL> alter tablespace mydata offline;
SQL> !mv /u02/prod/mytbs01.dbf /u02/prod/mydata01.dbf
SQL> alter tablespace mytbs rename datafile ‘/u02/prod/mytbs01.dbf’ to ‘/u02/prod/mydata01.dbf’;
SQL> alter tablespace mydata online;
# To rename or relocate system datafile
SQL> shutdown immediate
SQL> !mv /u02/prod/system.dbf /u02/prod/system 01.dbf
SQL> startup mount
SQL> alter database rename file ‘/u02/prod/system.dbf’ to ‘/u02/prod/system 01.dbf’;
SQL> alter database open;
Note: The above steps can also be used for normal data file renaming/relocation.
# To drop a table space
SQL> drop table space my data;
This will remove table space info from base tables, but still data files exist at OS level
Or
SQL> drop table space my data including contents;
This will remove table space info and also clears the data file (i.e it will empty the contents)
Or
SQL> drop table space my data including contents and data files;
This will remove at oracle and also OS level
# To reuse a data file
SQL> alter table space my data add data file ‘/u 02/prod/my data 02.dbf’ reuse;
Read More:
Some Interview Questions
Using the Result Cache
SELECT STATEMENT PROCESSING

Comments

comments

Check Also

Moving a database from Normal File System to ASM storage

Default storage for oracle database is File System, where database files resides on local storage. …

Leave a Reply

Your email address will not be published. Required fields are marked *