oracle database 19c - write sql script to calculate how much free space is left in the…
- User
oracle database 19c - write sql script to calculate how much free space is left in the array space, taking into account to which size the array space has autoextend rather than the current size
- Assistant
Here's a SQL script to calculate free space in tablespaces, accounting for autoextend settings:
-- Calculate tablespace free space including autoextend potential WITH tablespace_usage AS ( SELECT df.tablespace_name, -- Current allocated space (bytes) SUM(df.bytes) AS current_size_bytes, -- Maximum possible space considering autoextend SUM( CASE WHEN df.autoextensible = 'YES' THEN df.maxbytes ELSE df.bytes END ) AS max_possible_bytes, -- Current free space within allocated datafiles NVL(SUM(fs.bytes), 0) AS current_free_bytes FROM dba_data_files df LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name GROUP BY df.tablespace_name ), temp_usage AS ( SELECT tf.tablespace_name, SUM(tf.bytes) AS temp_current_bytes, SUM( CASE WHEN tf.autoextensible = 'YES' THEN tf.maxbytes ELSE tf.bytes END ) AS temp_max_bytes FROM dba_temp_files tf GROUP BY tf.tablespace_name ) SELECT ts.tablespace_name, ts.contents AS tablespace_type, -- Current sizes in MB ROUND(tu.current_size_bytes / 1024 / 1024, 2) AS current_size_mb, ROUND(tu.current_free_bytes / 1024 / 1024, 2) AS current_free_mb, -- Maximum possible sizes in MB ROUND(tu.max_possible_bytes / 1024 / 1024, 2) AS max_possible_mb, -- Total free space available (current free + potential autoextend) ROUND((tu.current_free_bytes + (tu.max_possible_bytes - tu.current_size_bytes)) / 1024 / 1024, 2) AS total_free_mb, -- Utilization percentages ROUND((tu.current_size_bytes - tu.current_free_bytes) / tu.current_size_bytes * 100, 2) AS current_used_pct, ROUND((tu.current_size_bytes - tu.current_free_bytes) / tu.max_possible_bytes * 100, 2) AS max_used_pct, -- Autoextend potential ROUND((tu.max_possible_bytes - tu.current_size_bytes) / 1024 / 1024, 2) AS autoextend_potential_mb, -- Status indicators CASE WHEN tu.max_possible_bytes > tu.current_size_bytes THEN 'AUTOEXTEND ON' ELSE 'FIXED SIZE' END AS autoextend_status FROM dba_tablespaces ts JOIN tablespace_usage tu ON ts.tablespace_name = tu.tablespace_name WHERE ts.contents != 'TEMPORARY' UNION ALL -- Handle temporary tablespaces separately SELECT ts.tablespace_name, ts.contents AS tablespace_type, ROUND(temp.temp_current_bytes / 1024 / 1024, 2) AS current_size_mb, NULL AS current_free_mb, -- Temp tablespaces don't have traditional free space ROUND(temp.temp_max_bytes / 1024 / 1024, 2) AS max_possible_mb, ROUND((temp.temp_max_bytes - temp.temp_current_bytes) / 1024 / 1024, 2) AS total_free_mb, NULL AS current_used_pct, NULL AS max_used_pct, ROUND((temp.temp_max_bytes - temp.temp_current_bytes) / 1024 / 1024, 2) AS autoextend_potential_mb, CASE WHEN temp.temp_max_bytes > temp.temp_current_bytes THEN 'AUTOEXTEND ON' ELSE 'FIXED SIZE' END AS autoextend_status FROM dba_tablespaces ts JOIN temp_usage temp ON ts.tablespace_name = temp.tablespace_name WHERE ts.contents = 'TEMPORARY' ORDER BY tablespace_name;Here's also a simplified version if you want to focus on specific tablespaces:
-- Simplified version for specific tablespace analysis SELECT df.tablespace_name, df.file_name, ROUND(df.bytes / 1024 / 1024, 2) AS current_mb, ROUND(df.maxbytes / 1024 / 1024, 2) AS max_mb, df.autoextensible, ROUND((df.maxbytes - df.bytes) / 1024 / 1024, 2) AS expansion_potential_mb, ROUND(NVL(fs.free_mb, 0), 2) AS current_free_mb, ROUND( NVL(fs.free_mb, 0) + CASE WHEN df.autoextensible = 'YES' THEN (df.maxbytes - df.bytes) / 1024 / 1024 ELSE 0 END, 2 ) AS total_available_mb FROM dba_data_files df LEFT JOIN ( SELECT tablespace_name, file_id, SUM(bytes) / 1024 / 1024 AS free_mb FROM dba_free_space GROUP BY tablespace_name, file_id ) fs ON df.file_id = fs.file_id WHERE df.tablespace_name = 'YOUR_TABLESPACE_NAME' -- Replace with specific tablespace ORDER BY df.file_name;Key points about this script:
Current Free Space: Space available within currently allocated datafiles that hasn't been used yet.
Autoextend Potential: Additional space that can be allocated if autoextend is enabled, calculated as (maxbytes - current bytes).
Total Available: Sum of current free space plus autoextend potential.
Important Considerations: The script handles both regular and temporary tablespaces differently since temporary tablespaces don't have traditional free space concepts. It also accounts for the fact that maxbytes might be set to a very large number (like 32TB) if unlimited autoextend is configured.
Replace
'YOUR_TABLESPACE_NAME'in the simplified version with your specific tablespace name, or remove the WHERE clause to see all tablespaces. - User
Show only those spaces using more than 90%.
- Assistant
Here's the modified SQL script to show only tablespaces using more than 90% of their maximum possible space (including autoextend):
-- Show tablespaces using more than 90% of maximum possible space WITH tablespace_usage AS ( SELECT df.tablespace_name, -- Current allocated space (bytes) SUM(df.bytes) AS current_size_bytes, -- Maximum possible space considering autoextend SUM( CASE WHEN df.autoextensible = 'YES' THEN df.maxbytes ELSE df.bytes END ) AS max_possible_bytes, -- Current free space within allocated datafiles NVL(SUM(fs.bytes), 0) AS current_free_bytes FROM dba_data_files df LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name GROUP BY df.tablespace_name ), temp_usage AS ( SELECT tf.tablespace_name, SUM(tf.bytes) AS temp_current_bytes, SUM( CASE WHEN tf.autoextensible = 'YES' THEN tf.maxbytes ELSE tf.bytes END ) AS temp_max_bytes, -- For temp tablespaces, calculate used space from v$temp_space_header SUM(tf.bytes) - NVL(( SELECT SUM(bytes_free) FROM v$temp_space_header tsh WHERE tsh.tablespace_name = tf.tablespace_name ), 0) AS temp_used_bytes FROM dba_temp_files tf GROUP BY tf.tablespace_name ) SELECT ts.tablespace_name, ts.contents AS tablespace_type, -- Current sizes in MB ROUND(tu.current_size_bytes / 1024 / 1024, 2) AS current_size_mb, ROUND(tu.current_free_bytes / 1024 / 1024, 2) AS current_free_mb, -- Maximum possible sizes in MB ROUND(tu.max_possible_bytes / 1024 / 1024, 2) AS max_possible_mb, -- Total free space available (current free + potential autoextend) ROUND((tu.current_free_bytes + (tu.max_possible_bytes - tu.current_size_bytes)) / 1024 / 1024, 2) AS total_free_mb, -- Utilization percentages ROUND((tu.current_size_bytes - tu.current_free_bytes) / tu.current_size_bytes * 100, 2) AS current_used_pct, ROUND((tu.current_size_bytes - tu.current_free_bytes) / tu.max_possible_bytes * 100, 2) AS max_used_pct, -- Autoextend potential ROUND((tu.max_possible_bytes - tu.current_size_bytes) / 1024 / 1024, 2) AS autoextend_potential_mb, -- Status indicators CASE WHEN tu.max_possible_bytes > tu.current_size_bytes THEN 'AUTOEXTEND ON' ELSE 'FIXED SIZE' END AS autoextend_status FROM dba_tablespaces ts JOIN tablespace_usage tu ON ts.tablespace_name = tu.tablespace_name WHERE ts.contents != 'TEMPORARY' -- Filter for tablespaces using more than 90% of maximum possible space AND ROUND((tu.current_size_bytes - tu.current_free_bytes) / tu.max_possible_bytes * 100, 2) > 90 UNION ALL -- Handle temporary tablespaces separately SELECT ts.tablespace_name, ts.contents AS tablespace_type, ROUND(temp.temp_current_bytes / 1024 / 1024, 2) AS current_size_mb, NULL AS current_free_mb, ROUND(temp.temp_max_bytes / 1024 / 1024, 2) AS max_possible_mb, ROUND((temp.temp_max_bytes - temp.temp_current_bytes) / 1024 / 1024, 2) AS total_free_mb, NULL AS current_used_pct, ROUND(temp.temp_used_bytes / temp.temp_max_bytes * 100, 2) AS max_used_pct, ROUND((temp.temp_max_bytes - temp.temp_current_bytes) / 1024 / 1024, 2) AS autoextend_potential_mb, CASE WHEN temp.temp_max_bytes > temp.temp_current_bytes THEN 'AUTOEXTEND ON' ELSE 'FIXED SIZE' END AS autoextend_status FROM dba_tablespaces ts JOIN temp_usage temp ON ts.tablespace_name = temp.tablespace_name WHERE ts.contents = 'TEMPORARY' -- Filter for temp tablespaces using more than 90% of maximum possible space AND ROUND(temp.temp_used_bytes / temp.temp_max_bytes * 100, 2) > 90 ORDER BY max_used_pct DESC;If you want to see tablespaces using more than 90% of their current allocated space instead (which might be more immediately actionable), use this simpler version:
-- Show tablespaces using more than 90% of current allocated space SELECT df.tablespace_name, ROUND(SUM(df.bytes) / 1024 / 1024, 2) AS allocated_mb, ROUND(NVL(SUM(fs.bytes), 0) / 1024 / 1024, 2) AS free_mb, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / 1024 / 1024, 2) AS used_mb, ROUND(((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes)) * 100, 2) AS used_pct, ROUND( SUM(CASE WHEN df.autoextensible = 'YES' THEN df.maxbytes ELSE df.bytes END) / 1024 / 1024, 2 ) AS max_possible_mb, CASE WHEN MAX(df.autoextensible) = 'YES' THEN 'AUTOEXTEND ON' ELSE 'FIXED SIZE' END AS autoextend_status FROM dba_data_files df LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name GROUP BY df.tablespace_name HAVING ROUND(((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes)) * 100, 2) > 90 ORDER BY used_pct DESC;The key difference is:
- First query: Shows tablespaces using >90% of their maximum possible space (including autoextend potential)
- Second query: Shows tablespaces using >90% of their currently allocated space
The first approach is better for long-term capacity planning, while the second is better for immediate space pressure identification.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.