oracle维护常用sql语句(主要)_oracle维护常用语句

oracle维护常用sql语句(主要)由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“oracle维护常用语句”。

1、oracle表空间利用率

SELECT UPPER(F.TABLESPACE_NAME)“表空间名”,D.TOT_GROOTTE_MB “表空间大小(M)”,D.TOT_GROOTTE_MBF.TOTAL_BYTES)D.TOT_GROOTTE_MB * 100,2),'990.99')||'%' “使用比(%)”,F.TOTAL_BYTES “空闲空间(M)”,F.MAX_BYTES “最大块(M)”

FROM(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024 * 1024), 2)TOTAL_BYTES,ROUND(MAX(BYTES)/(1024 * 1024), 2)MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME)F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024 * 1024), 2)TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME)D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY F.TABLESPACE_NAME;查询结果显示：

/

非系统表空间使用和(M)

select sum(“已使用空间(M)”)“已使用空间(M)和” from(SELECT UPPER(F.TABLESPACE_NAME)“表空间名”,D.TOT_GROOTTE_MB “表空间大小(M)”,D.TOT_GROOTTE_MBF.TOTAL_BYTES)/ D.TOT_GROOTTE_MB * 100,2),'990.99')||'%' “使用比(%)”,F.TOTAL_BYTES “空闲空间(M)”,F.MAX_BYTES “最大块(M)”

FROM(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024 * 1024), 2)TOTAL_BYTES,ROUND(MAX(BYTES)/(1024 * 1024), 2)MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME)F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024 * 1024), 2)TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME)D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME and F.TABLESPACE_NAME NOT IN('SYSAUX','USERS','UNDOTBS1','SYSTEM'))

2、查询当前用户默认表空间的使用情况

select tablespacename,sum(totalContent),sum(usecontent),sum(sparecontent),avg(sparepercent)from(SELECT b.file_id as id,b.tablespace_name as tablespacename,b.bytes as totalContent,(b.bytes-sum(nvl(a.bytes,0)))as usecontent,sum(nvl(a.bytes,0))as sparecontent,sum(nvl(a.bytes,0))/(b.bytes)*100 as sparepercent FROM dba_free_space a,dba_data_files b WHERE a.file_id=b.file_id and b.tablespace_name =(select default_tablespace from dba_users where username = user)

group by b.tablespace_name,b.file_name,b.file_id,b.bytes)

GROUP BY tablespacename;

3、查询所有用户表使用大小的前三十名

select * from(select segment_name,bytes from dba_segments where owner = USER order by bytes desc)where rownum

4、查询单张表的使用情况 select segment_name,bytes from dba_segments where segment_name = 'RE_STDEVT_FACT_DAY' and owner = USER

RE_STDEVT_FACT_DAY是您要查询的表名称

5、计算每个用户占用的磁盘空间 select owner,sum(bytes)/1024/1024/1024 “Space(G)” from dba_segments group by owner order by 2;

6、计算某个用户占用的磁盘空间

select owner,sum(bytes)/1024/1024/1024 “Space(G)” from dba_segments where owner='LIAOJL' group by owner;

7、查看表空间的名称及大小（分配大小）:

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;

8、查看表空间物理文件的名称及大小（分配大小）:

select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0)total_space from dba_data_files order by tablespace_name;

9、查看回滚段名称及大小: 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;

10、如何查看某个回滚段里面，跑的什么事物或者正在执行什么sql语句: select d.sql_text,a.name from v$rollname a,v$transaction b,v$seion c,v$sqltext d where a.usn=b.xidusn and b.addr=c.taddr and c.sql_addre=d.addre and c.sql_hash_value=d.hash_value and a.usn==2;(备注：你要看哪个，就把usn=?写成几就行了)

查看控制文件:

SQL>select * from v$controlfile;

查看日志文件:

SQL> col member format a50 SQL>select * from v$logfile;

11、如何查看当前SQL*PLUS用户的sid和serial#:

SQL>select sid, serial#, status from v$seion where audsid=userenv('seionid');

12、怎样识别IO竞争和负载平衡:

SQL>col 文件名 format a35 SQL>select df.name 文件名, fs.phyrds 读次数, fs.phywrts 写次数,(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds))读时间,(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts))写时间 from v$datafile df, v$filestat fs where df.file#=fs.file# order by df.name13、查看有哪些用户连接

select s.osuser os_user_name, decode(sign(48command), 1, to_char(command), 'Action Code #' || to_char(command))action, status seion_status,s.osuser os_user_name,s.sid,p.spid ,s.serial# serial_num,nvl(s.username, '[Oracle proce]')user_name,s.terminal terminal,s.program program,st.value criteria_value from v$setat st,v$seion s , v$proce 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 asc21、监控当前数据库谁在运行什么SQL语句: SELECT osuser, username, sql_text from v$seion a, v$sqltext b where a.sql_addre =b.addre order by addre, piece;

Column type format a8

22、查询每个用户执行过的sql语句 select B.USER_NAME,C.OSUSER,C.MACHINE,C.PROGRAM,C.MODULE, A.USER_IO_WAIT_TIME,B.HASH_VALUE,C.SERVICE_NAME, A.SQL_TEXT from v$sqlarea a, v$open_cursor b,v$seion c where A.ADDRESS=B.ADDRESS and C.USERNAME=B.USER_NAME order by C.OSUSER,B.USER_NAME