一、关于表状态
- /* Formatted on 2008/09/05 14:11 (Formatter Plus v4.8.8) */
- SELECT s.SID session_id, s.username,
- DECODE (lmode,
- 0, ' None ',
- 1, ' Null ',
- 2, ' Row-S (SS) ',
- 3, ' Row-X (SX) ',
- 4, ' Share ',
- 5, ' S/Row-X (SSX) ',
- 6, ' Exclusive ',
- TO_CHAR (lmode)
- ) mode_held,
- DECODE (request,
- 0, ' None ',
- 1, ' Null ',
- 2, ' Row-S (SS) ',
- 3, ' Row-X (SX) ',
- 4, ' Share ',
- 5, ' S/Row-X (SSX) ',
- 6, ' Exclusive ',
- TO_CHAR (request)
- ) mode_requested,
- o.owner || ' . ' || o.object_name || ' ( ' || o.object_type
- || ' ) ',
- s.TYPE lock_type, l.id1 lock_id1, l.id2 lock_id2
- FROM v$lock l, SYS.dba_objects o, v$session s
- WHERE l.SID = s.SID AND l.id1 = o.object_id;
- SELECT /*+ rule*/
- a.SID, b.owner, object_name, object_type
- FROM v$lock a, all_objects b
- WHERE TYPE = 'TM' AND a.id1 = b.object_id;
- --查看锁住的进程
- SELECT *
- FROM v$locked_object t1, v$session t2
- WHERE t1.session_id = t2.SID
- ORDER BY t2.logon_time;
- --查看锁住的进程
- select t2.username,t2.sid,t2.serial#,t2.logon_time ,machine
- from v$locked_object t1,v$session t2
- where t1.session_id=t2.sid order by t2.logon_time
- alter system kill session '184,5215';
- --查看锁住的表
- SELECT /*+ rule */
- LPAD (' ', DECODE (l.xidusn, 0, 3, 0))
- || l.oracle_username user_name, o.owner, o.object_name,
- o.object_type, s.SID, s.serial#
- FROM v$locked_object l, dba_objects o, v$session s
- WHERE l.object_id = o.object_id AND l.session_id = s.SID
- ORDER BY o.object_id, xidusn DESC
- --取出当前正在执行的sql语句
- SELECT s.sql_text, v.sql_hash_value, s.disk_reads, s.buffer_gets,
- s.optimizer_cost
- FROM gv$session v, gv$sql s
- WHERE v.status = 'ACTIVE'
- AND v.username != 'SYS'
- AND s.address = v.sql_address
- AND s.hash_value = v.sql_hash_value
- ORDER BY 3, 4, 5 DESC;
- alter system kill session '177,33561';
二、查看Oralce表空间信息
- --查看表空间的名称及大小
- select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
- from dba_tablespaces t, dba_data_files d
- where t.tablespace_name = d.tablespace_name
- group by t.tablespace_name;
- --查看表空间物理文件的名称及大小
- select tablespace_name, file_id, file_name,
- round(bytes/(1024*1024),0) total_space
- from dba_data_files
- order by tablespace_name;
- --查看回滚段名称及大小
- select segment_name, tablespace_name, r.status,
- (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
- max_extents, v.curext CurExtent
- From dba_rollback_segs r, v$rollstat v
- Where r.segment_id = v.usn(+)
- order by segment_name ;
- --查看控制文件
- select name from v$controlfile;
- --查看日志文件
- select member from v$logfile;
- --查看表空间的使用情况
- select sum(bytes)/(1024*1024) as free_space,tablespace_name
- from dba_free_space
- group by tablespace_name;
- --查看数据库库对象
- select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
- --查看数据库的版本
- Select version FROM Product_component_version
- Where SUBSTR(PRODUCT,1,6)='Oracle';
- --查看数据库的创建日期和归档方式
- Select Created, Log_Mode, Log_Mode From V$Database;
- --捕捉运行很久的SQL
- select username,sid,opname,
- round(sofar*100 / totalwork,0)||'%' as progress,
- time_remaining,sql_text
- from v$session_longops , v$sql
- where time_remaining <> 0
- and sql_address = address
- and sql_hash_value = hash_value;
- --查看数据表的参数信息
- SELECT partition_name, high_value, high_value_length, tablespace_name,
- pct_free, pct_used, ini_trans, max_trans, initial_extent,
- next_extent, min_extent, max_extent, pct_increase, FREELISTS,
- freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,
- empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,
- last_analyzed
- FROM dba_tab_partitions
- --WHERE table_name = :tname AND table_owner = :towner
- ORDER BY partition_position;
- --查看还没提交的事务
- select * from v$locked_object;
- select * from v$transaction;
- --查找object为哪些进程所用
- select
- p.spid,
- s.sid,
- s.serial# serial_num,
- s.username user_name,
- a.type object_type,
- s.osuser os_user_name,
- a.owner,
- a.object object_name,
- decode(sign(48 - command),
- 1,
- to_char(command), 'Action Code #' ,to_char(command) ) action,
- p.program oracle_process,
- s.terminal terminal,
- s.program program,
- s.status session_status
- from v$session s, v$access a, v$process p
- where s.paddr = p.addr and
- s.type = 'USER' and
- a.sid = s.sid and
- a.object='SUBSCRIBER_ATTR'
- order by s.username, s.osuser;
- --回滚段查看
- select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents
- Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,
- v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,
- sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs,
- v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and
- v$rollstat.usn (+) = v$rollname.usn order by rownum;
- --耗资源的进程(top session)
- select s.schemaname schema_name, decode(sign(48 - command), 1,
- to_char(command), 'Action Code #',to_char(command) ) action, status
- session_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num,
- nvl(s.username, '[Oracle process]') user_name, s.terminal terminal,
- s.program program, st.value criteria_value from v$sesstat st, v$session s , v$process p
- where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL' = 'ALL'
- or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc ;
三、连接信息sql
- select machine, count(machine) from sys.v_$session group by machine
- select count(*) from sys.v_$open_cursor
- select count(*) from v$session
- select username,machine,status from v$session where username='CALLCENTER'
- select count(*) from v$session where username='CALLCENTER'
- select * from v$session where machine='Skylink-ctc2'
- select * from v$session
- SELECT * FROM V$PARAMETER WHERE NAME='processes'
- select * from v$lock ,dba_objects,v$session where object_id=v$lock.id1 and v$lock.sid=v$session.sid
- select * from v$lock
- alter system kill session '153,21604';
- select username,machine,count(*) from v$session group by username,machine
- select sql_text, count(*) aa from sys.v_$open_cursor group by sql_text order by aa desc