Link to home
Start Free TrialLog in
Avatar of Waqasulhaq
Waqasulhaq

asked on

move hundreds of partitions of a table to new tablespace with exit script

Hello,
    I need to move many partitions of a particular table to a new tablespace with 'alter table move partition tablespace' command. I need to have some sort of exit script so that if I have to stop the "alter table move partition" script in the middle, I use that exit script to stop it from execution further, rather than pressing "ctrl+c"
All the help is highly appreciated :)
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>I need to have some sort of exit script so that if I have to stop the "alter table move

Not sure I fully understand but if I do:  in the script, insert current partition into a logging table and commit.  Then you know where you are at any time.

also create a new table like OK_TO_CONTINUE with a single column/row.

Then before the next partition move starts, query OK_TO_CONTINUE for a 'Y'.  If it isn't a 'Y', exit the script.

then you can stop the script by updating the table with a 'N' form another session.
Avatar of Waqasulhaq

ASKER

Below command is what I was to use to generate a script, and I want some sort of an exit script so that I can terminate in the middle, without having to press 'ctrl+c'.

select 'alter table '||TABLE_NAME||' move partition '||PARTITION_NAME||' tablespace xxx;' from dba_tab_partitions where TABLE_NAME='yyy' and PARTITION_NAME like 'P_2004%' order by PARTITION_NAME;

Thanks.
try something like this...  "check_exit" is a function that looks at some global value like a table, alert, system context, etc. and returns TRUE if it should exit, FALSE otherwise

BEGIN
    FOR x
        IN (SELECT      'alter table '
                     || table_name
                     || ' move partition '
                     || partition_name
                     || ' tablespace xxx;'
                         sql
                FROM dba_tab_partitions
               WHERE table_name = 'yyy' AND partition_name LIKE 'P_2004%'
            ORDER BY partition_name)
    LOOP
        EXECUTE IMMEDIATE x.sql;

        IF check_exit
        THEN
            EXIT;
        END IF;
    END LOOP;
END;


note this syntax is Oracle pl/sql,  I don't know the postrgres syntax unless you're using pl/sql compatibility
>>and I want some sort of an exit script so

I would use a stored procedure using the OK table that I mentioned above.  Keeps everything inside the database and you have more control.
using slightwv's  suggested table,  "check_exit" might look something like this...


CREATE TABLE check_table (ok_to_continue VARCHAR2(1));

CREATE OR REPLACE FUNCTION check_exit
    RETURN BOOLEAN
IS
    v_ok   check_table.ok_to_continue%TYPE;
BEGIN
    SELECT ok_to_continue INTO v_ok FROM check_table;

    RETURN (v_ok = 'Y');
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line('Error reading check_table');
        DBMS_OUTPUT.put_line(SQLERRM);
        RETURN FALSE;
END;
slightwv,
       You understand by problem correctly.
Do I have to create a logging table ?

What if I just create 'OK_TO_CONTINUE' table with one row/column and put an entry in script, say after every 3 'move partition' commands to check the 'OK_TO_CONTINUE' table for 'N' value.
If found, then exit. Sorry, not very good at sql.

select 'alter table '||TABLE_NAME||' move partition '||PARTITION_NAME||' tablespace xxx;' from dba_tab_partitions where TABLE_NAME='yyy' and PARTITION_NAME like 'P_2004%' order by PARTITION_NAME;

alter table '||TABLE_NAME||' move partition '||PARTITION_NAME||' tablespace xxx
alter table '||TABLE_NAME||' move partition '||PARTITION_NAME||' tablespace xxx;
alter table '||TABLE_NAME||' move partition '||PARTITION_NAME||' tablespace xxx;
check for N in  'OK_TO_CONTINUE', if yes then exit
alter table '||TABLE_NAME||' move partition '||PARTITION_NAME||' tablespace xxx;
the pl/sql block above will do that, but it'll check after every move.
sorry, by the time I posted my question, I had tons of replies from you guys. Thanks alot. I will look into it and update this post accordingly.
Once again, thanks.
sdtstuber,
   Thats exactly what I want, for the script to check after every move.
