-3

Can anyone help me in finding some data related to Oracle Metadata .

I have certain very specific questions around it . Listing them below as per my understanding :

  • Where can i get Table metadata details like Table Size, create timestamp and update timestamp for all the tables
  • Where can i get View metadata details like create timestamp and update timestamp for all the Views in the database
  • Where can i get Procedure metadata details like content of procedure create timestamp and update timestamp for all the Procedures in the database
  • Where can i get Column metadata details like create timestamp and update timestamp for all the Columns in all the tables in the database
  • Where can i get MACRO metadata details like content of MACRO create timestamp and update timestamp for all the Procedures in the database
  • Where can i get Data regarding metadata of Queries like Runtime, IO Time , CPU Time etc
  • Where can i get INDEX metadata details
Improve this question
2
  • Please consider reading How to Ask. Questions that show no previous research and are too broad may not be well received here (or anywhere else).
    – mustaccio
    Commented Aug 1, 2019 at 16:41
  • I reccomend that you start with the "Oracle Concepts Guide".
    – Michael Kutz
    Commented Aug 1, 2019 at 17:05

1 Answer 1

Reset to default
0

When you aren't sure where to look at, check the dictionary. For example:

SQL> select * From dictionary where lower(comments) like '%procedure%';

TABLE_NAME           COMMENTS
-------------------- -------------------------------------------------------
ALL_PROCEDURES       Functions/procedures/packages/types/triggers available
                     to the user

USER_AUDIT_OBJECT    Audit trail records for statements concerning objects,
                     specifically: table, cluster, view, index, sequence,  [
                     public] database link, [public] synonym, procedure, tri
                     gger, rollback segment, tablespace, role, user

USER_PROCEDURES      Description of the user functions/procedures/packages/t
                     ypes/triggers


SQL>

Even if it doesn't return exactly what you need, that can be a starting point. Note that some searches might return a lot of results (for example, if the search term is "table", my 11g XE returns 164 rows so - you might need to apply additional condition(s)).

Improve this answer
4
  • Note that this only returns something in the comments column for the SYS or SYSTEM users. It seems regular users (even with select_catalog_role) can't see those comments
    – user1822
    Commented Aug 2, 2019 at 5:20
  • I've just tried it, @a_horse, connected as dummy users SCOTT (whose cat was Tiger), MIKE (I use for testing purposes), a regular user which was granted only necessary privileges ... works the same for all of them (on 11gR2). Tried on one of users on 12cR2 - no difference. None of them was SYS nor SYSTEM (I don't have privileges to connect as those users anyway).
    – Littlefoot
    Commented Aug 2, 2019 at 5:26
  • Interesting. I tried it with 12.2 and 18c and did not see any comments as a regular user (where comments is not null returned exactly one row). This can also be seen on dbfiddle. Maybe there is an additional installation step to be done so that the view is populated.
    – user1822
    Commented Aug 2, 2019 at 5:31
  • Huh, I don't think so. My message was posted yesterday evening (CET); query I ran was executed on my home 11gXE and I certainly didn't install any additions (you know how it goes - run setup and click NEXT several times and here you are, XE is ready for you).
    – Littlefoot
    Commented Aug 2, 2019 at 5:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.