Oracle procedure to move tablespace -

friends... i'm testing oracle procedure move tables, indexes of tablespace new tablespace... i'm trying debug , fix simple procedure gives me cursor error... please point error?

i can generate manually there 200 tablespaces there plan move @ regular interval wanting automate task.

objective: accept old tablespace , new tablespace @ procedure run , use move objects in tablespace new tablespace.

1. i plan below:
   1. accept old_tbs, new_tbs @ procedure run
   2. move table old_tbs new_tbs
   3. rebuild indexes of table new_tbs
   4. move table b old_tbs new_tbs
   5. rebuild indexes of table b new_tbs

loop...

create or replace procedure movetbl (oldtbs in varchar2, newtbs in varchar2)   tblsql         varchar2(250);  cursor curtable (vowner varchar2, vtblname varchar2, vtbsname varchar2)    select owner, table_name, tablespace_name       dba_tables       tablespace_name = oldtbs       order 2;   rec1 curtable%rowtype;  begin   rec1 in curtable loop     dbms_output.putline('rec1.owner || rec1.table_name');      tblsql := 'alter table '||rec1.owner||'.'||rec1.table_name||' move tablespace '||newtbs;    execute immediate tblsql;  end loop;  --curtable loop  end movetbl; / 

you've declared cursor having 3 parameters, don't supply parameters when open in loop. suggest cursor doesn't need parameters, , should eliminated.

it may case schema/user under procedure being created doesn't have access dba_tables view.

edit

authid current user should added after create or replace procedure before is, shown below:

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; 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;   end loop;  --curtable loop end movetbl; 

share , enjoy.