Include table gather stats in procedure

Question

Friends, I'm using below procedure to move tables, indexes for each table and now trying to include dbms_stats.gather_table_stats in the procedure. Somehow whatever I do gathering stats not working.

Could somebody please suggest what I'm doing wrong?

Procedure accepts two parameter OldTbs = Old Tablespace and NewTbs = New Tablespace.

Logic I'm following; Loop

1. Move one table from the tablespaces

2. Move each indexes for table move in #1

3. Gather table stats of table which was moved in #1

Till #2 it works and now trying to include dbms_stats once all index rebuild completes for table. User has got the right to move any schema.

dmbs_stats.gather_table_stats(owner=>.......)


CREATE OR REPLACE PROCEDURE moveTbl (OldTbs in varchar2, NewTbs in varchar2)
    AUTHID CURRENT USER
IS
  CURSOR curTable IS
    SELECT owner, table_name, tablespace_name
      FROM dba_tables
      WHERE tablespace_name = OldTbs
      ORDER BY TABLE_NAME;

   CURSOR curIndex (lTabOwn IN Varchar2, lTabNan IN Varchar2) IS
      SELECT table_owner, table_name, owner, index_name, tablespace_name
        FROM dba_indexes
        WHERE table_owner = lTabOwn
        AND table_name = lTabNam;

BEGIN
  FOR rec1 IN curTable LOOP
    dbms_output.putline(rec1.owner || '.' || rec1.table_name);

    EXECUTE IMMEDIATE 'alter table ' || rec1.owner || '.' || rec1.table_name ||
                      ' move tablespace ' || NewTbs;

  FOR rec2 IN curIndex LOOP
     .....
     .....
     .....

  END LOOP;  --curIndex for loop

  dmbs_stats.gather_table_stats(ownname=>'''||rec1.owner || ''',tabname=> || rec1.table_name ||''', estimate_percent=>100, cascade=>true);

  END LOOP;  --curTable for loop
END moveTbl;

Answer 1

Just pass the owner and table name as parameters. You're not building a dynamic SQL statement so there is no reason to quote anything

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

Of course, this takes no view on the underlying wisdom of the code you're writing. If you're moving objects from one tablespace to another often enough that it's worth writing a stored procedure, I would strongly suspect that you're doing something very wrong. Unless you have trivial amounts of data or really odd data patterns, an estimate percentage of 100 also seems like serious overkill.