PLSQL views that can help identify a specific record the type, the field and table names in a large Oracle DB. With NO Schema or knowledge of which table to start with?
I need to reverse engineer an Oracle database with no access to the DB schema or how the table relationships work. The software manufacturer will not allow anyone to know that and pressures its customers to purchase a very expensive report writer consultant that most customers would rather circumvent.
This expert comment shows information about Views that are available for the Oracle tool PLSQL Developer. "The views to get tables, columns and data types are readily available. Then you would just need to loop through them to build the SQL to select out the data."
Does anyone know what he is talking about and where I might find the code for this?
I assume I plug them into PLSQL and run it against the DB.
Is there a site with all this kind of info?
This expert comment shows information about Views that are available for the Oracle tool PLSQL Developer. "The views to get tables, columns and data types are readily available. Then you would just need to loop through them to build the SQL to select out the data."
Does anyone know what he is talking about and where I might find the code for this?
I assume I plug them into PLSQL and run it against the DB.
Is there a site with all this kind of info?
Who is Participating?
another idiot vendor
if the idiot vendor has placed the database at your site and you have access to the server and the database is not encrypted
then you can read the whole thing as is
is that the case ?
is the database stored on your company server ?
if the idiot vendor has placed the database at your site and you have access to the server and the database is not encrypted
then you can read the whole thing as is
is that the case ?
is the database stored on your company server ?
Beat da cancer......Yes and Yes.
I thank you for that data modeler that looks promising. I also thank you for the tips on the programming.
That tool I spoke of was amazing to me at least because it helped me get at the data without any "database models"....where it shows all the tables in the database and links between them. I always thought that was called the "Database Schema." SQL Grep was a set and forget. It could run through a large database from anywhere from a minute to 12-15 minutes and since DB's can be so complex having the same record in multiple tables it was always helpful to see them all so I can figure out which one would be more efficient to use.
Again thank you. Now I just need to figure out how to build this concept in what looks like I will need JAVA for the GUI, Python or R and finally XML because it sounds like you are familiar with. I may be posting more questions on this which may teeter between Database and Programming forums.
I thank you for that data modeler that looks promising. I also thank you for the tips on the programming.
That tool I spoke of was amazing to me at least because it helped me get at the data without any "database models"....where it shows all the tables in the database and links between them. I always thought that was called the "Database Schema." SQL Grep was a set and forget. It could run through a large database from anywhere from a minute to 12-15 minutes and since DB's can be so complex having the same record in multiple tables it was always helpful to see them all so I can figure out which one would be more efficient to use.
Again thank you. Now I just need to figure out how to build this concept in what looks like I will need JAVA for the GUI, Python or R and finally XML because it sounds like you are familiar with. I may be posting more questions on this which may teeter between Database and Programming forums.
>>without any "database models"....where it shows all the tables in the database and links between them. I always thought that was called the "Database Schema
In the pure world the data model is a logical construct that should follow the rules of normalization. A schema in Oracle is pretty much a group of objects a user owns.
A physical system can be spread across multiple users objects.
SQL Developer will take a Users objects and revers engineer it into the nice GUI with the tables and all primary and foreign keys to show you the links.
>>the same record in multiple tables it was always helpful to see them all so I can figure out which one would be more efficient to use
This is dangerous. Repeating values is de-normalized data primarily done for "performance" but often just bad design.
If you find a value in tableA and decide it is "best" to use but it isn't the "master" value, then you can get bad results. TableB might be the primary location for the value and it can be changed there and not in tableA.
In the pure world the data model is a logical construct that should follow the rules of normalization. A schema in Oracle is pretty much a group of objects a user owns.
A physical system can be spread across multiple users objects.
SQL Developer will take a Users objects and revers engineer it into the nice GUI with the tables and all primary and foreign keys to show you the links.
>>the same record in multiple tables it was always helpful to see them all so I can figure out which one would be more efficient to use
This is dangerous. Repeating values is de-normalized data primarily done for "performance" but often just bad design.
If you find a value in tableA and decide it is "best" to use but it isn't the "master" value, then you can get bad results. TableB might be the primary location for the value and it can be changed there and not in tableA.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
To reverse engineer the database, I would get SQL Developer Data Modeler from Oracle:
http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html
Point it to your database and it will create a model.
>>Does anyone know what he is talking about
Yes I do! ;)
It is a worst case scenario. You can query DBA_TAB_COLUMNS to get all the tables and columns. Then you need to write code that dynamically builds queries to pull data from those tables and columns.
There are some tricks using XML that might help a little.
REMEMBER: That looking in ALL tables in ALL columns for some specific data is VERY inefficient and can take A LONG time!!!