Oracle数据库数据对象分析

Oracle数据库数据对象中最基本的是表和视图 其他还有约束 序列 函数 存储过程 包 触发器等 对数据库的 *** 作可以基本归结为对数据对象的 *** 作 理解和掌握Oracle数据库对象是学习Oracle的捷径 表和视图Oracle中表是数据存储的基本结构 ORACLE 引入了分区表和对象表 ORACLE i引入了临时表 使表的功能更强大 视图是一个或多个表中数据的逻辑表达式 本文我们将讨论怎样创建和管理简单的表和视图 管理表表可以看作有行和列的电子数据表 表是关系数据库中一种拥有数据的结构 用CREATE TABLE语句建立表 在建立表的同时 必须定义表名 列 以及列的数据类型和大小 例如 CREATE TABLE products( PROD_ID NUMBER( ) 　PROD_NAME VAECHAR ( ) 　STOCK_QTY NUMBER( ))这样我们就建立了一个名为products的表 关键词CREATE TABLE后紧跟的表名 然后定义了三列 同时规定了列的数据类型和大小 在创建表的同时你可以规定表的完整性约束 也可以规定列的完整性约束 在列上普通的约束是NOT NULL 关于约束的讨论我们在以后进行 在建立或更改表时 可以给表一个缺省值 缺省值是在增加行时 增加的数据行中某一项值为null时 oracle即认为该值为缺省值 下列数据字典视图提供表和表的列的信息 DBA_TABLES DBA_ALL_TABLES USER_TABLES USER_ALL_TABLES ALL_TABLES ALL_ALL_TABLES DBA_TAB_COLUMNS USER_TAB_COLUMNS ALL_TAB_COLUMNS表的命名规则表名标识一个表 所以应尽可能在表名中描述表 oracle中表名或列名最长可以达 个字符串 表名应该以字母开始 可以在表名中包含数字 下划线 # $等 从其它表中建立表可以使用查询从基于一个或多个表中建立表 表的列的数据类型和大小有查询结果决定 建立这种形式的表的查询可以选择其他表中所有的列或者只选择部分列 在CREATE TABLE语句中使用关键字AS 例如:SQL>CREATE TABLE emp AS SELECT * FROM employeeTABLE CREATEDSQL>CREATE TABLE Y AS SELECT * FROM X WHERE no= 需要注意的是如果查询涉及LONG数据类型 那么CREATE TABLE AS SELECT 将不会工作 更改表定义在建立表后 有时候我们可能需要修改表 比如更改列的定义 更改缺省值 增加新列 删除列等等 ORACLE使用ALTER TABLE语句来更改表的定义 增加列语法 ALTER TABLE [schema ] table_name ADD column_definition 例 ALTER TABLE orders ADD order_date DATETABLE ALTER 对于已经存在的数据行 新列的值将是NULL 更改列语法 ALTER TABLE [schema ] table_name MODIFY column_name new_attributes例 ALTER TABLE orders MODITY (quantity number( ) status varchar ( ))这个例子中我们修改了表orders 将STATUS列的长度增加到 将QUANTITY列减小到 修改列的规则如下 可以增加字符串数据类型的列的长度 数字数据类型列的精度 减少列的长度时 该列应该不包含任何值 所有数据行都为NULL 改变数据类型时 该列的值必须是NULL 对于十进制数字 可以增加或减少但不能降低他的精度 删除数据列优化ORACLE数据库 唯一的方法是删除列 重新建立数据库 在ORACLE i中有很多方法删除列 你可以删除未用数据列或者可以标示该列为未用数据列然后删除 删除数据列的语法是 ALTER TABLE [schema ] table_name DROP {COLUM column_names | (column_names)}[CASCADE CONSTRAINS] 要注意的是在删除列时关于该列的索引和完整性约束也同时删除 注意关键字CASCADE CONSTRAINS 如果删除的列是多列约束的一部分 那么这个约束条件相对于其他列也同时删除 如果用户担心在大型数据库中删除列要花太多时间 可以先将他们标记为未用数据列 标记未用数据列的语法如下 ALTER TABLE [schema ] table_name SET UNUSED {COLUM column_names | (column_names)}[CASCADE CONSTRAINS] 这个语句将一个或多个数据列标记为未用数据列 但并不删除数据列中的数据 也不释放占用的磁盘空间 但是 未用数据列在视图和数据字典中并不显示 并且该数据列的名称将被删除 新的数据列可以使用这个名称 基于该数据列的索引 约束 统计等都将被删除 删除未用数据列的语句是 ALTER TABLE [schema ] table_name DROP {UNUSED COLUM | COLUMN CONTINUE} 删除表和更改表名删除表非常简单 但它是一个不可逆转的行为 语法 DROP TABLE [schema ] table_name [CASCADE CONSTRAINTS] 删除表后 表上的索引 触发器 权限 完整性约束也同时删除 ORACLE不能删除视图 或其他程序单元 但oracle将标示他们无效 如果删除的表涉及引用主键或唯一关键字的完整性约束时 那么DROP TABLE语句就必须包含CASCADE CONSTRAINTS子串 更改表名RENAME命令用于给表和其他数据库对象改名 ORACLE系统自动将基于旧表的完整性约束 索引 权限转移到新表中 ORACLE同时使所有基于旧表的数据库对象 比如视图 程序 函数等 为不合法 语法 RENAME old_name TO new_name例 SQL>RENAME orders TO purchase_ordersTABLE RENAMED截短表TRUNCATE命令与DROP命令相似 但他不是删除整个数据表 所以索引 完整性约束 触发器 权限等都不会被删除 缺省情况下将释放部分表和视图空间 如果用户不希望释放表空间 TRUNCATE语句中要包含REUSE STORAGE子串 TRUNCATE命令语法如下 TRUNCATE {TABLE|CLUSTER} [schema ] name {DROP|REUSE STORAGE} 例 SQL>TRUNCATE TABLE t TABLE truncate 管理视图视图是一个或多个表中的数据的简化描述 用户可以将视图看成一个存储查询（stored query)或一个虚拟表(virtual table) 查询仅仅存储在oracle数据字典中 实际的数据没有存放在任何其它地方 所以建立视图不用消耗其他的空间 视图也可以隐藏复杂查询 比如多表查询 但用户只能看见视图 视图可以有与他所基于表的列名不同的列名 用户可以建立限制其他用户访问的视图 建立视图CREATE VIEW命令创建视图 定义视图的查询可以建立在一个或多个表 或其他视图上 查询不能有FOR UPDATE子串 在早期的ORACLE i版本中不支持ORDER BY子串 现在的版本中CREATE VIEW可以拥有ORDER BY子串 例 SQL>CREATE VIEW TOP_EMP AS SELECT empno EMPLOYEE_ID ename EMPLOYEE_NAME salary FROM empWHERE salary >用户可以在创建视图的同时更改列名 方法是在视图名后立即加上要命名的列名 重新定义视图需要包含OR REPLACE子串 SQL>CREATE VIEW TOP_EMP（EMPLOYEE_ID EMPLOYEE_NAME SALARY) AS SELECT empno ename salary FROM empWHERE salary >如果在创建的视图包含错误在正常情况下 视图将不会被创建 但如果你需要创建一个带错误的视图必须在CREATE VIEW语句中带上FORCE选项 如 CREATE FORCE VIEW ORDER_STATUS AS SELECT * FROM PURCHASE_ORDERSWHERE STATUS= APPPOVE SQL>/warning :View create with pilation errors 这样将创建了一个名为ORDER_STATUS的视图 但这样的视图的状态是不合法的 如果以后状态发生变化则可以重新编译 其状态也变成合法的 从视图中获得数据从视图中获得数据与从表中获得数据基本一样 用户可以在连接和子查询中使用视图 也可以使用SQL函数 以及所有SELECT语句的字串 插入 更新 删除数据用户在一定的限制条件下可以通过视图更新 插入 删除数据 如果视图连接多个表 那么在一个时间里只能更新一个表 所有的能被更新的列可以在数据字典USER_UPDATETABLE_COLUMNS中查到 用户在CREATE VIEW中可以使用了WITH子串 WITH READ ONLY子串表示创建的视图是一个只读视图 不能进行更新 插入 删除 *** 作 WITH CHECK OPTION表示可以进行插入和更新 *** 作 但应该满足WHERE子串的条件 这个条件就是创建视图WHERE子句的条件 比如在上面的例子中用户创建了一个视图TOP_EMP 在这个视图中用户不能插入salary小于 的数据行 删除视图删除视图使用DROP VIEW命令 同时将视图定义从数据字典中删除 基于视图的权限也同时被删除 其他涉及到该视图的函数 视图 程序等都将被视为非法 例 DROP VIEW TOP_EMP完整性约束 完整性约束用于增强数据的完整性 Oracle提供了 种完整性约束 　 Check　 NOT NULL　 Unique　 Primary　 Foreign key完整性约束是一种规则 不占用任何数据库空间 完整性约束存在数据字典中 在执行SQL或PL/SQL期间使用 用户可以指明约束是启用的还是禁用的 当约束启用时 他增强了数据的完整性 否则 则反之 但约束始终存在于数据字典中 禁用约束 使用ALTER语句ALTER TABLE table_name DISABLE CONSTRAINT constraint_name或ALTER TABLE policies DISABLE CONSTRAINT c_gender lishixinzhi/Article/program/Oracle/201311/18217

