TableSpace Management In Oracle

Tablespace management in Oracle involves various activities and considerations to ensure efficient storage, performance, and availability of database objects. Here are some key aspects of tablespace management in Oracle:

1. Creating a new tablespace

To create a new tablespace, use the CREATE TABLESPACE SQL command. Define the tablespace name, data file(s) location, size, and other attributes.

SQL> CREATE TABLESPACE my_tablespace DATAFILE '/path/to/my_tablespace.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE 500M;

2. Altering tablespace properties

Use the ALTER TABLESPACE SQL command to modify tablespace attributes. You can resize, add data files, set quotas, or change storage settings.

SQL> ALTER TABLESPACE my_tablespace ADD DATAFILE '/path/to/another_datafile.dbf' SIZE 200M;

3. Monitoring tablespace usage

Regularly check the space usage of tablespaces to avoid potential issues related to running out of space. Use views like DBA_TABLESPACES, DBA_DATA_FILES, and DBA_FREE_SPACE to monitor space consumption.

Below is the command to check all tablespaces actual sizes:

col program for a25
col username for a10
col TO_KILL for a15
col machine for a27
col program for a30
set lines 120
create table temp1 as (
select
fs.tablespace_name tablespace,
df.currentspace CurrentSize,
(df.currentspace - nvl(fs.freespace,0)) Used,
fs.freespace Free,
df.totalspace Total,
100-round(100 * ((fs.freespace+df.reservespace) / df.totalspace)) usedspace
from
(select
tablespace_name,
round(sum(decode(AUTOEXTENSIBLE,'YES',GREATEST(MAXBYTES,bytes),'NO',bytes)) / 1048576) TotalSpace,
round(sum(bytes)/ 1048576) currentspace,
round(sum(decode(AUTOEXTENSIBLE,'YES',greatest(maxbytes,bytes)-bytes,'NO',0))/ 1048576) reservespace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name
);
select Tablespace "TABLESPACE",CurrentSize "Size(MB)",Used "Used(MB)",Free "Free(MB)",Total "MAXBYTES(MB)",usedspace "Used %"  from temp1;

Run below command to drop table temp1 after you have the size of all tablespace as in last line you can see we are giving all tablespace size output in temp1 table.

SQL> Drop table temp1;

4. Tablespace autoextend

Enable autoextend on data files to allow the tablespace to automatically grow when it reaches its maximum size.

SQL> ALTER DATABASE DATAFILE '/path/to/my_tablespace.dbf' AUTOEXTEND ON NEXT 50M MAXSIZE 500M;

5. Tablespace relocation and resizing:

You can relocate a tablespace from one disk to another or resize it to allocate more or less space as needed.

##Example (Relocating):

SQL> ALTER TABLESPACE my_tablespace MOVE DATAFILE '/old_path/my_tablespace.dbf' TO '/new_path/my_tablespace.dbf'; 



##Example (Resizing):

SQL> ALTER DATABASE DATAFILE '/path/to/my_tablespace.dbf' RESIZE 200M;

6. Tablespace backups and recovery

Include tablespaces in your database backup strategy to ensure data recovery in case of data loss or corruption.

7. Performance optimization

Properly manage the placement of objects within tablespaces to optimize performance. Place frequently accessed tables and indexes on faster storage devices.

8. Tablespace maintenance

Perform regular maintenance tasks like defragmentation, reorganization, and statistics gathering to keep tablespaces healthy and maintain optimal performance.

9. Temporary tablespace management

Monitor and manage temporary tablespaces to ensure they have enough space to handle temporary operations like sorting and temporary tables.

10. Tablespace encryption (Advanced feature)

Oracle offers the ability to encrypt tablespace data for enhanced security. This feature helps protect sensitive data from unauthorized access.

Always exercise caution while performing operations on tablespaces, especially when resizing or dropping them, as they can have significant impacts on the database and its users. It’s essential to have a solid understanding of the database and a proper backup strategy in place to avoid data loss or service disruptions during tablespace management tasks.