Schema-wise space consumption and tablespaces

Schema-wise space consumption in tablespaces with last login 12c.

For 11g remove the highlighted text.

IN GB

SET LIN 120 PAGES 1000
COL SCHEMA_NAME FORMAT A20
COL USED_SPACE_GB FOR 999999999
COMPUTE SUM LABEL 'Grand Total in GB' OF USED_SPACE_GB ON REPORT
COMPUTE SUM LABEL 'Total for tablespace in GB' OF USED_SPACE_GB ON TABLESPACE_NAME

BREAK ON TABLESPACE_NAME SKIP 1 ON REPORT
 
SELECT B.TABLESPACE_NAME,B.OWNER SCHEMA_NAME,ROUND(SUM(B.BYTES/1024/1024/1024)) USED_SPACE_GB,A.CREATED,a.last_login
FROM DBA_USERS A,DBA_SEGMENTS B
WHERE A.USERNAME=B.OWNER
GROUP BY B.OWNER,A.CREATED,B.TABLESPACE_NAME,a.last_login
ORDER BY B.TABLESPACE_NAME,ROUND(SUM(B.BYTES/1024/1024/1024)) DESC;

IN MB

SET LIN 120 PAGES 1000
COL SCHEMA_NAME FORMAT A20
COL USED_SPACE_MB FOR 999999999.99
COMPUTE SUM LABEL 'Grand Total in GB' OF USED_SPACE_MB ON REPORT
COMPUTE SUM LABEL 'Total for tablespace in GB' OF USED_SPACE_MB ON TABLESPACE_NAME

BREAK ON TABLESPACE_NAME SKIP 1 ON REPORT
 
SELECT B.TABLESPACE_NAME,B.OWNER SCHEMA_NAME,ROUND(SUM(B.BYTES/1024/1024),2) USED_SPACE_MB,A.CREATED,a.last_login
FROM DBA_USERS A,DBA_SEGMENTS B
WHERE A.USERNAME=B.OWNER
GROUP BY B.OWNER,A.CREATED,B.TABLESPACE_NAME,a.last_login
ORDER BY B.TABLESPACE_NAME,ROUND(SUM(B.BYTES/1024/1024),2) DESC;