TABLESPACE MANAGEMENT
A) Check
existing tablespaces in database
$ sqlplus '/as sysdba'
SQL> select
tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMPTS1
USERS
B) Creating new
tablespace in database
SQL> create tablespace
rshore datafile '/u03/oradata/rshore/rshore1.dbf' size 25m;
Tablespace created.
Note: From oracle 9i onwards
whenever we create tablespace that by default locally managed tablespace.
C) Check for
created tablespace in database
SQL> select
tablespace_name from dba_tablespaces where tablespace_name='RSHORE';
TABLESPACE_NAME
------------------------------
RSHORE
D) Create tablespace with autoallocate option
SQL> create tablespace findata datafile
'/u03/oradata/rshore/findata01.dbf' size 25m extent management local
autoallocate;
Tablespace created.
Note:
AUTOALLOCATE causes the tablespace to be system managed with a minimum
extent size of 64K.
E) Create tablespace with uniform
option
SQL> create tablespace hrdata datafile '/u03/oradata/rshore/hrdata01.dbf'
size 25m extent management local uniform;
Tablespace created.
Note: The alternative to
AUTOALLOCATE is UNIFORM. which specifies that the tablespace is managed with extents
of uniform size. You can specify that size in the SIZE clause of UNIFORM. If you omit SIZE, then the default size is 1M.
The following example creates a tablespace with uniform 128K extents
SQL> create tablespace crmdata datafile '/u03/oradata/rshore/crmdata.dbf'
size 25m extent management local uniform size 128k;
Tablespace created
F) Enabling Autoextend for a Tablespace
SQL> create tablespace mfgdata datafile
'/u03/oradata/rshore/mfgdata01.dbf' size 10m autoeextend on next 10m maxsize
100m extent management local;
Tablespace created. Note: This
statement creates a tablespace named mfgdata with one datafile. When more space is required, 10m will be
added up to a maximum size of 100 megabytes:
g) Specifying
Segment Space Management for a Tablespace
SQL> create tablespace prcdata datafile
'/u03/oradata/rshore/prcdata01.dbf' size 10m
extent management local segment space management auto;
Tablespace created. Note: The following example creates a tablespace with automatic
segment-space management.
H) Check all available tablespaces
in database
SQL> select tablespace_name from
dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
RSHORE
FINDATA
HRDATA
MFGDATA
I) Creating undo
tablespace
SQL> create undo tablespace undotbs2 datafile
'/u03/oradata/rshore/undotbs02_01.dbf' size 50m;
Tablespace
created.
J) Creating temporary tablespace
SQL> create
temporary tablespace temp2 tempfile '/u03/oradata/rshore/temp2_01.dbf' size
50m;
Tablespace
created.
K) Add datafile to tablespace(increase tablespace size)
SQL> alter
tablespace findata add datafile '/u03/oradata/rshore/findata02.dbf' size 25m;
Tablespace
altered.
L) Resize tablespace datafile(increase tablespace size)
SQL> alter
database datafile '/u03/oradata/rshore/findata02.dbf' resize 50m;
Database altered.
M) Check datafiles
in specific tablespace
SQL> alter file_name from dba_data_files where
tablespace_name=’FINDATA’;
FILE_NAME
--------------------------------------------------------------------------------
/u03/oradata/rshore/findata01.dbf
/u03/oradata/rshore/findata02.dbf
Note: The above
command will list all datafiles present in Tablespace.
M) Checking tempfile in temporary tablespaces
SQL> select
FILE_NAME from dba_temp_files where TABLESPACE_NAME='TEMPTS1';
FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/rshore/temp01.dbf
N) Keeping tablespace in read-only mode
SQL> alter
tablespace findata read only;
Tablespace
altered.
O) Check tablespace status
SQL> select
tablespace_name, status from dba_tablespaces where tablespace_name='FINDATA';
TABLESPACE_NAME STATUS
------------------------------ ---------
FINDATA READ ONLY
P) Keep tablespace back to
online state
SQL> alter
tablespace findata read write;
Tablespace
altered.
Q) Check tablespace status
SQL> select
tablespace_name, status from dba_tablespaces where tablespace_name='FINDATA';
TABLESPACE_NAME STATUS
------------------------------ ---------
FINDATA ONLINE
R) Keeping
tablespace offline mode
SQL> alter
tablespace FINDATA offline;
Tablespace
altered.
S) Check tablespace status
SQL> select
tablespace_name, status from dba_tablespaces where tablespace_name='FINDATA';
TABLESPACE_NAME STATUS
------------------------------ ---------
FINDATA OFFLINE
T) Keep tablespace in online mode
SQL> alter
tablespace FINDATA online;
Tablespace
altered.
U) Check tablespace status
SQL> select
tablespace_name, status from dba_tablespaces where tablespace_name='FINDATA';
TABLESPACE_NAME STATUS
------------------------------ ---------
FINDATA ONLINE
V) Dropping tablespace
SQL> drop
tablespace MFGDATA;
Tablespace
dropped.
Note: Above
command just drop tablespace from database but it does not remove
Corresponding
datafiles from operating system level.
W) Dropping tablespace with contents
SQL> drop
tablespace PRCDATA including contents and datafiles;
Tablespace
dropped.
X) Checking size of all the tablespaces
SQL> select
tablespace_name, sum(bytes)/(1024*1024) from dba_data_files group by
tablespace_name;
TABLESPACE_NAME SUM(BYTES)/(1024*1024)
------------------------------ ----------------------
SYSAUX 300
UNDOTBS1 200
HRDATA 25
USERS
100
SYSTEM 300
FINDATA 75
UNDOTBS2 50
RSHORE 25
Y) Checking size of specific tablespace
SQL> select
tablespace_name, sum(bytes)/(1024*1024) from dba_data_files group by
tablespace_name having tablespace_name='FINDATA';
TABLESPACE_NAME SUM(BYTES)/(1024*1024)
------------------------------ ----------------------
FINDATA 75
Z) Checking free space in all tablespaces
SQL> select
tablespace_name, sum(bytes)/(1024*1024) from dba_free_space group by
tablespace_name;
TABLESPACE_NAME SUM(BYTES)/(1024*1024)
------------------------------ ----------------------
SYSAUX 228.375
UNDOTBS1 83.0625
HRDATA 24
USERS 99
CRMDATA 24
SYSTEM 60.9375
FINDATA 73
UNDOTBS2 47.75
RSHORE 24
A) Check free space in specific tablespace
SQL> select
tablespace_name, sum(bytes)/(1024*1024) from dba_free_space group by
tablespace_name having tablespace_name='FINDATA';
TABLESPACE_NAME SUM(BYTES)/(1024*1024)
------------------------------ ----------------------
FINDATA 73
B) Checking temporary tablespace size
SQL> select
tablespace_name, sum(bytes)/1024/1024 mb
from dba_temp_files
group by
tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
TEMP2 50
TEMPTS1 100
