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)

A schema audit before a migration, a quota review before approving more space, or a tablespace consolidation project all start with the same question: which tablespaces does a specific Oracle user actually touch, and how much room do they take up in each one? DBA_EXTENTS answers that question directly, without needing to know the schema's object list in advance or walking DBA_TABLES and DBA_INDEXES separately to build the same picture by hand.

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.

This kind of per-user rollup earns its keep outside a one-off audit too. Before dropping a schema, confirming every tablespace it touches means cleanup does not quietly leave orphaned extents behind in a tablespace nobody remembered the schema used. Before granting an additional quota, seeing where a user already holds space avoids duplicating an allocation the user does not actually need. Before a schema export and import into a new environment, the same query confirms which tablespaces the target database needs pre-created, since DBA_EXTENTS reports where a user's segments currently sit — not where a user is merely permitted to grow.

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.

DBA_EXTENTS vs DBA_SEGMENTS

DBA_EXTENTS reports space at the extent level — one row per extent, carrying EXTENT_ID, FILE_ID, and BLOCK_ID alongside the BYTES column this query sums. DBA_SEGMENTS reports one row per segment instead — a table, an index, a partition, or an LOB segment — with a single BYTES total already rolled up. For a per-tablespace summary like the one above, swapping the FROM clause to DBA_SEGMENTS and grouping on the same OWNER and TABLESPACE_NAME columns produces an equivalent total while scanning far fewer rows on a schema with a large number of extents per segment. DBA_EXTENTS is still the right source when the follow-up question needs extent-level detail — the actual EXTENT_ID or starting BLOCK_ID — rather than just a total, for example when investigating fragmentation across many small extents in an older dictionary-managed tablespace.

The LIKE Wildcard: What &user_id Actually Matches

WHERE owner LIKE '&user_id' behaves exactly like WHERE owner = '&user_id' unless the value substituted at the prompt contains a % or _ character, which LIKE treats as wildcards rather than literal text. Typing LARRY returns only the LARRY schema, identical to an equality check. Typing LARRY% returns every schema whose name starts with LARRY — convenient when a naming convention groups related application schemas under a shared prefix (LARRY_APP, LARRY_STG, LARRY_ARCH), but easy to trigger by accident if a real user name happens to contain a literal underscore, since _ matches any single character under LIKE unless escaped. Because OWNER values in the data dictionary are stored in uppercase by default, entering a lowercase or mixed-case value that does not match the stored case returns zero rows with no error at all — a frequent, silent point of confusion for anyone typing a schema name from memory.

Privileges Needed to Query DBA_EXTENTS

DBA_EXTENTS is one of the DBA_-prefixed data dictionary views, meaning it reports on every object in the database regardless of who owns it, unlike a USER_EXTENTS view scoped only to the connected user's own objects. Reading it requires either the DBA role, the narrower SELECT_CATALOG_ROLE role, or an explicit SELECT grant on the view itself. A session without one of those grants receives ORA-00942: table or view does not exist — Oracle's standard, deliberately non-specific response to a missing privilege on a dictionary object, which does not distinguish between "this object does not exist" and "you cannot see this object."

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.
• DBA_EXTENTS only shows currently allocated space. An object that has been dropped releases its extents back to the tablespace immediately, and it will not appear the next time this query runs.
• A user with objects spread across more than two or three tablespaces often signals either a loosely organized schema or an application that spans several functional areas — worth cross-checking against the application's data model.
• The BYTES total reflects allocated extent size, not the volume of data actually stored inside those extents. A table with many deleted rows that has never been shrunk can report a much larger MB total than its row count would suggest.
• Running this query against a schema before an export, and again after importing into a new environment, is a fast way to confirm the migration carried over every object and did not silently skip one.

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.
• For an environment with many small schemas, wrapping this query in a loop over DBA_USERS and printing a per-user, per-tablespace breakdown turns a single ad-hoc check into a standing capacity report worth keeping on file.
• Pairing this query's MB total against DBA_TS_QUOTAS shows not just how much space a user has consumed, but how much headroom remains against their assigned quota — the combination is what actually determines whether a user is at risk of ORA-01536: space quota exceeded.
• On a tablespace using Automatic Segment Space Management, the extent-level bytes this query totals are unaffected; ASSM changes how free space is tracked inside a block, not the size of the extents DBA_EXTENTS records.

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.

