SQL to check the free space allocated for a user who doesn’t have DBA access. preciously in oracle 11G it was possible to use the “user_free_space” View but from oracle 12 c this view access is restricted to DBA users. (source: Release changes)
SELECT ufs.tablespace_name ,(SUM(bytes) /(1024*1024)) AS FREESPACE
FROM user_free_space ufs
WHERE EXISTS
(SELECT DISTINCT tablespace_name
FROM all_tables
WHERE tablespace_name IS NOT NULL
AND ufs.tablespace_name = tablespace_name
AND tablespace_name =
(SELECT DEFAULT_TABLESPACE FROM USER_USERS WHERE USERNAME ='USER1'
)
)
GROUP BY tablespace_name;
Please advise?