I encountered a problem with decreasing freespace of a tablespace. The freespace has decreased and keep decreasing. But I don't know how to find the cause of this trouble. Do you know how to find the cause?
I would identify such problems with the following Steps:
1) Find out which tables are the biggest one in your tablespace. Typically these tables are causing the problem you are searching for. You can find these tables with the following Statement:
select owner,
segment_name,
segment_type,
tablespace_name,
sum(bytes)/1024/1024 "SIZE (MB)"
from dba_segments
where tablespace_name = "Your Tablespace"
group by owner, segment_name, segment_type, tablespace_name
order by 5 desc;
2) Now you have information about your biggest tables. You can check now how fast they where growing in the last time. For example, you can use the Optimizer History for checking the Table-Growth with the following statement:
select u.name, o.name, savtime, h.rowcnt
from sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
where h.obj# = o.obj#
and o.owner# = u.user#
and u.name = "Your Table/Index Owner"
and o.name = "Your Table/Index Name"
order by savtime;
Now its your turn to check if it is normal that these table grow that much. If you can't find your Problems with these Steps, it is also possible to have other Problems in your Tablespace. Check the Oracle Documentation for Table Fragemtation. Maybe this Link can also help you: https://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm