Oracle
1、查询oracle的连接数
select count(*) from v$session;
2、查询oracle的并发连接数
4select count(*) from v$session where status='ACTIVE';
3、查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;
4、查看所有用户：
select * from all_users;
---用户登录不区分大小写
alter system set sec_case_sensitive_logon=false;
---用户解锁
alter user j2_hx account unlock;
alter user j2_hx identified by j2_hx;
grant dba,resource,connect to j2_hx;
5、查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
6、查看角色(只能查看登陆用户拥有的角色)所包含的权限
13select * from role_sys_privs;
7、查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
8、查看所有角色：
select * from dba_roles;
9、查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
10、查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS;
Linux
查询文件最后300行
tail -n 300 log.xml >> log300.txt
查询心跳
select * from SYS.DBA_HIST_SYSMETRIC_SUMMARY t
查询磁盘组使用情况
select
col1 as "磁盘组名称",
col2 as "磁盘组总大小",
col3 as "磁盘组剩余大小",
round(col4,4)*100||'%' as "磁盘组使用率"
from(
select asmgroup. NAME as col1,
asmgroup.TOTAL_MB as col2,
asmgroup.FREE_MB as col3,
asmgroup.COLD_USED_MB/asmgroup.TOTAL_MB as col4
from v$asm_diskgroup asmgroup
);
#查看oracle alter 日志(153.12.72.110核心01 alter)
cd $ORACLE_BASE/diag/rdbms/gzltgshx/gzltgshx1/trace
tail -300f alert_gzltgshx1.log
#查看oracle alter 日志(10.104.168.156接收01 alter)
cd $ORACLE_BASE//diag/rdbms/gzltscjs/gzltscjs1/trace
tail -300f alert_gzltscjs1.log
#查询数据库集群心跳
select BEGIN_TIME btime,
INTSIZE,
METRIC_NAME mrtric_name,
NUM_INTERVAL val,
MINVAL,
MAXVAL,
AVERAGE avgval,
STANDARD_DEVIATION std,
SUM_SQUARES sum_square
from dba_hist_sysmetric_summary
where METRIC_NAME in
('Network Traffic Volume Per Sec', 'I/O Megabytes per Second')
and rownum < 11
order by std desc;
select * from v$asm_diskgroup;
select col1 as "名称"，
col2 as "总大小"，
col3 as "剩余大小",
col4 as "使用比"
from(
select
a.NAME as col1,
a.TOTAL_MB as col2,
a.FREE_MB as col3,
round(nvl(a.COLD_USED_MB/a.TOTAL_MB,0),4)*100 || '%' as col4
from v$asm_diskgroup a
);
---查询死锁
select a.XIDUSN,
a.OBJECT_ID,
b.OWNER,
b.OBJECT_NAME,
a.SESSION_ID,
a.ORACLE_USERNAME,
a.OS_USER_NAME,
a.PROCESS,
a.LOCKED_MODE
from v$locked_object a, dba_objects b
where a.OBJECT_ID = b.OBJECT_ID
select count(*) from v$process --当前的连接数
select value from v$parameter where name = 'processes' --数据库允许的最大连接数
修改最大连接数:
alter system set processes = 300 scope = spfile;
重启数据库:
shutdown immediate;
startup;
--查看当前有哪些用户正在使用数据
SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;
select count(*) from v$session #连接数
select count(*) from v$session where status='ACTIVE'#并发连接数
show parameter processes #最大连接
alter system set processes = value scope = spfile;重启数据库 #修改连接
SQL> Select count(*) from v$session where status='ACTIVE' ;
COUNT(*)
----------
20
SQL> Select count(*) from v$session;
COUNT(*)
----------
187
SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ----------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 450
SQL>
并发指active,I SEE
SQL> select count(*) from v$session #连接数
SQL> Select count(*) from v$session where status='ACTIVE'#并发连接数
SQL> show parameter processes #最大连接
SQL> alter system set processes = value scope = spfile;重启数据库 #修改连接
unix 1个用户session 对应一个操作系统 process
而 windows体现在线程
DBA要定时对数据库的连接情况进行检查，看与数据库建立的会话数目是不是正常，如果建立了过多的连接，会消耗数据库的资源。同时，对一些“挂死”的连接，可能会需要DBA手工进行清理。
以下的SQL语句列出当前数据库建立的会话情况:
select sid,serial#,username,program,machine,status
from v$session;
输出结果为:
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---- ------- ---------- ----------- --------------- --------
1 1 ORACLE.EXE WORK3 ACTIVE
2 1 ORACLE.EXE WORK3 ACTIVE
3 1 ORACLE.EXE WORK3 ACTIVE
4 1 ORACLE.EXE WORK3 ACTIVE
5 3 ORACLE.EXE WORK3 ACTIVE
6 1 ORACLE.EXE WORK3 ACTIVE
7 1 ORACLE.EXE WORK3 ACTIVE
8 27 SYS SQLPLUS.EXE WORKGROUP\\WORK3 ACTIVE
11 5 DBSNMP dbsnmp.exe WORKGROUP\\WORK3 INACTIVE
其中，
SID 会话(session)的ID号;
SERIAL# 会话的序列号，和SID一起用来唯一标识一个会话;
USERNAME 建立该会话的用户名;
PROGRAM 这个会话是用什么工具连接到数据库的;
STATUS 当前这个会话的状态，ACTIVE表示会话正在执行某些任务，INACTIVE表示当前会话没有执行任何操作;
如果DBA要手工断开某个会话，则执行:
alter system kill session \'SID,SERIAL#\'
sql语句
SQL语句如下：
SELECT username, machine, program, status, COUNT (machine) AS
连接数量
FROM v$session
GROUP BY username, machine, program, status
ORDER BY machine;
显示结果（每个人的机器上会不同）
SCHNEIDER|WORKGROUD\WANGZHENG|TOAD.exe|ACTIVE|1
SCHNEIDER|WORKGROUP\597728AA514F49D|sqlplusw.exe|INACTIVE|1
|WWW-Q6ZMR2OIU9V|ORACLE.EXE|ACTIVE|8
PUBLIC|||INACTIVE|0
select USER,sid,serial#,UTL_INADDR.GET_host_ADDRESS as host,SYS_CONTEXT('USERENV','ip_ADDRESS') as local,SYSDATE
from V$session;
1 查看物理CPU的个数
#cat /proc/cpuinfo |grep "physical id"|sort |uniq|wc –l
2、 查看逻辑CPU的个数
#cat /proc/cpuinfo |grep "processor"|wc –l
3、 查看CPU是几核
#cat /proc/cpuinfo |grep "cores"|uniq
4、 查看CPU的主频
#cat /proc/cpuinfo |grep MHz|uniq
5、 # uname -a
6、 Linux euis1 2.6.9-55.ELsmp #1 SMP Fri Apr 20 17:03:35 EDT i686 i686 i386 GNU/Linux
(查看当前操作系统内核信息)
7、 # cat /etc/issue | grep Linux
8、 Red Hat Enterprise Linux AS release 4 (Nahant Update 5(查看当前操作系统发行版信息)
9、 # cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
8 Intel(R) Xeon(R) CPU E5410 @ 2.33GHz
(看到有8个逻辑CPU, 也知道了CPU型号)
9 # cat /proc/cpuinfo | grep physical | uniq -c
4 physical id : 0
4 physical id : 1
(说明实际上是两颗4核的CPU)
10、# getconf LONG_BIT
32
(说明当前CPU运行在32bit模式下, 但不代表CPU不支持64bit)
11、# cat /proc/cpuinfo | grep flags | grep ' lm ' | wc –l
8(结果大于0, 说明支持64bit计算. lm指long mode, 支持lm则是64bit)
12、如何获得CPU的详细信息：
linux命令：cat /proc/cpuinfo
13、用命令判断几个物理CPU，几个核等：
逻辑CPU个数：
# cat /proc/cpuinfo | grep "processor" | wc -l
物理CPU个数：
# cat /proc/cpuinfo | grep "physical id" | sort | uniq | wc -l
14、每个物理CPU中Core的个数：
# cat /proc/cpuinfo | grep "cpu cores" | wc -l
15、是否为超线程？如果有两个逻辑CPU具有相同的”core id”，那么超线程是打开的。每个物理CPU中逻辑CPU(可能是core, threads或both)的个数：
# cat /proc/cpuinfo | grep "siblings"
1.查看CPU信息命令
cat /proc/cpuinfo
AIX 查询SMLCTL
2.查看内存信息命令
cat /proc/meminfo
3.查看硬盘信息命令
fdisk -l
查看CPU信息（型号）
# cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
8 Intel(R) Xeon(R) CPU E5410 @ 2.33GHz
(看到有8个逻辑CPU, 也知道了CPU型号)
# cat /proc/cpuinfo | grep physical | uniq -c
4 physical id : 0
4 physical id : 1
(说明实际上是两颗4核的CPU)
PS：Jay added on 10th, May,
# 其实是可能有超线程HT技术，不一定是有4核，也可能是2核4线程；当时还理解不清楚
# getconf LONG_BIT
32
(说明当前CPU运行在32bit模式下, 但不代表CPU不支持64bit)
# cat /proc/cpuinfo | grep flags | grep ' lm ' | wc -l
8
(结果大于0, 说明支持64bit计算. lm指long mode, 支持lm则是64bit)
再完整看cpu详细信息, 不过大部分我们都不关心而已.
# dmidecode | grep 'Processor Information'
查看内 存信息
# cat /proc/meminfo
# uname -a
Linux euis1 2.6.9-55.ELsmp #1 SMP Fri Apr 20 17:03:35 EDT i686 i686 i386 GNU/Linux
(查看当前操作系统内核信息)
# cat /etc/issue | grep Linux
Red Hat Enterprise Linux AS release 4 (Nahant Update 5)
(查看当前操作系统发行版信息)
查看机器型号
# dmidecode | grep "Product Name"
查看网卡信息
# dmesg | grep -i eth
---RAC 节点脱离操作
清理RAC集群节点配置
/u01/app/11.2.0/grid/crs/install
./rootcrs.pl -verbose -deconfig -force
./roothas.pl -deconfig -force -verbose
查询oracle归档大小
show parameter db_recivery_file_dest_size;
select * from v$recovery_file_dest;
log_1203.zip
alterlog_1203.zip
----查询月增量
select C.tablespace_name AS "表空间名称",
D."Total(MB)" AS "总量(MB)",
D."Used(MB)" - C."Used(MB)" AS "增量(MB)",
to_char(next_day(trunc(sysdate),1)-40,'yyyy/mm/dd')||'--'||to_char(next_day(trunc(sysdate),1)-11,'yyyy/mm/dd') "时间范围"
from (select B.name tablespace_name,
case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024)
when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2)
END as "Total(MB)",
round(A.tablespace_usedsize*8 / 1024) "Used(MB)",
A.rtime
from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B
where A.tablespace_id = B.TS#
and to_char(to_date(replace(rtime, '/', null),
'mmddyyyy hh24:mi:ss'),
'yyyymmdd hh24:mi') =
to_char(next_day(trunc(sysdate),2)-14,'yyyymmdd hh24:mi')) C,
(select B.name tablespace_name,
case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024)
when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2)
END as "Total(MB)",
round(A.tablespace_usedsize*8 / 1024) "Used(MB)",
A.rtime
from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B
where A.tablespace_id = B.TS#
and to_char(to_date(replace(rtime, '/', null),
'mmddyyyy hh24:mi:ss'),
'yyyymmdd hh24:mi') =
to_char(next_day(trunc(sysdate),2)-7,'yyyymmdd hh24:mi')) D
where C.tablespace_name = D.tablespace_name
and C.tablespace_name like'TS%';
---清理归档日志
1、设置ORACLE环境变量 export ORACLE_SID=gzltgshx
2、使用命令rman 进入到数据库逻辑单元
3、connect target/
4、crosscheck archivelog all;
5、delete archivelog all completed before'sysdate -7';
6、yes
---表空间使用率
SELECT df.tablespace_name,
COUNT(*) datafile_count,
ROUND(SUM(df.BYTES) / 1048576 / 1024, 2) size_gb,
ROUND(SUM(free.BYTES) / 1048576 / 1024, 2) free_gb,
ROUND(SUM(df.BYTES) / 1048576 / 1024 -
SUM(free.BYTES) / 1048576 / 1024,
2) used_gb,
ROUND(MAX(free.maxbytes) / 1048576 / 1024, 2) maxfree,
100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,
ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free
FROM dba_data_files df,
(SELECT tablespace_name,
file_id,
SUM(BYTES) BYTES,
MAX(BYTES) maxbytes
FROM dba_free_space
where bytes > 1024 * 1024
GROUP BY tablespace_name, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+)
AND df.file_id = free.file_id(+)
GROUP BY df.tablespace_name
ORDER BY 8
----空间使用率
---表空间使用率
SELECT df.tablespace_name,
COUNT(*) datafile_count,
ROUND(SUM(df.BYTES) / 1048576 / 1024, 2) size_gb,
ROUND(SUM(free.BYTES) / 1048576 / 1024, 2) free_gb,
ROUND(SUM(df.BYTES) / 1048576 / 1024 -
SUM(free.BYTES) / 1048576 / 1024,
2) used_gb,
/* ROUND(MAX(free.maxbytes) / 1048576 / 1024, 2) maxfree,
100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,
ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free*/
ROUND((ROUND(SUM(df.BYTES) / 1048576 / 1024 -
SUM(free.BYTES) / 1048576 / 1024,
2))/(ROUND(SUM(df.BYTES) / 1048576 / 1024, 2)),2)*100 || '%' AS "使用比"
FROM dba_data_files df,
(SELECT tablespace_name,
file_id,
SUM(BYTES) BYTES,
MAX(BYTES) maxbytes
FROM dba_free_space
where bytes > 1024 * 1024
GROUP BY tablespace_name, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+)
AND df.file_id = free.file_id(+)
and df.TABLESPACE_NAME like'TS%'
GROUP BY df.tablespace_name
/* ORDER BY 8*/
10.07 19.14
09.56 19.35
---核心
SELECT df.tablespace_name,
ROUND(SUM(df.BYTES) / 1048576 / 1024, 2) size_gb,
ROUND(SUM(df.BYTES) / 1048576 / 1024 -
SUM(free.BYTES) / 1048576 / 1024,
2) used_gb,
/* ROUND(MAX(free.maxbytes) / 1048576 / 1024, 2) maxfree,
100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,
ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free*/
ROUND((ROUND(SUM(df.BYTES) / 1048576 / 1024 -
SUM(free.BYTES) / 1048576 / 1024,
2))/(ROUND(SUM(df.BYTES) / 1048576 / 1024, 2)),2)*100 || '%' AS "使用率"
FROM dba_data_files df,
(SELECT tablespace_name,
file_id,
SUM(BYTES) BYTES,
MAX(BYTES) maxbytes
FROM dba_free_space
where bytes > 1024 * 1024
GROUP BY tablespace_name, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+)
AND df.file_id = free.file_id(+)
and df.TABLESPACE_NAME like'TS%'
GROUP BY df.tablespace_name
/* ORDER BY 8*/
---查询
SELECT df.tablespace_name,
ROUND(SUM(df.BYTES) / 1048576 / 1024, 2) size_gb,
ROUND(SUM(df.BYTES) / 1048576 / 1024 -
SUM(free.BYTES) / 1048576 / 1024,
2) used_gb,
/* ROUND(MAX(free.maxbytes) / 1048576 / 1024, 2) maxfree,
100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,
ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free*/
ROUND((ROUND(SUM(df.BYTES) / 1048576 / 1024 -
SUM(free.BYTES) / 1048576 / 1024,
2))/(ROUND(SUM(df.BYTES) / 1048576 / 1024, 2)),2)*100 || '%' AS "使用率"
FROM dba_data_files df,
(SELECT tablespace_name,
file_id,
SUM(BYTES) BYTES,
MAX(BYTES) maxbytes
FROM dba_free_space
where bytes > 1024 * 1024
GROUP BY tablespace_name, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+)
AND df.file_id = free.file_id(+)
and df.TABLESPACE_NAME like'TS_GS%'
GROUP BY df.tablespace_name
/* ORDER BY 8*/
