数据库操作技巧 之 oracle连表update、跨库查询、恢复被删除数据、解决锁表

1. 连表更新多个字段

   UPDATE '被修改的table' A -- 使用别名
   SET (
   	A .'被修改的字段A',
   	A .'被修改的字段B'
   ) = (
   	SELECT
   		b.'字段A',
   		b.'字段B'
   	FROM
   		'数据源table' b
   	WHERE
   		b.'关联字段' = A .'关联字段'
   )
   WHERE
   	EXISTS (
   		SELECT
   			1
   		FROM
   			'数据源table' b
   		WHERE
   			b.'关联字段' = A .'关联字段'
   	) 
   

2. 跨库查询
   • 建立连接
     • 方式一

     CREATE database link DATABASE_LINK_TEST --数据库别名
     CONNECT to DB_TEST identified BY DB_TEST --分别对应用户名和密码
     using '127.0.0.1:1512/ORCL'; --域名:端口号/数据库实例
     

     • 方式二

     CREATE DATABASE LINK 随便起个名字 CONNECT TO 用户名 IDENTIFIED BY 密码 USING '(DESCRIPTION =                       
         (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = 域名)(PORT = 端口号))
         )
         (CONNECT_DATA =
           (SERVICE_NAME = 数据库实例名称)
         )
       )';
     

   • 使用
     库名@连接名

   SELECT * FROM BASE_TEST@DATABASE_LINK_TEST
   

3. Oracle恢复删除数据 查询被删除前的时间节点的数据，使用plsql将被删除数据插入

   SELECT
   	*
   FROM
   	tableName AS OF TIMESTAMP TO_TIMESTAMP (
   		'2019-11-08 09:10:00',
   		'yyyy-MM-dd hh24:mi:ss'
   	)
   ) A
   

4. oracle 锁表解决方法
   当代码操作执行了很长时间了数据库口没有响应数据，直到超时,这时候有可能锁表了

• 查锁对象

  SELECT
  	object_name,
  	machine,
  	s. SID,
  	s.serial#
  FROM
  	gv$locked_object l,
  	dba_objects o,
  	gv$session s
  WHERE
  	l.object_id　 = o.object_id
  AND l.session_id = s. SID;
  

• 解锁,根据上面的语句查询出来的被锁对象的序列，杀死

  ALTER system kill session '3786, 12157';
  

• 查锁语句 根据序列查询被锁对象是因为操作哪个语句被锁的

  SELECT
  	b.sql_text
  FROM
  	v$session A,
  	v$sql b
  WHERE
  	A . SID = 3786 --session_id
  AND A .SQL_ADDRESS = b.ADDRESS (+);