Local And Dictionary Managed Tablespaces
TABLESPACE MANAGEMENT
The CREATE TABLESPACE statement to create a tablespace. It allocate space in the database that can contain schema objects
Types Of Tablespaces
- Permanent Tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in datafiles.
- Undo Tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.
- temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in tempfiles.
Permanent tablespaces
Permanent tablespaces are used to store user data
CREATE TABLESPACE tools DATAFILE '/u01/oradata/orcl/file_1.dbf' SIZE 100M;
CREATE TABLESPACE tools DATAFILE 'C:\ORA\tools01.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 500M;
Temp tablespaces
Temporary tablespaces are used to store data with short lifespan
CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u01/oradata/orcl/temp/file_1.dbf' SIZE 100M;
Undo tablespaces
Undo tablespaces are used to store "before image" data that can be used to undo transactions.
CREATE UNDO TABLESPACE undots DATAFILE '/u01/oradata/orcl/undo/file_1.dbf' SIZE 20M;
Bigfile tablespaces
This is a feature of Oracle 10G. A bigfile tablespace contains only one datafile or tempfile which can be as big as 2^32 (=4GB) blocks.
create bigfile tablespace beeeg_ts data file '/Uo1/oradata/orcl/big_01.dbf' size 2T
Dictionary managed tablespace
Extents are allocated according to the following storage parameters
Information about used and free extents is stored in the dictionary. |
Locally managed tablespace
A 'Bitmap' is stored in the tablespace. Each bit within this bitmap determines if a corresponding extent in the tablespace is free/used. The extent sizes are either uniform or autoallocate. Hence, the following storage parameters don't make sense and are not permitted:
|
Dictionary Managed Tablespaces Creation
SQL> CREATE TABLESPACE ts1 DATAFILE '/u01/oradata/orcl/ts1_01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);
Locally Managed Tablespaces Creation
SQL> CREATE TABLESPACE ts2 DATAFILE '/u01/oradata/orcl/ts2_01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;SQL> CREATE TABLESPACE ts3 DATAFILE '/u01/oradata/orcl/ts3_01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
Converting LMT and DMT
SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1'); PL/SQL procedure successfully completed.
SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2'); PL/SQL procedure successfully completed.
HTML Comment Box is loading comments...