自己使用oracle,自己总结的ORACLE日常运维常用的SQL

-- 查看哪个表的记录数最多

select t.num_rows,t.* from dba_tables t where owner='AMRDCS' order by t.num_rows desc;

-- 查询空块多的TABLE

select ex.owner,ex.segment_name,ex.segment_type,round(sum(ex.bytes)/1024/1024) tab_free from dba_free_space sp, dba_extents ex

where sp.block_id=ex.block_id

group by ex.owner,ex.segment_name,ex.segment_type

order by tab_free desc;

-- 查看数据库整体各个时间段的负载，每小时一天记录

WITH sysstat AS

(select sn.begin_interval_time begin_interval_time,

sn.end_interval_time end_interval_time,

ss.stat_name stat_name,

ss.value e_value,

lag(ss.value, 1) over(order by ss.snap_id) b_value

from dba_hist_sysstat ss, dba_hist_snapshot sn

where trunc(sn.begin_interval_time) >= sysdate - 7

and ss.snap_id = sn.snap_id

and ss.dbid = sn.dbid

and ss.instance_number = sn.instance_number

and ss.dbid = (select dbid from v$database)

and ss.instance_number = (select instance_number from v$instance)

and ss.stat_name = 'DB time')

select to_char (BEGIN_INTERVAL_TIME, 'mm-dd hh24:mi') || to_char (END_INTERVAL_TIME, '

hh24:mi') date_time, stat_name, round((e_value - nvl(b_value, 0)) / (extract(day

from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 + extract(hour

from(end_interval_time - begin_interval_time)) * 60 * 60 + extract(minute

from(end_interval_time - begin_interval_time)) * 60 + extract(second

from(end_interval_time - begin_interval_time))), 0) per_sec

from sysstat

where(e_value - nvl(b_value, 0)) > 0 and nvl(b_value, 0) > 0

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

查询归档日志大小

select  t.thread#,sum(t.BLOCKS*t.BLOCK_SIZE/1024/1024/1024) from v$archived_log t where t.FIRST_TIME > sysdate-1 group by t.thread#;

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('0jytavpsa7yaj',0));

select  t.thread#,to_char(t.FIRST_TIME,'yyyymmdd hh24'),round(sum(t.BLOCKS*t.BLOCK_SIZE/1024/1024/1024)) from v$archived_log t

where t.FIRST_TIME > sysdate-1 group by t.thread#,to_char(t.FIRST_TIME,'yyyymmdd hh24')

order by 1,2 ;

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

sql优化相关包

DBMS_ADVISOR, DBMS_SQLTUNE, DBMS_AUTO_SQLTUNE, and DBMS_WORKLOAD_REPOSITORY packages

select * from dba_advisor_definitions;

select * from DBA_ADVISOR_FINDINGS;

select dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE') from dual;

根据SQL_ID查看执行计划

select * from table(dbms_xplan.display_awr('sql_id'));

select * from table(dbms_xplan.display_cursor('sql_id'))

select * from table(dbms_xplan.display_cursor('sql_id',null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));

select * from table(dbms_xplan.display_awr('sql_id'));

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

关联关系查询：

select t.name,t.referenced_owner,t.referenced_name,s.type,s.text

from user_dependencies t,user_source s

where s.name=t.name and upper(s.text) like '%'||t.referenced_name ||'%'

--and t.name=upper('sa_customer_analysis_pkg')

and t.referenced_name=upper('hp_customer_temp_rec');

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

查看备份进度(查询v$session_longops)

select sid,serial#,context,sofar,totalwork round(sofar/totalwork*100,2) "% Complete",

from v$session_longops

where opname like 'RMAN:%'

and opname NOT LIKE 'RMAN: aggregate%';

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

12c 审计

select * from UNIFIED_AUDIT_TRAIL order by event_timestamp desc

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

查询绑定变量值

SELECT SQL_ID,NAME, POSITION, value_string, ANYDATA.accesstimestamp(value_anydata)

From gV$sql_Bind_Capture A

Where sql_id='aqfcs7drzzcfw';