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;