Hello,
Is it possible for the WITH clause below to be converted into a simple SELECT statement, does not matter if it is ANSI or non-ANSI format.
WITH USAGE_REPORT (con_id, name, total_size, bytes, space) AS
(
SELECT con_id, name, total_size, NULL, NULL
FROM v$pdbs
UNION ALL
SELECT con_id, NULL, NULL, bytes, NULL
FROM cdb_segments
where tablespace_name not like 'SYS%'
UNION ALL
SELECT con_id, NULL, NULL, NULL, space
FROM CDB_RECYCLEBIN
)
SELECT MIN (con_id) AS con_id
, MIN (name) AS pdb_name
, trunc(SUM (total_size/1024/1024)) AS size_alloc
, trunc(SUM (bytes/1024/1024)) AS size_used
, trunc(SUM ((space * 8192)/1024/1024)) AS rbin_size
, NVL ( trunc(SUM (bytes/1024/1024)), 0)
- NVL ( trunc(SUM ((space * 8192)/1024/1024)), 0) AS actl_size
FROM USAGE_REPORT
GROUP BY con_id
ORDER BY actl_size;
CON_ID PDB_NAME SIZE_ALLOC SIZE_USED RBIN_SIZE ACTL_SIZE
-----------------------------------------------------------------------
157 ORCL_PDB 128 100 10 90