LOCK Table from DML operations

User_BU3NG

Hi Experts,

I have a requrement, want my table to lock until some operations with in table creations are done. (Ex : altering fields, data types etc.. thru an automation). During the table creation in case of any failures occurs, table should be in locked from all DML operation for all users.

Thanks

BEDE

So, you mean that a table has to be locked, but, in the mean time, there must be going on some transactions, including commits and rollbacks.

The only way I can think of is something that goes along the lines below:

execute immediate 'lock table x_table in exclusive mode';

--- call procedures that run as autonomous transactions (having pragma autonomous_transaction) so that they may commit without having the lock on that table released even for a fraction of a second

commit;

User_3ABCE

@User_BU3NG

table should be in locked from all DML operation for all users

alter table tbl read only;
....
alter table tbl read write;

To make some "alter", you may need DML. For example, when adding a not null column without default value.

Use EBR to make changes temporary invisible to the application.

Arun Kumar Gupta

lock_table is session specific and once session is closed lock will also be released and other users will be able to perform DMLs.

A better option would be to make table read only before ALTER table ( DDL Commands) and if DDLs are successful make it read-write else it remains read only (even session is closed)

BEGIN
execute immediate 'alter table table_name read only';

--Perform Your DDL Commands here.

execute immediate 'alter table table_name read write';

END;
/

Note that this does not stop users from selecting data from table.

Regards

Arun

User_3ABCE

@BEDE

execute immediate 'lock table x_table in exclusive mode';

The locked table cannot be altered "Ex : altering fields, data types etc.."

Why "execute immediate"?

Arun Kumar Gupta

In case you dont want to make your DDLs dynamic (Dont want to use EXECUTE IMMEDIATE)

use below in sql script.

WHENEVER SQLERROR THEN EXIT

Regards

Arun

BEDE

https://community.oracle.com/tech/developers/discussion/comment/16840922#Comment_16840922

So, any DDL on a table locks the whole table while it is executed and it also commits at the end. Kind of you can't both have your cake and eat it at the same time. Once the DDL finished nothing would prevent other sessions from performing some DML (insert/update/delete) on that table.

I thought you wanted to have a table locked while you perform some DDL on other table(s).

Or, another way to implement some kind of locking mechanism in order to prevent process X to start while process Y is running is to use the facilities of the dbms_lock package (having the release_on_commit false allows commits and rollbacks in the session which acquired the lock, unlike lock table or select for update, which releases the lock on commit or update). But dbms_lock is far from being something that may prevent any session from performing some actions and may be used only for very specific purposes.

I think your issue is that the schemas, users, objects and grants are not well designed for what you actually need and that leads you to put the problem the way you do.

So, the classic way for handling such issues is to have the tables for which you wish the operations to be restricted under some conditions placed in a schema, and in that schema, have some packages that provide stored procedures which actually include the DML on those tables, and all the other users would not have grants on the tables themselves, but on those packages. So you may enforce complex business logic and required processing flows via PL/SQL packages.

BluShadow

Depending on what you want to actually do (in detail) you could also use the renaming of tables... i.e. table is renamed to another name (effectively table no longer exists to users), amendments are done on the renamed table and then, once complete the table is renamed back to the original name.

Of course that can have complications if there are referential constraints and suchlike, but without knowing the detail or how complex a task is actually required it's hard to say.

Usually though, if updates are required that include preventing user access, then you have scheduled 'downtime' on the system, where users are prevented from accessing the entire database (through whatever means is most suitable) and then only brought back 'online' for users when the updates are complete.

Solomon Yakobson

I have a requrement, want my table to lock until some operations with in table creations are done. (Ex : altering fields, data types etc.. thru an automation). During the table creation in case of any failures occurs, table should be in locked from all DML operation for all users.

Something doesn't add up. If any failurte occurs while creating table then table is not created and nobody can issue DML/DDL against it. In fact nobody can ssue DML/DDL against it even while table is being created. Session 1:

drop table test purge
/
create table test(id number)
/
create or replace
  trigger after_alter
  after alter
  on database
  begin
      if ora_dict_obj_owner = 'SCOTT'
        then
          dbms_lock.sleep(60);
      end if;
end;
/
alter table test
  add name varchar2(10)
/

So we wait 60 seconds before completing ALTER to mimc situation where DML is issued while we are altering table. And while we alter the table Session 2:

insert
 into test
 values(1)
/
  into test
       *
ERROR at line 2:
ORA-00947: not enough values

Elapsed: 00:00:50.31
SQL>

As you can see, session 2 waited till ALTER is done and then failed since now tabke has 2 columns.

Anyway, you need to explain in detail what are you trying to do.

SY.