oracle 如何登录Oracle数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/195410/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to log in an Oracle database?
提问by rics
I am interested in what methods of logging is frequent in an Oracle database. Our method is the following:
我对 Oracle 数据库中常用的日志记录方法感兴趣。我们的方法如下：
We create a log table for the table to be logged. The log table contains all the columns of the original table plus some special fields including timestamp, modification type (insert, update, delete), modifier's id. A trigger on the original table creates one log row for each insertion and deletion, and two rows for a modification. Log rows contain the data before and after the alteration of the original one.
我们为要记录的表创建一个日志表。日志表包含原始表的所有列以及一些特殊字段，包括时间戳、修改类型（插入、更新、删除）、修改器的 id。原始表上的触发器为每次插入和删除创建一个日志行，并为修改创建两行。日志行包含原始数据更改前后的数据。
Although state of the records can be mined back in time using this method, it has some drawbacks:
虽然使用这种方法可以及时挖掘记录的状态，但它有一些缺点：
- Introduction of a new column in the original table does not automatically involves log modification.
- Log modification affects log table and trigger and it is easy to mess up.
- State of a record at a specific past time cannot be determined in a straightforward way.
- ...
- 在原始表中引入新列不会自动涉及日志修改。
- 日志修改影响日志表和触发器，容易搞乱。
- 无法直接确定过去特定时间的记录状态。
- ...
What other possibilities exist? What kind of tools can be used to solve this problem?
还存在哪些其他可能性？什么样的工具可以用来解决这个问题？
I only know of log4plsql. What are the pros/cons of this tool?
我只知道log4plsql。这个工具的优缺点是什么？
Edit: Based on Brian's answer I have found the following referencethat explains standard and fine grain auditing.
编辑：根据布赖恩的回答，我找到了以下解释标准和细粒度审计的参考资料。
回答by Brian
It sounds like you are after 'auditing'. Oracle has a built-in feature called Fine Grain Auditing (FGA). In a nutshell you can audit everything or specific conditions. What is really cool is you can 'audit' selects as well as transactions. Simple command to get started with auditing:
听起来你是在“审计”之后。Oracle 有一个称为细粒度审计 (FGA) 的内置功能。简而言之，您可以审核所有内容或特定条件。真正酷的是您可以“审核”选择以及交易。开始审计的简单命令：
audit UPDATE on SCOTT.EMP by access;
Think of it as a 'trigger' for select statements. For example, you create policies:
将其视为 select 语句的“触发器”。例如，您创建策略：
begin
dbms_fga.add_policy (
object_schema=>'BANK',
object_name=>'ACCOUNTS',
policy_name=>'ACCOUNTS_ACCESS'
);
end;
After you have defined the policy, when a user queries the table in the usual way, as follows:
定义好策略后，当用户以通常的方式查询表时，如下：
select * from bank.accounts;
the audit trail records this action. You can see the trail by issuing:
审计追踪记录了这个动作。您可以通过发出以下命令查看轨迹：
select timestamp,
db_user,
os_user,
object_schema,
object_name,
sql_text
from dba_fga_audit_trail;
TIMESTAMP DB_USER OS_USER OBJECT_ OBJECT_N SQL_TEXT
--------- ------- ------- ------- -------- ----------------------
22-OCT-08 BANK ananda BANK ACCOUNTS select * from accounts
回答by Salamander2007
Judging from your description, I wonder if what you really need is not logging mechanism, but rather some sort of Historical value of some table. If this is the case, then maybe you better off using some kind of Temporal Database design (using VALID_FROM and VALID_TO fields). You can track changes in database using Oracle LogMinertools.
从你的描述来看，我想知道你真正需要的不是日志机制，而是某个表的某种历史值。如果是这种情况，那么也许您最好使用某种临时数据库设计（使用 VALID_FROM 和 VALID_TO 字段）。您可以使用Oracle LogMiner工具跟踪数据库中的更改。
As for your scenarios, I would rather stored the changes data in this kind of schema :
至于您的场景，我宁愿将更改数据存储在这种架构中：
+----------------------------------------------------------------------------+
| Column Name | Function |
+----------------------------------------------------------------------------+
| Id | PRIMARY_KEY value of the SOURCE table |
| TimeStamp | Time stamp of the action |
| User | User who make the action |
| ActionType | INSERT, UPDATE, or DELETE |
| OldValues | All fields value from source table, seperated by '|' |
| Newvalues | All fields value from source table, seperated by '|' |
+----------------------------------------------------------------------------+
With this type of logging table, you can easily determine :
使用这种类型的日志记录表，您可以轻松确定：
- Historical Change action of particular record (using Id)
- State of specific record in some point in time
- 特定记录的历史更改操作（使用 Id）
- 某个时间点的特定记录状态
Of course this kind of logging cannot easily determine all valid values of table in specific point in time. For this, you need to change you table design to Temporal Database Design.
当然，这种日志不能轻易确定特定时间点表的所有有效值。为此，您需要将表设计更改为Temporal Database Design。
回答by rics
In the similar question (How to Audit Database Activity without Performance and Scalability Issues?) the accepted answer mentions the monitoring of database traffic using a network traffic sniffer as an interesting alternative.
在类似的问题（如何在没有性能和可扩展性问题的情况下审核数据库活动？）中，接受的答案提到使用网络流量嗅探器监控数据库流量作为一个有趣的替代方案。
回答by Matthew Watson
log4plsql is a completely different thing, its for logging debug info from PL/SQL
log4plsql 是完全不同的东西，它用于记录来自 PL/SQL 的调试信息
For what you want, you need to either.
对于你想要的，你也需要。
- Setup a trigger
- Setup PL/SQL interface around the tables, CRUD operations happen via this interface, the interface ensures the log tables are updated.
- Setup interface in your application layer, as with PL/SQL interface, just higher up.
- Oracle 11g contains versioned tables, I have not used this at all though, so can make no real comment.
- 设置触发器
- 围绕表设置 PL/SQL 接口，通过该接口进行 CRUD 操作，该接口确保更新日志表。
- 在你的应用层设置接口，就像 PL/SQL 接口一样，只是更高。
- Oracle 11g 包含版本化表，不过我根本没有使用过它，因此无法发表真正的评论。
回答by Leigh Riffel
If you just interested in knowing what the data looked like in the recent past you could just use Oracles flashback query functionality to query the data for a specific time in the past. How far in the past is dependent on how much disk space you have and how much database activity there is. The bright side of this solution is that new columns automatically get added. The downside is that you can't flashback query past ddl operations.
如果您只是想知道最近过去的数据是什么样的，您可以使用 Oracle 的闪回查询功能来查询过去特定时间的数据。过去多远取决于您拥有多少磁盘空间以及有多少数据库活动。该解决方案的优点是自动添加新列。缺点是你不能闪回查询过去的 ddl 操作。
