oracle日常运维
转：
1、查看数据文件信息：
col file_name for a55
select tablespace_name,file_name,bytes/1024/1024/1024 gb,AUTOEXTENSIBLE from dba_data_files where tablespace_name='KHST_ECIF';
2、查看ASM磁盘组信息：
select group_number,name,total_mb/1024 total_gb,free_mb/1024 free_gb,TYPE from v$asm_diskgroup;
---查看ASM磁盘均衡时间：
select * from v$asm_operation;
3、查看ASM磁盘组磁盘的信息
set lin 1000 pagesize 999
col PATH for a33
col NAME for a15
col FAILGROUP for a15
select
GROUP_NUMBER,DISK_NUMBER,TOTAL_MB/1024,FREE_MB/1024,NAME,FAILGROUP,PATH,FAILGROUP_TYPE
from v$asm_disk where GROUP_NUMBER='1';
3.1、查看表空间大小：
SELECT a.tablespace_name,round(total/1024/1024/1024) "Total g",
round(free/1024/1024/1024) "Free g",ROUND((total-free)/total,4)*100 "USED%"
FROM (SELECT tablespace_name,SUM(bytes) free FROM
DBA_FREE_SPACE
GROUP BY tablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY 4;
3.2、表空间内的大表
col TABLE_NAME for a30
set pagesize 200
set linesize 200
col TABLE_NAME for a30
set linesize 200
select * from (select TABLESPACE_NAME,OWNER,SEGMENT_NAME "TABLE_NAME",to_number(decode(substr(BYTES/1024/1024,1,1),'.','0'||BYTES/1024/1024,BYTES/1024/1024)) total_MB
from dba_segments where TABLESPACE_NAME ='&tablespacename' and SEGMENT_TYPE='TABLE' order by total_MB desc ) where rownum<=50;
4、统计活动的undo
select sum(bytes /(102410241024)) from dba_undo_extents where status='ACTIVE';
5、查看大于20M的文件
find / -type f -size +20M -print0 | xargs -0 du -h | sort -nr
6、查看shared_pool的大小
select sum(bytes)/1024/1024/1024 from v$sgastat where pool='shared pool';
查看空闲的：
select * from v$sgastat where name = 'free memory' and pool = 'shared pool';
7、查看占用内存100k的sql语句：
select sql_text ,sharable_mem from v$sql where sharable_mem > '100000' order by sharable_mem
8、查看字符集
select userenv('language') from dual;
select * from nls_database_parameters;
9、Oracle查询temp表空间的名字和位置
select tablespace_name,file_name from dba_temp_files;
col FILE_NAME for a55
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024/1024 total_gb,USER_BYTES/1021/1024/1024 gb from dba_temp_files;
Oracle查询temp表空间的使用率
select
tablespace_name,round(free_space/1024/1024/1024,2)
"free(GB)",round(tablespace_size/1024/1024/1024,2)
"total(GB)",round(nvl(free_space,0)*100/tablespace_size,3) "Free
percent"
from dba_temp_free_space;
10、查看版本
set line 150
col ACTION_TIME for a30
col ACTION for a8
col NAMESPACE for a8
col VERSION for a10
col BUNDLE_SERIES for a5
col COMMENTS for a20
select * from dba_registry_history;
11、查看补丁版本：
ZB23NXYD2:/app/product/11.2.0/db/OPatch$opatch lsinventory
12、查看锁表
SELECT l.session_id sid, s.serial#, 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
ORDER BY sid, s.serial# ;
查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句：
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
l.os_user_name,s.machine, o.object_name,s.terminal,a.sql_text, a.action
FROM v$sqlarea a,v$session s, v$locked_object l
WHERE l.session_id = s.sid
AND s.prev_sql_addr = a.address
ORDER BY sid, s.serial#;
查看视图对应的表：
select * from dba_dependencies where NAME='视图名' and TYPE='VIEW';
13、杀锁命令
alter system kill session 'sid,serial#'
15、查看表大小
select TABLESPACE_NAME,OWNER,SEGMENT_NAME,sum(BYTES)/1024/1024 total_mb
from dba_segments where TABLESPACE_NAME='CARDW02' group by TABLESPACE_NAME,OWNER,SEGMENT_NAME;
16、查看兼容版本（grid的）
select name,compatibility,database_compatibility from v$asm_diskgroup;
17、查看aix操作系统的资源情况
prtconf|more
lparstat -i
HP：machinfo
WIN：msinfo32
SUSE：cat /proc/cpuinfo （model name ）
---查看资源使用情况：
HP：glance/top
AIX：nmon/topas
---查看内存大小：
HP： /usr/contrib/bin/machinfo | grep -i Memory
AIX: /usr/sbin/lsattr -E -l sys0 -a realmem
---查看swap分区：
HP：/usr/sbin/swapinfo -a
AIX：/usr/sbin/lsps -s
18、新建用户
alter user mcms_rb account unlock identified by &PASSWORD;
查看表空间下的用户
select distinct s.owner from dba_segments s where s.tablespace_name ='TBSNAME'
19、查看数据量：
select sum(bytes)/1024/1024 mb from dba_segments;
20、查看REDOLOG大小
select group#,members,bytes/1024/1024,status from v$log;
21、清理垃圾文件
cd &DIR
find ./ -ctime +3 |xargs rm
22、ASM磁盘
--- 检查磁盘大小（单位M）
bootinfo -s hdisk0
--- 查看磁盘的详细信息
lsattr -El hdisk0
--- 检查权限
ls -l /dev/hdisk*
【排序查看ls -ltr /dev |grep rhdisk】
--- 检查PVID
lspv | grep hdiskn
--- 检查保留策略
lsattr -E -l hdisk5 | grep reserve_policy
--- 查看磁盘是否为共享磁盘
lsattr -El hdisk0
比对两个主机对应的磁盘号是否一致：unique_id
--- 查看磁盘是否可用
lspv
看PVID是否为none，若为none则数据库可用，再查看数据库当前有没有使用，若没有则可用来扩容ASM磁盘组
23、按用户查看占用多少内存
svmon -U grid -w |more
svmon -U oracle -w | more
24、查看用户下有多少进程
svmon -PO unit=GB |grep aioserver |wc
svmon -PO unit=GB |grep oracle |wc
lsvg |lsvg -i -p
lsdev -c disk
-----AWR报告
@?/rdbms/admin/awrrpt.sql
-----ASH报告
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
======================
1、修改LINUX操作系统/dev/shm文件系统大小
开机自启动：
tmpfs /dev/shm tmpfs defaults,size=20G 0 0
立即生效：
mount -t tmpfs shmfs -o size=20g /dev/shm
25、查看服务器底层用的存储类型：
lscfg -vpl hdisk40
26、生成AWR报告
@?/rdbms/admin/awrrpt.sql
27、查看ASM磁盘挂载时间：
set lines 500 pages 2000
col g_name format a10
col g_n format 99
col d_n format 999
col m_status format a7
col mo_status format a7
col h_status format a11
col name format a20
col path format a20
col failgroup format a15
select g.group_number g_n,
g.disk_number d_n,
g.name name,
g.failgroup,
g.mount_status m_status,
g.header_status h_status,
g.mode_status mo_status,
g.path ,
to_char(g.mount_date, 'YYYY/MM/DD HH24:MI:SS') m_date
from v$asm_disk g
order by g_n, d_n
28、查看某个用户所拥有的角色
select * from dba_role_privs where grantee='用户名';
29、查看某个角色所拥有的权限
select * from dba_sys_privs where grantee='CONNECT';
查看进程：
set pages 9999
set lines 200
select process,client_process,sequence#,thread#,status from v$managed_standby;
============================================
--查询数据库负载
set pages 9999
set lines 200
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SELECT *
FROM ( SELECT A.INSTANCE_NUMBER,
A.SNAP_ID,
B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME,
B.END_INTERVAL_TIME + 0 END_TIME,
ROUND(VALUE - LAG( VALUE, 1 , '0')
OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB TIME"
FROM (SELECT B.SNAP_ID,
INSTANCE_NUMBER,
SUM(VALUE ) / 1000000 / 60 VALUE
FROM DBA_HIST_SYS_TIME_MODEL B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND UPPER (B.STAT_NAME) IN UPPER(('DB TIME' ))
GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,
DBA_HIST_SNAPSHOT B
WHERE A.SNAP_ID = B.SNAP_ID
AND B.DBID = (SELECT DBID FROM V$DATABASE)
AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)
WHERE TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE , 'YYYY-MM-DD')
ORDER BY BEGIN_TIME;
--查看最大连接数
select value from v$parameter where name ='processes';
--查两个节点连接数
select INST_ID,count(*) from gv$session group by inst_id;
--查看起库以来最大连接数
select resource_name,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name in ('processes','sessions');
--查看并发连接数
Select INST_ID,count(*) from gv$session where status='ACTIVE' group by inst_id;
--查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;
--查当前的等待事件
col wait_class for a20
set lines 200 pages 200
col event for a60
select event,count(*),wait_class from v$session_wait group by event,wait_class order by 3;
--查看归档是否有错误
select dest_name,error from v$archive_dest;
--mrp当前正在应用的日志序列
select process,status,sequence# from v$managed_standby;
