[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL query of Oracle 10g database.

Posted on 2016-10-23
8
Medium Priority
?
180 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 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
Independent Software Vendors: 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 1200 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 800 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month18 days, 21 hours left to enroll

834 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