How to get available space in tablespace for a user (Oracle)

Question

I'm working on a web application where I need to warn the user that they're running out of space in the given db user's tablespace. The application doesn't know the credentials of the db's system user, so I can't query views like dba_users, dba_free_space..etc.

My question is, is there a way in Oracle for a user to find out how much space there is left for them in their tablespace?

Thanks!

score 22 · Accepted Answer · answered May 16 '11 at 17:24

Forgive my ignorance on the subject, for I believed only views available on data storage were dba_free_space etc..

I realized that for the logged user, there are user_free_space.. views for them. Modified version of the query mentioned here would be the answer my question.

Query is as follows: (Getting the space left on the DEFAULT_TABLESPACE of the logged user)

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;

It would return free space in MB

Thanks, didn't know that. I knew about user_tables and similar views but didn't know about that one. — redcayuga, May 20 '11 at 19:12
It would return free space in MB multiplied on the amount of users in `user_users` having particular tablespace as a `default_tablespace`. So it works correctly only when user_users contains exactly one row per each joining tablespace. — diziaq, Aug 15 '18 at 12:10

score 3 · Answer 2 · answered May 13 '11 at 16:21

3

create a stored package as a user that has the necessary privileges. You may have to create a new user. Grant EXECUTE on the package to any user that needs it. The packages needs to have all the procedures and functions needed to access the DBA views but should be coded carefully to avoid accessing "too much" information. You may want to write a second package in the account of a non-privileged user to encapsulate the logic.

answered May 13 '11 at 16:21

redcayuga

1,241
6
4

Thanks, I think I found another resolution. – Emrah May 16 '11 at 18:10

score 2 · Answer 3 · answered Nov 29 '12 at 15:15

This is potentially very complex, as it's quite possible for the user to:

Receive an "out of space" error even though the tablespaces that they have privileges on, including their default tablespace, have plenty of space. This could happen when they insert into a table that is owned by a different user which is on a tablespace that your user has no quota on. In this case, your user probably does not have access to the views required to determine whether there is free space or not,
Be able to continue inserting data even though there is no free space on the tablespaces on which they have a quota -- they might not even have a quota on their default tablespaces.

So unless you have a rather simple case you really have to be very aware of the way that the user interacts with the database on a far deeper level, and look at free space from a more database-holistic viewpoint.

How to get available space in tablespace for a user (Oracle)

3 Answers3