A developer reports that an operation cannot allocate additional space in the USERS tablespace. Your initial investigation shows
that the tablespace is nearly full and that the schema JEFF owns several large segments there.
You expected JEFF to have a restricted quota. Determine whether JEFF currently holds the
UNLIMITED TABLESPACE system privilege, has a large or unlimited quota on USERS, and which segments account for the
allocated space.
Run the investigation in the pluggable database that contains JEFF. Use an administrative account authorized to query the required
data dictionary views. Assume that JEFF is an ordinary, nonquoted username and is therefore stored in uppercase.
Write SQL queries and a short explanation that address the following questions:
- Does
DBA_SYS_PRIVS show a direct grant of UNLIMITED TABLESPACE to JEFF?
- What do
BYTES and MAX_BYTES in DBA_TS_QUOTAS show for JEFF in USERS?
- Which of
JEFF's segments use the most allocated space in USERS?
- Optionally, how full is the tablespace when its current size, autoextend limits, and available underlying storage are considered?
Explain how you would interpret a returned privilege row, a quota value of -1, and the absence of a quota row.
Enter your SQL queries and explanation below. When you are finished, select Submit to compare your response
with the suggested solution.