oracle系统查询~3

查看实例的基本信息

SQL> col  host_name  for  a25
col  instance_name  for  a15
col  version  for  a15
col  status for a10
set  linesize  600
col  host_name  for  a20
select  instance_number,instance_name,host_name,version,startup_time,status,archiver  from  v$instance;SQL> SQL> SQL> SQL> SQL> SQL> INSTANCE_NUMBER INSTANCE_NAME	HOST_NAME	     VERSION	     STARTUP_T STATUS	  ARCHIVE
--------------- --------------- -------------------- --------------- --------- ---------- -------1 orcl		11g		     11.2.0.4.0      15-MAY-23 OPEN	  STARTEDselect  dbid,DATABASE_ROLE,GUARD_STATUS,DATAGUARD_BROKER,CREATED,name,db_unique_name,open_mode,LOG_MODE,ARCHIVELOG_CHANGE#   from  v$database;DBID DATABASE_ROLE    GUARD_S DATAGUAR CREATED   NAME	 DB_UNIQUE_NAME 		OPEN_MODE	     LOG_MODE	  ARCHIVELOG_CHANGE#
---------- ---------------- ------- -------- --------- --------- ------------------------------ -------------------- ------------ ------------------
1663010567 PRIMARY	    NONE    DISABLED 27-APR-23 ORCL	 orcl				READ WRITE	     ARCHIVELOG 	     1318273

查看asm磁盘组空间容量信息

SQL> col  name  for  a15
col  state  for  a15
select  group_number,name,state,type,free_mb/1024,total_mb/1024  from  v$asm_diskgroup_stat;SQL> SQL> GROUP_NUMBER NAME	     STATE	     TYPE   FREE_MB/1024 TOTAL_MB/1024
------------ --------------- --------------- ------ ------------ -------------1 OCR_VOTE	     MOUNTED	     EXTERN    1.5390625    2.006835942 DATA	         MOUNTED	     EXTERN   7.85742188    9.981445314 FRA	         MOUNTED	     EXTERN   1.71386719    3.009765633 NEW_SHAREDISK   MOUNTED	     EXTERN   7.79785156    7.99707031SQL> set line 200
col used_pct for a20
select group_number,name,trunc(total_mb/1024) total_gb,trunc(free_mb/1024) free_gb,
trunc((total_mb-free_mb)/1024) used_gb,round((1-free_mb/total_mb),4)*100||'%' used_pct,state,type from v$asm_diskgroup;
select  name,stateSQL> SQL>   2    from  v$asm_diskgroup;
GROUP_NUMBER NAME	       TOTAL_GB    FREE_GB    USED_GB USED_PCT		   STATE	   TYPE
------------ --------------- ---------- ---------- ---------- -------------------- --------------- ------1 OCR_VOTE		      2 	 1	    0 23.31%		   MOUNTED	   EXTERN2 DATA		          9 	 7	    2 21.28%		   MOUNTED	   EXTERN4 FRA		          3 	 1	    1 43.09%		   MOUNTED	   EXTERN3 NEW_SHAREDISK	      7 	 7	    0 2.49%		   MOUNTED	   EXTERN

查看数据总量大小

SQL> select sum(bytes/1024/1024/1024) from dba_data_files;SUM(BYTES/1024/1024/1024)
-------------------------1.51855469

查看数据库系统当前时间

SQL> SQL>  alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';Session altered.SQL> SQL> select  sysdate  from  dual;SYSDATE
-------------------
2023-05-15 04:12:03

查看备库归档应用情况

SQL> set  linesize  600  pagesize  100
SQL> select thread#,max(sequence#) from v$log_history group by thread#;THREAD# MAX(SEQUENCE#)
---------- --------------1	       792	       774	    	93	       10
备库上查看同步过来的归档日志的应用情况SQL> col name for a80
select name,SEQUENCE#,APPLIED from v$archived_log order by sequence#;SQL> NAME										  SEQUENCE# APPLIED
-------------------------------------------------------------------------------- ---------- ---------
+FRA/prod/archivelog/2021_06_14/thread_2_seq_1.261.1075182801				  1 NO
+FRA/prod/archivelog/2021_07_30/thread_4_seq_1.334.1079238051				  1 NO
+FRA/prod/archivelog/2021_07_30/thread_3_seq_1.331.1079236245				  1 NO查看备库归档日志gap状态
select  *  from  v$archive_gap;
select  *  from  gv$archive_gap;查看备库归档日志gap状态
select  *  from  gv$archive_gap;

会话状态

