PL SQL - Recover a user's volume from my tablespace

Question

I'm trying to recover the volume that a user uses in my database. But I can not find how to do it. Here is my code:

FUNCTION EspaceUtilise (v_user IN VARCHAR2) RETURN NVARCHAR2 AS requetes NVARCHAR2(4000);
  BEGIN
    DECLARE
        unused_bytes NUMBER;        
        total_bytes NUMBER;
        CURSOR c_infosUser IS SELECT * FROM all_users where USER_ID = v_user;
        v_rqt NVARCHAR2(4000);
    BEGIN
      OPEN c_infosUser;
      FETCH c_infosUser INTO v_rqt;
      WHILE c_infosUser%FOUND LOOP
        -- Get user volume here 
      FETCH c_infosUser INTO v_rqt;
      END LOOP;
      CLOSE c_infosUser;
      RETURN ;
    END;
  END;

I tried to use the method "SUM" and "DBMS_SPACE" but I can not properly use it.

Answer 1

Users consume space for two things: tables and indexes. The usage is tracked in segments. There are two data dictionary views for this: USER_SEGMENTS, which shows the consumption of the connected user, and DBA_SEGMENTS, which shows the consumption of all users and requires a higher level of privileged access. (Segments is one of the subset of data dictionary views which does not have a ALL_ version.)

The simplest version of the query would be:

select sum(bytes) as tot_segment_size
from dba_segments
where owner = 'WHOEVER'; 

The view has various other columns which you might want to use. Find out more.

If you really want to wrap that query in a function you will need to the appropriate privileges on the data dictionary view (or the SELECT ANY DICTIONARY role) granted to your user directly i.e. not through a role.