1、查询各数据文件的使用情况
select s.*, d.bytes/1024/1024 as "TOTAL SPACE(M)",
trunc((1-s."FREE SPACE(M)"/(d.bytes/1024/1024))*100,2) as "USED(%)",
d.tablespace_name,d.status,d.blocks,d.autoextensible
from dba_data_files d,
(select tablespace_name,file_id,sum(bytes)/1024/1024 as "FREE SPACE(M)"
from dba_free_space group by tablespace_name,file_id)s
where s.file_id=d.file_id order by 1;
2、查询各表空间的使用情况
select d.tablespace_name,d."TOTAL SPACE(M)",f."FREE SPACE(M)" ,
d."TOTAL SPACE(M)"-f."FREE SPACE(M)" as "used",
trunc((1-f."FREE SPACE(M)"/d."TOTAL SPACE(M)")*100,1) as "USED(%)"
from
(select tablespace_name,trunc(sum(bytes)/1024/1024,1) as "TOTAL SPACE(M)" from dba_data_files group by tablespace_name) d,
(select tablespace_name,trunc(sum(bytes)/1024/1024,1) as "FREE SPACE(M)" from dba_free_space group by tablespace_name) f
where d.tablespace_name=f.tablespace_name order by 4 desc;
3、查询某一字段或表占用空间的大小
SELECT owner,segment_name, SUM(bytes)/1024/1024 as "SPACE(M)"
FROM dba_segments
WHERE owner='IBSS'
And segment_name='TB_CM_CUST'
GROUP BY owner,segment_name
ORDER BY 3 DESC;
4、查询数据库中锁的情况
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL
p.s.
查询阻塞者
select sid, username, event, blocking_session,seconds_in_wait, wait_time
from v$session where state in ('WAITING') and wait_class != 'Idle';
查询阻塞SQL
select sid, sql_text from v$session s, v$sql q
where sid in (254,270) and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id);
查僵尸进程
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL
and s.sid not in(select sid from v$SESSION_WAIT where event='enqueue')
5、处理存储过程被锁
SELECT * FROM V$ACCESS
WHERE owner='IBSS'
And object=
6、查看回滚段状态
这也是DBA经常使用的脚本，因为回滚段是online还是full是他们的关怀之列嘛
SELECT a.segment_name,b.status
FROM Dba_Rollback_Segs a,v$rollstat b
WHERE a.segment_id=b.usn
ORDER BY 2
7、看哪些session正在使用哪些回滚段
sELECT r.name 回滚段名,s.sid,s.serial#,s.username 用户名,
s.status,t.cr_get,t.phy_io,t.used_ublk,t.noundo,
substr(s.program, 1, 78) 操作程序
FROM v$session s,v$transaction t,v$rollname r
WHERE t.addr = s.taddr and t.xidusn = r.usn
ORDER BY t.cr_get,t.phy_io
8、查看正在使用临时段的session
许多的时侯你在查看哪些段无法扩展时，回显的结果是临时段，或你做表空间统计时发现临段表空间的可用空间几乎为0,这时按oracle的说法是你只有重新启动数据库才能回收这部分空间。实际过程中没那么复杂，使用以下这段脚本把占用临时段的session杀掉，然后用alter tablespace temp coalesce;这个语句就把temp表空间的空间回收回来了。
SELECT se.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、查询游标泄露情况
select o.total,s.* from v$session s,(select sid,count(*) total from v$open_cursor group by sid) o
where s.sid=o.sid order by total desc
select * from v$sqltext where address in
(select address from v$open_cursor where sid='286' group by address) order by address,piece
select saddr,address,sql_text,count(*) from v$open_cursor group by saddr,address,sql_text order by count(*) desc
10、查询没主键的表
select * from user_tables where
table_name not in(select table_name from user_constraints where constraint_name like 'PK%')
select * from user_indexes where
index_name like 'PK%' and table_name not in(select table_name from user_constraints where constraint_name like 'PK%')
11、查询CPU占用情况
select p.spid,s.username,s.sid,s.serial#,s.machine,
decode(nvl(p.background,0),1,bg.description,s.program ) program,
ss.value/100 CPU,physical_reads disk_io
from v$process p,v$session s,
v$sesstat ss,v$sess_io si,v$bgprocess bg
where s.paddr=p.addr
and ss.sid=s.sid
and ss.statistic#=12
and si.sid=s.sid
and bg.paddr(+)=p.addr
order by ss.value desc;
12、查询数据库各SESSION的资源占用情况
select s.OSUSER,s.program,s.status,a.* from v$session s, (
select sid,
sum(decode(STATISTIC#,237,value,0))+sum(decode(STATISTIC#,236,value,0))+sum(decode(STATISTIC#,9,value,0))
+sum(decode(STATISTIC#,12,value,0))*1000+sum(decode(STATISTIC#,115,value,0))*100
+sum(decode(STATISTIC#,20,value,0))+sum(decode(STATISTIC#,15,value,0)) as "Weighted Sum",
sum(decode(STATISTIC#,237,value,0)) as "bytes received from client",
sum(decode(STATISTIC#,236,value,0)) as "bytes sent to client",
sum(decode(STATISTIC#,12,value,0)) as "CPU used by this session",
sum(decode(STATISTIC#,115,value,0)) as "redo size",
sum(decode(STATISTIC#,9,value,0)) as "session logical reads",
sum(decode(STATISTIC#,20,value,0)) as "session pga memory",
sum(decode(STATISTIC#,15,value,0)) "session uga memory"
from v$sesstat
group by sid) a
where s.sid=a.sid and s.type='USER'
order by "Weighted Sum" desc;
--可通过v$sysstat查看各STATISTIC#的解释
13、查看某一SESSION执行的SQL语句
select * from v$sqltext where hash_value in
(select prev_hash_value from v$session where sid=673)
order by piece
14、查看各用户的连接数
select schemaname,count(*) from v$session group by schemaname having schemaname not in('SYS' , 'SYSTEM')
15、一个好用的视图，查看连接数用
select * from v$resource_limit
16、通过进程号查看SID，及查看SQL脚本
select p.spid,s.* from v$session s,v$process p where p.addr=s.paddr and spid='36432'
select * from v$session where paddr=(select addr from v$process where spid='160620')
select * from v$sqltext
where address=(select sql_address from v$session where paddr=(select addr from v$process where spid='160620'))
order by piece
17、复制表
1.2.1 在同一个服务器的同一个Schema中复制数据：
同时指定From database和To database
SQL> copy from to create dept1 using select * from dept;
只指定From Database
SQL> copy from create dept2 using select * from dept;
只指定To Database
SQL> copy to create dept3 using select * from dept;
1.2.2 在同一个服务器的不同Schema中复制数据：
这种情况下必须同时指定From Database和To Database
SQL> copy from to create dept using select * from dept;
注意这种情况下，using select * from dept 中并不需要使用scott.demp的形式。
1.2.3 在不同的服务器间复制数据：
SQL> conn
已连接。
SQL> copy from to create dept using select * from dept;
1.2.4
SQL> COPY FROM to INSERT REIACOD USING SELECT * FROM REIACOD;
18、查看LOB字段信息
select * from all_tab_columns where owner='CECFMEMO' and data_type like '%LOB%'
select * from DBA_LOBS where owner='CECFMEMO' and table_name='T_ZJ_APPLY_BASIC'
select segment_name,bytes/1024/1024/1024 from dba_segments where owner='CECFMEMO' and segment_name='SYS_LOB0000025066C00010$$'
19
select * from v$session_wait where event not like '%rdbms ipc message%' and event not like '%SQL*%'
sqlplus -prelim / as sysdba
