Schema wise space Check

---------- For 12c database------------------

SET LIN 220 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;





---------- For 11g database------------------

SET LIN 220 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
FROM DBA_USERS A,DBA_SEGMENTS B
WHERE A.USERNAME=B.OWNER
GROUP BY B.OWNER,A.CREATED,B.TABLESPACE_NAME
ORDER BY B.TABLESPACE_NAME,ROUND(SUM(B.BYTES/1024/1024),2) DESC;