Re: using EXECUTE IMMEDIATE to run DBMS_STATS.GATHER_SCHEMA_STATS
From: <abani.narendra_at_gmail.com>
Date: Thu, 20 Sep 2012 08:20:55 -0700 (PDT)
Message-ID: <9049460d-8f83-4947-ba24-5632a5e5acf2_at_googlegroups.com>
On Friday, October 31, 2008 5:23:34 PM UTC+5:30, Kevin S wrote:
> When I run the block below, in oracle 10.2.0.3.0, I get the error
> shown after. Is this a problem with my coding or is it just not
> possible to run this commad in pl/sql?
>
> Thanks
>
> DECLARE
> v_dbms_stats VARCHAR2(200):= 'EXEC
> DBMS_STATS.GATHER_SCHEMA_STATS( ownname => ''LOAD'',
> estimate_percent => 100, cascade => TRUE,options => ''GATHER
> AUTO'');';
> BEGIN
> EXECUTE IMMEDIATE v_dbms_stats;
> END;
>
>
> ERROR at line 1:
> ORA-00900: invalid SQL statement
> ORA-06512: at line 4
Date: Thu, 20 Sep 2012 08:20:55 -0700 (PDT)
Message-ID: <9049460d-8f83-4947-ba24-5632a5e5acf2_at_googlegroups.com>
On Friday, October 31, 2008 5:23:34 PM UTC+5:30, Kevin S wrote:
> When I run the block below, in oracle 10.2.0.3.0, I get the error
> shown after. Is this a problem with my coding or is it just not
> possible to run this commad in pl/sql?
>
> Thanks
>
> DECLARE
> v_dbms_stats VARCHAR2(200):= 'EXEC
> DBMS_STATS.GATHER_SCHEMA_STATS( ownname => ''LOAD'',
> estimate_percent => 100, cascade => TRUE,options => ''GATHER
> AUTO'');';
> BEGIN
> EXECUTE IMMEDIATE v_dbms_stats;
> END;
>
>
> ERROR at line 1:
> ORA-00900: invalid SQL statement
> ORA-06512: at line 4
Hi I had a similar requirement which I resolved.
SET SERVEROUTPUT ON;
DECLARE
TAB VARCHAR2(30):='EMP';
BEGIN
EXECUTE IMMEDIATE 'BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname => ''SCOTT'',tabname => '''||TAB||''' , estimate_percent => 10, degree => 4, granularity => ''ALL'', cascade => TRUE);
END;';
END;
This should help! Thanks
Abanikant Received on Thu Sep 20 2012 - 10:20:55 CDT