| Privileges required to gather stats on tables in another schema [message #174122] |
Fri, 26 May 2006 00:36  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
Oracle 9.2.0.6.0
I want to collect statistics of table using following procedure
CREATE OR REPLACE PROCEDURE table_stats
AS
BEGIN
FOR c_tbl_nm IN (SELECT table_name
FROM dba_tables where OWNER = 'USER2')
LOOP
DBMS_STATS.gather_table_stats
('USER2',
UPPER (c_tbl_nm.table_name),
method_opt => 'For all indexed columns size 50',
CASCADE => TRUE
);
END LOOP;
END;
/
The above procedure is created in USER1 schema and user1 only will be excuting it.
However inspite of the fact that user1 has SYSDBA role,Alter any table SYStem Privilege and all object privileges on all tables in USER2 schema,
while excuting above procedure i am getting following error
*
ERROR at line 1:
ORA-20000: Object BZ_ERROR_HISTORY does not exist or insufficient privileges
to analyze
ORA-06512: at "SYS.DBMS_STATS", line 10070
ORA-06512: at "SYS.DBMS_STATS", line 10084
ORA-06512: at "USER1.TABLE_STATS", line 7
ORA-06512: at line 1
here "BZ_ERROR_HISTORY" is table in USER2 schema
Please suggst whether any further privileges are required by USer1 to run the above procedure on USER2 tables.
Thanks in Advance,
Pratap
|
|
|
|
| Re: Privileges required to gather stats on tables in another schema [message #174153 is a reply to message #174122] |
Fri, 26 May 2006 04:24  |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
is the code posted the actual code getting the error? -in that case its syntax needs to be changed for the FIRST TWO parameters..
| Quote: |
DBMS_STATS.gather_table_stats
('USER2',
UPPER (c_tbl_nm.table_name),
method_opt => 'For all indexed columns size 50',
CASCADE => TRUE
);
|
should be
DBMS_STATS.gather_table_stats
(ownname=>'USER2',
tabname=>UPPER (c_tbl_nm.table_name),
method_opt => 'For all indexed columns size 50',
CASCADE => TRUE
);
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
