Solved

SQL query of Oracle 10g database.

Posted on 2016-10-23
8
117 Views
Last Modified: 2016-10-26
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
Comment
Question by:DColin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41856498
How will you run the query (i,e, what tool(s) will you use to do this) ?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41856509
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
 
LVL 23

Expert Comment

by:David
ID: 41856516
Presumably you have access to the database itself? I would steer you toward Excel for queries using a jdbc set up.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DColin
ID: 41857161
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 41857169
using sqlplus, use the queries shown above to extract the schema information.

from there, write your query and spool to a file.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 300 total points
ID: 41857229
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
 
LVL 23

Assisted Solution

by:David
David earned 200 total points
ID: 41857320
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 41860533
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question