block select on table

[sumanhere2010]

we have a table in DWH which is source to downstreams.I want to block select from the table when we are inserting/truncating or doing maintanance of the table.

what happens is that if communication is not proper,somtimes downstreams just use this table when there is no data,and get surprising results.

in this scenario when the table is not to be used,can we just put a exception message to them when ever they run select on that table?

it would have been a perfect trigger case if blocking select in trigger was allowed.

[magicwand]

This is a tricky issue, since there is no simple way to check if someone is selecting from a table.

The easiest (and formally correct) work around is, not to truncate but delete the table and then insert the new values within the same transaction.

As long as the delete/insert - transaction is not comitted (even if the "refresh" takes days), all user - SELECTs will automatically read the "old" values out of the undo segments and continue to function.

All user SELECTs that start after the "refresh transactin" commit, see the new values.

This does not work with TRUNCATE, because TRUNCATE (like all DDL statements per definition) commits the running transaction.

[debasisdas]

Create a materialized view on the source table and let the team use that as the source instead of the actual table. You need to refresh the MV after each DML. If your users are ready to compromise with data which may be a bit out dated, this is the best solution for your case.

[magicwand]

debasisdas,

starting with 10g this will work exactly the same way as it would by not truncating but deleting (in versions < 10g Oracle used to TRUNCATE MVs before doing a complete refresh on materialized views, starting with 10g it uses DELETE).

In earlier (now unsupported) Oracle Versions you would have exaclty the same problem as you have with the table, when selecting from the materialized view during the (complete) refresh.

I think many people crossing over from SQL Server to Oracle are not aware that in contrary to SQL Srv. there are no "dirty reads" in Oracle, but read consistency is (and always was) guaranteed in Oracle.

So, whether the application queries read from the tables UNDO - segments or from the materialized views UNDO - segments doesn't make any difference, but you will add additional complexity:

if we define:

t(u) ... Time for updating the table
t(l) ... Time lag between committing the table update and starting the complete refresh of the MV
t(v) ... Time for doing the complete refresh of the materialized view

then it would take t(u) + t(l) + t(v) for the application to access the "new" data, but it would only take t(u) if you don't use a MV but select directly from the table.

[debasisdas]

Thats true.