What is a
tablespace and datafile:
Tablespace is the primary logic structure of
the oracle database. It consists of one or more physical datafiles. Datafiles
physical stores database data in storage.(DISKS) . So when we create a datafile
of 30G in database, it will eat 30G of space from defined storage. All the
table or index segment is created in tablespace only.
Types of
Tablespace:
In Oracle
Database, we have Default Tablespace as below.
1. SYSTEM TABLESPACE
2.
SYSAUX TABLESPACE
3.
UNDO TABLESPACE
4.
TEMPORARY TABLESPACE
SYSTEM TABLESPACE:
·
Each database contains a SYSTEM tablespace.(
It is created by default while creating the database itself) .
·
It contains the data dictionary tables of the
database.
·
All the view,procedures, functions, packages
and triggers are stored in SYSTEM tablespace
·
It is not recommended to user SYSTEM
tablespace for creating any other table or index.
SYSAUX TABLESPACE:
·
All the database metadata, that doesnt store
in SYSTEM tablespace, will be stored here.
·
Many database components datas are stored in
this tablespace
PERMANENT TABLESPACE:
·
When someone says tablespace, Means they are
mostly refering to PERMANENT TABLESPACE
·
This tablespace are used for storing the
actual schema tables or indexes . i.e it Stores user data.
·
It doesnt store any of the data dictionary
tables.
There are two types of PERMANENT TABLESPACE.
·
SMALL FILE – (DEFAULT) – This is the common
size tablespace, It can contain multiple datafiles with each datafile of size
31G;
·
BIG FILE – It can contain only one datafile
which can grow upto 128 TB.
UNDO TABLESPACE:
·
This tablespace stores Undo data:
·
We can’t create any table or index inside undo
tablespace.
·
Each instance in the database have one defined
UNDO tablespace. i.e for standalone database one assigned undo tablespace, and
for multi node rac system, each instance on the node will have one assigned
undo tablespace.
TEMPORARY TABLESPACE:
·
This tablespace stores the data temporarily
for sessions doing sorting and join operations.
·
Sort operations are also generated by SELECT *
FROM WHERE CLAUSE that join rows from within tables and between tables. The
segments generated during this process will be stored in temp tablespace.
·
Each database will have one default temporary
tablespace
Create
a NEW tablespace:
Command:
Create tablespace DATA datafile
'/u01/dbaclass/oradata/data01.dbf' size 5G autoextend on next 500M;
Here, 500M of extent will be
added to the datafile automatically, when the space is required.
For
creating a tablespace on ASM disk group:
– With OMF file system:
Command:
Create tablespace DATA datafile
'+DATAG' size 5G autoextend on next 500M;
--- Else we can give the
complete path also:
Create tablespace DATA datafile
'+DATAG/oradata/datafile/data01.dbf' size 5G autoextend on next 500M;
Adding
a datafile:
Command:
alter tablespace DATA add
datafile '/u01/dbaclass/oradata/data02.dbf' size 2G;
Resize
a datafile: ( To increase or decrease the datafile)
Command:
alter database datafile
'/u01/dbaclass/oradata/data02.dbf' resize 3G;
Create
a big tablespace;
Command:
CREATE BIGFILE TABLESPACE BIGTS
datafile '/u01/dbaclass/oradata/bigts01.dbf' size 100G autoextend on NEXT 1G;
Change
default tablespace:
Every database will have one default
tablespace. If any user is created without mentioning any tablespace_name, then
that schema objects will be created under default tablespace.
– Get the current default tablespace:
Command:
SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME =
'DEFAULT_PERMANENT_TABLESPACE';
ALTER DATABASE DEFAULT
TABLESPACE DATATBS;
Rename
a tablespace:
Command:
set pagesize 200
set lines 200
col file_name for a57
SQL❯ select
file_id,file_name,tablespace_name from dba_data_files where file_id=37;
FILE_ID FILE_NAME
TABLESPACE_NAME
----------
--------------------------------------------------------
------------------------------
37
/home/oracle/app/oracle/oradata/cdb1/testin1.dbf TESTING
--- Rename the tablespace_name
from TESTING to PRODUCING;
SQL❯ alter tablespace TESTING rename
to PRODUCING;
Tablespace altered.
SQL❯ select
file_id,file_name,tablespace_name from dba_data_files where file_id=37;
FILE_ID FILE_NAME
TABLESPACE_NAME
----------
--------------------------------------------------------
------------------------------
37
/home/oracle/app/oracle/oradata/cdb1/testin1.dbf PRODUCING
NOTE: SYSTEM and SYSAUX
tablespace cannot be renamed.
Drop
a tablespace:
Command:
-- Drop a tablespace without
removing the physical database files.
SQL❯ drop tablespace TESTING;
Tablespace dropped.
SQL❯ select file_name from
dba_data_files where tablespace_name='TESTING';
no rows selected
-- Drop tablespace including the physical
datafiles.
SQL❯ drop tablespace TESTING
including contents and datafiles;
Tablespace dropped.
Make
tablespace Read only:
Command:
alter tablespace DATA2 READ
ONLY;
alter tablespace DATA2 read
write;
Take
tablespace offline:
DBAs usually make the tablespace
offline, while restoring or recovering any underlying corrupt datafile.
Command:
-- To make a tablespace offline
ALTER TABLESPACE DATATS OFFLINE;
-- To make it online
ALTER TABLESPACE DATATS ONLINE;
Create
a temporary tablespace
Command:
CREATE TEMPORARY TABLESPACE
TEMP1 '/u01/dbaclass/tempfile/temp01.dbf' size 1G autoextend on next 200M;
Adding
a tempfile:
Command:
alter tablespace TEMP1 add
tempfile '/u01/dbaclass/tempfile/temp02.dbf' size 1G autoextend on next 200M;
Resize
tempfile:
Command:
alter database tempfile
'/u01/dbaclass/tempfile/temp02.dbf' resize 2G;
Drop
tempfile:
It will drop and remove the
tempfile from physical location.
Command:
ALTER DATABASE TEMPFILE
'/u01/dbaclass/tempfile/temp02.dbf' DROP INCLUDING DATAFILES;
Change
default temporary tablespace:
When we create a new user, we assign a default
temporary tablespace. If nothing is assigned, then it will use the default
temporary tablespace set a database level.
Get the current default temporary tablespace for the database
Command:
SQL❯ SELECT PROPERTY_VALUE FROM
DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP
-- Set New default temp
tablespace
ALTER DATABASE DEFAULT TEMPORARY
TABLESPACE TEMP2;
-- Check the new default temp
tablespace
SQL❯ SELECT PROPERTY_VALUE FROM
DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP2