CODEWITHSUNDEEP
oracle-databaseprocedure
homer
homer

Reputation: 433

Oracle procedure to move tablespace

Friends... I'm testing this Oracle procedure to move all tables, indexes of a tablespace to new tablespace... I'm trying to debug and fix this simple procedure but it gives me cursor error... could somebody please point my error?

I can generate something manually but there are 200 tablespaces there which I plan to move at regular interval so wanting to automate this task.

Objective: Accept old tablespace and new tablespace at procedure run and use it to move all objects in that tablespace to new tablespace.

  1. I plan to do something like below:
    1. Accept old_tbs, new_tbs at procedure run
    2. Move Table A from old_tbs to new_tbs
    3. Rebuild indexes of Table A to new_tbs
    4. Move Table B from old_tbs to new_tbs
    5. Rebuild indexes of Table B to new_tbs

loop...

CREATE OR REPLACE procedure moveTbl (OldTbs in varchar2, NewTbs in varchar2)
IS

 TblSQL         VARCHAR2(250);

CURSOR curTable (vOwner varchar2, vTblName varchar2, vTbsName varchar2)
IS
   SELECT owner, table_name, tablespace_name
      FROM dba_tables
      WHERE tablespace_name = OldTbs
      ORDER BY 2;

 rec1 curTable%ROWTYPE;

BEGIN
  FOR 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 for loop

END moveTbl;
/

Upvotes: 0

Views: 1838

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You've declared the cursor as having three parameters, but you don't supply any parameters when you open it in the FOR loop. I suggest that this cursor doesn't need any parameters, and they should be eliminated.

It may also be the case that the schema/user under which this procedure is being created doesn't have access to the DBA_TABLES view.

Edit

AUTHID CURRENT USER should be added immediately after the CREATE OR REPLACE PROCEDURE but before the IS, as shown below:

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;
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;
  END LOOP;  --curTable for loop
END moveTbl;

Share and enjoy.

Upvotes: 2

Related Questions