oracle常用SQL脚本

oracle常用SQL脚本，不断搜集中

1、文件IO统计
select TS.Name,
DF.Name File_Name,
FS.Phyblkrd Blocks_Read,
FS.Phyblkwrt Blocks_Written,
FS.Phyblkrd+FS.Phyblkwrt Total_IOs
from V$FILESTAT FS, V$DATAFILE DF,v$tablespace TS
where DF.File#=FS.File#
AND DF.ts#=TS.TS#
order by FS.Phyblkrd+FS.Phyblkwrt desc;

2、表空间统计
SELECT upper(f.tablespace_name) "表空间名",
d.Tot_grootte_Mb "表空间大小(M)",
d.Tot_grootte_Mb - f.total_bytes "已使用空间(M)",
to_char(round((d.Tot_grootte_Mb - f.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 4 DESC

3、查看回滚段状态
SELECT a.segment_name,b.status
FROM Dba_Rollback_Segs a,
v$rollstat b
WHERE a.segment_id=b.usn
ORDER BY 2

4、查看哪些session正在使用哪些回滚段
/*col 回滚段名 format a10
col SID format 9990
col 用户名 format a10
col 操作程序 format a80
col status format a6 trunc*/

SELECT r.name 回滚段名,
s.sid,
s.serial#,
s.username 用户名,
s.status,
s.SQL_ADDRESS,
t.cr_get,
t.phy_io,
t.used_ublk,
t.noundo,
substr(s.program, 1, 78) 操作程序
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
WHERE t.addr = s.taddr and t.xidusn = r.usn
-- AND r.NAME IN ('ZHYZ_RBS')
ORDER BY t.cr_get,t.phy_io

5、查看无法扩展的段
SELECT segment_name,
segment_type,
owner,
a.tablespace_name "tablespacename",
initial_extent/1024 "inital_extent(K)",
next_extent/1024 "next_extent(K)",
pct_increase,
b.bytes/1024 "tablespace max free space(K)",
b.sum_bytes/1024 "tablespace total free space(K)"
FROM dba_segments a,
(SELECT tablespace_name,MAX(bytes) bytes,SUM(bytes) sum_bytes FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
AND next_extent>b.bytes
ORDER BY 4,3,1

6、查看系统锁
SELECT A.OWNER,
A.OBJECT_NAME,
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID,
B.ORACLE_USERNAME,
B.OS_USER_NAME,
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE,
C.STATUS,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM
FROM ALL_OBJECTS A,
V$LOCKED_OBJECT B,
SYS.GV_$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID )
AND (B.PROCESS = C.PROCESS )
-- AND
ORDER BY 1,2 ;

7、查看Io较大正在运行的session
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,
se.MODULE,se.sql_address,st.event,st.p1text,st.p1,st.p2,st.p3,st.STATE,st.SECONDS_IN_WAIT,
si.physical_reads,si.block_changes
FROM v$session se,v$session_wait st,v$sess_io si,v$process pr
WHERE st.sid=se.sid
AND st.sid=si.sid
AND se.PADDR=pr.ADDR
AND se.sid>6
AND st.wait_time=0
AND st.event NOT LIKE '%SQL%'
ORDER BY physical_reads DESC

8、查看正在使用临时段的session
SELECT username,
sid,
serial#,
sql_address,
machine,
program,
tablespace,
segtype,
contents
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr

9、查看某一个操作系统进程的SQL
SELECT a.username,
a.machine,
a.program,
a.sid,
a.serial#,
a.status,
c.piece,
c.sql_text
FROM v$session a,
v$process b,
v$sqltext c
WHERE b.spid=****08
AND b.addr=a.paddr
AND a.sql_address=c.address(+)
ORDER BY c.piece

10、查看enqueue锁状态
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid
sess, id1, id2, lmode, request,
type FROM
V$LOCK WHERE (id1, id2, type)
IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
;

10、找出enqueue等待的holder进程
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid
sess, id1, id2, lmode, request,
type FROM
V$LOCK WHERE (id1, id2, type)
IN (SELECT id1, id2, type FROM V$LOCK WHERErequest>0)
ORDER BY id1, request
;

11、查看hotblock
SELECT /*+ ordered */
E.OWNER || '.' || E.SEGMENT_NAME SEGMENT_NAME,
E.EXTENT_ID EXTENT#,
X.DBABLK - E.BLOCK_ID + 1 BLOCK#,
X.TCH,
L.CHILD#
FROM SYS.V$LATCH_CHILDREN L, SYS.X$BH X, SYS.DBA_EXTENTS E
WHERE L.NAME = 'cache buffers chains'
AND L.SLEEPS > &SLEEP_COUNT
AND X.HLADDR = L.ADDR
AND E.FILE_ID = X.FILE#
AND X.DBABLK BETWEEN E.BLOCK_ID AND E.BLOCK_ID + E.BLOCKS - 1
ORDER BY X.TCH;

12、查看当前所有对象  
  
select * from tab;  
  
13、建一个和a表结构一样的空表  
  
create table b as select * from a where 1=2;  
或者  
create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;  
  
14、察看数据库的大小，和空间使用情况  
  
col tablespace format a20 
select b.file_id　　文件ID, 　　b.tablespace_name　　表空间,

　　b.file_name　　　　　物理文件名, 　　b.bytes　　　　　　　总字节数,
　　(b.bytes-sum(nvl(a.bytes,0)))　　　已使用, 　　sum(nvl(a.bytes,0))　　　　　　　　剩余,
　　sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比  
　　from dba_free_space a,dba_data_files b  
　　where a.file_id=b.file_id  
　　group by b.tablespace_name,b.file_name,b.file_id,b.bytes  
　　order by b.tablespace_name
　　/
　　dba_free_space --表空间剩余空间状况
　　dba_data_files --数据文件空间占用情况 
  
15、查看数据文件放置的路径  
  
col file_name format a50 
select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;  
  
16、显示当前连接用户  
  
show user  
  
17、连接字符串  
  
select 列1||列2 from 表1; 
select concat(列1,列2) from 表1;  
  
18、查询当前日期  
  
select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;  
  
19、用户间复制数据  
  
copy from user1 to user2 create table2 using select * from table1;  
  
20、视图中不能使用order by，但可用group by代替来达到排序目的  
  
create view a as select b1,b2 from b group by b1,b2;  
  
21、通过授权的方式来创建用户  
  
grant connect,resource to test identified by test;  
  
conn test/test