There’s More than One Solution I would Love to Share :
1-Before 11g, you can mark the tablespace read-only for the duration of the alter table
Alter Tablespace Test read only
2-in 11g you can mark table read-only :
Alter table test Read only
And You can Check Read Only Tables By :
Select table_name, read_only from dba_tables
Where owner = ‘myowner’ and table_name = ‘mytab’;
Just To Make Sure After You Alter table to Read Only prevent Update by Create Trigger
create or replace trigger
tabl_read_only
before
insert or update or delete
on mytab
begin
raise_application_error (-999999, ‘Table Is Read Only Now’);
end; /
3- You Can Check if there’s Lock on your Database :
SQL> set linesize 130
SQL> set pages 100
SQL> col username format a20
SQL> col sess_id format a10
SQL> col object format a25
SQL> col mode_held format a10
SQL> select oracle_username || ‘ (‘ || s.osuser || ‘)’ username
, s.sid || ‘,’ || s.serial# sess_id
, owner || ‘.’ || object_name object
, object_type
, decode( l.block
, 0, ‘Not Blocking’
, 1, ‘Blocking’
, 2, ‘Global’) status
, decode(v.locked_mode
, 0, ‘None’
, 1, ‘Null’
, 2, ‘Row-S (SS)’
, 3, ‘Row-X (SX)’
, 4, ‘Share’
, 5, ‘S/Row-X (SSX)’
, 6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id
/
Use
SQL> alter system kill session ‘SID,Serial# ‘;
Enjoy
OSama Mustafa
Technology Geek 