Insights and Best Practices

Turn a one-off check into a standing quota report

A single run of this query answers "where does this one user sit today." A scheduled version — looping over every row in DBA_USERS, or filtered to a known list of application schemas — turns the same logic into a weekly or monthly capacity report. Saving each run's output to a small tracking table with a timestamp column builds a trend line over time, which is what actually shows whether a schema's footprint is growing steadily, holding flat, or has jumped unexpectedly since the last check.

Compare the total against DBA_TS_QUOTAS before approving more space

The MB figure this query returns describes consumption, not headroom. DBA_TS_QUOTAS records the quota assigned to each user on each tablespace, alongside MAX_BYTES and BYTES already used. Reading the two side by side — this query's per-tablespace total against the quota row for the same user and tablespace — is what actually tells a DBA whether a user is close to ORA-01536: space quota exceeded, rather than just how much space they currently hold.

Watch for LOB segments and partitioned objects skewing the total

A schema with LOB columns (CLOB, BLOB) or partitioned tables can show a MB total dominated by a small number of very large segments rather than an even spread across many normal-sized objects. When a total looks disproportionately high for the apparent object count, joining DBA_EXTENTS to DBA_SEGMENTS and filtering on SEGMENT_TYPE (LOBSEGMENT, LOBINDEX, or a specific partition type) narrows down which segment is actually responsible before assuming the whole schema needs attention.

When to Run This Check

• Before dropping a schema, to confirm every tablespace it touches and avoid leaving space allocated under an assumption the schema was contained to just one tablespace.
• Before approving a quota increase, to see where a user already holds space rather than granting an allocation that duplicates existing headroom.
• Before and after a schema export/import between environments, to confirm the migration copied everything and the target tablespaces exist with enough free space.
• During a tablespace consolidation project, to identify which users' objects would need to move if a tablespace is retired.
• As part of a recurring capacity report, tracking per-user, per-tablespace growth over weeks or months rather than reacting to a single space-pressure alert.
• When investigating why a tablespace is filling faster than expected, to narrow the search down to the specific user or users responsible.

Troubleshooting Common Issues

The query returns ORA-00942: table or view does not exist. This is almost always a missing privilege, not a missing view. Confirm the connected session has the DBA role, SELECT_CATALOG_ROLE, or an explicit SELECT grant on DBA_EXTENTS rather than assuming the view itself is unavailable.

Zero rows returned for a user known to own objects. Check the case of the value substituted for &user_id. OWNER values in the data dictionary are stored in uppercase by default, so a lowercase or mixed-case entry that does not match exactly returns no rows and no error. Re-run with the schema name in uppercase before assuming the user has no objects.

The wildcard returns more schemas than expected. A value containing an unescaped % or _ is being matched as a wildcard, not literal text. If the target schema name genuinely contains one of those characters, escape it with ESCAPE clause syntax in the LIKE predicate, or switch to = for an exact match when no pattern matching is actually needed.

The MB total looks far larger than expected for the apparent object count. Check for LOB segments or partitioned objects skewing the total, as described above, before assuming the whole schema has grown unexpectedly. A single large LOB segment can account for the majority of an otherwise modest schema's footprint.

References

• DBA_EXTENTS — Oracle Database Reference 19c - Column reference for DBA_EXTENTS, including OWNER, TABLESPACE_NAME, and BYTES, used directly by the query above
• DBA_SEGMENTS — Oracle Database Reference 19c - Column reference for the segment-level alternative view, useful when extent-level detail is not needed for a per-tablespace total
• oracle-base.com - Tim Hall's Oracle reference site, a broad source for further tablespace, quota, and space management walkthroughs
• asktom.oracle.com - Tom Kyte's historical Q&A archive, useful for cross-referencing DBA_EXTENTS sizing questions and dictionary view privilege errors