常用的一些Oracle维护SQL

-- 常用数据库维护检查操作

--<-- 表空间检查 -->
SELECT a.tablespace_name,
       round(((a.bytes - b.bytes) * 100) / a.maxbytes, 2) "% USED",
       round(((a.maxbytes - a.bytes + b.bytes) * 100) / a.maxbytes, 2) "% FREE",
       round(a.maxbytes / 1024 / 1024 / 1024, 2) "TOTAL(G)",
       round((a.bytes - b.bytes) / 1024 / 1024 / 1024, 2) "USED(G)",
       round((a.maxbytes - a.bytes + b.bytes) / 1024 / 1024 / 1024, 2) "FREE(G)",
       round(a.maxbytes / 1024 / 1024, 2) "TOTAL(M)",
       round((a.bytes - b.bytes) / 1024 / 1024, 2) "USED(M)",
       round((a.maxbytes - a.bytes + b.bytes) / 1024 / 1024, 2) "FREE(M)",
       a.maxbytes / 1024 "TOTAL(K)",
       (a.bytes - b.bytes) / 1024 "USED(K)",
       (a.maxbytes - a.bytes + b.bytes) / 1024 "FREE(K)",
       a.maxbytes "TOTAL(B)",
       a.bytes - b.bytes "USED(B)",
       (a.maxbytes - a.bytes + b.bytes) "FREE(B)"
  FROM (SELECT ddf.tablespace_name,
               SUM(decode(ddf.autoextensible, 'NO', ddf.bytes, ddf.maxbytes)) maxbytes,
               SUM(ddf.bytes) bytes
          FROM dba_data_files ddf
         GROUP BY ddf.tablespace_name) a,
       (SELECT dfs.tablespace_name,
               SUM(dfs.bytes) bytes
          FROM dba_free_space dfs
         GROUP BY dfs.tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name
 ORDER BY round(((a.bytes - b.bytes) * 100) / a.maxbytes, 2) DESC;


 
--扩充表空间 (最大32.767G 单文件，不推荐)  /*为防止勿操作，以下扩充操作均以注释形式呈现*/
--alter database datafile 'D:\app\Administrator\oradata\YMS\SYSTEM01.DBF' autoextend on next 100m maxsize 32767m
     
--扩充增加文件。
--alter tablespace SYSTEM  add datafile 'D:\app\Administrator\oradata\YMS\SYSTEM02.dbf' size 1000m

--启用FlashBack模式查看表的某个历史记录；
CREATE TABLE jyq_test_bak AS SELECT * FROM jyq_test AS OF TIMESTAMP to_timestamp('2012-07-07 14:47:00', 'yyyy-mm-dd hh24:mi:ss');
--FLASHBACK TABLE jyq_test TO TIMESTAMP to_timestamp('2012-07-07 15:04:00', 'yyyy-mm-dd hh24:mi:ss');


-- -- 备份文件检查
SELECT * FROM v$backup_datafile;
SELECT * FROM v$backup_datafile_details;
SELECT * FROM v$backup_datafile_summary;
SELECT * FROM v$backup_controlfile_details;
SELECT * FROM v$backup_controlfile_summary;
SELECT * FROM v$backup_redolog;
SELECT * FROM v$backup_archivelog_details;
SELECT * FROM v$backup_archivelog_summary;
SELECT * FROM v$backup_spfile;
SELECT * FROM v$backup_spfile_details;
SELECT * FROM v$backup_spfile_summary;
SELECT * FROM v$backup_set;
SELECT * FROM v$backup_set_details;
SELECT * FROM v$backup_set_summary;
SELECT * FROM v$backup_piece;
SELECT * FROM v$backup_piece_details;
SELECT * FROM v$backup_copy_details;
SELECT * FROM v$backup_copy_summary;
SELECT * FROM v$backup_files;

--查看文件ID号 FILEID:绝对ID,RELaTIVE_FNO 相对ID
select * from dba_data_files where tablespace_name in (
select tablespace_name from dba_tablespaces);


--查看FlashBack的时间。
select oldest_flashback_scn os, to_char(oldest_flashback_time,'yy-mm-dd hh2 
4:mi:ss') ot, retention_target rt,flashback_size fs, estimated_flashback_size es 
 from v$flashback_database_log; 
 
 
--显示已经删除的列表
select object_name,original_name,type from user_recyclebin;

--将删除的表闪回
flashback table test1 to before drop;

 

-- 检查凌晨备份是否成功
SELECT *
  FROM v$backup_datafile
 WHERE completion_time > to_date(to_char(SYSDATE, 'yyyy-mm-dd') || '000000', 'yyyy-mm-dd hh24:Mi:ss')
   AND nvl(controlfile_type, 'D') <> 'B'
 ORDER BY completion_time;
 

 

-- 数据文件状态检查
SELECT ddf.NAME,
       ddf.checkpoint_change#,
       ddf.checkpoint_time,
       ddf.bytes / 1024 / 1024 "M",
       ddf.status,
       ddf.enabled
  FROM v$datafile ddf
 WHERE (ddf.status NOT IN ('ONLINE', 'SYSTEM') OR ddf.enabled <> 'READ WRITE');

-- --<-- 死锁检查 -->
-- 如果下面能够查出数据，并且STATUS为"INACTIVE"，可以使用命令杀掉
SELECT vs.sid,
       vs.serial#,
       vs.audsid,
       'alter system kill session ' || '''' || vs.sid || ',' || vs.serial# || '''' || ';',
       vs.username db_user,
       vs.status,
       vs.osuser client_os_user,
       vs.process,
       vs.machine client_machine,
       vs.terminal,
       vs.program,
       vs.module,
       vs.action,
       vs.logon_time,
       vp.spid,
       vp.username dbserver_os_user,
       vp.program
  FROM v$session vs,
       v$process vp
 WHERE vs.paddr = vp.addr
      -- AND vp.SPID = '1451322'
   AND vs.sid IN (SELECT vl.sid FROM v$lock vl WHERE vl.BLOCK = 1);
--  AND vs.PROCESS = '2961502'

-- 检查数据库失效对象

SELECT owner,
       object_name,
       subobject_name,
       object_type,
       last_ddl_time
  FROM dba_objects
 WHERE status = 'INVALID'
   AND owner NOT IN ('SYS', 'PUBLIC');
   
--闪回开启参数查询
select flashback_on from v$database;   

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

--
/*
-------------------RMan------------------------ 
备份文件格式定义：
%c 备份片的拷贝数
%d 数据库名称
%D 位于该月中的第几天(DD) 
%M 位于该年中的第几月(MM) 
%F 一个基于DBID 唯一的名称,这个格式的形式为c-IIIIIIIIII-YYYYMMDD-QQ, 
其中IIIIIIIIII 为该数据库的DBID，YYYYMMDD 为日期，QQ 是一个1-256 的序列
%n 数据库名称，向右填补到最大八个字符
%u 一个八个字符的名称代表备份集与创建时间
%p 该备份集中的备份片号，从1 开始到创建的文件数
%U 一个唯一的文件名，代表%u_%p_%c 
%s 备份集的号
%t 备份集时间戳
%T 年月日格式(YYYYMMDD) 

show all;                          --显示所有的配置策略信息。
show default  device type;         --显示默认的配置设备类型。
Show retention policy;             --？
Show maxsetsize;                   --？    

--将数据库系统恢复到最近七天内的任意时刻。任何超过最近七天的数据库备份将被标记为obsolete。
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; 

show parameter keep;


--检查查看过期的废弃备份内容
RMAN>report obsolete;

--删除过期备份   nopromt 指定无需确认立即删除。
RMAN> delete noprompt obsolete;  

--检查标记不存在的备份集文件为EXPIRED 
RMAN>crosscheck backup

--删除不存在的备份集文件。
RMAN>delete expired backup;
--


 */