How To Run DBMS_STATS.GATHER_TABLE_STATS Using Dynamic Query

Connect as scott, scott has DBA and ANALYSE ANY privileges

create or replace procedure getstattest IS
str1 varchar2(1000);
cnt NUMBER;
CURSOR c_C1 IS select TABLE_NAME, OWNER from dba_tables where OWNER IN ('SCOTT') and TABLE_NAME IN ('EMP', 'DEPT','BONUS','SALGRADE');
begin
FOR tab_rec IN c_C1
LOOP
cnt:=0;
dbms_output.put_line('Checking OWNER: '||tab_rec.OWNER);
dbms_output.put_line('Checking TAB: '||tab_rec.TABLE_NAME);
execute immediate 'SELECT COUNT (*) FROM '|| tab_rec.TABLE_NAME INTO cnt;
if cnt > 2 then
str1:='BEGIN dbms_stats.gather_table_stats(ownname =>'''||tab_rec.OWNER||''',tabname =>'''||tab_rec.TABLE_NAME||''',cascade => true,degree=>
0); END;';
dbms_output.put_line(str1);
execute immediate str1;
end if;
cnt:=0;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
       DBMS_OUTPUT.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM||' '||str1);
END;
/

SQL> set serverout on;

SQL> exec  getstattest

About Sher khan

Senior Oracle DBA, Oracle 10g and 11g OCE and OCS, Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g and 11g. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in statistics. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s