Tablespace Management

By On · Add Comment

A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. A datafile belongs to exactly one tablespace.

Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table’s or index’ data is stored.

Using multiple tablespaces allows you more flexibility in performing database operations.

  • Separate user data from data dictionary data to reduce I/O contention.
  • Separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline.
  • Store different the datafiles of different tablespaces on different disk drives to reduce I/O contention.
  • Take individual tablespaces offline while others remain online, providing better overall availability.
  • Optimizing tablespace use by reserving a tablespace for a particular type of database use, such as high update activity, read-only activity, or temporary segment storage.
  • Backup individual tablespaces.

Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently.

list all tablespaces in the database

DESC dba_tablespaces;

list default tablespaces

SELECT tablespace_name FROM dba_tablespaces ;

create new tablespace to check default tablespaces

CREATE tablespace tbs1 datafile ‘/u01/home/oradata/tbs1.dbf’ SIZE 100m;

files in the tablespaces

DESC dba_data_files;

view created tablespace

SELECT tablespace_name,file_name FROM dba_data_files;

add datafile in the tablepace

ALTER tablespace tbs1 ADD datafile ‘/u01/home/oradata/tbs2.dbf’ SIZE 100m;

drop a particular tablespace

DROP tablespace tbs1;

create new tablespace with existing datafile

CREATE tablespace tbs2 datafile ‘/u01/home/oradata/tbs1.dbf’ reuse;

drop tablespace and the datafiles

DROP tablespace tbs2 including contents AND datafiles;

check size of tablespaces

SELECT tablespace_name,file_name,bytes/1024/1024 FROM dba_data_files;

create tablespace with autoextend on

CREATE tablespace tbs2 datafile ‘/u01/home/oradata/tbs2.dbf’ SIZE 100m autoextend ON;

check the max size

SELECT tablespace_name,file_name,bytes/1024/1024, maxbytes/1024/1024 FROM dba_data_files;

drop datafile in a tablespace without dropping tablespace

ALTER tablespace DROP datafile '/u01/home/oradata/tbs1.dbf';

list status of the tablespace

SELECT tablespace_name,STATUS FROM dba_tablespaces;

change tablespace status to read only

ALTER tablespace tbs2 READ ONLY;

change tablespace status to online from read only

ALTER tablespace tbs2 READ WRITE;

change tablespace status to offline

ALTER tablespace tbs2 offline;

change tablespace from offline to online

ALTER tablespace tbs2 online;

rename tablespace

ALTER tablespace tbs2 RENAME TO tbs3;

list tablespace names
select name from v$tablespace;
rename datafile in contolfile

ALTER DATABASE RENAME file '/u01/home/oradata/tbs.dbf' TO '/u01/home/oradata/tbs3.dbf';

move a table in users tablespace

ALTER TABLE emp move tablespace users;

check free space in tablespace

DESC sm$ts_free;

check free space in all tablespaces

SELECT tablespace_name,bytes/1024/1024 FROM sm$ts_free;

check used space in tablespace

DESC sm$ts_used;

check used space in tablespace

SELECT tablespace_name,bytes/1024/1024 FROM sm$ts_used;

default tablespace for users

SELECT username,default_tablespace FROM dba_users;

total size of datafiles in the tablespace

SELECT SUM (bytes/1024/1024) FROM dba_data_files;

extend size of tablespace if its not autoextend

ALTER tablespace tbs3 resize 200m;

extent details

SELECT tablespace_name,extent_management,segment_space_management FROM dba_tablespaces;

create tablespace with extent aka uniform

CREATE tablespace Panora datafile ‘/u01/home/abc/Panora.dbf’ SIZE 100m extent management LOCAL uniform SIZE 100k;

create temporary tablespace

CREATE TEMPORARY temp3 tempfile ‘/u01/home/abc/tem3.dbf’ SIZE 50m;

make table as the default tablespace

ALTER DATABASE DEFAULT TEMPORARY tablespace temp3;

list default temporary tablespace

SELECT * FROM database_properties;

list all all temporary tablespaces

SELECT tablespace_name,FROM dba_temp_files;

create temp tablespace and to add to temp group

CREATE TEMPORARY tablespace temp4 tempfile '/u01/home/xyz/temp4.dbf' SIZE 50m tablespace GROUP temp2;

add another temp tablespace in temp group

ALTER tablespace temp3 tablespace GROUP temp2;

add temp2 to group default

ALTER DATABASE DEFAULT TEMPORARY tablespace temp2 GROUP;

create undo tablespace

CREATE undo tablespace undotbs3 datafile '/u01/home/abc/undotbs3.dbf' SIZE 50m;
Tagged with → • •  
Share →
0 comments
  Livefyre
Newest | Oldest
Skip to toolbar