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?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Are you looking to reverse engineer the database to understand the tables/columns and keys or are you looking for data stored somewhere in a database?
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!!!
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!!!
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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 ?