Identifying User Tablespaces and Space Usage in Oracle

Query to Display All Tablespaces Used by A Specified User and The Total Space Occupied (rounded to MB)


Sample SQL Command

1select	tablespace_name
2,	ceil(sum(bytes) / 1024 / 1024) "MB"
3from	dba_extents
4where	owner like '&user_id'
5group by tablespace_name
6order by tablespace_name
7/

Sample Oracle Output:

1TABLESPACE_NAME                                  MB
2---------------------------------------- ----------
3LARRY_DATA                                     9
4LARRY_INDEX                                    15
5
62 rows selected.

Purpose:

  • To identify and report the tablespaces where a user owns objects and the approximate amount of storage they consume.

Breakdown:

  1. select tablespace_name, ceil(sum(bytes) / 1024 / 1024) "MB": This clause selects two columns:
    • tablespace_name: Name of the tablespace where the user has objects.
    • ceil(sum(bytes) / 1024 / 1024) "MB": Calculated total space used in MB.
      • ceil( ... ): Rounds the calculated value up to the nearest whole number.
      • sum(bytes): Calculates the total number of bytes used by the user across all extents in the tablespace.
      • / 1024 / 1024: Converts bytes to megabytes (MB).
      • "MB": Renames the column for clarity.
  2. from dba_extents: This specifies the data source as the dba_extents system view. It holds information about allocated space within tablespaces.
  3. where owner like '&user_id': This clause filters for extents owned by the specified user.
    • owner: The schema (user) who owns the extent.
    • like '&user_id': Uses a wildcard to accept any user ID passed as the &user_id variable.
  4. group by tablespace_name: This clause groups the results by tablespace name, effectively summarizing space usage per tablespace.
  5. order by tablespace_name: This clause sorts the output alphabetically by tablespace name.

Key Points:

  • This code relies on the dba_extents system view, which requires appropriate privileges to access (typically DBA or SYSDBA).
  • The like clause with a wildcard allows for finding tablespaces used by any user matching the provided part. Ensure proper validation and sanitization of the &user_id variable.
  • The calculation assumes all extents belong to tables. Other object types might contribute, depending on the user's activity.
  • You can modify the calculation in the select clause to display different units or specific space metrics by using other columns from dba_extents.

Insights and Explanations:

  • Using ceil provides a simplified, human-readable representation of space usage. Consider round for different rounding behavior.
  • This code offers a basic overview. You can extend it to include additional information like the total number of extents or average extent size per tablespace.
  • While the code helps identify space consumption, further analysis might be needed to optimize tablespace usage or address potential storage concerns.

This code effectively reveals tablespaces used by a user and their approximate space usage. Remember to use it responsibly, control variable values, and potentially adapt it for more specific needs and insights.

Posts in this series