More precisely, I wan't to re-create the database model / schematics / the Entity Relationship Diagram (ERD) showing all the links between the tables of this existing hidden Oracle Database.
In my job, I use a proprietary application interface (PAI) to manage users and their activities.
This PAI hides the underlying database and any connection to it.
This means that I cannot use standard SQL management interfaces to access this database and its data.
My job requires that I produce User reports.
To do so, I have to extract these reports by using a specific menu of this PAI.
In this menu I can only execute an existing SELECT statement or POST a new SELECT statement in one TextBox.
Unfortunately, the data extracted by the existing SELECT statements is not appropriate to the business requirements.
Furthermore the documents provided by the editor of this PAI contain an extremely light and partial series of schematics of the underlying database.
So, What would you suggest I do to have a better vision of all these data links between the database tables ?
Once I have a better vision of these links, I will be able to write SELECT statements that are extracting more appropriate data for my employer's business.
Extra information :
The PAI prohibits the execution of the following statements : insert, update, dll & dml, functions or procedures.
I can execute the following SELECT Statements :
SELECT * FROM all_tables –> results in 800 tables
SELECT * FROM cols
SELECT * from tabs
SELECT * FROM all_views
SELECT * FROM user_tables
SELECT a.owner||'.'||a.table_name "Referenced Table",b.owner||'.'||b.table_name "Referenced by", b.constraint_name
"Foreign Key" from all_constraints a, all_constraints b where
b.constraint_type in('P','R') and a.constraint_name =
b.r_constraint_name
Best Answer
Many database modelling tools can extract the required data and create an initial ERD diagram. You will likely need to rearrange the diagram, but the initial diagram will provide a good starting point.
Given the limitations, you may need to use queries that generate DDL and create a database based on this DDL. I would avoid selecting from the all_* views as many of those objects will be system related, rather than application related.