Image taken from Oracle Documentation

Tablespace : is logical storage unit in Oracle Database.
ii) Tablespace connsit of one or more datafiles (check below)
iii) Information about Tablespace can be obtained from view DBA_TABLESPACES, USER_TABLESPACES, DBA_TEMP_FILES (for Temporary Tablespaces)
iv) Tablespace is further divided in to logical units Segments (Segment is divided in to Extent and Extent in to Block) . To know more about Segment, Extents and Blocks click here
v) Various type of tablespace are BIGFILE, SYSTEM, SYSAUX, UNDO

Datafiles : is physical structure to store oracle data
ii) One or more physical datafile are logically grouped together to make a tablespace
iii) Information about Datafile can be obtained from view DBA_DATA_FILES
iv) A Datafile can be associated with only one tablespace

Adding Tablespace in database
Use CREATE TABLESPACE command to add tablespace in Database like
CREATE TABLESPACE <tablespace_name> DATAFILE <location_of_datafile>
CREATE TABLESPACE my_tablespace DATAFILE ’/u01/oracle/oradata/data01.dbf’;

To create undo tablespace
CREATE UNDOTABLESPACE <tablespace_name> DATAFILE <location_of_datafile> SIZE <size> [AUTOEXTEND ON|OFF] [RETENTION GURANTEE|NOGURANTEE]

To create Temporary tablespace
CREATE TEMPORARYTABLESPACE <tablespace_name> TEMPFILE <location_of_datafile> SIZE <size> [AUTOEXTEND ON|OFF] 

Adding Datafile in a Tablespace
Use ALTER TABLESPACE to add datafile in tablespace like
ALTER TABLESPACE <tablespace_name> ADD DATAFILE <location_of_datafile> 
ALTER TABLESPACE my_tablespace ADD DATAFILE ’/u01/oracle/oradata/data02.dbf’;

To add temporary file in TEMP table space
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE ‘<location_of_tempfile>’ SIZE <size>;

Modify Datafile
You can modify datafile using ALTER DATABASE command like
ALTER DATABASE DATAFILE <location_of_datafile> AUTOEXTEND ON|OFF NEXT <size> MAXSIZE <size>;

ALTER DATABASE DATAFILE ‘/u01/oracle/oradata/data02.dbf’ AUTOEXTEND ON NEXT 30M MAXSIZE 1200M;

which means datafile data02.dbf can automatically grow upto 1200 MB size in blocks of 30 MB each time as required.

Related
http://www.psoug.org/reference/tablespaces.html

Popularity: 38% [?]

Share This