select inst_id,status, count(*) from gv$session group by inst_id,status;INST_ID STATUS     COUNT(*)
---------- -------- ----------1 INACTIVE	     22 ACTIVE	    422 INACTIVE	     11 ACTIVE	    43SQL> select inst_id,status,sql_id,count(*) from gv$session group by inst_id,status,sql_id order by 1,2 ;INST_ID STATUS   SQL_ID	    COUNT(*)
---------- -------- ------------- ----------1 ACTIVE   0kkhhb2w93cx0	   11 ACTIVE   5t22uhfka2mz3	   21 ACTIVE			  401 INACTIVE 4qm8a3w6a1rfd	   11 INACTIVE			   12 ACTIVE   5t22uhfka2mz3	   12 ACTIVE			  432 INACTIVE			   1SQL> select inst_id,status,count(*) from gv$session where type='USER' group by inst_id,status order by 1,2;INST_ID STATUS     COUNT(*)
---------- -------- ----------1 ACTIVE	     21 INACTIVE	     22 ACTIVE	     12 INACTIVE	     1

查看oracle连接数和会话情况

SQL> select  b.MACHINE,b.PROGRAM,b.sql_id,count(*) from v$process a,v$session b where a.ADDR=b.PADDR and  b.USERNAME is not null   group by  b.MACHINE, b.PROGRAM,b.sql_id  order by count(*) asc;MACHINE 							 PROGRAM					  SQL_ID	  COUNT(*)
---------------------------------------------------------------- ------------------------------------------------ ------
node1								 sqlplus@node1 (TNS V1-V3)			      5shdyn4p4f564 	 1
node1								 oraagent.bin@node1 (TNS V1-V3) 		  4qm8a3w6a1rfd 	 1
node1								 oraagent.bin@node1 (TNS V1-V3) 				        	 1SQL> select  b.sql_id,count(*) from v$process a,v$session b where a.ADDR=b.PADDR and  b.USERNAME is not null  and status='ACTIVE'   group by  b.sql_id  order by count(*) asc;SQL_ID		       COUNT(*)
-------------   ----------
aqq30ftctuuzc	         1SQL> select event,sql_id,count(*) from v$session group by event,sql_id order by 3;EVENT								 SQL_ID 	 COUNT(*)
---------------------------------------------------------------- ------------- ----------
PING										        	1
ASM background timer									1
SQL*Net message from client								1
ges remote message							    		1
smon timer									        	1
VKTM Logical Idle Wait									1
SQL*Net message from client					 4qm8a3w6a1rfd		1
Streams AQ: waiting for time management or cleanup tasks				1
pmon timer									                	1
Streams AQ: qmn coordinator idle wait							1
SQL*Net message to client					 g55gbwms27grw		1
Space Manager: slave idle wait			     					1
gcs remote message							            		1
GCR sleep								                		1
wait for unread message on broadcast channel						2
DIAG idle wait								            		2
jobq slave wait 								            	2
Streams AQ: qmn slave idle wait 					    		2
rdbms ipc message								                23kill掉非活动会话
SQL> select 'alter system kill session '''||s.sid||','||s.SERIAL#||''' immediate;'
from  v$session s where  s.status='INACTIVE';
'ALTERSYSTEMKILLSESSION'''||S.SID||','||S.SERIAL#||'''IMMEDIATE;'
------------------------------------------------------------------------------------------------------------------------
alter system kill session '17,11' immediate;
alter system kill session '146,7' immediate;

执行次数最高和执行时间达到阈值的sql

select *from (select 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_VALUEfrom v$sqlarea saleft join all_users uon sa.PARSING_USER_ID = u.user_idwhere sa.EXECUTIONS > 0order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)where rownum <= 50;查看指定会话中，执行时间超过阈值的sql
select inst_id,status,sql_id,count(*) from gv$session group by inst_id,status,sql_id order by 1,2 ;
SELECT A.USERNAME,A.SQL_ID,A.SQL_PLAN_OPERATION,A.SQL_PLAN_OPTIONS,A.ELAPSED_SECONDS,COUNT(1)FROM V$SESSION_LONGOPS A, V$SQL BWHERE A.START_TIME >= TRUNC(SYSDATE) - 10/24/60AND A.SQL_ID = B.SQL_IDAND A.USERNAME NOT IN ('SYS', 'SYSTEM')AND A.ELAPSED_SECONDS >=1and a.sql_id='5shdyn4p4f564'GROUP BY A.USERNAME,A.SQL_ID,A.SQL_PLAN_OPERATION,A.SQL_PLAN_OPTIONS,A.ELAPSED_SECONDSorder by A.ELAPSED_SECONDS asc;

查看ORACLE当前用户连接数：

