ORACLE关于dbLink及锁表的常规操作

--查看DBlink
　　select owner,object_name from dba_objects where object_type='DATABASE LINK';

--删除DBlink
　　drop /* public */ database link dblink_name;

--创建DBlink
 　 create /* public */ database link imageuat
　　　connect to imageuat identified by imageuat
　　    using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 10.21.68.57)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

--查询DBLINK
　　select * from dba_db_links;

--创建同义词
　　Create synonym docmain_syn for ES_DOC_MAIN@imageuat;//DBlink
　  Create synonym docmain_syn for imageuat.ES_DOC_MAIN;//同库

--删除同义词
　　DROP SYNONYM docmain_syn;

--查询同义词
　　select * from dba_synonyms;

 

--查看锁表及解锁

　　SELECT
　　S.USERNAME,
　　DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
　　O.OWNER,
　　O.OBJECT_NAME,
　　O.OBJECT_TYPE,
　　S.SID,
　　S.SERIAL#,
　　S.TERMINAL,
　　S.MACHINE,
　　S.PROGRAM,
　　S.OSUSER
　　FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O
　　WHERE L.SID = S.SID
　　AND L.ID1 = O.OBJECT_ID(+)
　　--COLENDING 数据库用户
　　AND S.USERNAME ='COLENDING';

--解锁
　　-- SID,SERIAL#
　　ALTER SYSTEM KILL SESSION '606,14613';
　　ALTER SYSTEM KILL SESSION '641,51551';

 

--无法解锁查看是否需要通过服务端os解锁
        select a.spid,b.sid,b.serial#,b.username from v$process a,v$session b where a.addr=b.paddr and b.status='KILLED';