Tablespaces and Datafiles Management

 TABLESPACES and DATAFILES MANAGEMENT:
 
   Tablespaces are the bridge between certain physical and logical components of the Oracle database. Tablespaces are where you store Oracle database objects such as tables, indexes and rollback segments.  You can think of a tablespace like a shared disk drive in Windows. You can store files on your shared drive, move files around and remove files. The same is true with tablespaces. A tablespace is made up of one or more database datafiles.     

Creating tablespace:
=====================
create tablespace APPSBI datafile '/u01/app/oracle/oradata/cts/appsbi01.dbf' size 200m;

Adding Datafile:
================
alter tablespace APPSBI add datafile '/u01/app/oracle/oradata/cts/appsbi02.dbf' size 200m;

Resizing Datafile:
==================
alter database datafile '/u01/app/oracle/oradata/cts/appsbi02.dbf' resize 300m;

To create UNDO tablepsace
=========================
create undo tablespace APP_UNDO datafile '/u01/app/oracle/oradata/cts/appundo01.dbf' size 200m;

To add UNDO datafile:
=====================
alter tablespace APP_UNDO add datafile '/u01/app/oracle/oradata/cts/appundo02.dbf' size 100m;

To Resize UNDO datafile:
========================
alter database datafile '/u01/app/oracle/oradata/cts/appundo02.dbf' resize 150m;

To Create TEMPORARY TABLESPACE:
===============================
create temporary tablespace app_temp tempfile '/u01/app/oracle/oradata/cts/apptemp01.dbf' size 100m;

select file_name,bytes/1024/1024 from dba_temp_files where tablespace_name='APP_TEMP';

To ADD Tempfile:
================
alter tablespace app_temp add tempfile '/u01/app/oracle/oradata/cts/apptemp02.dbf' size 200m;

To Resize Tempfile:
===================

alter database tempfile '/u01/app/oracle/oradata/cts/apptemp01.dbf' resize 150m;


To Check Free space of all the tablespaces:
================================

 select TABLESPACE_NAME,sum(bytes/1024/1024) from DBA_FREE_SPACE group by tablespace_name;

To check allocated Space of all the tablespaces:
====================================

 select TABLESPACE_NAME,sum(bytes/1024/1024) from DBA_DATA_FILES group by tablespace_name;

To Check used space of all the tablespaces:
================================
 select TABLESPACE_NAME,sum(bytes/1024/1024) from DBA_SEGMENTS group by tablespace_name;

To check all the datafiles with allocated size in particular tablespace:
==================================================
select file_name,bytes/1024/1024 from dba_data_files where tablespace_name='APPSBI';

Tablespace related operations:
=============================
offline/online
datafile add/resize
read/write mode
create/drop tablespace
logging/nologging

Related views:
=============
dba_tablespaces
dba_data_files
dba_temp_files
dba_temp_free_space
v$tempfile
v$datafile

Note:

1. We can create a table with particular tablespace spacification

create table test(eno number) tablespace appsbi;

2. We can allocate the datafile to  particular table

alter table test allocate extent (size 10k datafile '/u01/app/oracle/oradata/algates/appsbi02.dbf');