Oracle Database Tablespace Management.

To check for all the tablespaces in your database
issue the following query.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

6 rows selected.

To check for tablespace space related information
issue the following query.

SQL> select tablespace_name, file_name, bytes/1024/1024,
 maxbytes/1024/1024, autoextensible, increment_by from dba_data_files;
 

TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT INCREMENT_BY
--------------- ------------------ --- ------------
USERS
/u01/app/oracle/oradata/orcl/users01.dbf
              5         32767.9844 YES          160

UNDOTBS1
/u01/app/oracle/oradata/orcl/undotbs01.dbf
             60         32767.9844 YES          640

SYSAUX
/u01/app/oracle/oradata/orcl/sysaux01.dbf
            480         32767.9844 YES         1280

SYSTEM
/u01/app/oracle/oradata/orcl/system01.dbf
            670         32767.9844 YES         1280

EXAMPLE
/u01/app/oracle/oradata/orcl/example01.dbf
            100         32767.9844 YES           80



# tablespace_name = Name of the tablespace
# file_name = Name of the datafile
# bytes = size of the file in bytes
# maxbytes = Maximum data file size in bytes.
# autoextensible = whether auto-extended or not.
# increment_by = Number of blocks used for autoextension.



To check for temporary tablespace space related information
issue the following query.


SQL> select tablespace_name, file_name, bytes/1024/1024,
 maxbytes/1024/1024, autoextensible, increment_by from dba_temp_files
 /

TABLESPACE_NAME
------------------------------
FILE_NAME
----------------------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT INCREMENT_BY
--------------- ------------------ --- ------------
TEMP
/u01/app/oracle/oradata/orcl/temp01.dbf
             20         32767.9844 YES           80


 

The dba_free_space view describes the free extents available
in all the tablespaces in the database.

SQL> select sum(bytes)/1024/1024 mbytes, tablespace_name 
from dba_free_space group by tablespace_name;

    MBYTES TABLESPACE_NAME
---------- ------------------------------
    25.875 SYSAUX
    7.0625 UNDOTBS1
     .9375 USERS
   21.5625 EXAMPLE


Tablespace Creation

SQL> create tablespace test datafile 
    '/u01/app/oracle/oradata/orcl/test01.dbf'
     size 50m autoextend on next 100M
     extent management local 
     segment space management auto;

Tablespace created.


SQL> select tablespace_name from dba_tablespaces
 /

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TEST

7 rows selected.



SQL> select tablespace_name, file_name, bytes/1024/1024, 
maxbytes/1024/1024, autoextensible, increment_by from
dba_data_files where tablespace_name='TEST';

TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT INCREMENT_BY
--------------- ------------------ --- ------------
TEST
/u01/app/oracle/oradata/orcl/test01.dbf
             50         32767.9844 YES        12800



Add a datafile

SQL> alter tablespace test add datafile 
'/u01/app/oracle/oradata/orcl/test02.dbf'
 size 50M autoextend on next 100M;

Tablespace altered.


SQL> select tablespace_name, file_name, bytes/1024/1024,
 maxbytes/1024/1024, autoextensible,
 increment_by from dba_data_files
 where tablespace_name='TEST';

TABLESPACE_NAME
------------------------------
FILE_NAME
-------------------------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT INCREMENT_BY
--------------- ------------------ --- ------------
TEST
/u01/app/oracle/oradata/orcl/test01.dbf
             50         32767.9844 YES        12800

TEST
/u01/app/oracle/oradata/orcl/test02.dbf
             50         32767.9844 YES        12800



Bring a Datafile Online or Offline in Archive Log Mode.

alter database datafile '/u01/app/oracle/oradata/orcl/test02.dbf' offline;

alter database datafile '/u01/app/oracle/oradata/orcl/test02.dbf' online;

Drop a datafile


SQL> alter tablespace test drop datafile 
'/u01/app/oracle/oradata/orcl/test02.dbf';

Tablespace altered.


SQL> select tablespace_name, file_name, bytes/1024/1024,
 maxbytes/1024/1024, autoextensible,
 increment_by from dba_data_files
 where tablespace_name='TEST';

TABLESPACE_NAME
------------------------------
FILE_NAME
----------------------------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT INCREMENT_BY
--------------- ------------------ --- ------------
TEST
/u01/app/oracle/oradata/orcl/test01.dbf
             50         32767.9844 YES        12800

Altering Tablespace Size.

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/
test01.dbf' resize 1g; 


Drop a tablespace


SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.


Note: In order to calculate the value of "increment by" in MegaBytes
you have to know your block size.

Example:

SQL> select block_size from dba_tablespaces
where tablespace_name='TEST';

BLOCK_SIZE
----------
      8192

So value for the increment by clause would be =

(increment_by*block_size)/(1024*1024)

In my case it is (12800*8192)/(1024*1024)=100M

Changing a Tablespace’s Write Mode.

To alter the tablespace to read only mode.

SQL> alter tablespace test read only; 


To alter the tablespace to read write mode.

SQL> alter tablespace test read write; 



Renaming a Tablespace and datafiles.

SQL> alter tablespace test rename to abc; 

Renaming a tablespace does not rename its datafiles.
So, to rename or relocate a datafile do the following.

Scenario 1: When the database is up.

SQL> alter tablespace abc offline; 

If you are renaming or relocating the datafile execute the following command.

$mv /u01/app/oracle/oradata/orcl/test01.dbf /u01/app/oracle/oradata/orcl/abc01.dbf

Update the control file with the ALTER TABLESPACE statement:

alter tablespace abc
rename datafile
'/u01/app/oracle/oradata/orcl/test01.dbf'
to
'/u01/app/oracle/oradata/orcl/abc01.dbf'
 
SQL> alter tablespace abc online;

Scenario 2: When the database is down.

SQL> shutdown immediate

$mv /u01/app/oracle/oradata/orcl/test01.dbf /u01/app/oracle/oradata/orcl/abc01.dbf

SQL>startup mount

SQL> alter database rename file '/u01/app/oracle/oradata/orcl/test01.dbf'
to 
'/u01/app/oracle/oradata/orcl/abc01.dbf';

SQL> alter database open;


Controlling the Generation of Redo.

In some situations you may not require generation of redo log
data. For example in data warehouse environment where you
perform direct path inserts or use SQL*LOADER to load data.
In these situations you can use NOLOGGING option to turn off
redo generation.

If you are creating a new tablespace you can use the option
as following:

SQL> create tablespace test datafile 
    '/u01/app/oracle/oradata/orcl/test01.dbf'
     size 50m autoextend on next 100M
     extent management local 
     segment space management auto
     nologging;

If the tablespace is already created you can use the
following mode to alter its logging status.

SQL>alter tablespace test nologging:

SQL> select tablespace_name, logging from dba_talespaces where 
tablespace_name='TEST';

TABLESPACE_NAME           LOGGING
----------------------    ---------

TEST                      NOLOGGING