1 数据库信息查看
数据库信息查看
--查看实例名 select instance_number,instance_name,host_namefrom v$instance; --查看RAC监听配置 selectname,value,display_valuefrom v$parameter wherenamein('remote_listener','local_listener'); --查看内存SGA,PGA分配情况 select *from v$sgastat; select *from v$pgastat; --查看查看是否归档,创建时间及平台类型 select dbid,name,db_unique_name,created,log_mode,platform_namefrom v$database; |
--dblink查看 select *from dba_db_links; |
数据文件查看
--查看表空间的大小,使用大小,可用大小 SELECT a.tablespace_name, round(a.bytes / (1024 *1024 * 1024)) total_GB, round(b.bytes / (1024 *1024 * 1024)) used_GB, round(c.bytes / (1024 *1024 * 1024)) free_GB, trunc((b.bytes * 100) / a.bytes) "% USED ", trunc((c.bytes * 100) / a.bytes) "% FREE " FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c WHERE a.tablespace_name = b.tablespace_name AND a.tablespace_name = c.tablespace_name orderby a.tablespace_namedesc;
|
---查看数据文件的大小,使用大小,空闲大小 select a.file_id, a.file_name, a.filesize, b.freesize, (a.filesize - b.freesize) usedsize from (select file_id, file_name, round(bytes /1024 / 1024 /1024) filesize from dba_data_files) a, (select file_id,round(sum(dfs.bytes) /1024 / 1024 /1024) freesize from dba_free_space dfs groupby file_id) b where a.file_id = b.file_id;
|
--查看临时数据文件 SELECT *FROM DBA_TEMP_FILES; SELECT *FROM DBA_TEMP_FREE_SPACE;
--UNDO表空间监控 SELECT *FROM V$UNDOSTAT ORDERBY BEGIN_TIMEDESC; SELECT *FROM DBA_ROLLBACK_SEGS; SELECT *FROM V$ROLLSTAT; SELECT *FROM DBA_UNDO_EXTENTSWHERE STATUS='ACTIVE' SELECTNAME,VALUEFROM V$PARAMETERWHERENAME='UNDO_TABLESPACE' |
--查看大表占用的空间 select segment_name,round(sum(bytes) /1024 / 1024 /1024, 2) from user_segments where segment_typein ('TABLE','TABLE PARTITION') groupby segment_name orderbyround(sum(bytes) / 1024 / 1024 / 1024,2) desc; |
--查看ASM diskgroup的大小(asm磁盘组) select *from v$asm_diskgroup; select *from v$asm_disk_stat; |
2 锁的查看和执行计划查看
--查看被锁对象 SELECT object_name, machine, s.sid, s.serial#, s.blocking_session, s.wait_class, s.seconds_in_wait, s.inst_id FROM gv$locked_object l, dba_objects o, gv$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid;
--查看会话操作系统进程 select s.sid, s.serial#, s.username, p.spid from v$session s, v$process p where s.paddr = p.addr and s.sid ='1335' and s.serial# ='2147';
--查看会话的sql_id select a.SQL_ID from gv$session a where a.sid ='2671' and a.SERIAL# ='4499'--2dnhpsg563z4c --查看执行计划 select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5sc6fsqgt6x9w',0,'ADVANCED')); |
--查看阻塞锁 SELECT ( '节点 ' || a.inst_id || ' session ' || a.sid || ',' || a_s.serial# || ' 阻塞了节点 ' || b.inst_id || ' session ' || b.sid || ',' || b_s.serial#) blockinfo, a.inst_id, a_s.sid, a_s.schemaname, a_s.module, a_s.status, a.TYPE lock_type, a.id1, a.id2, DECODE (a.lmode, 0,'none', 1,NULL, 2,'row-S (SS)', 3,'row-X (SX)', 4,'share (S)', 5,'S/Row-X (SSX)', 6,'exclusive (X)') lock_mode, '后为被阻塞信息' remark_flag, b.inst_id blocked_inst_id, b_s.sid blocked_sid, b.TYPE blocked_lock_type, DECODE (b.request, 0,'none', 1,NULL, 2,'row-S (SS)', 3,'row-X (SX)', 4,'share (S)', 5,'S/Row-X (SSX)', 6,'exclusive (X)') blocked_lock_request, b_s.schemaname blocked_schemaname, b_s.module blocked_module, b_s.status blocked_status, b_s.sql_id blocked_sql_id, obj.owner blocked_owner, obj.object_name blocked_object_name, obj.object_type blocked_object_type FROM gv$lock a, gv$lock b, gv$session a_s, gv$session b_s, dba_objects obj WHERE a.id1 = b.id1 AND a.id2 = b.id2 AND a.block >0 --阻塞了其他人 AND b.request >0 AND ( (a.inst_id = b.inst_id AND a.sid <> b.sid) OR (a.inst_id <> b.inst_id)) AND a.sid = a_s.sid AND a.inst_id = a_s.inst_id AND b.sid = b_s.sid AND b.inst_id = b_s.inst_id AND b_s.row_wait_obj# = obj.object_id(+) ORDERBY a.inst_id, a.sid; |