asked on

oracle lock table

actually .. i wish lock whole table in my database to prevent the user to login in for the table in my database to select, insert, update ...and others.

The LOCK TABLE IN LOCK_MODE [NOWAIT] is not i want ...

How about the command for ALTER TABLE table_name ENABLE TABLE lock?

any option

Guy Hengel [angelIII / a3]

what you want is:
DENY SELECT, UPDATE, DELETE, INSERT FOR <username> ON <tablename>;

Guy Hengel [angelIII / a3]

actually, REVOKE and not DENY:
REVOKE SELECT, UPDATE, DELETE, INSERT FOR <username> ON <tablename>;

jrb1

Please delete that last question you entered. Keep your comments/responses all in a single question. (The question about lock modes is fine, but there seem to be three duplicate questions out there.)

pgsystech

ASKER

Lock whole table in oracle ...

rgrds
Gt

jrb1

Do you mean, lock all tables? What users? Even remove "select" access? What are you trying to accomplish?

MohanKNair

Use "SELECT FOR UPDATE statement"
https://www.experts-exchange.com/questions/21010749/SELECT-FOR-UPDATE-statement.html

Set transaction mode
SET TRANSACTION ISOLATION LEVEL <mode>
http://www.ss64.com/orasyntax/10locking.html

MohanKNair

Oracle Transactions
http://www.psoug.org/reference/transaction.html

Oracle DBMS_TRANSACTION
http://www.psoug.org/reference/dbms_transaction.html

pgsystech

ASKER

I like to lock one of the my tables in my database

Thank
GT

pgsystech

ASKER

Hi,
any opinion ..for me to lock the one of my tables in my database...

rgrds
tech

MohanKNair

A LOCK TABLE statement manually overrides default locking. The following statement acquires exclusive table locks for the EMP_TAB and DEPT_TAB tables.

LOCK TABLE Emp_tab, Dept_tab IN EXCLUSIVE MODE NOWAIT;

LOCK TABLE Emp_tab IN ROW SHARE MODE;
LOCK TABLE Emp_tab IN ROW EXCLUSIVE MODE;
LOCK TABLE Emp_tab IN SHARE MODE;

See this link
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10795/adfns_sq.htm#1024828

pgsystech

ASKER

The question is where i peform locking in my table , pls refer below :

SQL> LOCK TABLE HR.ABC IN EXCLUSIVE MODE;

The command, i perform once...can able to lock the table under schema name HR. However, when I log in as another username as TONG...

I able to select the table In HR schema and a I also can select the table .....

any Opnion to lock this table ABC under HR.schema. also, i and user also cannot perform select, insert,update in the particular locking table. Only Administrator got rights to done it.

I also did not want drop the table yet.. and lock the tables...

rgrds
GT

MohanKNair

It is not possible to lock the table for select statements.

pgsystech

ASKER

But the way.. Have any idea to turn off particular table in database?

rgrds
GT

ASKER CERTIFIED SOLUTION

MohanKNair

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pgsystech

ASKER

hI mohan,

Thank..for this option...I wish to use rename matter to solve it.

Rgrd
GT