整理oracle数据字典,oracle结构梳理---数据字典
数据字典(datadictionary)是 Oracle数据库的一个重要组成部分，这是一组用于记录数据库信息的表
u 数据库中所有方案对象(schemaobject)的定义(包括表，视图，索引，簇，同义词，序列，过程，函数，包，触发器等等)
u 数据库为一个方案对象分配了多少空间，以及该对象当前使用了多少空间
u 列的默认值
u 完整性约束(integrityconstraint)信息
u 数据库用户信息
u 每个用户被授予(grant)的权限(privilege)与角色(role)
u 审计(audit)信息，例如哪个用户对某个方案对象进行了访问或更新操作
u 数据库中的其他概要信息
数据字典，分为数据字典表(视图)和动态性能表(视图)
--数据字典:
基表(以$结尾):用于存放所有数据库对象信息
--数据字典视图:
DBA_ 数据库中所有对象的信息。
ALL_ 用户有权限访问的所有的对象.
USER_ 用户自己创建的
---常用的数据字典视图:
user_tables: 该用户表的信息
all_tables
dba_tables
user_indexes: 该用户索引的信息
all_
dba-_
user_views: 该用户视图的信息
user_users : 当前用户的信息
dba_constraints 约束信息
dba_extents、dba_segments区，段的信息
dba_sys_privs,dba_roles 权限角色信息等
dba_data_files 数据文件信息
1. Oracle server使用它来获取用户、schema对象、存储结构信息
2. 执行DDL语句时，oracle server会去修改它
3.用户和DBA把它作为只读查询来获取数据库的信息
--基表(以$结尾):用于存放所有数据库对象信息
基于数据字典基表的数据字典视图(DBA_, ALL_, USER_)
数据字典表 由$ORACLE_HOME/rdbms/admin/sql.bsq 脚本创建，
数据字典视图 由脚本$ORACLE_HOME/rdbms/admin/catalog.sql 创建
动态性能表并不是真正的表,由SYS用户所拥有,它是内存表
动态性能表
--- 动态性能表(X$开头)
--动态性能视图(V_$开头或GV_$开头)
动态性能视图的结构信息在v$fixed_view_definition
v_ 单个实例
gv_所有的实例,RAC
v$session
gv$session
--- 动态性能视图的同义词(V$开头或GV$开头)
动态性能视图也叫v$视图，它包含如下内容：
(1)system和session的数据
(2) 内存的使用和分配
(3) 文件的状态，包括RMAN备份文件
(4) Jobs和task(任务)的进程
(5) 执行的SQL
(6) 统计信息
-----数据字典的使用1：
-----chain_row
ANALYZETABLELISTCHAINEDROWS;
这一个临时表的意思 首先我们进行一个analyze的分布 其次： 数据就进了CHAINED_ROWS临时表里面
---dba_segments
select*fromdba_segments;
数据库大小就在dba_segments中的bytes或size，blocks和dba_tables里面的是有区别的，dba_tables中的是假的，是评估的。。。
object_id 和data_object_id，当表被truncate之后，data_object_id会改变，object_id不会变
----dba_indexes
select*fromdba_indexes;
dba_indexs中有一个cluster_factor
---dba_tables
dba_tables中的compression字段表示的是这个表是否是压缩表
---v$session
select*fromv$session;--其中的Paddr(v$process)TADDR(V$TRANSACTION)
---V$TRANSACTION
SELECT*FROMV$TRANSACTION;
---v$process
select*fromv$process;
1. 如何通过数据字典从sql_text里面找到存储过程？
select*fromdba_sourcewhereupper(text)like'%xxx%';
SELECT*fromDba_SourceASOFTIMESTAMPsysdate-2/1440WHERENAME='FLASH_BACK';-----sql_test修改之前的文本
对于正在运行的sql，v$sql里面有个program_id，可以通过program_id，可以通过其查找正在运行到sql属于哪个对象
selecta.program_id,
c.spid,
b.sid,
b.USERNAME,
a.child_number,
a.sql_id,
a.SQL_TEXT,
a.SQL_FULLTEXT,
a.EXECUTIONS ex,
trunc( a.ROWS_PROCESSED/casewhena.EXECUTIONS =0then1elsea.EXECUTIONSend)as"rows/exe" ,
a.FIRST_LOAD_TIME,
a.LAST_ACTIVE_TIME,
b.MACHINE,
b.MODULE,
AUDSID,
d.EVENT,
d.STATE,
d.WAIT_TIME,
d.SECONDS_IN_WAIT,
c.PGA_ALLOC_MEM,
b.service_name,
'alter system kill session '''|| to_char(b.SID) ||','||
to_char(b.SERIAL#) ||''';'killse
fromv$sql a, v$session b, v$process c, v$session_wait d
wherea.SQL_ID = b.SQL_ID
andb.PADDR = c.ADDR
andb.SID = d.SID
andb.STATUS='ACTIVE'
ANDB.SID<>(SELECTSIDFROMV$MYSTATWHEREROWNUM<2)
orderbyb.username, a.SQL_TEXT;
select*fromall_objectswhereobject_id='57473'---代入program_id
通过修改表结构达到硬解析的功能
grantselectona.tttoscott;--权限修改了 执行计划就肯定改了
CREATEORREPLACEVIEWV$SESS_STATAS
SELECTms.SID,
sn.statistic#,
sn.name,
sn.class,
ms.value
FROM
v$sesstat ms,
v$statname sn
WHEREsn.statistic# = ms.statistic#
andms.value <>0;
--select * from V$SESS_STAT where sid='77' and name like '%redo%';
---------------------------------------------------------------
v$sql_plan---SQL的真实 执行计划
dba_hist_sql_plan---如果查询历史数据
v$sql----鉴别创建索引表的效果，寻找cursor包括的存取路径鉴别索引是不是最优的，查看执行计划，监控执行计划
v$sess_stat---自己建立的视图，查看会话层面各种信息
v$sqlstats----sql层面的各种信息
elapsed_time=cputime+ wait_time---消耗的总时间buffer_gets逻辑读plan_hash_value执行计划id，根据这个id+v$sql_plan可以找出执行计划，如果有两个
----plan_hash_value说明执行计划改变了，我们可以比较一下开销，不是有sql_id就可以找到这个sql的绑定变量值 ，
---默认是900秒，才会重新开始捕获。在900内，绑定变量值的改变不会反应在这个视图中
v$active_session_history dba_hist_active_session_history
v$active_session_history
-----就是大名鼎鼎的ash
ASH以V$SESSION为基础，每秒采样一次，记录活动会话等待的事件。不活动的会话不会采样，采样工作由新引入的后台进程MMNL来完成。
ASH buffers 的最小值为1MB,最大值不超过30MB。内存中记录数据。期望值是记录一小时的内容。
把session_id传进去然后对于sql_id进行group by就可以知道整个会话最耗时的是哪个sql
select*FROMv$active_session_history;
selectsql_id,count(1)fromv$active_session_historygroupbysql_id;
select*fromv$sqlwheresql_id='48wqjkfmkxu2f';
@?/rdbms/admin/awrrpt
@?/rdbms/admin/addmrpt
@?/rdbms/admin/awrsqrpi (查看具体SQL的执行计划)
v$session (当前正在发生)
v$session_wait (当前正在发生)
v$session_wait_history (会话最近的10次等待事件)
v$active_session_history (内存中的ASH采集信息，理论为1小时)
wrh$_active_session_history (写入AWR库中的ASH信息，理论为1小时以上)
dba_hist_active_sess_history (根据wrh$_active_session_history生成的视图)
createorreplaceprocedurep_monit_active_sessionas
--将active_session写入历史表
begin
--当前时间
vCurtime :=sysdate;
deletefromdatasync_prc.Active_Session_History
wheresample_time >= vCurtime -20/ (24*60);
insertintodatasync_prc.Active_Session_History
select*
fromGv$active_Session_History
wheresample_time > vCurtime -20/ (24*60);
commit;
/*p_log('p_monit_active_session', '将active_session写入历史表');*/
EXCEPTION
whenothersTHEN
sendmail(Subject =>'p_monit_active_session',v_Msg =>SQLERRM,Receipint =>'huangchao@richinfo.cn');
end;