数据库维护SQL

--查询表空间使用情况
 select total.tablespace_name,
　　round(total.MB, 2) as Total_MB,
　　round(total.MB - free.MB, 2) as Used_MB,
　　round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
　　from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
　　from dba_free_space
　　group by tablespace_name) free,
　　(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
　　from dba_data_files
　　group by tablespace_name) total
　　where free.tablespace_name = total.tablespace_name;
 
 
  SELECT a.tablespace_name,
       a.bytes / 1024 / 1024 / 1024 total,
       b.bytes / 1024 / 1024 / 1024used,
       c.bytes / 1024 / 1024 / 1024free,
       (b.bytes * 100) / a.bytes "% USED ",
       (c.bytes * 100) / a.bytes "% FREE "
  FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
  WHERE a.tablespace_name = b.tablespace_name
   AND a.tablespace_name = c.tablespace_name;


　　--查询表空间的free space
　　select tablespace_name,
　　count(*) as extends,
　　round(sum(bytes) / 1024 / 1024 , 2) as MB,
　　sum(blocks) as blocks
　　from dba_free_space
　　group by tablespace_name;


　　--查询表空间的总容量
　　select tablespace_name, sum(bytes) / 1024 / 1024 /1024 as GB
　　from dba_data_files
　　group by tablespace_name;


　　--查询表空间使用率
　　select total.tablespace_name,
　　round(total.MB, 2) as Total_MB,
　　round(total.MB - free.MB, 2) as Used_MB,
　　round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
　　from (select tablespace_name, sum(bytes) / 1024 / 1024 /1024 as GB
　　from dba_free_space
　　group by tablespace_name) free,
　　(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
　　from dba_data_files
　　group by tablespace_name) total
　　where free.tablespace_name = total.tablespace_name;
 
 
--1.查找当前表级锁的SQL如下：
select sess.sid, 
    sess.serial#, 
    lo.oracle_username, 
    lo.os_user_name, 
    ao.object_name, 
    lo.locked_mode 
    from v$locked_object lo, 
    dba_objects ao, 
    v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid;


--2.杀掉锁表进程：
alter system kill session '436,35123';


--3.RAC环境中锁查找：
SELECT inst_id,DECODE(request,0,'Holder: ','Waiter: ')||sid sess, 
        id1, id2, lmode, request, type,block,ctime
