Tablespace
An Oracle database
consists of one or more logical objects (or) storage units called tablespaces, which collectively store all of the database's data. Each tablespace in an Oracle database
consists of upto 1024 files called datafiles, which are physical structures
that conform to the operating system in which Oracle is running.
If we specify a Tablespace to a user, the
user’s data will be stored in the datafiles which are under the assigned
tablespace only. If we didn’t specify any tablespace to a user, data will be
stored under the default tablespace.
Views for tablespaces:-
desc v$tablespace
desc dba_tablespaces
desc dba_data_files
desc dba_temp_files (for temp tablespace)
Creating Tablespace:
SQL> create
tablespace xxtrainee datafile '/u03/oravis/VIS/data/xxtrainee01.dbf' size 200m;
Tablespace created.
SQL> alter
tablespace xxtrainee add datafile '/u03/oravis/VIS/data/xxtrainee02.dbf' size
200m;
Tablespace altered.
SQL> select file_name,bytes
from dba_data_files where tablespace_name='XXTRAINEE';
FILE_NAME
--------------------------------------------------------------------------------
BYTES
----------
/u03/oravis/VIS/data/xxtrainee01.dbf
209715200
/u03/oravis/VIS/data/xxtrainee02.dbf
209715200
We can create several
data files under one tablespace while creating tablespace only
SQL> create
tablespace trainee datafile '/u03/oravis/VIS/data/trainee01.dbf'size 50m,
'/u03/oravis/VIS/data/trainee02.dbf'size 30m;
Tablespace created.
SQL> select
file_name,bytes from dba_data_files where tablespace_name='TRAINEE';
FILE_NAME
--------------------------------------------------------------------------------
BYTES
----------
/u03/oravis/VIS/data/trainee01.dbf
52428800
/u03/oravis/VIS/data/trainee02.dbf
31457280
SQL> exit
[oravis@test data]$ ls
control01.ctl sysaux01.dbf undo01.dbf
redo01.log temp01.dbf users01.dbf
redo02.log trainee01.dbf xxtrainee01.dbf
system01.dbf trainee02.dbf xxtrainee02.dbf
[oravis@test data]$ pwd
/u03/oravis/VIS/data
Renaming:
SQL> alter
tablespace trainee rename to associates;
Tablespace altered.
SQL> select
file_name from dba_data_files where tablespace_name='ASSOCIATES';
FILE_NAME
--------------------------------------------------------------------------------
/u03/oravis/VIS/data/trainee01.dbf
/u03/oravis/VIS/data/trainee02.dbf
(We can’t rename
system and sysaux tablespaces)
Changing Status:
Read Only:
Making a tablespace read-only prevents write
operations on the datafiles in the tablespace. The primary purpose of read-only
tablespaces is to eliminate the need to perform backup and recovery of large,
static portions of a database. Read-only tablespaces also provide a way to
protecting historical data so that users cannot modify it. Making a tablespace
read-only prevents updates on all tables in the tablespace, regardless of a
user's update privilege level.
SQL> select
tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------
---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
TABLESPACE_NAME STATUS
------------------------------
---------
UNDO ONLINE
TABLESPACE_NAME STATUS
------------------------------
---------
XXTRAINEE ONLINE
ASSOCIATES ONLINE
6 rows selected.
SQL> alter
tablespace xxtrainee read only;
Tablespace altered.
SQL> select
tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------
---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
TABLESPACE_NAME STATUS
------------------------------
---------
UNDO ONLINE
TABLESPACE_NAME STATUS
------------------------------
---------
XXTRAINEE READ ONLY
ASSOCIATES ONLINE
Read Write:
To get the status back
to the Online
SQL> alter
tablespace xxtrainee read write;
Tablespace altered.
SQL> select
tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------
---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
TABLESPACE_NAME STATUS
------------------------------
---------
UNDO ONLINE
TABLESPACE_NAME STATUS
------------------------------
---------
XXTRAINEE ONLINE
ASSOCIATES ONLINE
6 rows selected.
Offline/Online:
SQL> alter
tablespace xxtrainee offline;
Tablespace altered.
SQL> select
tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------
---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
TABLESPACE_NAME STATUS
------------------------------
---------
UNDO ONLINE
TABLESPACE_NAME STATUS
------------------------------
---------
XXTRAINEE OFFLINE
ASSOCIATES ONLINE
SQL> alter
tablespace xxtrainee online;
Tablespace altered.
SQL> select tablespace_name,status
from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------
---------
XXTRAINEE ONLINE
SQL> select
file_name,bytes from dba_data_files where tablespace_name='XXTRAINEE';
FILE_NAME
--------------------------------------------------------------------------------
BYTES
----------
/u03/oravis/VIS/data/xxtrainee01.dbf
209715200
/u03/oravis/VIS/data/xxtrainee02.dbf
209715200
SQL> select
sum(bytes) from dba_data_files where tablespace_name='XXTRAINEE';
SUM(BYTES)
----------
419430400
Drop Tablespace:
SQL> drop
tablespace xxtrianee;
Tablespace dropped.
(Or)
SQL> drop
tablespace xxtrianee including contents;
(Or)
SQL> drop
tablespace xxtrianee including contents and datafiles;
No comments:
Post a Comment