datafile size increasing !interesting! 1

[viztek]

Hello,

I find an abnormal increase in my production datafile size. There was no such task done on the database which could have increased the size of the file in such a manner.The rollback,temp,index etc are separate. I wonder what causes such an increase . my production datafile increased from 3.5 GB to 6.5 Gb. Could anybody guide me. It is very urgent.

Thanks,
Viztek
INDIA

 

[carp]

Without more information, it's rather hard to say.
It could be that somebody's account is set up so that they're building temporary segments in the wrong tablespace. In that case, a large query could conceivably expand your datafile and then disappear without a trace.
Or somebody could have just resized your file.
Or any of a dozen other possibilities.

 

[sem]

If you're using pctincrease for one or more segments and its size is already big enough you may get a huge increase any time it's increased.

 

[carp]

Especially in conjunction with a high value for PCTFREE. In that case, you could get quite a few extents tacked on with relatively few rows being inserted!

 

[karluk]

You should verify how much space in the tablespace is actually allocated to database objects

select sum(bytes) from dba_segments
where tablespace_name = 'YOUR_TABLESPACE';

If you find large amounts of space allocated, the most likely cause is a recent increase in the size of your largest tables. Try running the following query to check if the sizes seem reasonable.

select segment_name, bytes from dba_segments
where tablespace_name = 'YOUR_TABLESPACE'
and bytes > 10000000
order by bytes;

To check Carp's suggestion about the possibility that some id has the tablespace assigned to temporary segments, run the following:

select username from dba_users
where temporary_tablespace = 'YOUR_TABLESPACE';

If you find any users with an incorrect temporary ts, you can modify them with the command

alter user user_id temporary tablespace temp_ts_name;

Good luck! I hope you find the problem.