Tablespace and Datafiles in Oracle Database : Back to Basics
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
Did you get a chance to download Free Interview Questions related to Database? If not, download it here http://k21academy.com/oracle-dba-12c-interview-question