--统计每个用户下的连接个数  
select username,count(username) from v$session where username is not null group by username; 
SQL> 
USERNAME		       COUNT(USERNAME)
------------------------------ ---------------
SYSTEM					     1
SCOTT					     2
SYS					     3SQL> ----统计某个用户的当前连接总数：
select  count(*)  from  v$session  where  username='SCOTT';SQL> COUNT(*)
----------2SQL> ----显示连接明细：
select saddr,sid,serial#,paddr,username,status from v$session where username is null;  
col  username  for  a15
col  PROGRAM  for  a20
col  MACHINE  for  a20
col  action  for  a20
col  service_name  for  a15
select count(*),sid,serial#,uSQL> sername,LOGON_TIME,PROCESS,MACHINE,PROGRAM,ACTION,SERVICE_NAME from gv$session where username is not null and status='INACTIVE';
SADDR			SID    SERIAL# PADDR		USERNAME		       STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
0000000097ADB750	  1	     1 00000000978A9C20 			       ACTIVE
0000000097AD8670	  2	     1 00000000978ABD90 			       ACTIVE
0000000097AD5590	  3	     1 00000000978ADF00 			       ACTIVE
0000000097AD24B0	  4	     1 00000000978B0070 			       ACTIVE
0000000097ACF3D0	  5	     1 00000000978B21E0 			       ACTIVESQL> select sid,serial#,username,LOGON_TIME,MACHINE,PROGRAM,ACTION,SERVICE_NAME from v$session where username is null and status='INACTIVE'; no rows selectedSQL> select saddr,sid,serial#,paddr,username,status,logon_time from v$session where username is not null and status='INACTIVE';  SADDR			SID    SERIAL# PADDR		USERNAME	STATUS	 LOGON_TIME
---------------- ---------- ---------- ---------------- --------------- -------- -------------------
0000000097AAA950	 17	    11 00000000978C91B0 SYS	        	INACTIVE 2023-05-15 04:05:48
0000000097A92250	 25	    53 00000000978CB320 SYSTEM		INACTIVE 2023-05-15 04:41:40
0000000097A7FD10	 31	    61 00000000978D7BC0 SCOTT		INACTIVE 2023-05-15 04:46:34
000000009744C720	146	     7 00000000978D4998 SYS	         	INACTIVE 2023-05-15 04:05:48
000000009743A1E0	152	    61 00000000978D2828 SCOTT  		INACTIVE 2023-05-15 04:46:03select  *  from  v$session  where  username='SCOTT' order  by  machine desc;----快速响应处理kill连接数：
ps -ef |grep LOCAL=NO  |  wc  -l
ps -ef |grep LOCAL=NO|awk '{print $2}'|xargs kill -9

