oracle - Include table gather stats in procedure -

oracle - Include table gather stats in procedure -

friends, i'm using below procedure move tables, indexes each table , trying include dbms_stats.gather_table_stats in procedure. somehow whatever gathering stats not working.

could please suggest i'm doing wrong?

procedure accepts 2 parameter oldtbs = old tablespace , newtbs = new tablespace.

logic i'm following; loop

move 1 table tablespaces

move each indexes table move in #1

gather table stats of table moved in #1

till #2 works , trying include dbms_stats 1 time index rebuild completes table. user has got right move schema.

dmbs_stats.gather_table_stats(owner=>.......) create or replace procedure movetbl (oldtbs in varchar2, newtbs in varchar2) authid current user cursor curtable select owner, table_name, tablespace_name dba_tables tablespace_name = oldtbs order table_name; cursor curindex (ltabown in varchar2, ltabnan in varchar2) select table_owner, table_name, owner, index_name, tablespace_name dba_indexes table_owner = ltabown , table_name = ltabnam; begin rec1 in curtable loop dbms_output.putline(rec1.owner || '.' || rec1.table_name); execute immediate 'alter table ' || rec1.owner || '.' || rec1.table_name || ' move tablespace ' || newtbs; rec2 in curindex loop ..... ..... ..... end loop; --curindex loop dmbs_stats.gather_table_stats(ownname=>'''||rec1.owner || ''',tabname=> || rec1.table_name ||''', estimate_percent=>100, cascade=>true); end loop; --curtable loop end movetbl;

just pass owner , table name parameters. you're not building dynamic sql statement there no reason quote anything

dbms_stats.gather_table_stats( ownname => rec1.owner, tabname => rec1.table_name, estimate_percentage => 100, cascade => true );

of course, takes no view on underlying wisdom of code you're writing. if you're moving objects 1 tablespace plenty it's worth writing stored procedure, suspect you're doing wrong. unless have trivial amounts of info or odd info patterns, estimate percentage of 100 seems serious overkill.

oracle procedures