exec gather table stats from procedure

Question

If I create a procedure: ``` CREATE OR REPLACE PROCEDURE SchameB.PRC_GATHER_STATS IS BEGIN SYS.DBMS_STATS.GATHER_TABLE_STATS( 'SchName', 'TableName', CASCADE => TRUE); END; ``` and execute it ; ``` EXEC SchameB.PRC_GATHER_STATS; ``` this gives me error `ORA-20000: Unable to analyze TABLE "SchameA"."TableName", insufficient privileges or does not exist`. But this works: ``` EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS( 'SchameA', 'TableName', CASCADE => TRUE); ``` The user who `EXEC`s the procedure and the table are in different schemas. Why am I getting an error when doing this through a procedure?

Accepted Answer

To gather statistics on an object in another schema you need the `ANALYZE ANY` system privilege. I appears that the user that runs your procedure has that privilege, but granted through a role. As the documentation says:

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights.

You can either `GRANT ANALYZE ANY` directly to your user, or create the procedure with invoker's rights, as:

```
CREATE OR REPLACE PROCEDURE SchameB.PRC_GATHER_STATS
AUTHID CURRENT_USER IS
BEGIN
    SYS.DBMS_STATS.GATHER_TABLE_STATS('SchName', 'TableName', CASCADE => TRUE);
END;
/
```

When you `EXEC` the `DBMS_STATS` procedure directly, it's running as an anonymous block, and those always run with invoker's rights - honouring roles.

exec gather table stats from procedure

Solution

Problem