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;