Home » RDBMS Server » Server Administration » DBMS_STATS.GATHER_SCHEMA_STATS error
DBMS_STATS.GATHER_SCHEMA_STATS error [message #203211] Tue, 14 November 2006 06:04 Go to next message
gsgill76
Messages: 12
Registered: November 2006
Junior Member
Hi i am try to create SP as

create or replace procedure GenStat(UserName varchar2)
as
begin
dbms_stats.gather_schema_stats(
ownname => ''' || UserName || ''',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 15
);
end;
/

EXEC GenStat('SCOTT');

OUTPUT:
BEGIN GenStat('SCOTT'); END;

*
ERROR at line 1:
ORA-20000: Schema "' || USERNAME || '" does not exist or insufficient
privileges
ORA-06512: at "SYS.DBMS_STATS", line 1359
ORA-06512: at "SYS.DBMS_STATS", line 12194
ORA-06512: at "SYS.DBMS_STATS", line 12165
ORA-06512: at "SCOTT.GENSTAT", line 4
ORA-06512: at line 1

I had already grant (conn "sys/sys@DB as sysdba")

grant analyze any to scott;

Grant succeeded.

but still getting the same error.

I had alread gone
http://www.orafaq.com/forum/t/46081/0/??SQ=&S=94af66c3ca0a48fa4828ac2a96d6d707

but no much help.
Please help me

Regards,
Thanks.
Gurpreet S. Gill.

Report message to a moderator

Re: DBMS_STATS.GATHER_SCHEMA_STATS error [message #203223 is a reply to message #203211] Tue, 14 November 2006 06:44 Go to previous messageGo to next message
gsgill76
Messages: 12
Registered: November 2006
Junior Member
Mr. Mahesh Rajendran

this throws following error

BEGIN GenStat('SCOTT'); END;

*
ERROR at line 1:
ORA-20000: Schema "USERNAME" does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 1359
ORA-06512: at "SYS.DBMS_STATS", line 12194
ORA-06512: at "SYS.DBMS_STATS", line 12165
ORA-06512: at "SCOTT.GENSTAT", line 4
ORA-06512: at line 1

Line 5
ownname => 'UserName',
makes the ownname as 'UserName' not as the varilable passed as a parameter.

I want to pass the parameter that will set the value for the 'UserName' as ownname

Regards,
Thanks.
Gurpreet S. Gill

[Updated on: Tue, 14 November 2006 06:45]

Report message to a moderator

Re: DBMS_STATS.GATHER_SCHEMA_STATS error [message #203229 is a reply to message #203223] Tue, 14 November 2006 06:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Sorry. My mistake. The previous code will not work.

scott@9i > clear
scott@9i > get p1
  1    create or replace procedure GenStat(UserName varchar2)
  2    as
  3    begin
  4    dbms_stats.gather_schema_stats(ownname => username, estimate_percent => dbms_stats.auto_sample_size);
  5    DBMS_OUTPUT.PUT_LINE ('ownname =>'''||UserName||''',estimate_percent => dbms_stats.auto_sample_size');--ignore this. Just to debug.shows the statement that was parsed
  6*   end;
scott@9i > /

Procedure created.

scott@9i > EXEC GenStat('SCOTT');
ownname =>'SCOTT',estimate_percent => dbms_stats.auto_sample_size

PL/SQL procedure successfully completed.

Report message to a moderator

Re: DBMS_STATS.GATHER_SCHEMA_STATS error [message #203238 is a reply to message #203229] Tue, 14 November 2006 07:17 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
 
scott@9i > exec dbms_stats.delete_schema_stats('SCOTT');

PL/SQL procedure successfully completed.

scott@9i > select table_name,last_analyzed from user_tables;

TABLE_NAME                     LAST_ANAL
------------------------------ ---------
BAT
DEPT
EMP
MYLOB
MYTABLE
PLAN_TABLE

6 rows selected.

scott@9i > exec genstat('SCOTT');
ownname =>'SCOTT',estimate_percent => dbms_stats.auto_sample_size

PL/SQL procedure successfully completed.

scott@9i >  select table_name,last_analyzed from user_tables;

TABLE_NAME                     LAST_ANAL
------------------------------ ---------
BAT                            14-NOV-06
DEPT                           14-NOV-06
EMP                            14-NOV-06
MYLOB                          14-NOV-06
MYTABLE                        14-NOV-06
PLAN_TABLE                     14-NOV-06

6 rows selected.

[Updated on: Tue, 14 November 2006 07:18]

Report message to a moderator

Re: DBMS_STATS.GATHER_SCHEMA_STATS error [message #203401 is a reply to message #203238] Wed, 15 November 2006 00:50 Go to previous messageGo to next message
gsgill76
Messages: 12
Registered: November 2006
Junior Member
Mr. Mahesh Rajendran, thanks, Problem solved.
But still the doubt, if i user this code

CASE I:
CREATE OR REPLACE
procedure GenStat(UserName varchar2)
as
begin
      dbms_stats.gather_schema_stats(
      ownname          => ''' || UserName || ''',
      estimate_percent => dbms_stats.auto_sample_size,
      method_opt       => 'for all columns size auto',
      degree           => 15
   );
end;

throws error

CASE II

If i use this code
CREATE OR REPLACE 
procedure GenStat(UserName varchar2)
as
begin
      dbms_stats.gather_schema_stats(
      ownname          => UserName,
      estimate_percent => dbms_stats.auto_sample_size,
      method_opt       => 'for all columns size auto',
      degree           => 15
   );
end;

this works fine.

CASE III:

But if user this code
CREATE OR REPLACE 
procedure GenStat(UserName varchar2)
as
begin
      dbms_stats.gather_schema_stats(
      ownname          => 'SCOTT',
      estimate_percent => dbms_stats.auto_sample_size,
      method_opt       => 'for all columns size auto',
      degree           => 15
   );
end;

Again works fine.(I know the parameter is useless in this case)

Now my question is, although we need the single quotes around the ownername as in CASE III
ownname          => 'SCOTT'

why not so incase of SP with parameter as a ownername (UserName) CASE II
ownname          => UserName

If we need the single quote then we must use the CASE I as
ownname          => ''' || UserName || '''

but throws error

WHY?
I mean although we need single quote in case III, but not requre in CASE II

Kind Regards,
Thanks.
Gurpreet S. Gill

[Updated on: Wed, 15 November 2006 01:22]

Report message to a moderator

Re: DBMS_STATS.GATHER_SCHEMA_STATS error [message #203479 is a reply to message #203401] Wed, 15 November 2006 04:26 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
do what i did before. Use dbms_output to debug.
All other options have unwanted characters wrapped around.
And || is a concat operator.

Report message to a moderator

Previous Topic: OEM issues on a system which having software only(Oracle 10g rel2)
Next Topic: Is their any Compatability issues between Oracle9i Client and Oracle 10g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 06:46:44 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.