Thanks alot sdtuber and slightwv.
note the table was just one example, since slightwv had already describe it, I implemented it to show how it could be done.

you could also read from a tcp socket or dbms_pipe or lots of methods.  Pick whatever is easiest for you
You could also use the sqlplus script but adding a select 1 from ok_table; between each move and a whenever sqlerror exit at the top.

Then when you wanted to quit, just drop the ok_table.
I had another question:
I got below error when executing the PL/SQL code.


BEGIN
    FOR x
        IN (SELECT 'alter table '|| table_name|| ' move partition '|| partition_name|| ' tablespace xxx;'
                         sql
                FROM dba_tab_partitions
               WHERE table_name = 'TEST' AND TABLE_OWNER='yyy' AND partition_name LIKE 'P_2003%'
            ORDER BY partition_name)
    LOOP
        EXECUTE IMMEDIATE x.sql;

        IF check_exit
        THEN
            EXIT;
        END IF;
    END LOOP;
END;


ERROR at line 9:
ORA-06550: line 9, column 29:
PLS-00103: Encountered the symbol "SQL" when expecting one of the following:
<an identifier> <a double-quoted delimited-identifier> delete
exists prior
The symbol "<an identifier> was inserted before "SQL" to continue.
Slightvw,
   If i want to go via sql script-way, how do i incorporate the "select 1 from ok_table;" within the loop where ok_table, if has a value of Y, everything should stop. I'm just trying to keep it as simple as possible.
Thanks.


BEGIN
    FOR x
        IN (SELECT 'alter table '|| table_name|| ' move partition '|| partition_name|| ' tablespace xxx;'
                         sql
                FROM dba_tab_partitions
               WHERE table_name = 'TEST' AND TABLE_OWNER='yyy' AND partition_name LIKE 'P_2003%'
            ORDER BY partition_name)
    LOOP
        EXECUTE IMMEDIATE x.sql;
sorry, sql is an illegal column alias

change it to  sqlstr  


SELECT 'alter table '|| table_name|| ' move partition '|| partition_name|| ' tablespace xxx;'
            sqlstr



EXECUTE IMMEDIATE x.sqlstr;
>>ithin the loop where ok_table, if has a value of Y, everything should stop

In the sqlplus script way the contents of the table doesn't matter.  Just that the select succeeds.

When you drop the table the select will generate an error and the whenever action will exit.

BEGIN
    FOR x
        IN (SELECT 'alter table '|| table_name|| ' move partition '|| partition_name|| ' tablespace xxx;'
                         sqlstr
                FROM dba_tab_partitions
               WHERE table_name = 'TEST' AND TABLE_OWNER='yyy' AND partition_name LIKE 'P_2003%'
            ORDER BY partition_name)
    LOOP
        EXECUTE IMMEDIATE x.sqlstr;

        IF check_exit
        THEN
            EXIT;
        END IF;
    END LOOP;
END;
/


BEGIN
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 9
What are you running this with?

Try sqlplus.
yep. It was executed via sqlplus
Is there some hidden or control character there?  Begin is not an illegal character.
I dont think so. That's what baffled me. I just copied right onto sqlplus. is there anyway to determine if any hidden character present?
Don't copy/paste.

Save in an editor and execute the script.
same error when saved in an editor and executed in sqlplus.


BEGIN
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 9
it runs for me.    It doesn't do anything because I don't have your tables/owner/partition but the syntax is fine
Add the following to the top of your script then post the results from test.lst


Set echo on
Spool test

Begin
... The rest of your block

Spool off
Output from test.lst


SQL> BEGIN
  2      FOR x
  3          IN (SELECT 'alter table '|| table_name|| ' move partition '|| partition_name|| ' tablespace WHS_ADMIN_DATA;'
  4                           sqlstr
  5                  FROM dba_tab_partitions
  6                 WHERE table_name = 'RXSERV_TEST' AND TABLE_OWNER='AKHAN' AND partition_name LIKE 'P_2003%'
  7              ORDER BY partition_name)
  8      LOOP
  9          EXECUTE IMMEDIATE x.sqlstr;
 10  IF check_exit
 11          THEN
 12              EXIT;
 13          END IF;
 14      END LOOP;
 15  END;
 16  /
