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.