ORACLE 管理员常用脚本

最新推荐文章于 2024-08-05 17:42:49 发布

爱学习的尤工

最新推荐文章于 2024-08-05 17:42:49 发布

阅读量427

点赞数

文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/youp121/article/details/130976106

版权

这些脚本主要用于监控Oracle数据库的活动，包括查询执行超过1分钟的SQL，获取完整SQL文本，检查正在运行的脚本，杀死会话，查看锁定对象，修改用户密码，以及分析性能消耗最大的SQL等。此外，还涉及到了用户权限管理和对象权限检查。

摘要由CSDN通过智能技术生成

/*数据库管理脚本*/

-----1、查询正在执行时长超1分钟的脚本情况-------------------
 select sid,serial#,a.sql_id,A.SQL_EXEC_START 开始时间,ROUND((SYSDATE-A.SQL_EXEC_START)*24*60,2) 已执行时间分钟,machine 来源机台,program 来源程序,
b.SQL_FULLTEXT 完整SQL
from v$session a,V$SQLAREA B 
where a.STATUS='ACTIVE'
 AND A.USERNAME is not null
  and A.SQL_ID=B.SQL_ID
 AND ROUND((SYSDATE-A.SQL_EXEC_START)*24*60,2)>1;
 
   -----1、查询正在执行时长超1分钟的脚本情况-------------------
 select sid,serial#,a.sql_id,A.SQL_EXEC_START 开始时间,ROUND((SYSDATE-A.SQL_EXEC_START)*24*60,2) 已执行时间分钟,machine 来源机台,program 来源程序,
b.SQL_FULLTEXT 完整SQL
from v$session a,V$SQLAREA B 
where A.SQL_ID=B.SQL_ID
 AND ROUND((SYSDATE-A.SQL_EXEC_START)*24*60,2)>1;
 
 
 
 select b.SQL_FULLTEXT from V$SQLAREA B 
where B.SQL_ID ='0m2gk84ct7p32'
 
 ----------查询完整SQL-------------
 select sql_fulltext from v$sql where sql_id='3t60xxpp5r3g0';

select sid,serial#,a.*,a.sql_id,A.SQL_EXEC_START 开始时间,ROUND((SYSDATE-A.SQL_EXEC_START)*24*60,2) 已执行时间分钟,machine 来源机台,program 来源程序,
b.SQL_FULLTEXT 完整SQL
from v$session a,V$SQLAREA B 
where B.SQL_FULLTEXT LIKE '%BP_MACHINE_STATUS_LOG%'
 --AND A.USERNAME is not null
  and A.SQL_ID=B.SQL_ID;
 --AND ROUND((SYSDATE-A.SQL_EX_START)*24*60,2)>1;
 
 
 select sid,serial#,a.sql_id,A.SQL_EXEC_START 开始时间,ROUND((SYSDATE-A.SQL_EXEC_START)*24*60,2) 已执行时间分钟,machine 来源机台,program 来源程序,
b.SQL_FULLTEXT 完整SQL
from v$session a,V$SQLAREA B 
where B.SQL_FULLTEXT LIKE '%collect_baking_data%'
 --AND A.USERNAME is not null
  and A.SQL_ID=B.SQL_ID
 

