oracle数据字典-权限-角色-参数

每个数据库都提供了各自的数据字典的方案，虽然形式不同，但是目的和作用是一样的，比如在mysql里数据字典是在information_schema 里表现的，sqlserver则是在sys这个系统schema里来展示的。
Oracle的数据字典是Oracle数据库安装之后，自动创建的一系列数据库对象。数据字典是Oracle数据库对象结构的元数据信息。熟悉和深入研究数据字典对象，可以很大程度的帮助我们了解Oracle内部机制。
Oracle字典视图包括四个层次，分别为X$内部表、基础数据字典表、数据字典视图和动态性能视图。

动态性能视图概述

所谓动态性能视图，就是指将内存里的数据或控制文件里的数据以表的形式展现出来。他们实际都是虚拟表，并不是真正的表。只不过是为了能够让我们更好的管理数据库的性能，所以将内存里的活动情况以表的形式展现出来。只要数据库还在运行，就会不断更新动态性能视图。一旦数据库关闭或崩溃，动态性能视图里的数据就会全部丢失，下次重启时则会重新计算。就像所有的数据字典的名词都存在 dictionary里一样，v$fixed_table里存放了所有的动态性能视图的名称。所有的动态性能的属主都是sys，且都以v_$开头，创建同名词时则都以v$开头。

静态数据字典视图

静态数据字典表和视图

数据字典表不能直接访问，但可以通过数据字典视图来访问

动态性能视图
因为它们持续的更新，只要数据库打开在用的话

select * from dictionary;

select * from v$fixed_table;

select * from dba_synonyms;

#################################################
-------------------------

select * from resource_cost;
select * from resource_map;
select * from resource_view;

-----------------------
select * from dictionary;
select * from dict;
select * from dict_columns where table_name like '%PROFILE%';
select * from dba_profiles;
-----------------------

IND is a synonym for USER_INDEXES
OBJ is a synonym for USER_OBJECTS
SYN is a synonym for USER_SYNONYMS
TABS is a synonym for USER_TABLES
select * from ind;

select * from dba_synonyms
where synonym_name like '%TAB%';

-----------------------
select * from session_privs;
select * from session_roles;

select * from role_role_privs;
select * from role_sys_privs;
select * from role_tab_privs;
-----------------------
select * from dba_sys_privs;

select * from V$OBJECT_PRIVILEGE
where object_type_name='TABLE';

select * from dba_tab_privs;
-----------------------

select * from table_privilege_map;
select * from system_privilege_map;
----------------------

select * from sys_objects;

select * from dba_free_space;
select * from dba_undo_extents;
select * from dba_rollback_segs;
select * from dba_segments where owner like 'KYC%';

select * from dba_temp_free_space;
select * from dba_temp_files;

#############################################

静态数据字典视图

#######################################

select * from database_properties;

select * from user_tables;
select * from user_views;
select * from user_constraints;
select * from SYS.USER_CONS_COLUMNS;

select * from user_ts_quotas
select * from dba_ts_quotas

select * from all_users;
select * from dba_users;
select * from user_users;

select * from session_roles;
select * from session_privs;

constraint pk_mid primary key(mid)
constraint fk_mid foreign key(mid) references member(mid)
drop table member cascade constraint
on delete cascade
on delete set null

view
with check option
with read only

ALL_DIRECTORIES describes all directories accessible to the current user.

DBA_DIRECTORIES describes all directory objects in the database. Its columns are the
same as those in "ALL_DIRECTORIES" on page 2-55.

select username,profile from dba_users;
Select * FROM dba_profiles s Where s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

select * from dba_profiles;

动态性能视图

########################

select * from v$asm_template;

select group_number,count(*) from v$asm_template
group by group_number;

alter diskgroup data1 add template reli attributes (unprotected fine);

select * from v$asm_template
where group_number=2;

########################

select * from v$flash_recovery_area_usage;

########################

下面这两个视图是有关系的
select * from v$sesstat;
select sid,count(*) from v$sesstat group by sid;
select * from v$statname;记录了statistic#所代表的含义。

select statistic#,count(*) from v$sesstat group by statistic#;

