Data Dictionary Queries - Exercise

You have taken over administration of an Oracle AI Database 26ai environment. Before reviewing its storage controls, you need to answer two questions for the database container to which you are connected:

1. Which users have an unlimited quota on a specific, active tablespace?
2. Which users have been granted the UNLIMITED TABLESPACE system privilege?

These are separate forms of access. An unlimited quota applies to one tablespace, while the system privilege permits unlimited allocation in every tablespace.

1. Write a query against DBA_TS_QUOTAS that lists the user and tablespace for each unlimited quota. Exclude records for dropped tablespaces and sort the results by user and tablespace.
2. Write a query against DBA_SYS_PRIVS that lists each grantee of the UNLIMITED TABLESPACE system privilege. Include whether the privilege was granted with the ADMIN OPTION and sort the results by grantee.

Run the queries while connected to the container you intend to audit, using an account authorized to query these DBA_ views.

• In DBA_TS_QUOTAS, MAX_BYTES = -1 means that the quota has no limit.
• The DROPPED column indicates whether the associated tablespace has been dropped.
• UNLIMITED TABLESPACE is a system privilege, so its grants are recorded in DBA_SYS_PRIVS.