Table space usage

A tablespace is a disk area consisting of one or more disk files. A tablespace can accommodate many tables, indexes, or clusters.

Create a tablespace
Create tablespace ts_facial datafile 'C: \ oraclexe \ oradata \ df_facial.dbf' size 500 m
Uniform size 128 K; # specify the area size as 128 K. If not specified, the default area size is 64 K.

 
Delete a tablespace
Drop tablespace Cheng including contents and datafiles

Modify the tablespace size
Alter database datafile 'C: \ oraclexe \ oradata \ Xe \ Cheng. dbf' resize 50 m

Move tablespace (BLOB field not moved)
Alter table move tablespace room1;

Create an undo tablespace
Create undo tablespace undotbs02
Datafile '/Oracle/oradata/DB/undotbs02.dbf' size 50 m

Switch tablespace
Alter system set undo_tablespace = undotbs02;

Create temporary tablespace
Create temporary tablespace temp_data
Tempfile '/Oracle/oradata/DB/temp_data.dbf' size 50 m

Tablespace offline
Alter tablespace game offline;

If the data file is accidentally deleted, the recover option must be included.
Alter tablespace game offline for recover;

Bring tablespaces online
Alter tablespace game online;

Offline data files
Alter database datafile 3 offline;

Bring data files online
Alter database datafile 3 online;

Read-Only tablespace
Alter tablespace game read only;

Make tablespaces readable and writable
Alter tablespace game read write;

Delete a tablespace
Drop tablespace data01 including contents and datafiles;

View tablespace Information
Select a. tablespace_name,
A. bytes total,
B. bytes used,
C. bytes free,
(B. bytes * 100)/A. bytes "% used ",
(C. bytes * 100)/A. bytes "% free"
From SYS. sm $ ts_avail A, SYS. sm $ ts_used B, SYS. sm $ ts_free C
Where a. tablespace_name = B. tablespace_name
And a. tablespace_name = C. tablespace_name;

Select DF. tablespace_name "tablespace name ",
Totalspace "total space M ",
Freespace "available space M ",
Round (1-freespace/totalspace) * 100, 2) "Application Rate %"
From (select tablespace_name, round (sum (bytes)/1024/1024) totalspace
From dba_data_files
Group by tablespace_name) DF,
(Select tablespace_name, round (sum (bytes)/1024/1024) freespace
From dba_free_space
Group by tablespace_name) FS
Where DF. tablespace_name = FS. tablespace_name;