Oracle是关系型数据库管理系统 它功能强大 性能卓越 在当今大型数据库管理系统中占有重要地位 在正常情况下 Oracle数据库会保证数据的安全 稳定 为用户提供正确的数据 但由于计算机系统的故障(硬件故障 软件故障 网络故障和系统故障)影响数据库系统的 *** 作 影响数据库中数据的正确性 甚至破坏数据库 使数据库中全部或部分数据丢失 整个系统都将处于瘫痪状态 因此 如何保证Oracle数据库的安全就成为整个系统安全的重要组成部分

Oracle数据库的安全策略包括数据库的备份和恢复 用户角色管理

一 数据库备份所使用的结构

Oracle数据库使用几种结构来保护数据 数据库后备 日志 回滚段和控制文件

数据库后备是由构成Oracle数据库的物理文件的 *** 作系统后备所组成 当介质故障时进行数据库恢复 利用后备文件恢复毁坏的数据文件或控制文件

每一个Oracle数据库实例都提供日志 记录数据库中所作的全部修改 每一个运行的Oracle数据库实例相应地有一个在线日志 它与Oracle后台进程LGWR一起工作 立即记录该实例所作的全部修改 归档(离线)日志是可选择的 一个Oracle数据库实例一旦在线日志填满后 可形成在线日志归档文件 归档的在线日志文件被唯一标识并合并成归档日志

