Month: March 2025

Managing object-level privileges in Oracle has always been granular — sometimes too granular. If you wanted a user to have SELECT on every table in a schema, you had to grant it table by table. Add a new table later? Grant again. Automate it? Write a script.

Oracle 23ai introduces schema-level privileges — the ability to grant a privilege on an entire schema with a single statement that automatically applies to all current and future objects in that schema.

Granting schema-level SELECT:

GRANT SELECT ANY TABLE ON SCHEMA hr TO reporting_user;

reporting_user can now SELECT from any table (current or future) in the hr schema. No more individual grants per table.

Other supported schema-level privileges:

GRANT INSERT ANY TABLE   ON SCHEMA app_data TO etl_user;
GRANT UPDATE ANY TABLE   ON SCHEMA app_data TO etl_user;
GRANT DELETE ANY TABLE   ON SCHEMA app_data TO etl_user;
GRANT EXECUTE ANY PROCEDURE ON SCHEMA hr   TO app_user;
GRANT SELECT ANY SEQUENCE   ON SCHEMA hr   TO app_user;

Revoking schema-level privileges:

REVOKE SELECT ANY TABLE ON SCHEMA hr FROM reporting_user;

Key behavior: Schema privileges are dynamic. When a new table is created in hr, reporting_user immediately has SELECT on it — no additional grant required.

Checking existing schema-level grants:

SELECT grantee, privilege, schema
FROM   dba_schema_privs
ORDER BY schema, grantee;

Real-world impact

This dramatically simplifies privilege management for read-only reporting users, ETL processes, and microservice accounts that need access to a full schema rather than individual tables. It’s a long-requested feature that reduces the DBA overhead of schema management and eliminates entire categories of “who forgot to grant this?” support tickets.

Oracle 23ai adds a meaningful set of new aggregate functions to SQL. These fill gaps in analytical and statistical capabilities that previously required PL/SQL, external tools, or verbose workarounds.

CHECKSUM

Returns a deterministic checksum value for a set of rows. Extremely useful for data validation and change detection.

SELECT CHECKSUM(*) AS row_fingerprint
FROM   employees
WHERE  department_id = 10;

If any row in the result set changes, the checksum changes — making it ideal for comparing snapshots.

BIT_AND_AGG / BIT_OR_AGG / BIT_XOR_AGG

Bitwise aggregate functions — apply bitwise AND, OR, or XOR across all values in a group.

-- Which permissions are held by ALL members of a group?
SELECT role_id, BIT_AND_AGG(permission_flags) AS common_permissions
FROM   role_members
GROUP BY role_id;

-- Which permissions are held by AT LEAST ONE member?
SELECT role_id, BIT_OR_AGG(permission_flags) AS any_permission
FROM   role_members
GROUP BY role_id;

SKEWNESS_POP / SKEWNESS_SAMP

Measures statistical skewness of a distribution — how asymmetric the data is around the mean.

SELECT SKEWNESS_POP(salary) AS salary_skew
FROM   employees;

A positive value means the distribution tails right (a few very high salaries); negative means it tails left.

KURTOSIS_POP / KURTOSIS_SAMP

Measures the “tailedness” of a distribution — whether outliers are common or rare.

SELECT KURTOSIS_SAMP(response_time_ms) AS response_time_kurtosis
FROM   api_logs;

Why these matter

Before 23ai, these calculations required either loading data into R/Python, implementing them manually as complex SQL expressions, or using the DBMS_STAT_FUNCS package. Now they’re first-class aggregate functions available anywhere SQL is valid — in reports, views, materialized views, and analytics queries.

Here’s one of my favorite SQL improvements in Oracle 23ai, and one with a personal history I’ll share more about in September: GROUP BY ALL.

Every developer who has written aggregate queries knows the pain. You write a SELECT with some aggregate functions (SUM, COUNT, AVG), and Oracle forces you to list every non-aggregated column in the GROUP BY clause. Add a column to the SELECT? Add it to GROUP BY too. Forget one? ORA-00979: not a GROUP BY expression.

GROUP BY ALL solves this automatically. It groups by every non-aggregate expression in the SELECT list.

Before (Oracle ≤ 19c / 21c):

SELECT department_id, department_name, job_id, location_id,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary
FROM   employees
GROUP BY department_id, department_name, job_id, location_id;

After (Oracle 23ai):

SELECT department_id, department_name, job_id, location_id,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary
FROM   employees
GROUP BY ALL;

Oracle automatically groups by department_id, department_name, job_id, and location_id — every column that isn’t inside an aggregate function.

Adding a new column? No GROUP BY change needed:

SELECT department_id, department_name, job_id, location_id, country_id,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary
FROM   employees
GROUP BY ALL;  -- country_id is automatically included

Works with ROLLUP, CUBE, and GROUPING SETS too:

SELECT department_id, job_id,
       SUM(salary)
FROM   employees
GROUP BY ROLLUP ALL;

This feature is one of those “why didn’t this always exist?” improvements. Less boilerplate, fewer bugs from missing a column in GROUP BY, and queries that are easier to extend. I’ll have more to say about the origins of this idea in September’s special series.