I need to add some indexes to a moderately large table and I'd like to know in advance if the tablespace has room to hold the index. (The extents are suppose to automatically increas in size, but the DBA's don't always have that set up correctly and I don't want to be the one that crashes the system.) What is the SQL I need to run to see how much space is available?
How to Check Oracle Extent Free Space Available
oracle
Related Topic
- Oracle: how to find out storage space used by a table
- How to do a consistency check of oracle database
- How to delete data and free up disk space in Oracle DBMS 9i
- Oracle Enterprise Manager showing Agent Unreachable, negative disk space
- Oracle EM detect a disk full, but the system has plenty of free space
Best Answer
But it isn't always that simple. While there may be, for example, 100M available and you may want to create an index that you estimate will be 80M, it may try to grab a new extent of 120M and fail.
Also, if you had a table of 50M and dropped it, your 100M available may be two chunks of 50M. Again, if the database tries to get a 100M extent, it can fail.
And if the tablespace has no free space but is set to autoextend (segment_space_management on dba_tablespaces) then it may show up as having insufficient space but would work. Or not, depending if the underlying file system has enough space.