`

批量处理所有不活动的连接死连接：

set  pagesize  1500
select 'alter system kill session '''||s.sid||','||s.SERIAL#||''' immediate;'
from  v$session s where username is null and status='INACTIVE';SQL> select 'alter system kill session '''||s.sid||','||s.SERIAL#||''' immediate;' from  v$session s where status='INACTIVE';'ALTERSYSTEMKILLSESSION'''||S.SID||','||S.SERIAL#||'''IMMEDIATE;'
------------------------------------------------------------------------------------------------------------------------
alter system kill session '17,11' immediate;
alter system kill session '25,53' immediate;
alter system kill session '31,61' immediate;
alter system kill session '146,7' immediate;
alter system kill session '152,61' immediate;

查询慢sql

SELECT A.USERNAME,A.SQL_ID,A.SQL_PLAN_OPERATION,A.SQL_PLAN_OPTIONS,A.ELAPSED_SECONDS,COUNT(1)FROM V$SESSION_LONGOPS A, V$SQL BWHERE A.START_TIME >= TRUNC(SYSDATE) - 10/24/60AND A.SQL_ID = B.SQL_IDAND A.USERNAME NOT IN ('SYS', 'SYSTEM')AND A.ELAPSED_SECONDS >=10GROUP BY A.USERNAME,A.SQL_ID,A.SQL_PLAN_OPERATION,A.SQL_PLAN_OPTIONS,A.ELAPSED_SECONDSorder by A.ELAPSED_SECONDS;

查看数据量总量大小

SQL> select sum(bytes/1024/1024/1024) from dba_data_files;SUM(BYTES/1024/1024/1024)
-------------------------1.52832031[oracle@node1 ~]$ echo -e 'select sum(bytes/1024/1024/1024) from dba_data_files;' | sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon May 15 05:10:46 2023Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing optionsSQL> 
SUM(BYTES/1024/1024/1024)
-------------------------1.52832031

查看数据库版本信息，补丁版本信息

select  *  from  v$version;
col  ACTION_TIME    for  a30
col  ACTION         for  a10
col  BUNDLE_SERIES  for  a15
col  COMMENTS     for  a20
col  NAMESPACE     for  a20
col  VERSION        for  a20
select  *  from  dba_registry_history;
select version, id, bundle_series, comments from dba_registry_history; 
19			        RDBMS_19.10.0.0.0DBRUR_LINUX.X64_210712
19.0.0.0.0			Patch applied on 19.3.0.0.0: Release_Update - 190410122720
19.0.0.0.0			Patch applied from 19.3.0.0.0 to 19.10.3.0.0: Release_Update_Revision - 210712214337
如果数据库没有打过任何补丁，dba_registry_history字典表中没有bundle_series列，打过补丁后就会自动更新了有了。查询数据库补丁包信息
[oracle@rac2:/home/oracle]$$ORACLE_HOME/OPatch/opatch lsinventory  | grep 'Patch'Oracle Interim Patch Installer version 12.2.0.1.25
OPatch version    : 12.2.0.1.25
Patch  32923641     : applied on Tue Apr 25 18:54:15 CST 2023
Unique Patch ID:  24336134
Patch description:  "OCW RELEASE UPDATE REVISION 19.10.3.0.0 (32923641)"
Patch  32923627     : applied on Tue Apr 25 18:51:28 CST 2023
Unique Patch ID:  24340942
Patch description:  "Database Release Update Revision : 19.10.3.0.210720 (32923627)"
OPatch succeeded.

查看导入导出进程是否运行

set  linesize  600  pagesize  100
col  OWNER_NAME  for  a10
col  JOB_MODE  for  a10
col  OPERATION  for  a15
col  JOB_NAME  for  a20
col  state  for  a15
select * from dba_datapump_jobs;
----查看运行的导出进程信息：
select * from dba_datapump_jobs  where state='EXECUTING';
----杀掉导出导入进程：
expdp 'userid="/ as sysdba"' attach=JOB1   #根据实际情况确认自动生成JOB名字
expdp \'sys/passwd@ip:prot/name as sysdba\'  attach=SYS_EXPORT_FULL_01在命令行下输入 KILL_JOB
----删除历史不用的导入导出目录：
drop directory expdp_dump;
drop directory dir_dp;

查看数据库后台alert日志存放路径

SELECT P1.VALUE || '/diag/rdbms/' || lower(P2.VALUE) || '/' || P3.VALUE ||'/trace/alert_' || P3.VALUE || '.log'FROM V$PARAMETER P1, V$PARAMETER P2, V$PARAMETER P3WHERE P1.NAME = 'diagnostic_dest'AND P2.NAME = 'db_name'AND P3.NAME = 'instance_name';

归档

删除过期归档：
delete  expired  archivelog  all;归档检查：
crosscheck archivelog all;删除所有的归档：
delete noprompt archivelog all;

查看数据库中用户的基本信息

column  USERNAME  format  a25
column  TEMPORARY_TABLESPACE  format  a20
col  ACCOUNT_STATUS  for  a25
col  profile  for  a15
COL  DEFAULT_TABLESPACE  FOR  A20
select  username,user_id,account_status,LOCK_DATE,default_tablespace,temporary_tablespace,created,profile  from dba_users;
select  username,user_id,account_status,LOCK_DATE,default_tablespace,temporary_tablespace,created,profile  from dba_users  where  username='xxx';
select  username,user_id,account_status,LOCK_DATE,default_tablespace,temporary_tablespace,created,profile  from dba_users  where  username=upper('xxx');select  username,user_id,account_status,LOCK_DATE,default_tablespace,temporary_tablespace,created,profile  from dba_users  where  username in('xxx','xxx');
select  username,user_id,account_status,LOCK_DATE,default_tablespace,temporary_tablespace,created,profile  from dba_users  where  username=upper('xxx');select  username,user_id,account_status,LOCK_DATE,default_tablespace,temporary_tablespace,created,profile  from dba_users  where  username  like  '%xxx%';select username,account_status,password_versions from dba_users where username='xxx';收集指定用户的统计信息
exec dbms_stats.gather_schema_stats('xxx',estimate_percent=>50,method_opt=>'for all columns size auto',degree=>4,cascade=>true);

查询Oracle库中一个用户使用了哪些表空间

有时候一个用户使用了多个表空间，而在数据库翻新时有可能因为表空间不存在而报错，
因此在导入之前应该查一下表空间是否存在。select distinct tablespace_name from dba_segments where owner='user_name';

DBA权限

查看哪些用户有sysdba权限
select * from V$PWFILE_USERS;查看哪些用户被授予了DBA权限:
select * from dba_role_privs where granted_role='DBA';用户权限检查（查看）
select * from dba_sys_privs where grantee='xxx';
col  grantee  for  a20
col  granted_role  for  a20
select * from dba_role_privs where grantee='xxx';
select grantee,owner,table_name,privilege from dba_tab_privs where grantee='xxx';
----查看角色拥有的权限信息：
select  *  from  role_sys_privs  where  role='xxx';
select  *  from  dba_sys_privs  where  GRANTEE='xxx';