0

i have a pl\sql process that run an alter table exchange partition operation.

another sessions makes selects on this table, and if the select started before the alter but doesn't finished yet (alter table start during the select) - the select throws an exception (object no longer exists).

since the exchnage operation is realy fast, i want to prevent any action on the table and then execute the alter. or if the select already start - to delay the exchnage until it will be finished and then execute the alter.

the selects comming from a-lot of sessions and i can't constrol them. so i just can't add any code before\after the selects - only before\after the alter table.

is there a way to make something like this???

i tried rdbm_redefinition instead the exchange but always the select failes, for the same reason..

thanks.

user2671057
  • 1,411
  • 2
  • 25
  • 43
  • What do you want to have happen when another session attempts to read the table what `SELECT` statements are "prevented" by whatever solution you come up with? You want them to just wait? Or you want them to get an error like "table XYZ is unavailable due to maintenance, please try again in a few minutes". I believe the former is impossible. The latter might be doable with a FGA policy. – Matthew McPeak Mar 21 '17 at 12:20
  • i want them to wait - the exchange is realy fast , less then 1 second. so if a select start after the exchange already start - the select can wait, stand by.. no exception. like there is a lock. but more important - if there is a select that already start - the exchange need wait. – user2671057 Mar 21 '17 at 12:51
  • I don't think it can be done. How much data is in the partition you are exchanging? – Matthew McPeak Mar 21 '17 at 12:52
  • change between few thousands to milions.. – user2671057 Mar 21 '17 at 13:30

0 Answers0