回滚段用于存储正在进行的事务(为未提交的事务)所修改值的老值 该信息在数据库恢复过程中用于撤消任何非提交的修改

控制文件 一般用于存储数据库的物理结构的状态 控制文件中某些状态信息在实例恢复和介质恢复期间用于引导Oracle

二 在线日志

一个Oracle数据库的每一实例有一个相关联的在线日志 一个在线日志由多个在线日志文件组成 在线日志文件(online redo log file)填入日志项(redo entry) 日志项记录的数据用于重构对数据库所作的全部修改

三 归档日志

Oracle要将填满的在线日志文件组归档时 则要建立归档日志(archived redo log) 其对数据库备份和恢复有下列用处

数据库后备以及在线和归档日志文件 在 *** 作系统和磁盘故障中可保证全部提交的事物可被恢复

在数据库打开和正常系统使用下 如果归档日志是永久保存 在线后备可以进行和使用

数据库可运行在两种不同方式下 NOARCHIVELOG方式或ARCHIVELOG 方式 数据库在NOARCHIVELOG方式下使用时 不能进行在线日志的归档 如果数据库在ARCHIVELOG方式下运行 可实施在线日志的归档

四 Oracle的备份特性

Oracle备份包括逻辑备份和物理备份

逻辑备份

数据库的逻辑备份包含读一个数据库记录集和将记录集写入文件

( )输出(Export)输出可以是整个数据库 指定用户或指定表

( )输入(Import)输入将输出建立的二进制转储文件读入并执行其命令

物理备份

物理备份包含拷贝构成数据库的文件而不管其逻辑内容

