oracle管理SQL查询.docx

oracle管理SQL查询.docx

《oracle管理SQL查询.docx》由会员分享，可在线阅读，更多相关《oracle管理SQL查询.docx（32页珍藏版）》请在冰豆网上搜索。

oracle管理SQL查询

常用SQL查询：

1、查看表空间的名称及大小

selectt.tablespace_name,round（sum（bytes/（1024*1024））,0）ts_size

fromdba_tablespacest,dba_data_filesd

wheret.tablespace_name=d.tablespace_name

groupbyt.tablespace_name;

2、查看表空间物理文件的名称及大小

selecttablespace_name,file_id,file_name,

round（bytes/（1024*1024）,0）total_space

fromdba_data_files

orderbytablespace_name;

3、查看回滚段名称及大小

selectsegment_name,tablespace_name,r.status,

（initial_extent/1024）InitialExtent,（next_extent/1024）NextExtent,

max_extents,v.curextCurExtent

Fromdba_rollback_segsr,v$rollstatv

Wherer.segment_id=v.usn（+）

orderbysegment_name;

4、查看控制文件

selectnamefromv$controlfile;

5、查看日志文件

selectmemberfromv$logfile;

6、查看表空间的使用情况

selectsum（bytes）/（1024*1024）asfree_space,tablespace_name

fromdba_free_space

groupbytablespace_name;

SELECTA.TABLESPACE_NAME,A.BYTESTOTAL,B.BYTESUSED,C.BYTESFREE,

（B.BYTES*100）/A.BYTES"%USED",（C.BYTES*100）/A.BYTES"%FREE"

FROMSYS.SM$TS_AVAILA,SYS.SM$TS_USEDB,SYS.SM$TS_FREEC

WHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;

7、查看数据库库对象

selectowner,object_type,status,count（*）count#fromall_objectsgroupbyowner,object_type,status;

8、查看数据库的版本　

SelectversionFROMProduct_component_version

WhereSUBSTR（PRODUCT,1,6）='Oracle';

9、查看数据库的创建日期和归档方式

SelectCreated,Log_Mode,Log_ModeFromV$Database;

10、捕捉运行很久的SQL

columnusernameformata12

columnopnameformata16

columnprogressformata8

selectusername,sid,opname,

     round（sofar*100/totalwork,0）||'%'asprogress,

     time_remaining,sql_text

fromv$session_longops,v$sql

wheretime_remaining<>0

andsql_address=address

andsql_hash_value=hash_value

/

11。

查看数据表的参数信息

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

  FROMdba_tab_partitions

--WHEREtable_name=:

tnameANDtable_owner=:

towner

ORDERBYpartition_position

12.查看还没提交的事务

select*fromv$locked_object;

select*fromv$transaction;

13。

查找object为哪些进程所用

select

p.spid,

s.sid,

s.serial#serial_num,

s.usernameuser_name,

a.typeobject_type,

s.osuseros_user_name,

a.owner,

a.objectobject_name,

decode（sign（48-command）,

1,

to_char（command）,'ActionCode#'||to_char（command））action,

p.programoracle_process,

s.terminalterminal,

s.programprogram,

s.statussession_status  

fromv$sessions,v$accessa,v$processp  

wheres.paddr=p.addrand

    s.type='USER'and   

    a.sid=s.sid  and

a.object='SUBSCRIBER_ATTR'

orderbys.username,s.osuser

14。

回滚段查看

selectrownum,sys.dba_rollback_segs.segment_nameName,v$rollstat.extents

Extents,v$rollstat.rssizeSize_in_Bytes,v$rollstat.xactsXActs,

v$rollstat.getsGets,v$rollstat.waitsWaits,v$rollstat.writesWrites,

sys.dba_rollback_segs.statusstatusfromv$rollstat,sys.dba_rollback_segs,

v$rollnamewherev$rollname.name（+）=sys.dba_rollback_segs.segment_nameand

v$rollstat.usn（+）=v$rollname.usnorderbyrownum

15。

耗资源的进程（topsession）

selects.schemanameschema_name,   decode（sign（48-command）,1,

to_char（command）,'ActionCode#'||to_char（command））action,   status

session_status,  s.osuseros_user_name,  s.sid,        p.spid,        s.serial#serial_num,  

nvl（s.username,'[Oracleprocess]'）user_name,  s.terminalterminal,   

s.programprogram,  st.valuecriteria_valuefromv$sesstatst,  v$sessions,v$processp  

wherest.sid=s.sidand  st.statistic#=to_number（'38'）and  （'ALL'='ALL'

ors.status='ALL'）andp.addr=s.paddrorderbyst.valuedesc,p.spidasc,s.usernameasc,s.osuserasc

16。

查看锁（lock）情况

select/*+RULE*/ls.osuseros_user_name,  ls.usernameuser_name,  

decode（ls.type,'RW','Rowwaitenqueuelock','TM','DMLenqueuelock','TX',

'Transactionenqueuelock','UL','Usersuppliedlock'）lock_type,  

o.object_nameobject,  decode（ls.lmode,1,null,2,'RowShare',3,

'RowExclusive',4,'Share',5,'ShareRowExclusive',6,'Exclusive',null）

lock_mode,   o.owner,  ls.sid,  ls.serial#serial_num,  ls.id1,  ls.id2   

