Here, we will look on how to create and manage different types of tablespaces
1. Creating of localy managed tablespace:
sys@OCM11G> create tablespace test datafile '/oracle/oradata/test01.dbf' size 10M extent management local autoallocate;
Tablespace created.
sys@OCM11G> select tablespace_name,extent_management,segment_space_management from dba_tablespaces;
TABLESPACE_NAME EXTENT_MAN SEGMEN
------------------------------ ---------- ------
SYSTEM LOCAL MANUAL
SYSAUX LOCAL AUTO
UNDOTBS1 LOCAL MANUAL
TEMP LOCAL MANUAL
TEST LOCAL AUTO
sys@OCM11G> create tablespace test2 datafile '/oracle/oradata/test2.dbf' size 5M extent management local segment space
management auto;
Tablespace created.
sys@OCM11G> create tablespace test3 datafile '/oracle/oradata/test3.dbf' size 5M extent management local segment space
management manual;
Tablespace created.
sys@OCM11G> select tablespace_name,extent_management,segment_space_management from dba_tablespaces;
TABLESPACE_NAME EXTENT_MAN SEGMEN
------------------------------ ---------- ------
SYSTEM LOCAL MANUAL
SYSAUX LOCAL AUTO
UNDOTBS1 LOCAL MANUAL
TEMP LOCAL MANUAL
TEST2 LOCAL AUTO
TEST3 LOCAL MANUAL
2. Bigfile Tablespaces:
A bigfile tablespace is a tablespace with a single, but very large datafile.
sys@OCM11G> create bigfile tablespace test datafile '/oracle/oradata/test1.dbf' size 20M;
Tablespace created.
sys@OCM11G> select tablespace_name,bigfile from dba_tablespaces;
TABLESPACE_NAME BIG
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
TEST YES
3. Temporary Tablespaces
To see the free space in the TEMP tablespace:
sys@OCM11G> select * from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP 104857600 5242880 103809024
sys@OCM11G> create temporary tablespace temp1 tempfile '/oracle/oradata/temp2.dbf' size 100M extent management local
uniform size 3M;
Tablespace created.
sys@OCM11G> alter database default temporary tablespace temp1;
Database altered.
sys@OCM11G> create bigfile temporary tablespace temp1 tempfile '/oracle/oradata/temp2.dbf' size 100M uniform size 3M;
Tablespace created.
sys@OCM11G> create temporary tablespace temp2 tempfile '/oracle/oradata/temp2.dbf' size 50M extent management local
uniform size 3M tablespace group tmpgrp;
Tablespace created.
sys@OCM11G> create bigfile temporary tablespace temp3 tempfile '/oracle/oradata/temp3.dbf' size 50M uniform size 3M
tablespace group tmpgrp;
Tablespace created.
sys@OCM11G> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMPGRP TEMP2
TMPGRP TEMP3
sys@OCM11G> create temporary tablespace temp4 tempfile '/oracle/oradata/temp4.dbf' size 50M extent management local
uniform size 3M;
Tablespace created.
sys@OCM11G> alter tablespace temp4 tablespace group tmpgrp;
Tablespace altered.
sys@OCM11G> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMPGRP TEMP2
TMPGRP TEMP3
TMPGRP TEMP4
sys@OCM11G> alter database default temporary tablespace tmpgrp;
Database altered.
sys@OCM11G> alter tablespace temp4 tablespace group '';
Tablespace altered.
sys@OCM11G> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMPGRP TEMP2
TMPGRP TEMP3
sys@OCM11G> drop tablespace temp4 including contents and datafiles;
Tablespace dropped.
sys@OCM11G> drop tablespace temp3 including contents and datafiles;
Tablespace dropped.
sys@OCM11G> drop tablespace temp2 including contents and datafiles;
Tablespace dropped.
sys@OCM11G> select * from dba_tablespace_groups;
no rows selected
4. Specifying Nonstandard Block Sizes for Tablespaces
sys@OCM11G> alter system set db_16k_cache_size=5M scope=both;
System altered.
sys@OCM11G> create tablespace test datafile '/oracle/oradata/test.dbf' size 5M extent management local segment space
management auto blocksize 16k;
Tablespace created.
sys@OCM11G> drop tablespace test including contents and datafiles;
Tablespace dropped.
sys@OCM11G> alter system reset db_16k_cache_size scope=spfile sid='*';
System altered.
5. Altering Tablespace Availability
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
TEST ONLINE
SQL> alter tablespace test offline normal;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
TEST OFFLINE
SQL> alter tablespace test online;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
TEST ONLINE
SQL> alter tablespace test offline temporary;
Tablespace altered.
SQL> alter tablespace test online;
Tablespace altered.
SQL> alter tablespace test offline immediate;
Tablespace altered.
SQL> alter tablespace test online;
alter tablespace test online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/oracle/oradata/test.dbf'
Tablespace needs to be restored from backup.
6. Read-Only tablespaces:
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
TEST ONLINE
SQL> alter tablespace test read only;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
TEST READ ONLY
SQL> alter tablespace test read write;
Tablespace altered.
7. Shrinking a Locally Managed Temporary Tablespace
SQL> select bytes/1024/1024 from dba_temp_files;
BYTES/1024/1024
---------------
100
SQL> alter tablespace temp shrink space keep 20M;
Tablespace altered.
SQL> select bytes/1024/1024 "MB" from dba_temp_files;
MB
----------
21
8. Renaming tablespace:
SQL> alter tablespace test rename to test1;
Tablespace altered.
9. Undo tablespace
UNDO_MANAGEMENT=AUTO,MANUAL
UNDO_TABLESPACE=UNDOTBS1
UNDO_RETENTION= is managed automatically if the tablesapce has fixed size. If its autoextensible, you can use the undo_retention parameter.
sys@OCM11G> select begin_time,end_time,tuned_undoretention from v$undostat;
BEGIN_TIM END_TIME TUNED_UNDORETENTION
--------- --------- - ------------------
12-AUG-11 12-AUG-11 343751
12-AUG-11 12-AUG-11 343751
12-AUG-11 12-AUG-11 303493
12-AUG-11 12-AUG-11 271114
12-AUG-11 12-AUG-11 437739
12-AUG-11 12-AUG-11 356343
12-AUG-11 12-AUG-11 268065
12-AUG-11 12-AUG-11 179787
12-AUG-11 12-AUG-11 557312
sys@OCM11G> create undo tablespace undotbs2 datafile '/oracle/oradata/undotbs2.dbf' size 10M;
Tablespace created.
alter system set undo_tablespace=UNDOTBS2 scope=both;
sys@OCM11G> create tablespace test datafile '/oracle/oradata/test01.dbf' size 10M extent management local autoallocate;
Tablespace created.
sys@OCM11G> select tablespace_name,extent_management,segment_space_management from dba_tablespaces;
TABLESPACE_NAME EXTENT_MAN SEGMEN
------------------------------ ---------- ------
SYSTEM LOCAL MANUAL
SYSAUX LOCAL AUTO
UNDOTBS1 LOCAL MANUAL
TEMP LOCAL MANUAL
TEST LOCAL AUTO
sys@OCM11G> create tablespace test2 datafile '/oracle/oradata/test2.dbf' size 5M extent management local segment space
management auto;
Tablespace created.
sys@OCM11G> create tablespace test3 datafile '/oracle/oradata/test3.dbf' size 5M extent management local segment space
management manual;
Tablespace created.
sys@OCM11G> select tablespace_name,extent_management,segment_space_management from dba_tablespaces;
TABLESPACE_NAME EXTENT_MAN SEGMEN
------------------------------ ---------- ------
SYSTEM LOCAL MANUAL
SYSAUX LOCAL AUTO
UNDOTBS1 LOCAL MANUAL
TEMP LOCAL MANUAL
TEST2 LOCAL AUTO
TEST3 LOCAL MANUAL
2. Bigfile Tablespaces:
A bigfile tablespace is a tablespace with a single, but very large datafile.
sys@OCM11G> create bigfile tablespace test datafile '/oracle/oradata/test1.dbf' size 20M;
Tablespace created.
sys@OCM11G> select tablespace_name,bigfile from dba_tablespaces;
TABLESPACE_NAME BIG
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
TEST YES
3. Temporary Tablespaces
To see the free space in the TEMP tablespace:
sys@OCM11G> select * from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP 104857600 5242880 103809024
sys@OCM11G> create temporary tablespace temp1 tempfile '/oracle/oradata/temp2.dbf' size 100M extent management local
uniform size 3M;
Tablespace created.
sys@OCM11G> alter database default temporary tablespace temp1;
Database altered.
sys@OCM11G> create bigfile temporary tablespace temp1 tempfile '/oracle/oradata/temp2.dbf' size 100M uniform size 3M;
Tablespace created.
sys@OCM11G> create temporary tablespace temp2 tempfile '/oracle/oradata/temp2.dbf' size 50M extent management local
uniform size 3M tablespace group tmpgrp;
Tablespace created.
sys@OCM11G> create bigfile temporary tablespace temp3 tempfile '/oracle/oradata/temp3.dbf' size 50M uniform size 3M
tablespace group tmpgrp;
Tablespace created.
sys@OCM11G> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMPGRP TEMP2
TMPGRP TEMP3
sys@OCM11G> create temporary tablespace temp4 tempfile '/oracle/oradata/temp4.dbf' size 50M extent management local
uniform size 3M;
Tablespace created.
sys@OCM11G> alter tablespace temp4 tablespace group tmpgrp;
Tablespace altered.
sys@OCM11G> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMPGRP TEMP2
TMPGRP TEMP3
TMPGRP TEMP4
sys@OCM11G> alter database default temporary tablespace tmpgrp;
Database altered.
sys@OCM11G> alter tablespace temp4 tablespace group '';
Tablespace altered.
sys@OCM11G> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMPGRP TEMP2
TMPGRP TEMP3
sys@OCM11G> drop tablespace temp4 including contents and datafiles;
Tablespace dropped.
sys@OCM11G> drop tablespace temp3 including contents and datafiles;
Tablespace dropped.
sys@OCM11G> drop tablespace temp2 including contents and datafiles;
Tablespace dropped.
sys@OCM11G> select * from dba_tablespace_groups;
no rows selected
4. Specifying Nonstandard Block Sizes for Tablespaces
sys@OCM11G> alter system set db_16k_cache_size=5M scope=both;
System altered.
sys@OCM11G> create tablespace test datafile '/oracle/oradata/test.dbf' size 5M extent management local segment space
management auto blocksize 16k;
Tablespace created.
sys@OCM11G> drop tablespace test including contents and datafiles;
Tablespace dropped.
sys@OCM11G> alter system reset db_16k_cache_size scope=spfile sid='*';
System altered.
5. Altering Tablespace Availability
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
TEST ONLINE
SQL> alter tablespace test offline normal;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
TEST OFFLINE
SQL> alter tablespace test online;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
TEST ONLINE
SQL> alter tablespace test offline temporary;
Tablespace altered.
SQL> alter tablespace test online;
Tablespace altered.
SQL> alter tablespace test offline immediate;
Tablespace altered.
SQL> alter tablespace test online;
alter tablespace test online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/oracle/oradata/test.dbf'
Tablespace needs to be restored from backup.
6. Read-Only tablespaces:
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
TEST ONLINE
SQL> alter tablespace test read only;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
TEST READ ONLY
SQL> alter tablespace test read write;
Tablespace altered.
7. Shrinking a Locally Managed Temporary Tablespace
SQL> select bytes/1024/1024 from dba_temp_files;
BYTES/1024/1024
---------------
100
SQL> alter tablespace temp shrink space keep 20M;
Tablespace altered.
SQL> select bytes/1024/1024 "MB" from dba_temp_files;
MB
----------
21
8. Renaming tablespace:
SQL> alter tablespace test rename to test1;
Tablespace altered.
9. Undo tablespace
UNDO_MANAGEMENT=AUTO,MANUAL
UNDO_TABLESPACE=UNDOTBS1
UNDO_RETENTION= is managed automatically if the tablesapce has fixed size. If its autoextensible, you can use the undo_retention parameter.
sys@OCM11G> select begin_time,end_time,tuned_undoretention from v$undostat;
BEGIN_TIM END_TIME TUNED_UNDORETENTION
--------- --------- - ------------------
12-AUG-11 12-AUG-11 343751
12-AUG-11 12-AUG-11 343751
12-AUG-11 12-AUG-11 303493
12-AUG-11 12-AUG-11 271114
12-AUG-11 12-AUG-11 437739
12-AUG-11 12-AUG-11 356343
12-AUG-11 12-AUG-11 268065
12-AUG-11 12-AUG-11 179787
12-AUG-11 12-AUG-11 557312
sys@OCM11G> create undo tablespace undotbs2 datafile '/oracle/oradata/undotbs2.dbf' size 10M;
Tablespace created.
alter system set undo_tablespace=UNDOTBS2 scope=both;