oracle 使用

--收缩表空间
SELECT 'alter database datafile ''' || A.FILE_NAME || ''' resize ' ||
        ROUND(A.FILESIZE - (A.FILESIZE - C.HWMSIZE - 100) * 0.8) || 'M;',
       A.FILESIZE || 'M' AS "数据文件的总大小", C.HWMSIZE || 'M' AS "数据文件的实用大小"
FROM   (SELECT FILE_ID, FILE_NAME, ROUND(BYTES / 1024 / 1024) AS FILESIZE
         FROM   DBA_DATA_FILES) A,
       (SELECT FILE_ID, ROUND(MAX(BLOCK_ID) * 8 / 1024) AS HWMSIZE
         FROM   DBA_EXTENTS
         GROUP  BY FILE_ID) C
WHERE  A.FILE_ID = C.FILE_ID AND
       A.FILESIZE - C.HWMSIZE > 100;
--查找当前所有会话：
select t2.username,t2.sid,t2.serial#,t2.logon_time,
vs.SQL_TEXT
    from v$locked_object t1,v$session t2 ,v$sql vs
   where t1.session_id=t2.sid
   and vs.sql_id=t2.sql_id
    order by t2.logon_time;
SELECT OSUSER 电脑登录身份,
    PROGRAM 发起请求的程序,
    USERNAME 登录系统的用户名,
    SCHEMANAME,
    sharable_mem/1024 s ,
    persistent_mem/1024  p,
    runtime_mem/1024 r,
    B.Cpu_Time 花费cpu的时间,
    STATUS,
    B.SQL_TEXT 执行的sql,terminal ,logon_time 
,sql_exec_start ,prev_exec_start
 上次执行sql的时间 ,LAST_CALL_ET/60/60 持续时间
    ,decode(CURRENT_QUEUE_DURATION,1
,'已经在队列中','还在排队')
FROM V$SESSION A
LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
          AND A.SQL_HASH_VALUE = B.HASH_VALUE
          where status = 'ACTIVE' and OSUSER<>'oracle'
           
ORDER BY sharable_mem+persistent_mem+runtime_mem DESC


--kill会话
alter system  kill session '446,1599'; --'sid,serial'
alter system  kill session '446,1599' immediate; 

--操作系统级别kill会话
select 'kill -9 '|| p.spid
from v$session s, v$process p
where s.paddr = p.addr
and s.SID=XXX and s.SERIAL#=XXX;

--查询pid的sql
SELECT   /*+ ORDERED */
         sql_text
    FROM v$sqltext a
   WHERE (a.hash_value, a.address) IN (
            SELECT DECODE (sql_hash_value,
                           0, prev_hash_value,
                           sql_hash_value
                          ),
                   DECODE (sql_hash_value, 0, 
	prev_sql_addr, sql_address)
              FROM v$session b
             WHERE b.paddr = (SELECT addr
                   FROM v$process c
                      WHERE c.spid = '&pid'))
ORDER BY piece ASC
--查询pid的登录信息
 select sid,serial# ,username,osuser,machine,program,process,
to_char(logon_time,'yyyy/mm/dd hh24:mi:ss')
     from v$session
    where paddr in      
( select addr from v$process where spidin(&pid));

-- 监控进程
select distinct p.spid unix_process,
s.terminal,
to_char(s.logon_time,'YYYY/MON/DD HH24:MI') Logon_Time,
s.username
from v$process p, v$session s
where p.addr=s.paddr order by 2

--查看哪些用户连了oracle
select s.osuser os_user_name, 
decode(sign(48 - command), 1, to_char(command),
'Action Code #' || to_char(command) ) action
, p.program oracle_process,
status session_status, s.terminal terminal, 
s.program program,
s.username user_name, 
s.fixed_table_sequence activity_meter, '' query,
0 memory, 0 max_memory, 0 cpu_usage, 
s.sid, s.serial# serial_num
from v$session s, 
v$process p where s.paddr=p.addr and s.type = 'USER'
order by s.username, s.osuser

--耗资源的进程
select s.schemaname schema_name, decode(sign(48 - command), 1,
to_char(command), 'Action Code #' || to_char(command) ) action, status
session_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num,
nvl(s.username, '[Oracle process]') user_name, s.terminal terminal,
s.program program, st.value criteria_value from v$sesstat st, v$session s , v$process 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 asc


--捕捉运行很久的sql
select username,sid,opname,
round(sofar*100 / totalwork,0) || '%' as progress,
time_remaining,sql_text
from v$session_longops , v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value


--回滚段暂用的进程
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 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 ='UNDOTBS1'
ORDER BY used_ublk DESC

--查看锁情况
select /*+ RULE */ ls.osuser os_user_name, 
ls.username user_name,
decode(ls.type, 'RW',
 'Row wait enqueue lock', 'TM', 'DML enqueue lock'
, 'TX',
'Transaction enqueue lock', 'UL',
 'User supplied lock') lock_type,
o.object_name object, 
decode(ls.lmode, 1, null, 2, 'Row Share', 3,
       'Row Exclusive', 4, 'Share', 5, 
'Share Row Exclusive', 6, 'Exclusive', null)
lock_mode, o.owner, ls.sid, ls.serial# serial_num,
 ls.id1, ls.id2
from sys.dba_objects o, 
( select s.osuser, s.username, l.type,
l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s,
v$lock l where s.sid = l.sid ) ls 
where o.object_id = ls.id1 and o.owner

<> 'SYS' order by o.owner, o.object_name;

--查看sql执行进度
select sid,serial#,  opname,vs.SQL_TEXT, 
target_desc, sofar, totalwork, trunc(sofar/totalwork*100,2) 
|| '%' as perwork
from v$session_longops a,v$sql vs 
where sofar != totalwork and totalwork<>0
and a.SQL_ID=vs.SQL_ID