oracle调优SQL语句

select t.*, t.rowid from psecurity t where (t.suser,t.iobject,t.ipermission)
in (select suser,iobject,ipermission from psecurity group by  suser,iobject,ipermission having count(*)>1)
-- 检查当前剩余空间
SELECT F.TABLESPACE_NAME,
       (T.TOTAL_SPACE - F.FREE_SPACE) "USED (MB)",
       F.FREE_SPACE "FREE (MB)",
       T.TOTAL_SPACE "TOTAL (MB)",
       (ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) ||  '% ' PER_FREE
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BLOCKS *
                         (SELECT VALUE / 1024
                            FROM V$PARAMETER
                           WHERE NAME = 'db_block_size') / 1024)) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME

SET NEWPAGE NONE HEADING OFF SPACE 0 PAGESIZE 0 TRIMOUT ON TRIMSPOOL ON LINESIZE 2500 colsep | feedback off termout off pages 0
set colsep |
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
spool deltab.sql
select 'truncate table ' || tname || ' drop storage' ||';' from tab
where TNAME not like '%TB%'
and TNAME like '%LZW%';
spool off;
exit;

SET NEWPAGE NONE HEADING OFF SPACE 0 PAGESIZE 0 TRIMOUT ON TRIMSPOOL ON LINESIZE 2500 colsep | feedback off termout off pages 0
set colsep |
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
spool lzw_tablespace.txt
select a.segment_name||'|'||sum(BYTES)/1024/1024||'|' from user_segments a 
group by a.segment_name
order by sum(BYTES)/1024/1024 desc ;
spool off;

nohup sqlplus  ZJLZ/xx@szj @deltab.sql>deltab.log &

ORACLE 查询数据库锁相关的语句

--查询那些用户，操纵了那些表造成了锁机
SELECT 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, all_objects o
WHERE l.sid = s.sid
   AND l.id1 = o.object_id(+)
   AND s.username is NOT Null;

--查出被锁的表，和锁住这个表的会话ID
select a.session_id, b.*
from v$locked_object a, all_objects b
where a.object_id = b.object_id;

--查出对应的SQL语句
select vs.SQL_TEXT,
       vsess.sid,
       vsess.SERIAL#,
       vsess.MACHINE,
       vsess.OSUSER,
       vsess.TERMINAL,
       vsess.PROGRAM,
       vs.CPU_TIME,
       vs.DISK_READS
from v$sql vs, v$session vsess
where vs.ADDRESS = vsess.SQL_ADDRESS
   and vsess.sid = (1033);

五、

１.查哪个过程被锁

查V$DB_OBJECT_CACHE视图:

SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER='过程的所属用户' AND LOCKS!='0';


2. 查是哪一个SID,通过SID可知道是哪个SESSION.

查V$ACCESS视图:

SELECT * FROM V$ACCESS WHERE OWNER='过程的所属用户' AND NAME='刚才查到的过程名';


3. 查出SID和SERIAL#

查V$SESSION视图:

SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='刚才查到的SID'


查V$PROCESS视图:

SELECT SPID FROM V$PROCESS WHERE ADDR='刚才查到的PADDR';


4. 杀进程

(1).先杀ORACLE进程:

ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#';


(2).再杀操作系统进程:

KILL -9 刚才查出的SPID

或

ORAKILL 刚才查出的SID 刚才查出的SPID

六、查找最耗费系统资源的SQL
--CPU
select b.sql_text,
a.buffer_gets,
a.executions,
a.buffer_gets/decode(a.executions , 0 , 1 , a.executions),
c.username
from V$sqlarea a,
v$sqltext_with_newlines b,
dba_users c
where a.parsing_user_id = c.user_id
and a.address = b.address
order by a.buffer_gets desc , b.piece
;

--IO
select b.sql_text,
a.disk_reads,
a.executions,
a.disk_reads/decode(a.executions , 0 , 1 , a.executions),
c.username
from v$sqlarea a,
v$sqltext_with_newlines b,
dba_users c
where a.parsing_user_id = c.user_id
and a.address = b.address
order by a.disk_reads desc , b.piece
;


select s.sid,s.value "CPU Used"
from v$sesstat s,v$statname n
where s.statistic#=n.statistic# and n.name='CPU used by this session'
and s.value>0
order by 2 desc; 


查看各表空间名称

select name from v$tablespace
查看某个表空间信息

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1';
查看回滚段的使用情况，哪个用户正在使用回滚段的资源，如果有用户最好更换时间（特别是生产环境）。

select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s 
where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username; 
检查UNDO Segment状态

select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
创建新的Oracle释放UNDO表空间，并设置自动扩展参数；

create undo tablespace undotbs2 datafile
'D:\Oracle\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS02.DBF'
size 10m reuse autoextend on next 100m maxsize unlimited;
动态更改spfile配置文件；

alter system set undo_tablespace=undotbs2 scope=both;
等待原UNDO表空间所有UNDO SEGMENT OFFLINE；

select usn,xacts,status,rssize/1024/1024/1024,
hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
再执行看UNDO表空间所有UNDO SEGMENT ONLINE；

select usn,xacts,status,rssize/1024/1024/1024,
hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
删除原有的UNDO表空间；

drop tablespace undotbs1 including contents;
确认删除是否成功；

select name from v$tablespace;
最后需要在重启数据库或者重启计算机后到存储数据文件的路径下删除数据文件（为什么要手动删除呢：以上步骤只是删除了Oracle释放undo表空间的逻辑关系，即删除了数据文件在数据字典中的关联，不会自动删除项关联的数据文件）。