如何找出在 Oracle 中创建特定表的时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4442323/
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 find out when a particular table was created in Oracle?
提问by Moeb
In Oracle, is there a way to find out when a particular table was created?
在 Oracle 中，有没有办法找出特定表的创建时间？
Similarly, is there a way to find out when a particular row was inserted/last updated?
同样，有没有办法找出特定行的插入/上次更新时间？
回答by Justin Cave
SELECT created
FROM dba_objects
WHERE object_name = <<your table name>>
AND owner = <<owner of the table>>
AND object_type = 'TABLE'
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).
将告诉您何时创建表（如果您无权访问 DBA_OBJECTS，则可以使用 ALL_OBJECTS 来代替，假设您对该表具有 SELECT 权限）。
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
但是，从一行中获取时间戳的一般答案是，只有添加了列来跟踪该信息（当然，假设您的应用程序也填充了这些列），您才能获取该数据。然而，有各种特殊情况。如果 DML 发生得相对最近（最有可能发生在过去几个小时内），您应该能够从闪回查询中获取时间戳。如果 DML 发生在最近几天（或者无论您保留存档日志的时间有多长），您都可以使用 LogMiner 提取时间戳，但这将是一项非常昂贵的操作，特别是如果您要获取多行的时间戳。如果您在启用 ROWDEPENDENCIES（不是默认设置）的情况下构建表，则可以使用
SELECT scn_to_timestamp( ora_rowscn ) last_modified_date,
ora_rowscn last_modified_scn,
<<other columns>>
FROM <<your table>>
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_TIMESTAMPfunction also isn't going to be able to map SCN's to timestamps forever.
获取该行的最后修改日期和 SCN（系统更改编号）。但是，默认情况下，如果没有 ROWDEPENDENCIES，SCN 仅在块级别。该SCN_TO_TIMESTAMP函数也不能永远将 SCN 映射到时间戳。
回答by bhangm
You can query the data dictionary/catalog views to find out when an object was created as well as the time of last DDL involving the object (example: alter table)
您可以查询数据字典/目录视图以找出对象的创建时间以及涉及该对象的上次 DDL 的时间（例如：alter table）
select *
from all_objects
where owner = '<name of schema owner>'
and object_name = '<name of table>'
The column "CREATED" tells you when the object was created. The column "LAST_DDL_TIME" tells you when the last DDL was performed against the object.
“CREATED”列告诉您对象的创建时间。“LAST_DDL_TIME”列告诉您上次对对象执行 DDL 的时间。
As for when a particular row was inserted/updated, you can use audit columns like an "insert_timestamp" column or use a trigger and populate an audit table
至于何时插入/更新特定行，您可以使用诸如“insert_timestamp”列之类的审计列或使用触发器并填充审计表
回答by shary.sharath
You copy and paste the following code. It will display all the tables with Name and Created Date
您复制并粘贴以下代码。它将显示所有带有名称和创建日期的表
SELECT object_name,created FROM user_objects
WHERE object_name LIKE '%table_name%'
AND object_type = 'TABLE';
Note:Replace '%table_name%'with the table name you are looking for.
注意：将'%table_name%'替换为您要查找的表名。
回答by Saurabh Gadariya
SELECT CREATED FROM USER_OBJECTS WHERE OBJECT_NAME='<<YOUR TABLE NAME>>'
回答by Deepjyoti Dev
Try this query:
试试这个查询：
SELECT sysdate FROM schema_name.table_name;
SELECT sysdate FROM schema_name.table_name;
This should display the timestamp that you might need.
这应该显示您可能需要的时间戳。
