Permanent Tablespace:- This type of Tablespace is default and is used by end users for storing segment data in the form of extents.
Temporary Tablespace:- This Tablespaces are internally used by Oracle during huge sort operations by creating temporary segments and drops the segments implicitly once sorting is finished.
Tablespace Groups:- It is a logical collection of Temporary Tablespaces together which is applicable for very huge sort operation where single Temporary Tablespace is not sufficient.
Checking space availability in
LMTS
SQL>select name, (sum(a.blocks*8192))/1024/1024 “size MB” from dba_lmt_free_space a, v$tablespace b where a.tablespace_id=b.ts# group by name;
SQL>select name, (sum(a.block*8192))/1024/1024 “Size in MB” from dba_dmt_free_space a, v$tablespace b where a.tablespace_id=b.ts# group by name;
CREATE LOCALLY MANAGED PERMANENT TABLESPACE
SQL>create tablespace dev datafile ‘/u99/dev01.dbf’ size 10m extent management local autoallocate;
CREATE LOCALLY MANAGED TEMPORARY TABLESPACE
SQL>create temporary tablespace mujju tempfile ‘/u99/mujju01.dbf’ size 10m extent management local uniform size 64k;
CREATE DICTIONARY MANAGED TABLESPACE
SQL>create tablespace Mujeeb datafile ‘/u02/oracle/data/mujeeb01.dbf’ size 50m extent management dictionary default storage (initial 50k next 50k minextents 2 maxextents 50 pctincrease 0);ALTERING DICTIONARY MANAGED TABLESPACE
SQL>alter tablespace Mujeeb add datafile ‘/u02/oracle/data/mujeeb02.dbf’ size 1m;ALTERING DEFAULT STORAGE PARAMETERS OF DICTIONARY MANAGED TBS
SQL>alter tablespace mujeeb default storage (next 100k maxextents 20 pctincrease 0);STORAGE PARAMETER
INITIAL:- the very first extent size to be allocated for a segment
NEXT:- Size of next extent, to be allocated for segment
MIN EXTENT:- The minimum number of extents to be allocated for a segment if space is demanded.
MAX EXTENT:- The number of maximum extents a segment can have.
PCT INCREASE:- The percentage of increase in new extent size depending upon its previous extent size.
STORAGE PARAMETERS are applicable only for segments created on DMTS(Dictionary Managed TableSpaces) only and not on LMTS(Locally Managed TableSpaces).
Rather segments created on LMTS can be specified to have uniform size of extents which is 1mb by default.
| | 9i (2k block) DMTS | 10g(8k block) DMTS | 10g (8k block) LMTS |
| INITIAL | 10K | 40K | 64K |
| NEXT | 10K | 40K | 64K |
| MINEXTENT | 1 | 1 | 1 |
| MAXEXTENT | 121 | 505 | 2 BILLION |
| PCT INCREASE | 50% | 50% | NOT APPLICABLE |
| SMALL FILE TABLESPACE | BIG FILE TABLESPACE (10g onwards) | ||||||||||||||||||||||||
| 1. May contain multiple datafiles | 1.Contains only Single Datafile | ||||||||||||||||||||||||
| 2. Increase/Decrease of datafile size is supported using SQL>alter database<command> | 2. Increase/Decrease of datafile size is supported using SQL>alter database datafile fileid resize<size> | ||||||||||||||||||||||||
| 3. Datafile size is limited Calculation=Oracle Block X 4(in gb)
| 3. Datafile is sufficiently very large
If Oracle Database is created with all Big File Tablespace. With 32k block size the Database can grow upto 8 EXA BYTES. Since Oracle Database supports maximum of 65,535 Datafiles. |
TAKING TABLESPACE OFFLINE:-
The Tablespace can be taken offline for making it unavailable or a portion of it unavailable to users while the users are still working on different Tablespace. Tablespace can also be taken offline for taking backups or to make group of tables unavailable while updating or maintaining the application.
If the Tablespace is taken offline then all the datafiles of that Tablespace will be offline.
We cannot take the following Tablespaces Offline:-
SYSTEM TABLESPACE
UNDO TABLESPACE
TEMPORARY TABLESPACE
RECEIPIE(HALDI, NAMAK, MIRCHI, GARAM MASALA etc.,) or else
SQL>
alter tablespace mujeeb offline <statement>Before taking the Tablespace offline make sure that the users are pre informed so that they will not be working of the objects that are going offline as they will not have access to those objects when that particular Tablespace is offline.
| Clause | Description |
| NORMAL | A tablespace can be taken offline normally if no error conditions exist for any of the datafiles of the tablespace. No datafile in the tablespace can be currently offline as the result of a write error. When you specify OFFLINE NORMAL, the database takes a checkpoint for all datafiles of the tablespace as it takes them offline. NORMAL is the default. If we must have to take a tablespace offline, use the NORMAL clause (the default) if possible. This setting guarantees that the tablespace will not require recovery to come back online, even if after incomplete recovery you reset the redo log sequence using an SQL>ALTER DATABASE OPEN RESETLOGS statement. |
| TEMPORARY | A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. When you specify OFFLINE TEMPORARY, the database takes offline the datafiles that are not already offline, checkpointing them as it does so. If no files are offline, but you use the temporary clause, media recovery is not required to bring the tablespace back online. However, if one or more files of the tablespace are offline because of write errors, and you take the tablespace offline temporarily, the tablespace requires recovery before you can bring it back online. Specify TEMPORARY only when we cannot take the tablespace offline normally. As in this case, only the files taken offline because of errors need to be recovered before the tablespace can be brought online. |
| IMMEDIATE | A tablespace can be taken offline immediately, without the database taking a checkpoint on any of the datafiles. When you specify OFFLINE IMMEDIATE, media recovery for the tablespace is required before the tablespace can be brought online. You cannot take a tablespace offline immediately if the database is running in NOARCHIVELOG mode. Specify IMMEDIATE only after trying both the normal and temporary settings. |
SQL>alter tablespace mujeeb offline normal;