FROM GV$LOCK
WHERE (id1, id2, type) IN
       (SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY id1, request;
  




--4.监控当前数据库谁在运行什么SQL语句 
select osuser, username, sql_text  
from  v$session a, v$sqltext b 
where  a.sql_address =b.address order by address, piece;


 


--5.找使用CPU多的用户session 
select a.sid,spid,status,substr(a.program,1,40) prog, a.terminal,osuser,value/60/100 value 
from  v$session a,v$process b,v$sesstat c 
where  c.statistic#=12 and  
       c.sid=a.sid and  
       a.paddr=b.addr  
       order by value desc;




--6.查看死锁信息
SELECT (SELECT username
          FROM v$session
         WHERE SID = a.SID) blocker, a.SID, 'is blocking',
       (SELECT username
          FROM v$session
         WHERE SID = b.SID) blockee, b.SID
  FROM v$lock a, v$lock b
 WHERE a.BLOCK = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2;
 
 
 select sess.sid,
       sess.serial#,
       lo.oracle_username,
       lo.os_user_name,
       ao.object_name,
       lo.locked_mode
  from v$locked_object lo, dba_objects ao, v$session sess
 where ao.object_id = lo.object_id
   and lo.session_id = sess.sid;






--7.具有最高等待的对象
SELECT   o.OWNER,o.object_name, o.object_type, a.event,
         SUM (a.wait_time + a.time_waited) total_wait_time
    FROM v$active_session_history a, dba_objects o
   WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
     AND a.current_obj# = o.object_id
GROUP BY o.OWNER,o.object_name, o.object_type, a.event
ORDER BY total_wait_time DESC;




SELECT   a.session_id, s.osuser, s.machine, s.program, o.owner, o.object_name,
         o.object_type, a.event,
         SUM (a.wait_time + a.time_waited) total_wait_time
    FROM v$active_session_history a, dba_objects o, v$session s
   WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
     AND a.current_obj# = o.object_id
     AND a.session_id = s.SID
GROUP BY o.owner,
         o.object_name,
         o.object_type,
         a.event,
         a.session_id,
         s.program,
         s.machine,
         s.osuser
ORDER BY total_wait_time DESC;


 


--8.查询当前连接会话数
select s.value,s.sid,a.username
from 
v$sesstat S,v$statname N,v$session A
where 
n.statistic#=s.statistic# and
name='session pga memory'
and s.sid=a.sid
order by s.value;


 


--9.等待最多的用户
SELECT   s.SID, s.username, SUM (a.wait_time + a.time_waited) total_wait_time
    FROM v$active_session_history a, v$session s
   WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
GROUP BY s.SID, s.username
ORDER BY total_wait_time DESC;


 


--10.等待最多的SQL
SELECT   a.program, a.session_id, a.user_id, d.username, s.sql_text,
         SUM (a.wait_time + a.time_waited) total_wait_time
    FROM v$active_session_history a, v$sqlarea s, dba_users d
   WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
     AND a.sql_id = s.sql_id
     AND a.user_id = d.user_id
GROUP BY a.program, a.session_id, a.user_id, s.sql_text, d.username;


 


--11.查看消耗资源最多的SQL
SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls,address
FROM V$SQLAREA
WHERE buffer_gets > 10000000 OR disk_reads > 1000000
ORDER BY buffer_gets + 100 * disk_reads DESC;






--12.查看某条SQL语句的资源消耗
SELECT a.SQL_TEXT,hash_value, buffer_gets, disk_reads, executions, parse_calls,address
FROM V$SQLAREA a
WHERE hash_Value = 1389092676 AND address = hextoraw('07000009574062A8');




--13.查询会话执行的实际SQL
SELECT   a.SID, a.username, s.sql_text
    FROM v$session a, v$sqltext s
   WHERE a.sql_address = s.address
     AND a.sql_hash_value = s.hash_value
     AND a.status = 'ACTIVE'
ORDER BY a.username, a.SID, s.piece;




--14.显示正在等待锁的所有会话
SELECT * FROM DBA_WAITERS;


--15.添加表空间
alter tablespace system add datafile '+DATA/oadb/system02.dbf' size 20240M;




--16.查找前10条性能差的sql语句 
SELECT *
  FROM (select PARSING_USER_ID,
               EXECUTIONS,
               SORTS,
               COMMAND_TYPE,
               DISK_READS,
               sql_text
          FROM v$sqlarea
         order BY disk_reads DESC)
 where ROWNUM < 10;


--17.ORCLE中ASM磁盘空间使用情况查询
select name,total_mb/1024 as TOTAL_GB ,free_mb/1024 AS FREE_GB from v$asm_diskgroup;




--18.查看数据库directory
select * from dba_directories


--19.查看REDO
select * from v$logfile;


--20.查看ARCHIVELOG
select * from v$archived_log
 show parameter db_recover  --查看归档空间大小
--21.查看用户密码有效期
select * from dba_users
SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';


--22.修改用户密码
alter user  user_name identified by password;


--23.查看ARCHIVELOG 使用率
select * from V$FLASH_RECOVERY_AREA_USAGE;




--24.查看表空间创建语句
select dbms_lob.substr(dbms_metadata.get_ddl('TABLESPACE',a.tablespace_name)) from dba_tablespaces a;


--25.查看控制文件中存储条目
select * from v$controlfile_record_section




--26.查看RMAN进程完成情况
 SELECT sid, serial#, context, sofar, totalwork,
                round(sofar/totalwork*100,2) "% Complete"
         FROM v$session_longops
         WHERE opname LIKE 'RMAN%'
         AND opname NOT LIKE '%aggregate%'
         AND totalwork != 0
         AND sofar <> totalwork;
         
         
         
--27.查看目前日志组和日志文件情况         
 select * from v$logfile order by 1;
 select group#,thread#,bytes/1024/1024,archived,status,members from v$log;
 
--28.查看日志组切换频率和归档日志生成量


select b.SEQUENCE#, b.FIRST_TIME,a.SEQUENCE#,
a.FIRST_TIME,round(((a.FIRST_TIME-b.FIRST_TIME)*24)*60,2) 
from v$log_history a, v$log_history b 
where a.SEQUENCE#=b.SEQUENCE#+1 
and b.THREAD#=1 order by a.SEQUENCE# desc;


select trunc(completion_time),
sum(mb)/1024 day_gb from(select name,
completion_time,blocks*block_size /1024 /1024 mb 
from v$archived_log) 
group by trunc(completion_time) 
order by (trunc(completion_time));


--29.KILL 掉某个SESSION 
--OS(PID) ->v$process(SPID) 
--v$session(Paddr)>v$process(addr)


select * from v$process a where a.SPID='9350';--0000001000AABBC8
select * from v$session b where b.PADDR='0000001000AABBC8';--2197  1
ALTER SYSTEM KILL SESSION 'sid,serial#'; 


--30.获取某SESSION SQL内容
select * from v$process a where a.SPID='9237';--0000001000AABBC8
select b.SQL_ID from v$session b where b.PADDR='0000000FF8AA63B8';--
select * from v$sqltext c where c.SQL_ID='0bqv9pzh8quw5' order by c.PIECE 


--31.获取AWR报告
sql> @?/rdbms/admin/awrrpt.sql


--32.查询某表每个数据块有多少行
select 'T' tbl_name,
       rows_per_block,
       count(*) number_of_such_blocks,
       rows_per_block * count(*)
  from (select dbms_rowid.ROWID_BLOCK_NUMBER(rowid), count(*) rows_per_block
          from cology.HRMRESOURCE
         group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid))
 group by 'T', rows_per_block;
 
