Oarcle 9i Database (small) convert tables to CSV for MySQL

Ok great guys, I'm understanding my options and I'm willing to do the MS ACCESS and EXCEL.  
I'm not a DBA but can follow steps,  What would be the commands now that I'm in at this user?

I think it will be easier to download a trial of TOAD then install access.  What would be the commands from TOAD to export?  Or do I still need a platform like ACCESS or EXCEL?  I've attached two screenshots showing I'm in now.
pprod.JPG
sqlplus-command.JPG

I've loaded MS OFFICE 2003 with ACCESS and trying to connect to the Oracle database and import the tables to Access.  From there you can write the tables to Excel.

What are the steps, this is kind of an old topic so searching google, bing, and youtube.   Alot show Access 2007-2010 and use external data, some show downloading a OBCD connector?  

I tried to install Toad and it gives all types of problems, it need .NET framework, then I tried 2.2 and failed, then tried 1.1 and said OS has SP1 so it already has it.  And Toad is the latest version so I suppose it needs an up to date Framework but won't tell me like 3.0/4.0.

Update, I created the Oracle ODBC driver and did test connect with UN and PW and it worked.  Now I'm thinking to use access to connect but this is 2003 access.

You'll probably want to create a Data Source connection to the Oracle database.

Start / Programs / Administrative Tools / Data Sources (ODBC)

Create a User DSN entry to the Oracle instance.  

Then open Access and connect to the Oracle database.  You should be able to link the tables and export them to Excel.

I haven't worked in Access in over 15 years, so I don't know exactly how it "links" to Oracle tables.  I agree that setting up an ODBC Data Source as a User DSN should allow Access (or Excel or any other ODBC-compliant tool) to connect to your Oracle database.

Inside Oracle, all of the tables are owned by an Oracle user (also called a "schema").  You will likely need to use either the full Oracle name for each table, which is: [schema_name].[table_name], or make it clear to Access which schema(s) you want to look in to find the tables.

For example, if you have a schema name of: "HR" and a table named: "Employees", the full Oracle name for that table is: hr.employees.  Also note that schema names and table names in Oracle are usually not case-sensitive.  In Oracle they are usually displayed in all upper-case characters, but in Oracle SQL commands, they can be referred to with any combination or upper and/or lower case letters.  

ODBC-based tools are not that way.  They are usually case-sensitive, and they often add double quotes around schema and table names.  If they use all upper-case letters for Oracle schema and table names, that usually works.  The exception would be if someone used an ODBC-based tool to create an Oracle table.  Then it may have a mixed-case name and will need to specified with double quotes and with the exact same case,

Ok MS Access 2003 doesn't have import or external data source.  So I'm downloading and install MS Access 2007.  Will report back.  Takes long time to install on VM.

Ok So MS Access 2007 had import errors but did look like it was working.
Examples were  Incorrect Date Time Format, Corrupt table, and there must have been 500 selection to import.

Then I Tried 2013 to see if maybe just a new version would work.  You cannot install on a Win2k3 SP1.  So I tried Office 2010 and it still needed SP2.  So Went back and installed SP2.  Office 2010 gave same kind of import errors so I gave up on MS access.  

Tried to install Toad 11.2 and it gives errors as well when launching.  (ATTACHED)

I tried Oracle SQL Developer and it launches and says point to the folder path for Java JDK but I've search and cannot find.  Do I just download?  Will test this today not sure what JDK version I need.    (ATTACHED)

There is something that says SQL Navigator and I will attach screenshots as it shows the small tables I need into CSV or Excel like we said.  Just a more readable format for a developer.    (ATTACHED)
oracle-sql-dev.JPG
access.JPG
toad-only-error.JPG
pprod.JPG
sqlnavigator.JPG

Ok lets go with SQL Developer, it will not work on the server so I've taking the advice and got the VM in Bridge mode and can ping and test.  When setting up ran into basic errors and had to add the port and change the IP in the listerner.ora, then had to restart the listner services.  So it looks like I'm getting close but a few last errors.  

