oracle的一些记录

1、恢复数据

flashback table UF_DRZRY to timestamp to_timestamp('2022-06-20 11:37:11','yyyy-mm-dd hh24:MI:SS');

alter table UF_DRZRY enable row movement;

2、删除的表

select * from user_recyclebin t where t.original_name= upper('uf_person_kq_check');

恢复表

FLASHBACK TABLE uf_person_kq_check TO BEFORE DROP;

3、查看表被数据库中哪些object使用

select * from dba_dependencies  where referenced_name = upper('formtable_main_293')

4、查询执行最慢的sql

select *
 from (select sa.SQL_TEXT,
        sa.SQL_FULLTEXT,
        sa.EXECUTIONS "执行次数",
        round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
        round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
        sa.COMMAND_TYPE,
        sa.PARSING_USER_ID "用户ID",
        u.username "用户名",
        sa.HASH_VALUE
     from v$sqlarea sa left join all_users u
      on sa.PARSING_USER_ID = u.user_id
     where sa.EXECUTIONS > 0
     order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
 where rownum <= 50;

5、查询次数最多的 sql

select *
 from (select s.SQL_TEXT,
        s.EXECUTIONS "执行次数",
        s.PARSING_USER_ID "用户名",
        rank() over(order by EXECUTIONS desc) EXEC_RANK
     from v$sql s
     left join all_users u
      on u.USER_ID = s.PARSING_USER_ID) t
 where exec_rank <= 100;

6、查询某个表被哪些存储过程引用

SELECT * from user_source a where upper(text) like '%table_name%';

7、查锁表：

SELECT
	sess.sid,
	sess.serial#,
	lo.oracle_username, -- 登陆账号名称
	lo.os_user_name,    -- 登录电脑名称
	ao.object_name,     -- 被锁表名
	lo.locked_mode      -- 死锁级别
FROM
	v$locked_object lo,
	dba_objects ao,
	v$session sess 
WHERE
	ao.object_id = lo.object_id 
	AND lo.session_id = sess.sid;

8、解锁

--释放SESSION SQL:  
--alter system kill session 'sid, serial#';  
ALTER system kill session '235, 49711';

9、查看引起锁表的sql语句

SELECT
	A.USERNAME,A.MACHINE,
	A.PROGRAM,A.SID,
	A.SERIAL#,A.STATUS,
	C.PIECE,C.SQL_TEXT  
FROM
	V$SESSION A,V$SQLTEXT C  
WHERE
	A.SID IN ( SELECT DISTINCT T2.SID FROM V$LOCKED_OBJECT T1, V$SESSION T2 WHERE T1.SESSION_ID = T2.SID )  
	AND A.SQL_ADDRESS = C.ADDRESS ( + )  
ORDER BY C.PIECE;

10、跨服务器访问（创建数据链）

查看用户是否有创建 dblink 的权限：

select * from user_sys_privs where privilege like upper('%DATABASE LINK%');

给用户分配创建link权限

grant create  database link to 用户名

创建链接方法

CREATE DATABASE LINK 链接名          
CONNECT TO  服务器用户名 IDENTIFIED BY 服务器密码
USING '(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS = (PROTOCOL = TCP)(HOST = 登录服务器ip地址)(PORT = 1521))
	)
	(CONNECT_DATA =
		(SERVER = DEDICATED)
		(SERVICE_NAME = 服务名)
	)
)';

创建链接后可以通过下面语句查询

select * from 表名@链接名

查询CLOB字段报错

--创建临时表    
create table temp_test as select * from test@linkoracle where  id =: ID ;
--将远程数据插入到临时表中
insert into temp_test select * from test@linkoracle where id = : ID;
--将临时表数据插入到目标数据库表中
insert into test select * from test_temp;
--提交
commit;    
--查询
select * from test_temp;

29、Oracle获取awr报告步骤：

a、ssh中切换到oracle数据库：su - oracle

b、连接进入数据库：sqlplus / as sysdba

c、输入命令：@?/rdbms/admin/awrrpt.sql

d、直接enter，因为默认html

e、选择awr生成的日期：

num.days：1（1为今天）

f、分别选择开始和结束的pid，pid为具体生成的日志编号

g、可用sftp直接从oracle的目录下拷贝出来，然后用浏览器打开

awr默认目录：/home/oracle/awr***.html