Oracle支持两种不同类型的物理文件备份 脱机备份(offline backup)和联机备份(online backup)

( ) 脱机备份

脱机备份用在当数据库已正常关闭 数据库处于 offline 时 要备份下列文件

所有数据文件

所有控制文件

所有联机日志

init ora(可选的)

( ) 联机备份

联机备份可用来备份任何运作在ARCHIVELOG方式下的数据库 在这种方式下 联机日志被归档 在数据库内部建立一个所有作业的完整记录

联机备份过程具备强有力的功能 第一 提供了完全的时间点(point in time)恢复 第二 在文件系统备份时允许数据库保持打开状态

备份方式特性比较

五 Oracle数据库的角色管理

Oracle数据库系统在利用角色管理数据库安全性方面采取的基本措施有

通过验证用户名称和口令 防止非Oracle用户注册到Oracle数据库 对数据库进行非法存取 *** 作

授予用户一定的权限 限制用户 *** 纵数据库的权力

授予用户对数据库实体的存取执行权限 阻止用户访问非授权数据

提供数据库实体存取审计机制 使数据库管理员可以监视数据库中数据的存取情况和系统资源的使用情况

采用视图机制 限制存取基表的行和列集合

六 Oracle数据库的安全策略

由于Oracle数据库备份有三种方式 每种方式具有不同的恢复特性 因此应集成数据库与文件系统备份 集成逻辑备份和物理备份

(一) 备份策略

在 *** 作系统级别 使用大容量磁盘阵列 通过磁盘映像技术使每一个数据库文件自动分布于每个物理磁盘 这样 当某个磁盘出现物理损坏时 *** 作系统会自动引发映像磁盘来取代失效的磁盘 保证数据库的正常运行

在多个不同的物理磁盘上保持多个控制文件的备份 控制文件在数据库恢复期间用于引导Oracle 因此保持多个控制文件的备份 可以确保在出现磁盘故障后 能有可用的控制文件用于数据库恢复

使数据库运行在ARCHIVELOG(归档)方式下 归档日志存放于另一映像的逻辑磁盘上 每晚进行一次联机备份 *** 作 备份所有数据文件 所有归档日志文件 一个控制文件 每周进行一次输出(Export) *** 作

(二) 恢复策略

实例失败

从实例失败中恢复是自动进行的 实例失败一般是由服务器失败引起的 当数据库实例失败后 重新启动服务器 启动数据库 Oracle检查数据文件和联机日志文件 并把所有文件同步到同一个时间点上

磁盘失败

如果丢失的是控制文件 只要关闭数据库 从保留有控制文件的地方拷贝一份即可

如果丢失的是数据文件 可用前一天晚上的联机备份进行恢复 步骤如下

)从备份中把丢失的文件存在原来位置

)加载数据库

)恢复数据库

)打开数据库

错误删除或修改对象

在这种情况下 一般希望能追溯返回到错误发生前的那个时间点上 这叫做时间点恢复 完成恢复的步骤如下

)从当前数据库输出(Export) 输入(Import)到备用数据库 使备用数据库与当前数据库保持一致

)向前滚动备用数据库到错误发生前的那个时间点

)从备用数据库输出受错误影响的逻辑对象

)使用上一步产生的输出文件输入那些受影响的对象到当前数据库

(三) 用户角色管理

对所有客户端按工作性质分类 分别授予不同的用户角色

对不同的用户角色 根据其使用的数据源 分别授予不同的数据库对象存取权限

lishixinzhi/Article/program/Oracle/201311/16847

