• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

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.
0
DColin
Asked:
DColin
2 Solutions
 
PortletPaulfreelancerCommented:
How will you run the query (i,e, what tool(s) will you use to do this) ?
0
 
sdstuberCommented:
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.
0
 
DavidSenior Oracle Database AdministratorCommented:
Presumably you have access to the database itself? I would steer you toward Excel for queries using a jdbc set up.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DColinAuthor Commented:
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?
0
 
sdstuberCommented:
using sqlplus, use the queries shown above to extract the schema information.

from there, write your query and spool to a file.
0
 
sdstuberCommented:
Here's an abbreviated example of what I was trying to describe above...

change the username, password and db name as needed, and, pick your own schema and table

C:\>sqlplus myuser/mypassword@mydb

SQL*Plus: Release 12.1.0.1.0 Production on Mon Oct 24 12:03:19 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> select owner,table_name from all_tables;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
APEX_050000                    WWV_FLOW_DEBUG_MESSAGES2
MY_SCHEMA                      MY_TABLE
SYSTEM                         LOGMNR_SESSION_ACTIONS$
....  (many rows removed)

5744 rows selected.

SQL> select column_name,data_type from all_tab_cols where table_owner = 'MY_SCHEMA' and table_name = 'MY_TABLE';
select column_name,data_type from all_tab_cols where table_owner = 'MY_SCHEMA' and table_name = 'MY_TABLE'
                                                     *
ERROR at line 1:
ORA-00904: "TABLE_OWNER": invalid identifier


SQL> select column_name,data_type from all_tab_cols where owner = 'MY_SCHEMA' and table_name = 'MY_TABLE';

COLUMN_NAME
------------------------------
DATA_TYPE
--------------------------------------------------------------------------------
PLOT_TIME
DATE

LEGEND
VARCHAR2

INSPECTION_TYPE
VARCHAR2


COLUMN_NAME
------------------------------
DATA_TYPE
--------------------------------------------------------------------------------
REGION
VARCHAR2

AS_OF
DATE

PLOT_MSG
VARCHAR2


6 rows selected.

SQL> select plot_time, plot_msg from MY_SCHEMA.MY_TABLE;

PLOT_TIME
---------
PLOT_MSG
--------------------------------------------------------------------------------
27-MAY-16
1864 rows retrieved

27-MAY-16
1864 rows retrieved

27-MAY-16
323 rows retrieved


PLOT_TIME
---------
PLOT_MSG
--------------------------------------------------------------------------------
27-MAY-16
1864 rows retrieved

27-MAY-16
1864 rows retrieved

27-MAY-16
1864 rows retrieved


6 rows selected.

Open in new window

0
 
DavidSenior Oracle Database AdministratorCommented:
DColin, I need to correct my earlier suggestion for using JDBC, it was late. ODBC was intended.  This link explains how to query from Excel or Access, one of your original points.  SQL*Developer is my goto tool but the issue is finding the right schema -- yes?

You're retrieving a lot of information now. I propose you take Sean's statement but let's hold off on the table_names for a moment.  I'd like to see the set of all users who have tables you can read. It's unlikely you are going to need data from the overhead accounts SYS or SYSTEM, so I'll omit those for now. Consider:
SELECT DISTINCT OWNER FROM all_tables WHERE OWNER NOT IN ('SYS','SYSTEM')  ORDER BY 1;

If this returns more than a couple of owners, you may need more information about the data you want to query. After all, each schema may own a table named CUSTOMERS, for example.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now