Database Administration (MOSC)

MOSC Banner

unable to extend table

edited Jun 12, 2013 5:49AM in Database Administration (MOSC) 2 commentsAnswered
Recently we have had several occasions when the table next extent can not extend. (next_extent > free space left in the tablespace).

I wrote a query and want to run it frequently to keep an eye on the tablespace free space. However, this query takes a long time. I am not sure whether my query is ok.

select a.segment_name, a.segment_type from dba_segments a, dba_free_space b where a.tablespace_name=b.tablespace_name and a.next_extent > b.bytes;

Currently, I have a query that checks the percentage of the tablespace that are free and used. However, the percentage sometimes is not enough to be proactive in monitoring the space. Usually, I increase the tablespace size then free space falls below 20%. Sometimes some table's next extents could be larger than the 20% free space left. In those case, that table will be nailed.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center