oracle内置常用表和视图及其日志管理.docx
《oracle内置常用表和视图及其日志管理.docx》由会员分享，可在线阅读，更多相关《oracle内置常用表和视图及其日志管理.docx（18页珍藏版）》请在冰豆网上搜索。
oracle内置常用表和视图及其日志管理
DBA_TABLES、ALL_TABLES以及USER_TABLES此三个视图可以用来查询ORACLE中关系表信息，它们之间的关系和区别有:
DBA_TABLES >= ALL_TABLES >= USER_TABLES
DBA_TABLES意为DBA拥有的或可以访问的所有的关系表。
ALL_TABLES意为某一用户拥有的或可以访问的所有的关系表。
USER_TABLES意为某一用户所拥有的所有的关系表。
由上可知，当某一用户本身就为数据库DBA时，DBA_TABLES与ALL_TABLES等价。
此规律可以类推至Oracle中其它类似名称的视图。
需要注意的是在ORACLE数据库中大小写是敏感的，而此三表中数据默认都是大写的，所以在进行查询的时候注意小写的数据可能会造成数据无法查到。
一.Oracle表明细及说明
1.dba_开头表 dba_users 数据库用户信息
dba_segments 表段信息
dba_extents 数据区信息
dba_objects 数据库对象信息
dba_tablespaces数据库表空间信息
dba_data_files 数据文件设置信息
dba_temp_files 临时数据文件信息
dba_rollback_segs 回滚段信息
dba_ts_quotas 用户表空间配额信息
dba_free_space 数据库空闲空间信息
dba_profiles 数据库用户资源限制信息
dba_sys_privs 用户的系统权限信息
dba_tab_privs 用户具有的对象权限信息
dba_col_privs 用户具有的列对象权限信息
dba_role_privs 用户具有的角色信息
dba_audit_trail 审计跟踪记录信息
dba_stmt_audit_opts 审计设置信息
dba_audit_object 对象审计结果信息
dba_audit_session 会话审计结果信息
dba_indexes 用户模式的索引信息
2.user_开头表
user_objects 用户对象信息
user_source 数据库用户的所有资源对象信息
user_segments 用户的表段信息
user_tables 用户的表对象信息
user_tab_columns 用户的表列信息
关于这个还涉及到两个常用的例子如下：
2.1.oracle中查询某个字段属于哪个表
Sql代码
selecttable_name,ownerfromdba_tab_columnstwheret.COLUMN_NAMElikeupper（'%username%'）;
2.2.oracle中查询某个表的列数
Sql代码
selectcount（*）fromuser_tab_columnswheretable_name=upper（'sys_operate'）
注:
这两个例子都用到了upper这个函数，是因为在这里表名得大写，否则查出的结果不是正确的
user_constraints用户的对象约束信息
user_sys_privs 当前用户的系统权限信息
user_tab_privs 当前用户的对象权限信息
user_col_privs 当前用户的表列权限信息
user_role_privs 当前用户的角色权限信息
user_indexes 用户的索引信息
user_ind_columns 用户的索引对应的表列信息
user_cons_columns 用户的约束对应的表列信息
user_clusters 用户的所有簇信息
user_clu_columns 用户的簇所包含的内容信息
user_cluster_hash_expressions 散列簇的信息
3.v$开头表
v$database 数据库信息
v$datafile 数据文件信息
v$controlfile 控制文件信息
v$logfile 重做日志信息
v$instance 数据库实例信息
v$log 日志组信息
v$loghist 日志历史信息
v$sga 数据库SGA信息
v$parameter 初始化参数信息
v$process 数据库服务器进程信息
v$bgprocess 数据库后台进程信息
v$controlfile_record_section 控制文件记载的各部分信息
v$thread 线程信息
v$datafile_header 数据文件头所记载的信息
v$archived_log 归档日志信息
v$archive_dest 归档日志的设置信息
v$logmnr_contents 归档日志分析的DMLDDL结果信息
v$logmnr_dictionary日志分析的字典文件信息
v$logmnr_logs 日志分析的日志列表信息
v$tablespace 表空间信息
v$tempfile 临时文件信息
v$filestat 数据文件的I/O统计信息
v$undostat Undo数据信息
v$rollname 在线回滚段信息
v$session 会话信息
v$transaction 事务信息
v$rollstat 回滚段统计信息
v$pwfile_users 特权用户信息
v$sqlarea 当前查询过的sql语句访问过的资源及相关的信息
v$sql 与v$sqlarea基本相同的相关信息
v$sysstat 数据库系统状态信息
4.all_开头表
all_users 数据库所有用户的信息
all_objects 数据库所有的对象的信息
all_def_audit_opts 所有默认的审计设置信息
all_tables 所有的表对象信息
all_indexes 所有的数据库对象索引的信息
5.session_开头表
session_roles 会话的角色信息
session_privs 会话的权限信息
6.index_开头表
index_stats 索引的设置和存储信息
二.oracle最重要的9个动态性能视图
v$session+v$session_wait（在10g里功能被整合,凑合算1个吧.）
v$process
v$sql
v$sqltext
v$bh （更宁愿是x$bh）
v$lock
v$latch_children
v$sysstat
v$system_event
三.按组分的几组重要的性能视图
1.System的overview
v$sysstat,v$system_event,v$parameter
2.某个session的当前情况
v$process,v$session,v$session_wait,v$session_event,v$sesstat
3.SQL的情况
v$sql,v$sqlarea,v$SQL_PLAN,V$SQL_PLAN_STATISTICS,v$sqltext_with_newlines
4.Latch/lock/ENQUEUE
v$latch,v$latch_children,v$latch_holder,v$lock,V$ENQUEUE_STAT,V$ENQUEUE_LOCK
5.IO方面的
v$segstat,v$filestat,v$tempstat,v$datafile,v$tempfile
6.sharedpool/Librarycache
v$Librarycache,v$rowcache,x$ksmsp
7.几个advice也不错
v$db_cache_advice,v$PGA_TARGET_ADVICE,v$SHARED_POOL_ADVICE
四、oracle日志文件的整理
1增加日志组
实例1：
为oracle数据库增加一个重做日志组，组号自动分配，组中包含两个日志成员，大小为10M，并查询动态性能视图确定能否成功。
操作步骤如下：
1. 以系统管理员身份登入：
sqlplusw sys/jlfx as sysdba
或sqlplusw sys/wangwangassysdba
2. 增加日志组：
alterdatabasejlfxaddlogfile（'d:
\log\log4a.log','d:
\log\log4b.log'）size10M;或
alterdatabasedog addlogfile（'d:
\log\log4a.log','d:
\log\log4b.log'）size10M;
3. 查询日志信息（包括组号，成员数，成员，每个成员的大小）：
SQL>colmemberformata50
SQL>selecta.group#,a.members,a.bytes,b.memberfromv$loga,v$logfilebwherea.group#=b.group#;
GROUP#MEMBERSBYTES MEMBER
------------------------ ------------------------------------------------
3 1 10485760 D:
\ORACLE\PRODUCT\10.2.0\ORADATA\DOG\REDO03.LOG
2 1 10485760 D:
\ORACLE\PRODUCT\10.2.0\ORADATA\DOG\REDO02.LOG
1 1 10485760 D:
\ORACLE\PRODUCT\10.2.0\ORADATA\DOG\REDO01.LOG
或
SQL>selectgroup#,memberfromv$logfile;
GROUP# MEMBER
------ ------------------------------------------------------------
3 D:
\ORACLE\PRODUCT\10.2.0\ORADATA\DOG\REDO03.LOG
2 D:
\ORACLE\PRODUCT\10.2.0\ORADATA\DOG\REDO02.LOG
1 D:
\ORACLE\PRODUCT\10.2.0\ORADATA\DOG\REDO01.LOG
或
SQL>selectgroup#,members,bytesfromv$log;
GROUP# MEMBERS BYTES
-------------------- ----------------------------------------
1 1 10485760
2 1 10485760
3 1 10485760
例2：
为oracle数据库再增加一个重做日志组，指定组号，组中包含两个日志成员，大小为10M，并查询动态性能视图确定能否成功操作步骤如下：
1. 以系统管理员身份登入：
sqlplusw sys/jlfx as sysdba
2. 增加日志组：
SQL>alterdatabasejlfxaddlogfilegroup5（'d:
\log\log5a.log','d:
\log5b.log'）size10M;
或SQL>alterdatabasedogaddlogfilegroup5（'d:
\log\log5a.log','d:
\log5b.log'）size10M;
3. 查询日志信息：
SQL>colmemberformata50
SQL>selecta.group#,a.status,a.members,a.bytes,b.memberfromv$loga,v$logfilebwherea.group#=b.group#orderbya.group#;
或SQL>selectgroup#,memberfromv$logfile;
或
SQL>selectgroup#,members,bytesfromv$log;
2增加日志成员
实例3：
为oracle服务器的第4个重做日志组增加一个日志成员"log4c.lg".
操作步骤如下：
1. 以系统管理员身份登入：
sqlplusw sys/jlfx as sysdba
2. 增加日志成员：
SQL>alterdatabase jlfxaddlogfilemember'd:
\log\log4c.log'togroup4;
或
SQL>alterdatabase dogaddlogfilemember'd:
\log\log4c.log'togroup4;
3. 查询日志信息：
SQL>colmemberformata50
SQL>selecta.group#,a.status,a.members,a.bytes,b.memberfromv$loga,v$logfilebwherea.group#=b.group#;
或SQL>selectgroup#,memberfromv$logfile;（显示用户数据库日志组和成员）
或SQL>selectgroup#,members,bytesfromv$log;
练习：
为每个日志组添加一个日志成员。
3删除日志组（注意：
current/active状态下或只有两个日志组都不能删除该日志组）
实例4：
删除日志组4
操作步骤如下：
1. 以系统管理员身份登入：
sqlplusw sys/jlfx as sysdba
2. 查看日志组的状态（只有INACTIVE和UNUSED状态的可删除）
SQL>selectgroup#,status,members,bytes fromv$log;
3. 删除日志组4（下面的命令只是逻辑删除，要物理删除得用os命令）
SQL>alterdatabasejlfxdroplogfilegroup4;
或SQL>alterdatabasedogdroplogfilegroup4;
此时不能再往日志组4中添加成员，如：
alterdatabase dogaddlogfilemember'd:
\log\log4d.log'togroup4;
出现错误信息：
第 1 行出现错误:
ORA-00359:
日志文件组 4 不存在
如果2号日志组是当前日志组，执行删除2号日志组的操作，则会出现错误。
SQL>alterdatabasedogdroplogfilegroup2;
错误提示信息如下：
alterdatabasedogdroplogfilegroup2
*
第 1 行出现错误:
ORA-01623:
日志 2 是实例 dog（线程 1） 的当前日志 - 无法删除
ORA-00312:
联机日志 2 线程 1:
'D:
\ORACLE\PRODUCT\10.2.0\ORADATA\DOG\REDO02.LOG'
4. 查询日志信息：
SQL>colmemberformata50
SQL>selecta.group#,a.status,a.members,a.bytes,b.memberfromv$loga,v$logfilebwherea.group#=b.group#;
或SQL>selectgroup#,memberfromv$logfile;
或SQL>selectgroup#,members,bytesfromv$log;
4删除日志成员（注意：
current状态下或日志组中只有一个日志成员时不能删除）
实例5：
删除日志组5中的第2个日志组成员
操作步骤如下：
1. 以系统管理员身份登入：
sqlplusw sys/jlfx as sysdba
2. 查看日志组的状态
SQL>selectgroup#,status,members,bytes fromv$log;
3. 删除日志组4中的第2、3个日志组成员
SQL>alterdatabasedogdroplogfilemember'd:
\log\log4b.log','d:
\log\log4c.log';
4. 查询日志信息：
SQL>colmemberformata50
SQL>selecta.group#,a.status,a.members,a.bytes,b.memberfromv$loga,v$logfilebwherea.group#=b.group#;
或SQL>selectgroup#,memberfromv$logfile;
或SQL>selectgroup#,members,bytes,statusfromv$log;
注意：
日志组中只有一个日志成员时不能删除，如：
SQL>alterdatabasedogdroplogfilemember'd:
\log\log4a.log';
此时，出现如下信息：
第 1 行出现错误:
ORA-00361:
无法删除最后一个日志成员 d:
\log\log4a.log（组 4）
5清空重做日志
实例6：
清空重做日志组4
操作步骤如下：
1. 以系统管理员身份登入：
sqlplusw sys/jlfx as sysdba
2. 关闭数据库
SQL>shutdownimmediate
删除4号日志组的所有成员，再启动则出现错误：
3. 启动数据库
SQL>startup
ORACLE 例程已经启动。
错误提示信息如下：
TotalSystemGlobalArea 285212672bytes
FixedSize 1248552bytes
VariableSize 71303896bytes
DatabaseBuffers 205520896bytes
RedoBuffers 7139328bytes
数据库装载完毕。
ORA-00313:
无法打开日志组 4（用于线程 1） 的成员
ORA-00312:
联机日志 4 线程 1:
'D:
\LOG\LOG4A.LOG'
ORA-00312:
联机日志 4 线程 1:
'D:
\LOG\LOG4C.LOG'
ORA-00312:
联机日志 4 线程 1:
'D:
\LOG\LOG4B.LOG'
查看数据库的启动状态：
SQL>selectstatusfromv$instance;
或直接启动数据库到加载状态（特别注意）
SQL>startup mount
4. 清空重做日志组4，恢复该日志组的成员
SQL>alterdatabase clearlogfile group4;
5. 查询日志信息：
SQL>colmemberformata50
SQL>selecta.group#,a.status,a.members,a.bytes,b.memberfromv$loga,v$logfilebwherea.group#=b.group#;
或
SQL>selecta.group#,a.status,a.membersfromv$loga,v$logfilebwherea.group#=b.group#;
6.改变日志的模式
SQL>altersystemswitchlogfile;
6移动日志文件
实例7：
移动日志文件，观察日志文件丢失时的现象，并重新定位。
操作步骤如下：
1. 以系统管理员身份登入：
sqlplusw sys/jlfx as sysdba
2. 关闭数据库
shutdown
3. 物理移动日志成员到目标位置
将文件d:
\oracle\product\10.2.0\oradata\jlfx\REDO03.log 移动到 d:
\REDO03.log;
4. 打开数据库
startup;
5. 执行rename命令，重新定位日志文件
alter database jlfx rename file'd:
\o
升级会员 