fromsys.dba_objectso,（  selects.osuser,   s.username,   l.type,    

l.lmode,   s.sid,   s.serial#,   l.id1,   l.id2  fromv$sessions,    

v$lockl  wheres.sid=l.sid）lswhereo.object_id=ls.id1and   o.owner

<>'SYS'  orderbyo.owner,o.object_name

17。

查看等待（wait）情况

SELECTv$waitstat.class,v$waitstat.countcount,SUM（v$sysstat.value）sum_value

FROMv$waitstat,v$sysstatWHEREv$sysstat.nameIN（'dbblockgets',

'consistentgets'）groupbyv$waitstat.class,v$waitstat.count

18。

查看sga情况

SELECTNAME,BYTESFROMSYS.V_$SGASTATORDERBYNAMEASC

19。

查看catchedobject

SELECTowner,             name,             db_link,             namespace,

          type,             sharable_mem,             loads,             executions,  

         locks,             pins,             kept       FROMv$db_object_cache

20。

查看V$SQLAREA

SELECTSQL_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_PROCESSEDFROMV$SQLAREA

21。

查看object分类数量

selectdecode（o.type#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,

'SYNONYM',6,'SEQUENCE','OTHER'）object_type,count（*）quantityfrom

sys.obj$owhereo.type#>1groupbydecode（o.type#,1,'INDEX',2,'TABLE',3

'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE','OTHER'）unionselect

'COLUMN',count（*）fromsys.col$unionselect'DBLINK',count（*）from

22。

按用户查看object种类

selectu.nameschema,  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（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  fromsys.obj$o,sys.user$u  whereo.type#>=1and   u.user#=

o.owner#and  u.name<>'PUBLIC'  groupbyu.name   orderby

sys.link$unionselect'CONSTRAINT',count（*）fromsys.con$

23。

有关connection的相关信息

1）查看有哪些用户连接

selects.osuseros_user_name,   decode（sign（48-command）,1,to_char（command）,

'ActionCode#'||to_char（command））action,    p.programoracle_process,    

statussession_status,   s.terminalterminal,   s.programprogram,   

s.usernameuser_name,   s.fixed_table_sequenceactivity_meter,   ''query,   

0memory,   0max_memory,    0cpu_usage,   s.sid,  s.serial#serial_num   

fromv$sessions,   v$processp  wheres.paddr=p.addrand   s.type='USER'

orderbys.username,s.osuser

2）根据v.sid查看对应连接的资源占用等情况

selectn.name,

v.value,

n.class,

n.statistic#

fromv$statnamen,

v$sesstatv

wherev.sid=71and

v.statistic#=n.statistic#

orderbyn.class,n.statistic#

3）根据sid查看对应连接正在运行的sql

select/*+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,

sysdatestart_time,

sysdatefinish_time,

'>'||addresssql_address,

'N'status

fromv$sqlarea

whereaddress=（selectsql_addressfromv$sessionwheresid=71）

24．查询表空间使用情况

selecta.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-ddhh24:

mi:

ss'）"采样时间"

from（selectf.tablespace_name,

  sum（f.bytes）bytes_alloc,

  sum（decode（f.autoextensible,'YES',f.maxbytes,'NO',f.bytes））maxbytes

fromdba_data_filesf

groupbytablespace_name）a,

（selectf.tablespace_name,

   sum（f.bytes）bytes_free

fromdba_free_spacef

groupbytablespace_name）b,

（selectround（max（ff.length）*16/1024,2）Largest,

  ts.nametablespace_name

fromsys.fet$ff,sys.file$tf,sys.ts$ts

wherets.ts#=ff.ts#andff.file#=tf.relfile#andts.ts#=tf.ts#

groupbyts.name,tf.blocks）c

wherea.tablespace_name=b.tablespace_nameanda.tablespace_name=c.tablespace_name

25.查询表空间的碎片程度

selecttablespace_name,count（tablespace_name）fromdba_free_spacegroupbytablespace_name

havingcount（tablespace_name）>10;

altertablespacenamecoalesce;

altertablenamedeallocateunused;

createorreplaceviewts_blocks_vas

selecttablespace_name,block_id,bytes,blocks,'freespace'segment_namefromdba_free_space

unionall

selecttablespace_name,block_id,bytes,blocks,segment_namefromdba_extents;

select*fromts_blocks_v;

selecttablespace_name,sum（bytes）,max（bytes）,count（block_id）fromdba_free_space

groupbytablespace_name;

26。

查询有哪些数据库实例在运行

selectinst_namefromv$active_instances;

===========================================================

#########创建数据库----look$ORACLE_HOME/rdbms/admin/buildall.sql#############

createdatabasedb01

maxlogfiles10

maxdatafiles1024

maxinstances2

logfile

GROUP1（'/u01/oradata/db01/log_01_db01.rdo'）SIZE15M,

GROUP2（'/u01/oradata/db01/log_02_db01.rdo'）SIZE15M,

GROUP3（'/u01/oradata/db01/log_03_db01.rdo'）SIZE15M,

datafile'u01/oradata/db01/system_01_db01.dbf'）SIZE100M,

undotablespaceUNDO

datafile'/u01/oradata/db01/undo_01_db01.dbf'SIZE40M

def