Can I run DBMS_STATS.gather_index_stats on a index which is in another schema in oracle?

When I run gather_index_stats on index which is in another schema, it is showing an error "Privilege's are missing". So, is this facility available in Oracle? If yes, how can I achieve it? Or is there any privilege's which I have to provide?

Thanks

CodePudding user response：

By default, you can't do anything with other user's objects - you need to have privileges to do so.

I'm not a DBA and don't have any database to illustrate the "solution", so - unless someone explains it better - see if this helps.

In order to be able to work on someone else's objects, a privileged user - such as SYS (or any other which has DBA privileges) - should grant you one of ANY privilege (such as alter any table). "Any" means "owned by anyone", which also means that you can't be granted privilege to gather stats only for that "another" user you're talking about, but "any user" in the database.

System privileges are powerful so - think twice whether you should grant them to anyone.

I think (as I can't test it myself nor have I found it in documentation) that privilege you need is ANALYZE ANY.

• privileged user (SYS) should

  grant analyze any to you;
  

• connected as you, you'd then e.g.

  exec dbms_stats.gather_index_stats('SCOTT', 'PK_EMP');
  

  (where SCOTT represents that "other user" you're talking about)