This discussion is archived
3 Replies Latest reply: Jul 31, 2013 10:32 AM by EdStevens RSS

DBA_FREE_SPACE reference in stored procedure

andy_schnelle Newbie
Currently Being Moderated

Hi all,

 

I tried googling around and could not find a solution.  I am referencing the DBA_FREE_SPACE system view in a stored procedure.  I received an error for insuffiient privileges for that particular piece of the stored proc.  I then explicity granted SELECT on the system view to the user schema which holds the stored proc.  I still am getting the insufficient privileges error and can not figure out why.  Has anyone experienced this or do you have any ideas I can try?

 

Thanks in advance.

  • 1. Re: DBA_FREE_SPACE reference in stored procedure
    Suri Pro
    Currently Being Moderated

    Hi,

     

    Please execute SELECT * FROM dba_free_space  ? Are you able to select the data ?

     

    Cheers,

    Suri

  • 2. Re: DBA_FREE_SPACE reference in stored procedure
    EdStevens Guru
    Currently Being Moderated

    andy_schnelle wrote:

     

    Hi all,

     

    I tried googling around and could not find a solution.  I am referencing the DBA_FREE_SPACE system view in a stored procedure.  I received an error for insuffiient privileges for that particular piece of the stored proc.  I then explicity granted SELECT on the system view to the user schema which holds the stored proc.  I still am getting the insufficient privileges error and can not figure out why.  Has anyone experienced this or do you have any ideas I can try?

     

    Thanks in advance.

     

    Maybe we should see your code and the exact error message, not your interpretation of the error.  Typically, not having SELECT privilge on a table or view will return 'ORA-00942: table or view does not exist'. 

     

     

    SQL> conn scott/tiger

    ERROR:

    ORA-28001: the password has expired

     

     

    Changing password for scott

    New password:

    Retype new password:

    Password changed

    Connected.

    SQL> select * from dba_free_space;

    select * from dba_free_space

                  *

    ERROR at line 1:

    ORA-00942: table or view does not exist

     

     

    SQL> conn / as sysdba

    Connected.

    SQL> grant select on dba_free_space to scott;

     

    Grant succeeded.

     

    SQL> conn scott/lion

    Connected.

    SQL> select * from dba_free_space;

     

    TABLESPACE_NAME   FILE_ID   BLOCK_ID  BYTES     BLOCKS

    ------------------------------ ---------- ---------- ---------- ----------

    RELATIVE_FNO

    ------------

  • 3. Re: DBA_FREE_SPACE reference in stored procedure
    andy_schnelle Newbie
    Currently Being Moderated

    Hi,

     

    Thanks for your help, I was able to figure this out.  The particular user name needed additional system privileges granted.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points