Managing and Creating a Permanent Tablespaces on Oracle Database 11GR2

oracle-11g-logo

Some importants notes from Oracle’s documentation:
– “… you cannot rename or drop the SYSTEM tablespace or take it offline.”
– “As for the SYSTEM tablespace, management of the SYSAUX tablespace requires a higher level of security and you cannot rename or drop it.”

Locally Managed Tablespace : Define through the command EXTENT MANAGEMENT LOCAL where you can:

– Choose two types of extent management:

AUTOALLOCATE: (default) Database manage extents.

CREATE TABLESPACE tech01 DATAFILE '/u01/app/oracle/oradata/DB/tech01.dbf' SIZE 100M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE ;

tablespace01

UNIFORM: You define the size on command and you won’t have unusable space in your tablespace.

CREATE TABLESPACE tech02 DATAFILE '/u01/app/oracle/oradata/DB/tech02.dbf' SIZE 100M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

tablespace02

– Choose two types of segment space management:

AUTOMATIC (Automatic segment space management, more efficient and default method):Use bitmaps to manage free space in the segment.

SQL> CREATE TABLESPACE tech03 DATAFILE '/u01/app/oracle/oradata/DB/tech03.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

tablespace03

MANUAL (Manual segment space management):Use freelists to manage free space in the segment.

SQL> CREATE TABLESPACE tech04 DATAFILE '/u01/app/oracle/oradata/DB/tech04.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT MANUAL;

tablespace04

Bigfile tablespaces: Tablespaces that have an unique datafile that can have a very large datafile different that a traditional smallfile tablespaces.

One important note from Oracle’s documentation:

Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment space management, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace.

CREATE BIGFILE TABLESPACE techLARGE DATAFILE '+DATA/DB/techlarge.dbf' SIZE  100G;

Temporary Tablespace: Created by default after Oracle database creation named TEMP. But you can create another temporary tablespace and set as the default temporary tablespace.

-- Select to see the default temporary tablespace

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

-- To see the usage space



-- Creating a new temporary tablespace:



-- Defing the temporary tablespace as default:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_databasket; 



 Tablespace Groups for use multiple temporary tablespaces: One user can use temporary space from multiple tablespaces. As described from Oracle's documentation "You do not explicitly create a tablespace group." . Then we will create a group named group1 and assign the temporary tablespace temp_databasket to his.

 -- Assigning the temporary tablespace temp_databasket to group:

SQL> ALTER TABLESPACE temp_databasket TABLESPACE GROUP group1; 

-- Querying the DBA_TABLESPACE_GROUPS view:

SQL> select * from DBA_TABLESPACE_GROUPS;

  

-- Adding other temporary tablespace to group1 and check the tablespace into the group:

SQL> ALTER TABLESPACE TEMP  TABLESPACE GROUP group1;

SQL> select * from DBA_TABLESPACE_GROUPS;

-- Assigning the group1 as default temporary tablespace, and now all users will use tablespaces TEMP and TEMP_DATABASKET

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE group1;
 

 Specifying Nonstandard Block Sizes for Tablespaces

Example:
DB_BLOCK_SIZE on this environment is 8192 bytes.

You must set at least one DB_nK_CACHE_SIZE and DB_CACHE_SIZE initialization parameter.



Then below has an example:



Taking this tablespace offline and brings her online again...

SQL> alter tablespace tab_nondefault offline;

SQL> alter tablespace tab_nondefault online;

Taking this tablespace as a read only tablespace  and brings her writable again...

SQL> alter tablespace tab_nondefault read only;

SQL> alter tablespace tab_nondefault read write;

Increasing tab_nondefault tablespace (you can either add or increase a datafile  to her)...

SQL> alter database datafile '/u01/app/oracle/oradata/DB/tab_nondefault01.dbf' resize 100M;

SQL> alter tablespace tab_nondefault add datafile '/u01/app/oracle/oradata/DB/tab_nondefault02.dbf' size 100m;



**This procedure is the same for temporary tablespace but instead you use datafile you use tempfile.

Renaming tab_nondefault tablespace to tab_nodefault01 (all datafile must be online)...

Renaming the datafiles for tab_nodefault01 (the datafile or tablespace must be offline)

SQL> alter tablespace tab_nondefault01 offline;

$ mv /u01/app/oracle/oradata/DB/tab_nondefault01.dbf /u01/app/oracle/oradata/DB/tab_nondefault01rename.dbf
$ mv /u01/app/oracle/oradata/DB/tab_nondefault02.dbf /u01/app/oracle/oradata/DB/tab_nondefault02rename.dbf

$ ls -ltr *rename*

SQL> alter database rename file '/u01/app/oracle/oradata/DB/tab_nondefault01.dbf' to '/u01/app/oracle/oradata/DB/tab_nondefault01rename.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/DB/tab_nondefault02.dbf'  to '/u01/app/oracle/oradata/DB/tab_nondefault02rename.dbf';
SQL> alter tablespace tab_nondefault01 online;

If you don't set the parameter DB_nK_CACHE_SIZE you cannot create a tablespace with different block size.




Droping tablespaces
 -- Permanents

SQL> drop tablespace tab_nondefault01 including contents and datafiles;


-- Temporary (You must change another temporary tablespace as default).

SQL> alter database default temporary tablespace TEMP_DATABASKET;
SQL> drop  tablespace temp including contents and datafiles;




 Doing shrink on one temporary tablespace...