oracle常用性能视图查询语句

find /目录/ -mtime +1 -name "*.log" -exec rm -f {} \;

--表空间使用率

SELECT a.tablespace_name "tablespace_name",
       round(total / (1024 * 1024 * 1024),4) "tablespace_space(G)",
       round((total - free) / (1024 * 1024 * 1024),4) "tablespace_used(G)",
       round(free / (1024 * 1024 * 1024),4) "tablespace_free(G)",
       round((total - free) / total, 4) * 100 "used%",
       to_char(sysdate,'yyyy-mm-dd hh24:mi;ss')
  FROM (SELECT tablespace_name, SUM(bytes) free
          FROM dba_free_space
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total
          FROM dba_data_files
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name;

 

--锁表

SELECT b.sid,
       b.serial#,
       spid      操作系统ID,
       paddr,
       sql_text  正在执行的SQL,
       b.machine 计算机名 ,
      b.sql_exec_start,
      c.last_load_time
  FROM v$process a, v$session b, v$sqlarea c
 WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value
   and sid in (select t2.sid
                 from v$locked_object t1, v$session t2, DBA_OBJECTS t3
                where t1.session_id = t2.sid
                  and t1.OBJECT_ID = t3.OBJECT_ID
            --      and b.machine = 'gzbusdb' -- 本地计算机，识别存储过程
               -- and t3.object_name=upper('T_CLN_ENER_CONS')
               )

 

select sql_text from v$sqlarea a,v$session b where a.SQL_ID=b.PREV_SQL_ID and b.SID=?;

--解锁语句

alter system kill session 'sid,serial#'

--调度作业

select job,what,failures,broken from user_jobs

exec dbms_job.broken(341,true);
 

begin
 dbms_job.remove(541);
 commit;
end;
/

variable aaa number
begin
 dbms_job.submit(:aaa,'PC_MID_DROP_PART_DAYS;',sysdate,'TRUNC(sysdate) + 1 +1/ (24)',true);
  commit;
end;
/

--收集统计信息

 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');

select table_name, global_stats, last_analyzed, num_rows  from user_tables where table_name='T_COMM_ENER_CONS_STAT_INFO'
select table_name, PARTITION_NAME,global_stats, last_analyzed, num_rows  from  USER_TAB_PARTITIONS where table_name='T_COMM_ENER_CONS_STAT_INFO'

--收集持续时间设置

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(4, 'hour'));
END;
/

--无效对象，含编译错误等

SELECT OBJECT_NAME, STATUS FROM user_OBJECTS WHERE STATUS <> 'VALID';

--分区统计情况

select table_name,
       partition_name,
       last_analyzed,
       partition_position,      
       num_rows
  from user_tab_statistics t
 where table_name ='表名';

--占用空间

1、表占用空间：select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' group by segment_name;

2、索引占用空间：select segment_name ,sum(bytes)/1024/1024 from user_segments where segment_type ='INDEX' group by segment_name;

3、分区表TABLE PARTITION占用空间：select segment_name,sum(bytes)/1024/1024 Mbytes from user_segments where segment_type='TABLE PARTITION' group by segment_name;

--索引失效判断

--分区索引

select index_name,partition_name,status  from user_ind_partitions where  
 partition_name like '%2018%' and status  ='USABLE'

--索引状态为N/A，有可能是分区索引

select TABLE_NAME,INDEX_NAME,status from user_indexes  where TABLE_NAME  in  (select TABLE_NAME from T_DROP_PAR_LIST)
order by TABLE_NAME

  valid:当前索引有效
  N/A  :分区索引有效
  unusable:索引失效

解决办法： 
 1. 重建索引才是解决这类问题的完全的方法。
     alter index index_name rebuild (online);
 2. 如果是分区索引只需要重建那个失效的分区 。
     alter index index_name rebuild partition partition_name (online);

--查看正在运行的存储过程

select a.*,b.serial# from v$access A,v$session B where  A.SID=B.SID
and A.OBJECT like '存储过程名%' 

--系统上删除

select b.spid,a.osuser,b.program from v$session a,v$process b where a.paddr=b.addr  and a.sid=8     --8就是上面的sid
在OS上杀死这个进程（线程）
在unix上，用root身份执行命令：#kill -9 12345（即第2步查询出的spid） 

--正在执行的SQL

SELECT b.sid oracleID,
       b.username 登录Oracle用户名,
       b.serial#,
       spid 操作系统ID,
       paddr,
       sql_text 正在执行的SQL,
       b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value

--用户解锁

alter user can_cs_eq account unlock;

--删除重复记录

delete from BBSCOMMENT a
where
    (a.DETAIL_ID,a.COMMENT_BODY) in(select DETAIL_ID,COMMENT_BODY from BBSCOMMENT group by DETAIL_ID,COMMENT_BODY having count(*) > 1)
    and rowid not in (select min(rowid) from BBSCOMMENT group by DETAIL_ID,COMMENT_BODY having count(*)>1);

---获取oracle前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<11;

--彻底杀掉rman进程spid

SELECT sid, spid, client_info FROM v$process p, v$session s 

WHERE p.addr = s.paddr 

AND client_info LIKE '%rman%';

--彻底终止正在运行的expdp

select job_name,state from dba_datapump_jobs --EXECUTING

expdp dpuser/dpuser ATTACH=SYS_EXPORT_SCHEMA_03

stop_job=immediate

--依赖某个表的对象
select OBJECT_NAME
  from dba_objects
 where OBJECT_ID in (select OBJECT_ID
                       from public_dependency
                      where REFERENCED_OBJECT_ID in
                            (select OBJECT_ID
                               from dba_objects
                              where owner = 'SC'
                                and object_type = 'TABLE'
                                and OBJECT_NAME = 'T_CLN_DRV_SCT_BHV')
                     )

select sql_text
  from v$sqlarea
 where address in
       (select sql_address
          from v$session
         where paddr in (select addr from v$process where spid = 进程号));