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.
DColinAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor Commented:
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
 
PortletPaulCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
 
DavidConnect With a Mentor Senior 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
All Courses

From novice to tech pro — start learning today.