Solved

SQL query of Oracle 10g database.

Posted on 2016-10-23
8
106 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
8 Comments
 
LVL 48

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
Industry Leaders: 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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle DB monitor SW 21 72
Import and exporting Oracle Data with encrypted columns 4 58
ER Diagram 3 39
running myfile.SQL from command line SQLPLUS 12c does not exit. 7 33
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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

713 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