Query Oracle for information on a Database
Posted on 2014-03-03
I am in the process of developing an Application Catalog for our small group. Part of the App Catalog is to show which databases and tables are used by the application. I am planning on having a database that contains the Database names that our group currently works with. What I want to do is given a Database name, query Oracle to get all the information about that database. For example, I would like to get the following information:
* Tables in the database
* Field definitions for each table in the database
* Views in the database
* Indexes on each table
* Triggers used in the database and the tables associated with the triggers
* Sequences defined in the table
* Materialized Views
* Public Synonyms
* any other info for the database
I would also like to be able to store the unique id associated with each table in the database so I can assign tables to specific applications. For example:
Application 1 defined in the Application Catalog uses the Table1 and Table2 tables from the Test database. The Test database contains 15 tables. I want to be able to store a value in my table for the Application Catalog that shows the app uses Table1 and Table2. I don't want to store the actual table names because they are already defined in ORACLE and I don't want to "recreate the wheel".
I hope this makes sense and any help is greatly appreciated!