DColin
asked on
SQL query of Oracle 10g database.
Hi Experts,
I have an Oracle 10g database on my computer and I would like to extract some data via an SQL query. I do not know the database schema so I would first need to extract the schema and then create and run the query. How would I go about this I did not install the database so have no knowledge of how Oracle databases works. I am comfortable using and querying databases like Access.
I have an Oracle 10g database on my computer and I would like to extract some data via an SQL query. I do not know the database schema so I would first need to extract the schema and then create and run the query. How would I go about this I did not install the database so have no knowledge of how Oracle databases works. I am comfortable using and querying databases like Access.
How will you run the query (i,e, what tool(s) will you use to do this) ?
select * from all_tables -- this will show you the table owners and table names that you have access to
select * from all_tab_cols -- this will show you the columns of those tables
once you have those...
select * from some_owner.some_table to extract your data.
If you have a tool like sql developer (free from otn.oracle.com) you can browse the schemas and objects and write your queries from there.
select * from all_tab_cols -- this will show you the columns of those tables
once you have those...
select * from some_owner.some_table to extract your data.
If you have a tool like sql developer (free from otn.oracle.com) you can browse the schemas and objects and write your queries from there.
Presumably you have access to the database itself? I would steer you toward Excel for queries using a jdbc set up.
ASKER
The only tool I have is the SQL Plus utility that shipped with the Oracle installation package.
The first thing I would need to do is connect to the required database using SQL plus.
Then extract the database schema so I can work out my query.
Then run the created query and store the result into a readable file format.
Any ideas?
The first thing I would need to do is connect to the required database using SQL plus.
Then extract the database schema so I can work out my query.
Then run the created query and store the result into a readable file format.
Any ideas?
using sqlplus, use the queries shown above to extract the schema information.
from there, write your query and spool to a file.
from there, write your query and spool to a file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You will most likely need either a username and password or a privileged (DBA) user account and password to connect to the database in sql*plus to run any kind of query. On many operating systems where an Oracle database is installed, you can sue this syntax at the command prompt:
sqlplus / as sysdba
If this works for you and brings you to a "SQL>" prompt, be careful! This gets you into the database as the system administrator, so you could change or delete anything or everything! If you start all commands (queries) with the word "select" though, you are safe getting in this way. When you are done, type "exit" and press [Enter]. Use some of the queries that others suggested above to get a list of the schemas (owners) of the tables, then of the table and/or column names that contain the data you are interested in.
Before running a query like this one from ststuber:
select column_name,data_type from all_tab_cols where owner = 'MY_SCHEMA' and table_name = 'MY_TABLE';
use this command:
column data_type format a20;
Then run the query, and the results will be displayed in two columns.
sqlplus / as sysdba
If this works for you and brings you to a "SQL>" prompt, be careful! This gets you into the database as the system administrator, so you could change or delete anything or everything! If you start all commands (queries) with the word "select" though, you are safe getting in this way. When you are done, type "exit" and press [Enter]. Use some of the queries that others suggested above to get a list of the schemas (owners) of the tables, then of the table and/or column names that contain the data you are interested in.
Before running a query like this one from ststuber:
select column_name,data_type from all_tab_cols where owner = 'MY_SCHEMA' and table_name = 'MY_TABLE';
use this command:
column data_type format a20;
Then run the query, and the results will be displayed in two columns.