英文:
How to list all the functions in an Oracle DB and order them by their modification date
问题
我想获取 Oracle 数据库中所有函数的列表，以及它们各自的修改日期。是否有一个 SQL 查询可以提供这些信息？
我尝试使用以下查询：
SELECT * FROM DBA_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION';
这个查询我在 Donald Burleson 的文章中找到，但是我收到了一个错误消息：
ORA-00942: 表或视图不存在。
英文:
I would like to obtain a list of all functions in an Oracle database, along with their respective modification dates. Is there an SQL query that could provide me with this information?
I attempted to use the following query:
SELECT * FROM DBA_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION'
which I found in a Donald Burleson article, but I received an error message stating:
> ORA-00942: table or view does not exist.
答案1
得分: 5
如果您无法访问 DBA_OBJECTS 视图，很可能是由于缺少由数据库管理员授予的必要特权，因为该视图包含整个数据库中所有对象的元数据。
然而，您可以改为使用 ALL_OBJECTS 视图，该视图仅包含您有权访问的对象的元数据。因此，如果您能够访问此视图，可以使用其 LAST_DDL_TIME 列，该列提供了每个对象的上次修改时间戳，这些修改是由DDL语句（包括授权和撤销）引起的。
话虽如此，您的查询应该更新如下：
SELECT OBJECT_NAME, LAST_DDL_TIME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION'
ORDER BY LAST_DDL_TIME DESC
英文:
If you're unable to access the DBA_OBJECTS view, it is likely due to a lack of necessary privileges granted by the DBA, since this view contains metadata for all objects of the entire database.
However, you can utilize the ALL_OBJECTS view instead, which contains metadata only for objects that your user has access to. So, if you are able to access this view, you can make use of its LAST_DDL_TIME column, which provides the timestamp for the last modification of each object, resulting from a DDL statement (including grants and revokes).
That being said, your query should be updated as follows:
SELECT OBJECT_NAME, LAST_DDL_TIME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION'
ORDER BY LAST_DDL_TIME DESC
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论