-- and a.SQL_EXEC_START BETWEEN TO_TIMESTAMP ('2022-11-11 06:30:00', 'YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP ('2022-11-11 06:35:00', 'YYYY-MM-DD HH24:MI:SS');
 
 -- 1 查询正在执行的sql语句

select b.sid, b.username, b.serial#, a.spid, b.paddr, c.sql_text, b.machine

from v$process a, v$session b, v$sqlarea c

where a.addr = b.paddr

and b.sql_hash_value = c.hash_value;

-----------查看在运行的脚本----
select * from dba_jobs_running;

 

-- 2 根据sid与serial# kill 需要中止的sql语句

 select sql_text, b.sid,b.serial#, 'alter system kill session'''||b.SID||','||b.serial#||''';',b.username
from v$process a, v$session b, v$sqlarea c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value 
 

-- 3 查询oracle当前被锁对象

select c.sql_id,a.session_id,c.SQL_EXEC_START, c.serial#, a.locked_mode, a.oracle_username, a.os_user_name, c.machine,
       c.terminal, b.object_name, c.logon_time,D.SQL_FULLTEXT 
from   v$locked_object a, all_objects b, v$session c,V$SQLAREA D
where  a.object_id = b.object_id and a.session_id = c.sid AND C.SQL_ID=D.SQL_ID
order  by c.sid, c.serial#; 

----3、根据sid查出对应的session;
select SID,SERIAL#,SQL_EXEC_START from V$Session where STATUS='ACTIVE' AND ROUND((SYSDATE-SQL_EXEC_START)*24*60,2)>1;

-- 查看被锁的表
SELECT l.session_id sid,
s.serial#,
S.STATE,
S.PREV_EXEC_START,
ROUND((SYSDATE-S.SQL_EXEC_START)*24*60,2),
S.STATUS,
l.locked_mode 锁模式,
l.oracle_username 登录用户,
l.os_user_name 登录机器用户名,
s.machine 机器名,
s.terminal 终端用户名,
o.object_name 被锁对象名,
s.logon_time 登录数据库时间
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
AND S.STATUS='ACTIVE'
ORDER BY logon_time;


-----4、kill对应的session;
alter system kill session '5794,49447';
 
 
 --------------查询备份数据----------------、
 SELECT *
  FROM T_WM_JOURNAL
AS OF TIMESTAMP TO_TIMESTAMP ('2022-05-20 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
       WHERE JOURNALID   IN  ( 'NR2022051901714', 'NR2022051601290', 'NR2022051601300', 'NR2022051600649');
       
/* 查看JOB执行情况脚本，  flat 0=有效，1=无效 */
SELECT
j.JOB JOB编号,
j.LOWNER 所属用户,
j.LAST_DATE 最后运行时间,
ROUND(j.TOTAL,2) 运行总耗时,
j.NEXT_DATE 下次运行时间,
j.INTERVAL# 执行频率,
j.FAILURES 失败次数,
j.FLAG 状态,
j.WHAT 备注 
FROM  sys.job$ j ORDER BY FLAG;

SELECT * FROM DBA_JOBS;

SELECT * FROM DBA_JOBS_RUNNING;


----------修改用户密码-----
alter user tfadmin identified by lw2022#11;

-----------查询触发器内容----------
select * from all_triggers where (owner='LW' OR OWNER='LWPRO');  

select * from all_source where (owner='LW' OR OWNER='LWPRO') And text like '%CUX_PO_LABEL_PRINT_V%';

----------查询存储过程------------------
SELECT *  FROM user_source  WHERE text like '%BP_MACHINE_STATUS_LOG%';

--当前执行sql语句
select a.SID,
       a.SERIAL#,
       a.USERNAME,
       b.PARSE_CALLS,
       b.PARSING_SCHEMA_NAME,
       b.CPU_TIME/1000000,
       b.ELAPSED_TIME/1000000,
       b.DISK_READS,
       b.DIRECT_WRITES,
       b.BUFFER_GETS,
       a.event,
        b.sql_text,
        b.SQL_FULLTEXT
  from v$session a inner join v$sqlarea b
 on a.SQL_HASH_VALUE = b. hash_value and b.PARSING_SCHEMA_NAME=upper('smsdb');

--物理读最高sql语句

select a.USERNAME,                                                     
       a. USER_ID,
       b.PARSE_CALLS,
       b.PARSING_SCHEMA_NAME,
       b.CPU_TIME/1000000,
       b.ELAPSED_TIME/1000000,
       b.DISK_READS,
       b.DIRECT_WRITES,
       b.BUFFER_GETS,
        b.sql_text,
        b.SQL_FULLTEXT
  from dba_users a inner join v$sqlarea b
 on a.USER_ID = b.PARSING_USER_ID and b.PARSING_SCHEMA_NAME=upper('smsdb') and disk_reads>1000000;


--查询前10名执行最多次数SQL语句
select sql_text "SQL语句", executions "执行次数"
  from (select sql_text,
               executions,
               rank() over
               
               (order by executions desc) exec_rank
          from v$sqlarea)
 where exec_rank <= 10;
  
 
 --查询前10名占用CPU最高的SQL语句  
 select sql_text "SQL语句",
                       c_t "SQL执行时间（秒）",executions "执行次数",cs "每次执行时间（秒）"　from (select sql_text,
                       cpu_time /1000000 c_t,executions,ceil(executions/(cpu_time/1000000))cs,
                       rank() over(order by cpu_time desc) top_time
                  from v$sqlarea) where  top_time <= 10

--查询前10名执行时间最长SQL语句                  
select sql_text "SQL语句",
                       c_t "处理时间(秒)",executions "执行次数",cs "每次执行时间（秒）"
                from (select sql_text,
                       ELAPSED_TIME / 1000000 c_t,executions,ceil(executions/(ELAPSED_TIME/1000000))cs,
                       rank() over(order by ELAPSED_TIME desc) top_time
                  from v$sqlarea) where  top_time <= 10

--查询前10名最耗资源SQL语句                  
select sql_text "SQL语句",
                       DISK_READS "物理读次数",cs "每次执行时间（秒）"
                from (select sql_text,
                       ELAPSED_TIME / 1000000 c_t,executions,ceil(executions/(ELAPSED_TIME/1000000))cs,DISK_READS,
                       rank() over(order by DISK_READS desc) top_disk
                  from v$sqlarea) where  top_disk <= 10

 
--查询前10名最耗内存SQL语句                 
select sql_text "SQL语句",
                       BUFFER_GETS "内存读次数",cs "每次执行时间（秒）"
                from (select sql_text,
                       ELAPSED_TIME / 1000000 c_t,executions,ceil(executions/(ELAPSED_TIME/1000000))cs,BUFFER_GETS,
                       rank() over(order by BUFFER_GETS desc) top_mem
                  from v$sqlarea) where  top_mem <= 10

--查看锁表语句

 Select
c.sid,
c.serial#,
d.name,
b.object_name,
c.username,
c.program,
c.osuser
from gv$Locked_object a, All_objects b, gv$session c, audit_actions d
where a.object_id = b.object_id
and a.inst_id = c.inst_id(+)
and a.session_id = c.sid(+)
and c.command = d.action;

-----------数据库增加表字段--------
ALTER TABLE cm_log ADD (cm_cause VARCHAR2(100));


-----------
--首先查到到所需修改用户名称的用户需要：
select * from sys.user$ where name='MS2105258004';--(回车)如：user#等于66
现在就可以修改用户名称了：update user$ set name='新的用户名称' where user#=66;(回车)
系统会提示：1 row updated.说明修改成功，
再输入：commit；（回车）提交所作修改。

----修改用户密码---
ALTER USER 'MS2105258004' IDENTIFIED BY 'lw610824%%';

alter user 160 identified by 123456;

alter user 'MS2105258004' IDENTIFIED BY 'lw610824%%';

SELECT A.FINAL_BLOCKING_SESSION,A.* FROM V$SESSION A WHERE A.FINAL_BLOCKING_SESSION IS NOT NULL;

       SELECT * FROM LWPRO.T_PRD_SFC A WHERE A.SFC_NO='SC229-YP2301020013-H-C0220003-03'

---------查看SQL执行时间-----
SELECT sid “SID”,
SERIAL#,
STATUS,
STATE,
ELAPSED_SECONDS “已经执行-秒”,
TIME_REMAINING “预计完成时间-秒”,
START_TIME 开始时间,
last_update_time 最后更新时间,
MACHINE 电脑号,
OSUSER 计算机用户,
PROGRAM 操作程序,
jindu “进度”,
SQL_TEXT
FROM (SELECT A.USERNAME,
A.SID,
A.SERIAL#,
A.OPNAME,
a.TARGET,
a.START_TIME,
a.last_update_time,
C.OSUSER,
C.MACHINE,
C.PROGRAM,
C.STATUS,
C.STATE,
ROUND(A.SOFAR * 100 / TOTALWORK, 0) || ‘%’ AS jindu,
A.TIME_REMAINING,
a.ELAPSED_SECONDS,
B.SQL_TEXT
FROM gV $SESSION_LONGOPS A, gV$ SQL B, gV$SESSION C
WHERE A.SQL_ADDRESS = B.ADDRESS
AND A.SQL_HASH_VALUE = B.HASH_VALUE
AND A.SID = C.SID
–AND C.STATUS = ‘ACTIVE’
– AND a.ELAPSED_SECONDS > 1
–AND a.last_update_time BETWEEN TRUNC (SYSDATE) + 9 / 24
– AND TRUNC (SYSDATE) + 21 / 24
)
WHERE jindu != ‘100%’;

-------230320查看用户权限表单及无权限表单权限开放----
select A.OWNER,A.TABLE_NAME,B.GRANTEE,B.privilege from all_tables a
left join DBA_TAB_PRIVS B ON A.TABLE_NAME=B.TABLE_NAME
where A.Table_Name=‘QCFC_BASE_CUSMODE’ GROUP BY A.OWNER,A.TABLE_NAME,B.GRANTEE,B.privilege;–AND B.GRANTEE=‘MES_READ’;–（查询当前数据库中的所有表）

   SELECT *　FROM 
   (SELECT AA.OWNER,AA.TABLE_NAME,BB.GRANTEE,BB.privilege FROM 
   (SELECT A.OWNER,A.TABLE_NAME FROM ALL_TABLES A WHERE A.OWNER IN('LWPRO','PM_USER')) AA
   LEFT JOIN
   (SELECT B.TABLE_NAME,B.GRANTEE,B.privilege FROM DBA_TAB_PRIVS B WHERE B.GRANTEE='MES_READ') BB 
   ON AA.TABLE_NAME=BB.TABLE_NAME) CC WHERE CC.GRANTEE IS NULL;

   SELECT 'grant select on '||owner||'.'|| table_name ||' to MES_READ;' from dba_tables where table_name in(SELECT TABLE_NAME　FROM 
   (SELECT AA.OWNER,AA.TABLE_NAME,BB.GRANTEE,BB.privilege FROM 
   (SELECT A.OWNER,A.TABLE_NAME FROM ALL_TABLES A WHERE A.OWNER IN('LWPRO','PM_USER')) AA
   LEFT JOIN
   (SELECT B.TABLE_NAME,B.GRANTEE,B.privilege FROM DBA_TAB_PRIVS B WHERE B.GRANTEE='MES_READ') BB 
   ON AA.TABLE_NAME=BB.TABLE_NAME) CC WHERE CC.GRANTEE IS NULL);
   
   SELECT * FROM all_tables A WHERE A.TABLE_NAME LIKE 'V_%' AND A.OWNER IN('LWPRO','PM_USER');
   
   SELECT * FROM all_tables A WHERE A.TABLE_NAME='QCFC_BASE_CUSMODE';
   
   SELECT * FROM LWPRO.QCFC_BASE_CUSMODE;  MES_READ MES_READ
   
   
   
   
   
   
   

   
   select table_name from user_tables;--（查询当前用户可以访问的表）
   
   select * from user_tab_privs where owner='LWPRO';


    select * from user_tables where owner='LWPRO';

select OWNER||‘.’||TABLE_NAME from all_all_tables where owner=‘LWPRO’
union
select OWNER||‘.’||TABLE_NAME from user_tab_privs where grantee=‘LWPRO’

------------e用户查看及管理--------
select * from dba_users; --查看所有用户
select * from all_users; --查看所有用户
select * from user_users; --查看当前用户

---------查看用户系统权限
SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM USER_SYS_PRIVS;

---------查看用户对象或角色权限
SELECT * FROM DBA_TAB_PRIVS A WHERE A.GRANTEE='MES_READ' AND A.OWNER='LWPRO';

SELECT * FROM ALL_TAB_PRIVS A WHERE A.GRANTEE='MES_READ';

SELECT * FROM USER_TAB_PRIVS;-----当前用户的表权限

--------查看所有角色
SELECT * FROM DBA_ROLES;

--------查看用户或角色所拥有的角色
SELECT * FROM DBA_ROLE_PRIVS A WHERE A.GRANTED_ROLE='MES_READ' AND A.GRANTEE='MS2103258028';
SELECT * FROM USER_ROLE_PRIVS;

/*按表名查找表单*/
select A.OWNER,A.TABLE_NAME,A.NUM_ROWS,A.LAST_ANALYZED from dba_tables A where table_name like '%BP_X_RAY%' ORDER BY NUM_ROWS DESC;


SELECT * FROM ALL_SOURCE WHERE TYPE='PROCEDURE'  AND TEXT LIKE '%WIP_DATACOLECTION%';