Oracle: how to check space used by a tablespace when no dba privs

user840930 picture user840930 · Feb 12, 2015 · Viewed 16.2k times · Source

I need to check space used by a tablespace but I have no dba privs. Is there a way to do this?

Answer

mmmmmpie picture mmmmmpie · Feb 12, 2015

Unfortunately without explicit permissions to the dba_free_space or dba_segments views you are stuck with your users default tablespace:

SELECT
  ts.tablespace_name,
  TO_CHAR(SUM(NVL(fs.bytes,0))/1024/1024, '99,999,990.99') AS MB_FREE
FROM
  user_free_space fs,
  user_tablespaces ts,
  user_users us
WHERE
  fs.tablespace_name(+)   = ts.tablespace_name
AND ts.tablespace_name(+) = us.default_tablespace
GROUP BY
  ts.tablespace_name;

If you need to check the size of a tablespace for which you don't have a user with that as their default tablespace you're stuck with going back to your DBA.
Test with the system tablespace as default:
enter image description here
Test with an app tablespace as the default tablespace:
enter image description here
This schema does not have query on the dba views:

select * from dba_free_space;
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error at Line: 13 Column: 15