More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Oracle DBA Checklist - Daily Procedures - spacebound.sql
-- spacebound.sql
--
-- To identify space-bound objects. If all is well, no rows are returned.
-- If any space-bound objects are found, look at value of NEXT extent
-- size to figure out what happened.
-- Then use coalesce (alter tablespace coalesce;).
-- Lastly, add another datafile to the tablespace if needed.
--
SELECT a.table_name, a.next_extent, a.tablespace_name
FROM all_tables a,
( SELECT tablespace_name, max(bytes) as big_chunk
FROM dba_free_space
GROUP BY tablespace_name ) f
WHERE f.tablespace_name = a.tablespace_name
AND a.next_extent > f.big_chunk
(Continued on next question...)