BEGIN
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 9


SQL> spool off
>>ORA-06512: at line 9

OH, line 9.  I missed that from mobile.

"9          EXECUTE IMMEDIATE x.sqlstr;"


Change the code and post the results from:

set serveroutput on

BEGIN
...
LOOP
   dbms_output.put_line(x.sqlstr);
          EXECUTE IMMEDIATE x.sqlstr;
...


Seems like a different error. I added line "dbms.output.put_line(x.sqlstr);" as you had mentioned and got below error.


SQL> spool asim_testt.lst
SQL> BEGIN
  2      FOR x
  3          IN (SELECT 'alter table '|| table_name|| ' move partition '|| partition_name|| ' tablespace WHS_ADMIN_DATA;'
  4                           sqlstr
  5                  FROM dba_tab_partitions
  6                 WHERE table_name = 'RXSERV_TEST' AND TABLE_OWNER='AKHAN' AND partition_name LIKE 'P_2003%'
  7              ORDER BY partition_name)
  8      LOOP
  9          dbms_output.put_line(x.sqlstr);
 10          EXECUTE IMMEDIATE x.sqlstr;
 11  IF check_exit
 12          THEN
 13              EXIT;
 14          END IF;
 15      END LOOP;
 16  END;
 17  /
BEGIN
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 10

Thanks for looking into all this :)
I don't see that your turned serveroutput on

put this before the BEGIN

SET serveroutput ON


the problem is the dynamic sql is probably incorrect,  the block is fine, but your alter table isn't right
ah I see it


take the semicolon off your sql

SELECT 'alter table '|| table_name|| ' move partition '|| partition_name|| ' tablespace WHS_ADMIN_DATA'
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You are right. It throws below error, but the syntax seems to be fine.


alter table RXSERV_TEST move partition P_20031231 tablespace WHS_ADMIN_DATA;
BEGIN
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 10
the syntax is wrong

you still have the semicolon
Seems like that semicolon was the culprit :)


SQL> BEGIN
  2      FOR x
  3          IN (SELECT 'alter table '|| table_name|| ' move partition '|| partition_name|| ' tablespace WHS_ADMIN_DATA'
  4                           sqlstr
  5                  FROM dba_tab_partitions
  6                 WHERE table_name = 'RXSERV_TEST' AND TABLE_OWNER='AKHAN' AND partition_name LIKE 'P_2003%'
  7              ORDER BY partition_name)
  8      LOOP
  9          dbms_output.put_line(x.sqlstr);
 10          EXECUTE IMMEDIATE x.sqlstr;
 11  IF check_exit
 12          THEN
 13              EXIT;
 14          END IF;
 15      END LOOP;
 16  END;
 17  /
alter table RXSERV_TEST move partition P_20031231 tablespace WHS_ADMIN_DATA
Error reading check_table
ORA-01403: no data found

PL/SQL procedure successfully completed.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks alot sdstuber. I got to learn alot from this as well.
I think ill leave the exception handling as-is and put Y in check_table before I start the real run. Then when I have to stop in the middle, I just updated check_table and enter any other value but Y.

I dont understand oen thing though. How does the procedure execute the statement without the semicolon? Is it because the statement is being mentioned as a "SQLSTR" to the execution is automatic?

Thanks again sdstuber and slightwv. DUnno where I would have been without this website.
the semicolon is an end-of-statement delimiter for sql*plus,  it's not actually part of the SQL itself.

that's why execute immediate barks at you when you include it.  similarly you wouldn't use  "/" after a pl/sql block invoked via execute immediate

the sqlstr  is just a name given to the column returned by the select statement,  you have to give it a name that can then be referenced inside the loop.  It could have been called bill or run_this or sdstuber_told_me_to   almost anything except key words, that's why "sql" wasn't allowed
Thanks for clearing that up. I'll experiment on the side (not touching the working setup to get a better understanding.
Take care guys :)
Should a split of the points not been in order?
See...thats what 'im trying to figure how.....It didnt give me any option to split the points. How do i go about it.
yes please, open the question for me. I would like to have the points split between the two of you.
There we go.
Thanks again guys.