0

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?

Julien Marrec
  • 11,605
  • 4
  • 46
  • 63
janson soundranayagam
  • 1
  • 1
  • The page you linked to doesn't say anything about `user_free_space`. Why do you think that is restricted now? What error do you get from your query? (And are you running this in the root or a PDB, and as a local or common user?) – Alex Poole Jan 03 '17 at 09:43
  • I have non DBA user “USER1” who is created with the belong access. [create user USER1 identified by super default tablespace users; grant connect , create view, SELECT ANY TABLE, resource to USER1;] – janson soundranayagam Jan 05 '17 at 07:13
  • I have non DBA local user “USER1” who is created with the belong access. create user USER1 identified by super default tablespace users; grant connect , create view, SELECT ANY TABLE, resource to USER1; I used the below SQL to find the free space to in oracle 11G and it was providing me the results, But after migration to oracle 12c the SQL is not provided any results. When checked in detail I found that without DBA rights the ‘user_free_space’ doesn’t work. Note : That the SQL is run in ROOT. – janson soundranayagam Jan 05 '17 at 07:21
  • Not getting any results is very different to getting an error, e.g. from `user_free_space` not being visible. I'm not sure why you think that isn't visible without DBA rights - it's the *user* view, not the DBA view. Are you connected as user1 in the root container? Or as a common user? What happens when you run your query in the PDB the user was created in? – Alex Poole Jan 05 '17 at 17:54
  • The issue solved , Thanks "Alex Poole" The "USER1" user creation SQL has to be modified in below manner in order to access the “user_free_space” view. The existing user creation SQL used. (Oracle 11G) create user USER1 identified by super default tablespace users; grant connect,create view, SELECT ANY TABLE, resource to USER1; – janson soundranayagam Jan 16 '17 at 10:42
  • The SQL must be modified as below (Oracle 12 C) ,such that the “user_free_space” view can be used. create user USER1 identified by super default tablespace users; grant connect,create view, SELECT ANY TABLE,resource to USER1; GRANT UNLIMITED TABLESPACE to USER1; • In oracle 11G when assigning “Resource” predefined role to a user , he is by default inherits “Unlimited table” access. But in oracle 12c it’s not the case. – janson soundranayagam Jan 16 '17 at 10:45
  • You should post an answer not put all that in comments. But that doesn't affect *access to* the `user_free_space` view; it looks like it just means there is something to report - so you were getting no data back before, rather than an error. [But `resource` didn't grant unlimited tablespace in 11g either](https://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#r45c1-t12); it's usually better to use quotas, which your 11g instance may have done (or you modified the role). I would avoid `select any table too`, generally. – Alex Poole Jan 16 '17 at 11:00
  • Thanks for the suggestion i will remove the Select any table. – janson soundranayagam Jan 18 '17 at 06:04

0 Answers0