Day-9 : Tablespaces

Explain the relationship among database, tablespace and data file?

Database is logically divided into one or more tablespaces. Each tablespace is divided into one or more physical datafiles internally.

--------------------------------------------------------------------------------------------------------------------------

How do you add a data file to a tablespace?

ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>;

Example:

ALTER TABLESPACE TS1 ADD DATAFILE '/u01/dba/data/users02.dbf' SIZE 2G;

--------------------------------------------------------------------------------------------------------------------------

What is rollback segment?

A database contains one or more rollback segments to temporarily store "undo" information.

--------------------------------------------------------------------------------------------------------------------------

Name a tablespace automatically created when you create a database.

SYSTEM,SYSAUX tablespaces.

--------------------------------------------------------------------------------------------------------------------------

What are the uses of rollback segment?

1. Undo the changes when a transaction is rolled back.

2. Ensure read consistency (other transactions do not see uncommitted changes made to the database).

3. Recover the database to a consistent state in case of failures.

--------------------------------------------------------------------------------------------------------------------------

 What is a temporary segment?

Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment extents are released to the system for future use.

--------------------------------------------------------------------------------------------------------------------------

Can you add more than one datafile to big file Tablespace?

No. we can't have more than one datafile for Bigfile tablespace. if we try to add we will get below error.

SQL> alter tablespace BIG_TS add datafile '/u01/dba/devdb/data/reorg_2.dbf' size 10M;

alter tablespace BIG_TS add datafile '/u01/dba/devdb/data/reorg_2.dbf' size 10M

*

ERROR at line 1:

ORA-32771: cannot add file to bigfile tablespace

--------------------------------------------------------------------------------------------------------------------------

How do you resize a data file?

ALTER DATABASE DATAFILE '/u01/app/dba/devdb/users01.dbf'  RESIZE 2G;

(or)

ALTER DATABASE DATAFILE 5  RESIZE 2G; --> this is resizing the datafile using file id

--------------------------------------------------------------------------------------------------------------------------
What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
A temporary tablespace is used for temporary objects such as sort structures. 
A Permanent tablespaces are used to store those objects meant to be used as the true objects of the database.

--------------------------------------------------------------------------------------------------------------------------
An error appears about a table that can’t extend due to insufficient space in the tablespace. What would you do?
1. Rebuild the indexes
2. Add another datafile in other disk
3. Coalesce the tablespace

4. Re-organize the fragmented tables