每一个sid出现679次，总共有28个sid,即视图v$sesstat总共有679*28=19012行数据

########################

V$LOGFILE contains information about redo log files.

所有的日志文件信息（包括online log与standby log两种类型）

########################

V$LOG displays log file information from the control file.

online日志文件信息

########################

select * from v$standby_log;

standby日志文件信息

########################

select * from v$archived_log;

不管是online还是standby，查看所有已经归档的日志

########################

V$LOG_HISTORY displays log history information from the control file.

V$LOGHIST 兼容目的，oracle推荐使用上面的

select * from v$log_history
where thread#=1
order by sequence# desc;
select * from v$log_history
where thread#=2
order by sequence# desc;

select * from v$log_history
order by first_time desc;

##########################
V$MANAGED_STANDBY displays current status information for some Oracle Database
processes related to physical standby databases in the Data Guard environment. This
view does not persist after an instance shutdown.

下面是在主库的rac1上查看动态性能视图的结果集

select * from v$managed_standby;

下面是在备库上查看动态性能视图的结果集

下面的mrp0是非实时应用的状态 wait_for_log

如果是实时应用的状态是 apply log

alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect;

select * from v$managed_standby;

###############################

select * from v$hm_check;
select * from v$hm_check_param;
select * from v$hm_run;

select c.name check_name,p.name parameter_name,p.type,p.default_value,p.description
from v$hm_check_param p,v$hm_check c
where p.check_id=c.id and c.internal_check='N'
order by c.name;

##############################

select * from v$sga;
select * from v$sgainfo;
select * from v$sgastat;
select pool,count(*) from v$sgastat
group by pool;
select sum(bytes) from v$sgastat
where pool='java pool';

select * from v$sgastat
where pool='shared pool';
where pool is null;

================================
内存相关的性能视图
select * from v$sga;
select * from v$sgainfo;
select * from v$sgastat;
select pool,count(*) from v$sgastat
group by pool;
select sum(bytes) from v$sgastat
where pool='java pool';

select * from v$sgastat
where pool='shared pool';
where pool is null;

select * from v$sgastat;
select * from v$pgastat;

select * from v$memory_dynamic_components;
select * from v$sga_dynamic_components;

select * from v$memory_current_resize_ops;
select * from v$sga_current_resize_ops;

select * from v$memory_resize_ops;
select * from v$sga_resize_ops;

select * from v$memory_target_advice;
select * from v$sga_target_advice;
select * from v$pga_target_advice;

##############################

V$LOCK lists the locks currently held by the Oracle Database and outstanding requests
for a lock or latch

outstanding adj. 杰出的；显著的；未解决的；未偿付的

V$LOCKED_OBJECT lists all locks acquired by every transaction on the system. It shows
which sessions are holding DML locks (that is, TM-type enqueues) on what objects
and in what mode

pl/sql source text

select * from user_source;

select * from user_errors;

##############################

初始化参数

##############################

db_name
control_files
--指定下面这个或不指定，默认就是auto,这样就启用了automatic undo management
undo_management=auto
db_create_file_dest
db_recovery_file_dest
db_recovery_file_dest_size
db_create_online_log_dest_n
db_files
db_block_checksum
file_mapping
db_flashback_retention_target=1440
undo_retention=900
audit_trail
audit_file_dest
audit_sys_operations=true
audit_syslog_level

闪回技术涉及的初始化参数与数据字典

undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
db_flashback_retention_target integer 1440
select * from v$restore_point;

parallel_clause涉及的初始化参数

show parameter parallel_degree;

基于parallel_degree_policy初始化参数，如果设置为auto，那么这个子句被忽略，并且优化器来决定所有语句的最佳并行度，如果设置为manual或limited，才起作用

pl/sql涉及的参数

show parameter plsql;

##############################

权限和角色

##############################

包

dbms_stats
字典
dba_tab_modifications
user_tab_modifications
参数
statistics_leveltypical,basic
optimizer_mode ALL_ROWS
权限
create user
create session 最小权限
unlimited tablespace
alter user

SELECT * FROM user_tables
WHERE table_name= AND num_rows is not null;