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
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:
Create tablespace DATA datafile '/u01/app/oracle/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:
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:
alter tablespace DATA add datafile '/u01/app/oracle/oradata/data02.dbf' size 2G;
Resize a datafile: ( To increase or decrease the datafile)
alter database datafile '/u01/app/oracle/oradata/data02.dbf' resize 3G;
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:
SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';
ALTER DATABASE DEFAULT TABLESPACE DATATBS;
Rename a tablespace:
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 /u01/app/oracle/oradata/cdb1/test1.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 /u01/app/oracle/oradata/cdb1/test1.dbf PRODUCING
NOTE: SYSTEM and SYSAUX tablespace cannot be renamed.
Drop a tablespace:
-- 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:
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.
-- To make a tablespace offline
ALTER TABLESPACE DATATS OFFLINE;
-- To make it online
ALTER TABLESPACE DATATS ONLINE;
Create a temporary tablespace:
CREATE TEMPORARY TABLESPACE TEMP1 '/u01/app/oracle/tempfile/temp01.dbf' size 1G autoextend on next 200M;
Adding a tempfile:
alter tablespace TEMP1 add tempfile '/u01/app/oracle/tempfile/temp02.dbf' size 1G autoextend on next 200M;
Resize tempfile:
alter database tempfile '/u01/app/oracle/tempfile/temp02.dbf' resize 2G;
Drop tempfile:
It will drop and remove the tempfile from physical location.
ALTER DATABASE TEMPFILE '/u01/app/oracle/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
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
