7

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

user840930
  • 5,214
  • 21
  • 65
  • 94
  • Ask your DBA for the same. – Lalit Kumar B Feb 12 '15 at 11:59
  • If you can select from the DBA_ views (which don't require "DBA privileges", just access to the appropriate views) you can use the SQL from my answer to [this question](http://stackoverflow.com/questions/2207122/true-tablespace-size-in-oracle/2207140#2207140). Best of luck. – Bob Jarvis - Слава Україні Feb 12 '15 at 11:59
  • can't ask DBA at moment – user840930 Feb 12 '15 at 12:02
  • don't have access to DBA_ views – user840930 Feb 12 '15 at 12:02
  • Not really. The DBA_ views rely on system tables which are not directly accessible - for example, the DBA_DATA_FILES view reads from a table (or perhaps view?) named `x$kccfe` which at a guess might contain datafile extent information - but I can't select directly from it. Thus, even if someone picked apart the DBA_ views it's likely that the resultant SELECT wouldn't execute. So if you want to know what space is available in a tablespace you either A) need to get access to the DBA_ views, or B) ask someone with the requisite privileges for the information. Best of luck. – Bob Jarvis - Слава Україні Feb 12 '15 at 12:30
  • If you can see the server, maybe you can see the size of the datafiles (.dbf) that make up the tablespace. They should have a fairly similar name, but there may be more than one file per tablespace. As mentioned above if you don't have dba privs you can't access the tablespace information. – kayakpim Feb 12 '15 at 12:31

1 Answers1

15

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
mmmmmpie
  • 2,908
  • 1
  • 18
  • 26