如何找出在 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屋！