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 :)
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 :)
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.
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
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.
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('Erro r reading check_table');
DBMS_OUTPUT.put_line(SQLER RM);
RETURN FALSE;
END;
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
BEGIN
SELECT ok_to_continue INTO v_ok FROM check_table;
RETURN (v_ok = 'Y');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Erro
DBMS_OUTPUT.put_line(SQLER
RETURN FALSE;
END;
ASKER
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;
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.
ASKER
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.
Once again, thanks.
ASKER
sdtstuber,
Thats exactly what I want, for the script to check after every move.
Thanks alot sdtuber and slightwv.
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 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.
Then when you wanted to quit, just drop the ok_table.
ASKER
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.
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.
ASKER
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;
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;
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.
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.
ASKER
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.
Try sqlplus.
ASKER
yep. It was executed via sqlplus
Is there some hidden or control character there? Begin is not an illegal character.
ASKER
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.
Save in an editor and execute the script.
ASKER
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
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
Set echo on
Spool test
Begin
... The rest of your block
Spool off
ASKER
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
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.sql str);
EXECUTE IMMEDIATE x.sqlstr;
...
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.sql
EXECUTE IMMEDIATE x.sqlstr;
...
ASKER
Seems like a different error. I added line "dbms.output.put_line(x.sq lstr);" 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.sql str);
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 :)
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.sql
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
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'
take the semicolon off your sql
SELECT 'alter table '|| table_name|| ' move partition '|| partition_name|| ' tablespace WHS_ADMIN_DATA'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
you still have the semicolon
ASKER
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.sql str);
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.
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.sql
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
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
ASKER
Thanks for clearing that up. I'll experiment on the side (not touching the working setup to get a better understanding.
Take care guys :)
Take care guys :)
Should a split of the points not been in order?
ASKER
See...thats what 'im trying to figure how.....It didnt give me any option to split the points. How do i go about it.
ASKER
yes please, open the question for me. I would like to have the points split between the two of you.
ASKER
There we go.
Thanks again guys.
Thanks again guys.
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.