Oracle管理与维护常用SQL语句汇总Word文件下载.docx

1、select * from v$version;查看表空间的使用情况select sum（bytes）/（1024*1024） as free_space,tablespace_namefrom dba_free_spacegroup by tablespace_name;SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,（B.BYTES*100）/A.BYTES % USED,（C.BYTES*100）/A.BYTES % FREEFROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,

2、SYS.SM$TS_FREE CWHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;1、查看表空间的名称及大小select t.tablespace_name, round（sum（bytes/（1024*1024）,0） ts_sizefrom dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_namegroup by t.tablespace_name;2、查看表空间物理文件的名称及

3、大小select tablespace_name, file_id, file_name,round（bytes/（1024*1024）,0） total_spacefrom dba_data_filesorder by tablespace_name;3、查看回滚段名称及大小select segment_name, tablespace_name, r.status,（initial_extent/1024） InitialExtent,（next_extent/1024） NextExtent,max_extents, v.curext CurExtentFrom dba_rollback

4、_segs r, v$rollstat vWhere r.segment_id = v.usn（+）order by segment_name ;4、查看控制文件select name from v$controlfile;5、查看日志文件select member from v$logfile;6、查看表空间的使用情况7、查看数据库库对象select owner, object_type, status, count（*） count# from all_objects group by owner, object_type, status;8、查看数据库的版本Select version

5、FROM Product_component_versionWhere SUBSTR（PRODUCT,1,6）=Oracle;9、查看数据库的创建日期和归档方式Select Created, Log_Mode, Log_Mode From V$Database;10、如何远程判断Oracle数据库的安装平台11、查看数据表的参数信息SELECT partition_name, high_value, high_value_length, tablespace_name,pct_free, pct_used, ini_trans, max_trans, initial_extent,next_e

6、xtent, 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_analyzedFROM dba_tab_partitions-WHERE table_name = :tname AND table_owner = :townerORDER BY partition_position12、查看还没提交的事务s

7、elect * from v$locked_object;select * from v$transaction;14、回滚段查看select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extentsExtents, 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.sta

8、tus status from v$rollstat, sys.dba_rollback_segs,v$rollname where v$rollname.name（+） = sys.dba_rollback_segs.segment_name andv$rollstat.usn （+） = v$rollname.usn order by rownum15、捕捉运行很久的SQLcolumn username format a12column opname format a16column progress format a8select username,sid,opname, round（s

9、ofar*100 / totalwork,0） | % as progress, time_remaining,sql_textfrom v$session_longops , v$sqlwhere time_remaining 0and sql_address = addressand sql_hash_value = hash_value/查看数据表的参数信息SELECT partition_name, high_value, high_value_length, tablespace_name, pct_free, pct_used, ini_trans, max_trans, init

10、ial_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 = :查找object为哪些进程所用selectp.spid,s.sid,s.serial# seri

11、al_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_statusfrom v$session s, v$access a

12、, v$process pwhere s.paddr = p.addr and s.type = USER and a.sid = s.sid and a.object=SUBSCRIBER_ATTRorder by s.username, s.osuser耗资源的进程（top session）select s.schemaname schema_name, decode（sign（48 - command）, 1, | to_char（command） ） action, statussession_status, s.osuser os_user_name, s.sid, p.spid ,

13、 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 pwhere st.sid = s.sid and st.statistic# = to_number（38） and （ALL = or s.status = ） and p.addr = s.paddr order by st.value desc, p

14、.spid asc, s.username asc, s.osuser asc查看锁（lock）情况select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,decode（ls.type, RW, Row wait enqueue lockTMDML enqueue lockTX,Transaction enqueue lockULUser supplied lock） lock_type,o.object_name object, decode（ls.lmode, 1, null, 2, Row Share, 3,Row

15、 Exclusive, 4, Share, 5, Share Row Exclusive, 6, Exclusive, null）lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2from sys.dba_objects o, （ select s.osuser, s.username, l.type,l.lmode, s.serial#, l.id1, l.id2 from v$session s,v$lock l where s.sid = l.sid ） ls where o.object_id = ls.i

16、d1 and o.owner 1 group by decode （o.type#,1, , 3 , 5 , ） union selectCOLUMN , count（*） from sys.col$ union select DB LINK , count（*） from25。按用户查看object种类select u.name schema, sum（decode（o.type#, 1, 1, NULL） indexes,sum（decode（o.type#, 2, 1, NULL） tables, sum（decode（o.type#, 3, 1, NULL）clusters, sum（

17、decode（o.type#, 4, 1, NULL） views, sum（decode（o.type#, 5, 1,NULL） synonyms, sum（decode（o.type#, 6, 1, NULL） sequences,sum（decode（o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1）others from sys.obj$ o, sys.user$ u where o.type# = 1 and u.user# =o.owner# and u.name PUBLIC group by u.n

18、ame order bysys.link$ union select CONSTRAINT , count（*） from sys.con$有关connection的相关信息1）查看有哪些用户连接select s.osuser os_user_name, decode（sign（48 - command）, 1, to_char（command）, p.program oracle_process,status session_status, s.program program,s.username user_name, s.fixed_table_sequence activity_mete

19、r, query,0 memory, 0 max_memory, 0 cpu_usage, s.serial# serial_numfrom v$session s, v$process p where s.paddr=p.addr and2）根据v.sid查看对应连接的资源占用等情况select n.name,v.value,n.class,n.statistic#from v$statname n,v$sesstat vwhere v.sid = 71 andv.statistic# = n.statistic#order by n.class, n.statistic#3）根据sid查看

20、对应连接正在运行的sqlselect /*+ PUSH_SUBQ */command_type,sql_text,sharable_mem,persistent_mem,runtime_mem,sorts,version_count,loaded_versions,open_versions,users_opening,executions,users_executing,loads,first_load_time,invalidations,parse_calls,disk_reads,buffer_gets,rows_processed,sysdate start_time,sysdate

21、 finish_time, | address sql_address,Nfrom v$sqlareawhere address = （select sql_address from v$session where sid = 71）27查询表空间使用情况select a.tablespace_name 表空间名称100-round（nvl（b.bytes_free,0）/a.bytes_alloc）*100,2） 占用率（%）round（a.bytes_alloc/1024/1024,2） 容量（M）round（nvl（b.bytes_free,0）/1024/1024,2） 空闲（M）round（a.bytes_alloc-nvl（b.bytes_free,0）/1024/1024,2） 使用（M）Largest 最大扩展段（M）to_char（sysdate,yyyy-mm-dd hh24:mi:ss） 采样时间 （select f.tablespace_name, sum（f.bytes） bytes_alloc, sum（decode（f.autoextensible,YES,f.maxbytes,NO,f.bytes） maxbytesfrom d