Free Up Space Oracle Tablespace (Users)

Question

I'm newbie in Oracle Database. I want to ask related to Oracle Tablespace, especially for Users Tablespace. Every week, I always view and control the capacity of tablespace. However, tablespace user growth is very fast, so the size of the tablespace becomes large.

Tablespace information :

Tablespace Name                Total Size (MB) Used Space (MB) Free Space (MB)  Usage (%)
------------------------------ --------------- --------------- --------------- ----------
RBS                                       6000          311.38         5688.62       5.19
SYSAUX                                  113000        42662.75        70337.25      37.75
SYSTEM                                   29000         8568.44        20431.56      29.55
TEMP                                     34000              34           33966         .1
USERS                                   154000       103946.94        50053.06       67.5

How to clean up without losing business transaction data?

Answer 1

it is important to understand what is consuming the space in the USERS -- use this query to identify the top tables consuming the most space

SELECT owner, segment_name, segment_type, sum(bytes)/1024/1024 "Size (MB)" 
FROM dba_segments 
WHERE tablespace_name = 'USERS' 
GROUP BY owner, segment_name, segment_type 
ORDER BY 4 DESC;

• review the data in those tables to see if there is any unnecessary data that can be deleted or archived

• consider compressing the data in the tables

• consider moving some of the less frequently accessed data to a separate tablespace (using MOVE or by exporting the data + dropping the table + creating a new table in a different tablespace +and importing the data back in)

• add additional datafiles to the tablespace to increase its size(temporary solution)

PS. any cleanup activities should be thoroughly tested and validated to ensure that business transaction data is not lost -- perform regular backups of the database to ensure that data can be recovered in case any issues come up

Answer 2

While Lemonina's answer should help you manage the USERS tablespace, your SYSAUX tablespace is suspiciously large and worth investigating. Although SYSUAX is an "Oracle maintained" tablespace, you or a DBA do have a good amount of control over how much space is used there. Run this query to see which Oracle occupant is using the most space in SYSAUX:

select *
from v$sysaux_occupants
order by space_usage_kbytes desc;

For example, one common SYSAUX problem happens when the database is storing too much AWR (active workload repository) data. That data is useful for tracking performance over the past eight days. Some DBAs will want to track performance for a longer period of time, and will increase the default retention from eight days to a much larger number without realizing how much space that data will consume.

Each different SYSUAX "occupant" has a different way to change the retention period or flush old data. The preceding query will tell you which occupant to look up in the manual, or which procedure you can run to remove old data.