How to expand tablespaces in Oracle

Step 1: view the Oracle tablespace usage:

1. Select dbf. tablespace_name,
2. Dbf. totalspace "Total (M )",
3. Dbf. Total number of totalblocks as blocks,
4. Dfs. freespace "total remaining amount (M )",
5. Dfs. freeblocks "remaining blocks ",
6. (Dfs. freespace/dbf. totalspace) * 100 "idle percentage"
7. From (select t. tablespace_name,
8. Sum (t. bytes)/1024/1024 totalspace,
9. Sum (t. blocks) totalblocks
10. From dba_data_files t
11. Group by t. tablespace_name) dbf,
12. (Select tt. tablespace_name,
13. Sum (tt. bytes)/1024/1024 freespace,
14. Sum (tt. blocks) freeblocks
15. From dba_free_space tt
16. Group by tt. tablespace_name) dfs
17. Where trim (dbf. tablespace_name) = trim (dfs. tablespace_name)

Step 2: Find the tablespace to be extended and view its data file path:

Select * from dba_data_files t where t. tablespace_name = 'tablespace name'

Step 3: Increase the tablespace size:

Two methods are available to increase the tablespace size:

1. Modify the data file size:

Alter database datafile 'full path data file name' resize *** M

2. Add a data file:

Alter tablespace name

Add datafile 'full path data file name' size *** M

That's all.

Note: 1. Try to keep the free percentage above 10% in the tablespace. If it is less than 10%, add datafile or resizedatafile. Generally, the data file should not exceed 2 GB.

2. Set automatic expansion of tablespace data files:

Alter database datafile 'full path data file name' autoextend on;