After google people argue over using the SID or the service name, which is it and why am i getting the attached errors.  Which one should I use, and then what exact edit to listner.ova?  

Once this is connected my next questions which was in the beginning is how to I tell SQL developer to produce or export to excel or the other CSV we talked about.  It looked like Access would of worked but just cannot handle.  

Thank you both for the help.  Both will get points I promise, thank you again.  I will continue to try both the SID and service name, not sure where to specify service name in the listner.ova?
sid-error.JPG
sid.JPG
sqlsid.JPG

The listener should be running on the same server with the database.  If you've edited its configuration you might prevent the application or other processes from connecting to the database.

On the server, open a command prompt and enter "lsnrctl status".  That will show you the connections between the listener and database(s).  It will also show the configuration file that the listener is using.

Then start a SQL session and connect to the database.

sqlplus /nolog
conn / as sysdba
set lines 200
show parameters

Note the values of db_name, local_listener, and service _names

I've corrected the SID name and just using XE:

Now I get the error message below:

SQL Developer from My Machine to the VM with DB says:  Test failed: No more data to read from socket  ( I feel so close.)

The status for listener is also below, but I know using the right one and right details now it looks like.  

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.206.139)(PORT=15
21)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.6.0 - Produc
tion
Start Date                07-DEC-2017 14:23:27
Uptime                    0 days 0 hr. 0 min. 3 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   E:\oracle\ora92\network\admin\listener.ora
Listener Log File         E:\oracle\ora92\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.206.139)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
Services Summary...
Service "PPROD" has 1 instance(s).
  Instance "PPROD", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

Also I made a copy of production so this is a test sandbox VM application and ok if app stops working.  I don't want to use frontend I just want to strip all the data out of the backend once I connect and then export like you stated to Excel/CSV.

# LISTENER.ORA Network Configuration File: E:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.206.139)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = XE)
      (ORACLE_HOME = E:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = PPROD)
      (ORACLE_HOME = E:\oracle\ora92)
      (SID_NAME = PPROD)
    )
  )

I can ping, attached.
Two error with Firewall on Windows 2k3

On Firewall I have the Oracle TCP Port added as exception

Firewall ON - Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection

Firewall Off - no data socket (Attached)

Trying to find where more detailed log files may be but I see what your saying.
firewall-OFF-REAL-.JPG
ping.JPG
firewall-on-REal-with-exception.JPG
firewall.JPG

Had to use service name PPROD in tsanames.ora
Turn off windows firewall

read on another forum SID won't work in this version, but anyway they said Service name and then found that in tsanames.ora
So I'm connected!

Now looking to export into something readable not a oracle DB.  Any suggestions, both of you gets points, thanks for helping me on this!

Looks Like I found export, any suggestions?  Getting weird excel error gonna just test pdf or html.
exportfinal.JPG
exporterror.JPG

You're trying to export the DDL (sql) to a file with a .sql extension and the data to the same file but formatted as a spreadsheet.

Turn off the Export DDL, and use the correct extension for an Excel spreadsheet.

Ok Im connected and their are no tables and the export works but the files are 0KB.  something else must be going on I think I'm going to give up.  I'm connected to the correct PPROD and it is still not working.

No tables, maybe I'm in the wrong DB but there is only one?  PPROD...
notables.JPG

Your user may not have permissions to tables,  Or the application may be filtering out tables that aren't yours.

What schema owns the tables?

Can you query those tables?   SELECT count(*) FROM schemaname.tablename;

Wow I think that worked!  I'm getting some 5KB excel with data.  There seem to be 100 tables and some make take time to convert.  I'm going to get started.  I'm writing up what I did and will split the points 50/50.

You can highlight and select all to export.  He will be happy and I will try to give him a few options.  Points for SQL Developer it does seem like a very good tool.  Thanks again Experts!

Great job!  EE Rocks!

Yes, it's usually a lot faster than paid support from a vendor.  (And some of the free "experts" here actually have some real-world experience.)

HAHAHAH a vendor couldn't of figured that out.