如何找出在Oracle中创建特定表的时间? [英] How to find out when a particular table was created in Oracle?

问题描述

在Oracle中，有没有一种方法可以找出创建特定表的时间?

In Oracle, is there a way to find out when a particular table was created?

类似地，是否有办法找出何时插入/最后更新了特定行?

Similarly, is there a way to find out when a particular row was inserted/last updated?

推荐答案

SELECT created
  FROM dba_objects
 WHERE object_name = <<your table name>>
   AND owner = <<owner of the table>>
   AND object_type = 'TABLE'

会告诉您何时创建表(如果您无权访问DBA_OBJECTS，则可以使用ALL_OBJECTS来代替，假设您对表具有SELECT特权).

will tell you when a table was created (if you don't have access to DBA_OBJECTS, you could use ALL_OBJECTS instead assuming you have SELECT privileges on the table).

不过，从行中获取时间戳的一般答案是，只有添加了列以跟踪该信息时，您才能获取该数据(当然，假设您的应用程序也填充了列).但是，有各种特殊情况.如果DML发生的时间相对较近(最有可能在最近几个小时内发生)，则您应该能够从闪回查询中获取时间戳.如果DML是在最近几天内发生的(或保留存档日志的时间过长)，则可以使用LogMiner提取时间戳，但这将是非常昂贵的操作，尤其是当您要获取多行的时间戳时.如果您在启用ROWDEPENDENCIES的情况下构建表(不是默认设置)，则可以使用

The general answer to getting timestamps from a row, though, is that you can only get that data if you have added columns to track that information (assuming, of course, that your application populates the columns as well). There are various special cases, however. If the DML happened relatively recently (most likely in the last couple hours), you should be able to get the timestamps from a flashback query. If the DML happened in the last few days (or however long you keep your archived logs), you could use LogMiner to extract the timestamps but that is going to be a very expensive operation particularly if you're getting timestamps for many rows. If you build the table with ROWDEPENDENCIES enabled (not the default), you can use

SELECT scn_to_timestamp( ora_rowscn ) last_modified_date,
       ora_rowscn last_modified_scn,
       <<other columns>>
  FROM <<your table>>

获取该行的最后修改日期和SCN(系统更改号).但是，默认情况下，如果没有ROWDEPENDENCIES，则SCN仅处于块级别. SCN_TO_TIMESTAMP函数也无法永远将SCN映射到时间戳.

to get the last modification date and SCN (system change number) for the row. By default, though, without ROWDEPENDENCIES, the SCN is only at the block level. The SCN_TO_TIMESTAMP function also isn't going to be able to map SCN's to timestamps forever.

这篇关于如何找出在Oracle中创建特定表的时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！