作为Oracle DBA 我们有时候需要追踪数据误删除或用户的恶意 *** 作情况 此时我们不仅需要查出执行这些 *** 作的数据库账号 还需要知道 *** 作是由哪台客户端（IP地址等）发出的 针对这些问题 一个最有效实用而又低成本的方法就是分析Oracle数据库的日志文件 本文将就Oracle日志分析技术做深入探讨 一 如何分析即LogMiner解释 从目前来看 分析Oracle日志的唯一方法就是使用Oracle公司提供的LogMiner来进行 Oracle数据库的所有更改都记录在日志中 但是原始的日志信息我们根本无法看懂 而LogMiner就是让我们看懂日志信息的工具 从这一点上看 它和tkprof差不多 一个是用来分析日志信息 一个则是格式化跟踪文件 通过对日志的分析我们可以实现下面的目的 查明数据库的逻辑更改 侦察并更正用户的误 *** 作 执行事后审计 执行变化分析 不仅如此 日志中记录的信息还包括 数据库的更改历史 更改类型（INSERT UPDATE DELETE DDL等） 更改对应的SCN号 以及执行这些 *** 作的用户信息等 LogMiner在分析日志时 将重构等价的SQL语句和UNDO语句（分别记录在V$LOGMNR_CONTENTS视图的SQL_REDO和SQL_UNDO中） 这里需要注意的是等价语句 而并非原始SQL语句 例如 我们最初执行的是 delete a where c <>cyx 而LogMiner重构的是等价的 条DELETE语句 所以我们应该意识到V$LOGMNR_CONTENTS视图中显示的并非是原版的现实 从数据库角度来讲这是很容易理解的 它记录的是元 *** 作 因为同样是 delete a where c <>cyx 语句 在不同的环境中 实际删除的记录数可能各不相同 因此记录这样的语句实际上并没有什么实际意义 LogMiner重构的是在实际情况下转化成元 *** 作的多个单条语句 另外由于Oracle重做日志中记录的并非原始的对象（如表以及其中的列）名称 而只是它们在Oracle数据库中的内部编号（对于表来说是它们在数据库中的对象ID 而对于表中的列来说 对应的则是该列在表中的排列序号 COL COL 等） 因此为了使LogMiner重构出的SQL语句易于识别 我们需要将这些编号转化成相应的名称 这就需要用到数据字典（也就说LogMiner本身是可以不用数据字典的 详见下面的分析过程） LogMiner利用DBMS_LOGMNR_D BUILD()过程来提取数据字典信息 LogMiner包含两个PL/SQL包和几个视图 dbms_logmnr_d包 这个包只包括一个用于提取数据字典信息的过程 即dbms_logmnr_d build()过程 dbms_logmnr包 它有三个过程 add_logfile(name varchar options number) 用来添加/删除用于分析的日志文件 start_logmnr(start_scn number end_scn number start_time number end_time number dictfilename varchar options number) 用来开启日志分析 同时确定分析的时间/SCN窗口以及确认是否使用提取出来的数据字典信息 end_logmnr() 用来终止分析会话 它将回收LogMiner所占用的内存 与LogMiner相关的数据字典 v$logmnr_dictionary LogMiner可能使用的数据字典信息 因logmnr可以有多个字典文件 该视图用于显示这方面信息 v$logmnr_parameters 当前LogMiner所设定的参数信息 v$logmnr_logs 当前用于分析的日志列表 v$logmnr_contents 日志分析结果 二 Oracle i LogMiner的增强支持更多数据/存储类型 链接/迁移行 CLUSTER表 *** 作 DIRECT PATH插入以及DDL *** 作 在V$LOGMNR_CONTENTS的SQL_REDO中可以看到DDL *** 作的原句（CREATE USER除外 其中的密码将以加密的形式出现 而不是原始密码） 如果TX_AUDITING初始化参数设为TRUE 则所有 *** 作的数据库账号将被记录 提取和使用数据字典的选项 现在数据字典不仅可以提取到一个外部文件中 还可以直接提取到重做日志流中 它在日志流中提供了 *** 作当时的数据字典快照 这样就可以实现离线分析 允许对DML *** 作按事务进行分组 可以在START_LOGMNR()中设置MITTED_DATA_ONLY选项 实现对DML *** 作的分组 这样将按SCN的顺序返回已经提交的事务 支持SCHEMA的变化 在数据库打开的状态下 如果使用了LogMiner的DDL_DICT_TRACKING选项 Oracle i的LogMiner将自动对比最初的日志流和当前系统的数据字典 并返回正确的DDL语句 并且会自动侦察并标记当前数据字典和最初日志流之间的差别 这样即使最初日志流中所涉及的表已经被更改或者根本已经不存在 LogMiner同样会返回正确的DDL语句 在日志中记录更多列信息的能力 例如对于UPDATE *** 作不仅会记录被更新行的情况 还可以捕捉更多前影信息 支持基于数值的查询 Oracle i LogMiner在支持原有基于元数据（ *** 作 对象等）查询的基础上 开始支持基于实际涉及到的数据的查询 例如涉及一个工资表 现在我们可以很容易地查出员工工资由 变成 的原始更新语句 而在之前我们只能选出所有的更新语句 三 Oracle i/ i的日志分析过程 LogMiner只要在实例起来的情况下都可以运行 LogMiner使用一个字典文件来实现Oracle内部对象名称的转换 如果没有这个字典文件 则直接显示内部对象编号 例如我们执行下面的语句 delete from C A where C = gototop and ROWID = AAABg AAFAAABQaAAH 如果没有字典文件 LogMiner分析出来的结果将是 delete from UNKNOWN OBJ# where COL = HEXTORAW( d a d ae ) and ROWID = AAABg AAFAAABQaAAH 如果想要使用字典文件 数据库至少应该出于MOUNT状态 然后执行dbms_logmnr_d build过程将数据字典信息提取到一个外部文件中 下面是具体分析步骤 确认设置了初始化参数 UTL_FILE_DIR 并确认Oracle对改目录拥有读写权限 然后启动实例 示例中UTL_FILE_DIR参数如下 SQL>show parameter utlNAME TYPEvalue utl_file_dir string　/data /cyx/logmnr 这个目录主要用于存放dbms_logmnr_d build过程所产生的字典信息文件 如果不用这个 则可以不设 也就跳过下面一步 生成字典信息文件 exec dbms_logmnr_d build(dictionary_filename =>dic ora dictionary_location =>/data /cyx/logmnr )其中dictionary_location指的是字典信息文件的存放位置 它必须完全匹配UTL_FILE_DIR的值 例如 假设UTL_FILE_DIR=/data /cyx/logmnr/ 则上面这条语句会出错 只因为UTL_FILE_DIR后面多了一个 / 而在很多其它地方对这一 / 是不敏感的 dictionary_filename指的是放于字典信息文件的名字 可以任意取 当然我们也可以不明确写出这两个选项 即写成 exec dbms_logmnr_d build( dic ora /data /cyx/logmnr )如果你第一步的参数没有设 而直接开始这一步 Oracle会报下面的错误 ERROR at line :ORA : initialization parameter utl_file_dir is not setORA : at SYS DBMS_LOGMNR_D line ORA : at SYS DBMS_LOGMNR_D line ORA : at line 需要注意的是 在oracle for Windows版中会出现以下错误 : : SQL>execute dbms_logmnr_d build( oradict ora c:\oracle\admin\ora\log )BEGIN dbms_logmnr_d build( oradict ora c:\oracle\admin\ora\log )END*ERROR at line :ORA : Subscript outside of limitORA : at SYS DBMS_LOGMNR_D line ORA : at line 解决办法 编辑 $ORACLE_HOME/rdbms/admindbmslmd sql 文件 把其中的TYPE col_desc_array IS VARRAY( ) OF col_description改成 TYPE col_desc_array IS VARRAY( ) OF col_description保存文件 然后执行一遍这个脚本 : : SQL>@c:\oracle\ora \rdbms\admin\dbmslmd sqlPackage created Package body created No errors Grant succeeded 然后重新编译DBMS_LOGMNR_D包 : : SQL>alter package DBMS_LOGMNR_D pile bodyPackage body altered 之后重新执行dbms_logmnr_d build即可 : : SQL>execute dbms_logmnr_d build( oradict ora c:\oracle\admin\ora\log )PL/SQL procedure successfully pleted 添加需要分析的日志文件 SQL>exec dbms_logmnr add_logfile( logfilename=>/data /cyx/rac arch/arch_ _ arc options=>dbms_logmnr new)PL/SQL procedure successfully pleted 这里的options选项有三个参数可以用 NEW 表示创建一个新的日志文件列表 ADDFILE 表示向这个列表中添加日志文件 如下面的例子 REMOV lishixinzhi/Article/program/Oracle/201311/18949

---