--33.每分钟会话数


select substr(a.sample_time, 1, 15) || substr(a.sample_time, 23), count(1)
  from dba_hist_active_sess_history a
 group by substr(a.sample_time, 1, 15) || substr(a.sample_time, 23);


--34.每小时会话数
select substr(a.sample_time, 1, 12) || substr(a.sample_time, 23), count(1)
  from dba_hist_active_sess_history a
 group by substr(a.sample_time, 1, 12) || substr(a.sample_time, 23);
 
--35.检查RMAN备份状态
select START_TIME,
       END_TIME,
       STATUS,
       OBJECT_TYPE,
       OUTPUT_DEVICE_TYPE,
       a.OPERATION
  from v$rman_status a
 order by START_TIME  desc;
.
--36.获取表空间创建语句
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'FREPORT') FROM dual;




--37.查看那些会话获得锁，哪些会话被哪些会话堵塞
select INST_ID,
       SID,
       TYPE,
       ID1,
       ID2,
       LMODE,
       REQUEST,
       CTIME,
       BLOCK,
       DECODE(BLOCK, 0, '', 'blocker') blocker,
       DECODE(request, 0, '', 'waiter') waiter
  from gv$lock
 where (ID1, ID2, TYPE) in
       (select ID1, ID2, TYPE from gv$lock where request > 0)
 order by blocker;


--38.查看数据文件、数据库SCN
select file#,CHECKPOINT_CHANGE#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss') cpt from v$datafile;
select dbid,CHECKPOINT_CHANGE# from v$database; 


--39.生成ADDM报告
@?/rdbms/admin/addmrpt


--40.查询某会话超过多久的等待时间
select session_id,
       name,
       p1,
       p2,
       p3,
       wait_time,
       current_obj#,
       current_file#,
       current_block#
  from v$active_session_history ash, v$event_name enm
 where ash.event# = enm.event#
   and session_id = &sid
   and sample_time >= (sysdate - &minute / (24 * 60));


--41、查看UNDO表空间剩余空间
  select sum(bytes)/(1024*1024) as free_space,tablespace_name
  from dba_free_space
  group by tablespace_name;
  
  
   SELECT seg.tablespace_name "Tablespace Name",
        ts.bytes / 1024 / 1024 "TS Size(MB)",
        ue.status "UNDO Status",
        count(*) "Used Extents",
        round(sum(ue.bytes) / 1024 / 1024, 2) "Used Size(MB)",
        round(sum(ue.bytes) / ts.bytes * 100, 2) "Used Rate(%)"
   FROM dba_segments seg,
        DBA_UNDO_EXTENTS ue,
        (SELECT tablespace_name, sum(bytes) bytes
           FROM dba_data_files
          GROUP BY tablespace_name) ts
  WHERE ue.segment_NAME = seg.segment_NAME
    and seg.tablespace_name = ts.tablespace_name
  GROUP BY seg.tablespace_name, ts.bytes, ue.status
  ORDER BY seg.tablespace_name;


  
--42、查看某SQL 最近是否执行过
select *
  from v$active_session_history a, v$sqltext B
 where a.SQL_ID = b.SQL_ID
   and b.SQL_TEXT like '%YT_WZ_DATA_DAY%';  
 
--43、查看RMAN备份片
select t.HANDLE, t.BYTES, t.START_TIME
  from SYS.V_$BACKUP_PIECE t
 where t.START_TIME >= to_date('2016-10-31', 'yyyy-mm-dd');
 
--44、查询SCN
select a.CHECKPOINT_CHANGE# from v$database a;   --CONTROLFILE
select a.CHECKPOINT_CHANGE#,a.LAST_CHANGE# from v$datafile a --CONTROLFILE
select a.CHECKPOINT_CHANGE# from v$datafile_header a  --DATAFILE   


--45、查询EXPDP导出任务
select * from dba_datapump_jobs;
select * from dba_datapump_sessions;


--46、删除表空间
 drop tablespace ods including contents and datafiles;