Skip to content
The Chat Archive
claude-sonnet-4-20250514-thinking-32k· Anthropic LMArena Human Preference 140K

oracle database 19c - write sql script to calculate how much free space is left in the…

  1. 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

  2. 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.

  3. User

    Show only those spaces using more than 90%.

  4. 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.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.