ITPub博客

首页 > 数据库 > Oracle > 201611巡检SQL

201611巡检SQL

原创 Oracle 作者:ttboylb 时间:2016-11-22 15:49:12 0 删除 编辑




select * from v$option a where a.PARAMETER='Real Application Clusters'; --rac


select * from v$instance; --版本


select *from v$database; --创建时间,归档模式


select name,
       trunc(total_mb / 1024) as TOTAL_G,
       trunc(free_mb / 1024) AS FREE_G,
       trunc((free_mb / total_mb) * 100) || '%' as 空闲比率
  from v$asm_diskgroup; --ASM
--ASM


select userenv('language') from dual; --字符集


select t.DEST_NAME,t.DESTINATION,t.STATUS from v$archive_dest t 
 
--查询表空间
 
 
 SELECT UPPER(F.TABLESPACE_NAME) AS "表空间名称",
        ROUND(D.AVAILB_BYTES, 2) AS "表空间当前大小(G)",
        ROUND(D.MAX_BYTES, 2) AS "MAX空间大小(G)",
        ROUND((D.AVAILB_BYTES - F.USED_BYTES), 2) AS "已使用空间(G)",
        TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,
                      2),
                '999.99') AS "使用比",
        ROUND(F.USED_BYTES, 6) AS "空闲空间(G)",
        F.MAX_BYTES AS "最大块(M)",D.AUTOEXTENSIBLE
   FROM (SELECT TABLESPACE_NAME,
                ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,
                ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES
           FROM SYS.DBA_FREE_SPACE
          GROUP BY TABLESPACE_NAME) F,
        (SELECT DD.TABLESPACE_NAME,
                ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES,
                ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES)) /
                      (1024 * 1024 * 1024),
                      6) MAX_BYTES, MAX(DD.AUTOEXTENSIBLE) AUTOEXTENSIBLE
           FROM SYS.DBA_DATA_FILES DD
          GROUP BY DD.TABLESPACE_NAME) D
  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
  ORDER BY 4 DESC
  
--查询无效对象  
    SELECT owner,object_name,object_type,status
FROM dba_objects WHERE status <>'VALID'
ORDER BY owner,object_name;




-----修改SYSAUX 大小
  SELECT *  
    FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB  
            FROM DBA_SEGMENTS  
           WHERE TABLESPACE_NAME = 'SYSAUX'  
          GROUP BY SEGMENT_NAME  
           ORDER BY 2 DESC)  
   WHERE ROWNUM < 10; 
   
   
SELECT COUNT(*) FROM dba_hist_snapshot;
   
select a.snap_interval,a.retention,a.topnsql from dba_hist_wr_control a;
    
    
SELECT * FROM  WRM$_SNAPSHOT t ORDER BY t.snap_id ASC;
    
select table_name,partition_name 
from dba_tab_partitions 
where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';  


 select *
   from (select segment_name,
                PARTITION_NAME,
                segment_type,
                bytes / 1024 / 1024
           from dba_segments
          where tablespace_name = 'SYSAUX'
          order by 4 desc)
  where rownum <= 10;
 
 alter table sys.wrh$_active_session_history truncate partition WRH$_ACTIVE_4050449616_0 update global indexes;
 
  








--获取自动收集统计信息的历史执行情况
select * from ALL_SCHEDULER_JOB_RUN_DETAILS


select t.table_name,t.partition_name, t.num_rows,t.last_analyzed  from dba_tab_partitions t where table_name='WM_PRESCRIPTIONS'


select b.table_name,b.num_rows,b.last_analyzed from dba_tables b where table_name='WM_PRESCRIPTIONS'




-- 查看统计信息JOB历史运行次数
SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed
  FROM dba_autotask_client_history
 WHERE client_name like '%stats%';


--查看统计信息JOB历史运行时间
select t1.window_name, t1.repeat_interval, t1.duration
  from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2
 where t1.window_name = t2.window_name
   and t2.window_group_name in
       ('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');


WINDOW_NAME REPEAT_INTERVAL DURATION
------------------------------ ------------------------------------------------------------ ---------------------------------------------------------------------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 0 4:0:0.0
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 0 4:0:0.0
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 0 4:0:0.0
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 0 4:0:0.0
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 0 4:0:0.0
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 0 20:0:0.0
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 0 20:0:0.0


--WINDOW_NAME:任务名
--REPEAT_INTERVAL:任务重复间隔时间
--DURATION:持续时间




修改的步骤:
--1.停止任务
BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."FRIDAY_WINDOW"',
force=>TRUE);
END;


--2.修改任务的持续时间,单位是分钟
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(180, 'minute'));
END;




--3.开始执行时间,BYHOUR=2,表示2点开始执行
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0');
END;




--4.开启任务
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."FRIDAY_WINDOW"');
END;


--------------------add数据文件


SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=''


-- ALTER TABLESPACE TSP_HISDB ADD DATAFILE  
'+DATA' SIZE 100M
AUTOEXTEND ON NEXT 1M MAXSIZE 30G;


--开启自动扩展
ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' AUTOEXTEND ON;


 


--关闭自动扩展
ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' AUTOEXTEND OFF;










------rman
rman


sql 'alter system archive log current';


crosscheck archivelog all;      
Delete noprompt expired archivelog all;  
Delete  noprompt archivelog all completed before 'sysdate - 2';  
delete noprompt archivelog all completed before 'sysdate';
delete noprompt force  archivelog all;   


BACKUP DATABASE FORMAT 's:\backup\%U' SKIP INACCESSIBLE 
PLUS ARCHIVELOG FORMAT 's:\backup\%U'
DELETE ALL INPUT;






BACKUP DATABASE FORMAT '+DATADISK/SXHIS/RMAN/%U' SKIP INACCESSIBLE 
PLUS ARCHIVELOG FORMAT '+DATADISK/SXHIS/RMAN/%U'
DELETE ALL INPUT;


BACKUP DATABASE FORMAT '+DATADISK/EMR/RMAN/%U' SKIP INACCESSIBLE 
PLUS ARCHIVELOG FORMAT '+DATADISK/EMR/RMAN/%U'
DELETE ALL INPUT;










-------------------------------------
查询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;




SELECT s.sid,
s.serial#,
s.machine,sl.opname,sl.target,sl.message,s.program,sl.sql_hash_value,
TRUNC(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
TRUNC(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#
AND sl.totalwork>0
AND sl.sofar<>sl.totalwork






----------------------------






top 启动时间


dmesg |more
 




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12131609/viewspace-2128871/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle 解锁 lock
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-01-07

  • 博文量
    60
  • 访问量
    252007