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
pgsystechAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MohanKNairConnect With a Mentor Commented:
The best option is to rename the table. When it is required rename back to original name.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what you want is:
DENY SELECT, UPDATE, DELETE, INSERT FOR <username> ON <tablename>;

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, REVOKE and not DENY:
REVOKE SELECT, UPDATE, DELETE, INSERT FOR <username> ON <tablename>;
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
jrb1Commented:
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.)
0
 
pgsystechAuthor Commented:
Lock whole table in oracle ...

rgrds
Gt
0
 
jrb1Commented:
Do you mean, lock all tables?  What users?  Even remove "select" access?  What are you trying to accomplish?
0
 
MohanKNairCommented:
Use "SELECT FOR UPDATE statement"
http://www.experts-exchange.com/Databases/Oracle/Q_21010749.html

Set transaction mode
SET TRANSACTION ISOLATION LEVEL <mode>
http://www.ss64.com/orasyntax/10locking.html
0
 
MohanKNairCommented:
0
 
pgsystechAuthor Commented:
I like to lock one of the my tables in my database

Thank
GT
0
 
pgsystechAuthor Commented:
Hi,
any opinion ..for me to lock the one of my tables in my database...


rgrds
tech
0
 
MohanKNairCommented:
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
0
 
pgsystechAuthor Commented:
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
0
 
MohanKNairCommented:
It is not possible to lock the table for select statements.
0
 
pgsystechAuthor Commented:
But the way.. Have any idea to turn off particular table in database?

rgrds
GT
0
 
pgsystechAuthor Commented:
hI mohan,

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


Rgrd
GT
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.