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

We have a Oarcle 9i Database in a legacy system and want to convert tables to CSV with data.  For some reason they have Oracle 9i and on Windows 2003 Server.  This is a legacy application that was setup by an old administrator and I need to access to recover the data to move.

This is a blackbox system - cannot connect to network and vendor lock the DB and changed all Oracle system passwords.  

So with help from EE guys we figured out that the below will allow as sysdba.

1.  the sqlnet.ora parameter SQLNET.AUTHENTICATION_SERVICES=(NTS)
2.   sqlplus "/ as sysdba"


Well once in that I can only export a backup and other commands on the command line.  I have a developer who is going to take this small DB and convert to MySQL.  What command would allow to convert/export on the Oracle 9I command line as sysdba to a CSV with tables names and rows?  

He wants a CSV so he can import into MySQL.  If there is another way to accomplish this on the server and I don't know I'm willing to try anything.  Maybe a third party tools?  

Thank you in advance!  Will be online and able to respond and test commands.
System AdminAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
With SYSDBA access you can create your own user and plunder around the database to your heart's content.

Do you have MS Access on the server?  In not, install it.  Then connect to the Oracle database and import the tables to Access.  From there you can write the tables to Excel.

Kent
0
Mark GeerlingsDatabase AdministratorCommented:
The Oracle database software does not include an automated way to extract all of the records from each table in CSV (or fixed length, or any other database-independent) format.   Here are some options that you do have:
1. At a (DOS) command prompt, instead of starting sqlplus, you can run Oracle's export (or expdp) utility to export the entire database,  But the resulting "dump" file is a binary file that is intended to be used only by the Oracle import (or impdp) utility on a different Oracle database.
2. Also at a (DOS) command prompt, instead of starting sqlplus, you can run Oracle's RMAN utility to create a full database backup.  But this is also intended to be used only by the same or a different Oracle database server to recover the database.
3. If you do use this command to get into the database as an administrator:
 sqlplus "/ as sysdba"
you can use these three SQL commands to create a new user account that will allow you to export all of the data either manually, or with tools like: TOAD, SQL Developer, Access or any other ODBC-compliant program that can connect to Oracle:
create user exp_user identified by [password];
grant connect, create session, DBA to exp_user;
grant select any table to exp_user;

That will allow you then to connect to this database as the new "exp_user" and extract all of the contents of the database.  You could write and run a SQL query manually for each table, spooling the output to a separate file for each table.  But, it will likely be easier for you to use TOAD, SQL Developer, Access or some other tool that can create *.CSV files for you.
1
System AdminAuthor Commented:
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
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

System AdminAuthor Commented:
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.
0
System AdminAuthor Commented:
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.
0
Kent OlsenDBACommented:
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.
0
Mark GeerlingsDatabase AdministratorCommented:
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,
0
System AdminAuthor Commented:
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.
0
System AdminAuthor Commented:
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
0
Mark GeerlingsDatabase AdministratorCommented:
I think SQL Developer will be your best option.  (But, I admit I'm biased, because that is the only one of those tools that I use.)  Yes, it does need a Java JDK and I'm not an expert on those.  I don't remember how I got SQL Developer to work a few months ago when I got a new laptop.  I may have had help getting (or finding?) a Java JDK installed, and then telling SQL Developer where that is.
0
Kent OlsenDBACommented:
You can also install any of those tools onto your own desktop and connect to the database on the server.

Assuming your desktop is newer than Win2k, you should be able to install and run just about anything.  Just make sure that the drivers match your desktop environment, not the server's.  (If you're running a 64-bit desktop install the 64-bit drivers.  Don't install the 32-bit drivers because the database is on a 32-bit system.)

Kent
0
System AdminAuthor Commented:
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
0
Kent OlsenDBACommented:
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
0
System AdminAuthor Commented:
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>
0
System AdminAuthor Commented:
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.
0
Kent OlsenDBACommented:
Ok.  The database status to the listener should be "READY", not "UNKNOWN".  That's why the connections are failing.

Can you post the contents of file: E:\oracle\ora92\network\admin\listener.ora
0
System AdminAuthor Commented:
# 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)
    )
  )
0
Kent OlsenDBACommented:
UNKNOWN shouldn't be an issue.  It simply means that the instance is registered statically (using SID_LIST) in the listener.ora file.  I use dynamic registration and am not used to seeing it.  Apologies.

Can you ping the server from your desktop?  Is there a firewall between your desktop and the server that could be blocking port 1521?
0
System AdminAuthor Commented:
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
0
System AdminAuthor Commented:
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!
0
System AdminAuthor Commented:
Looks Like I found export, any suggestions?  Getting weird excel error gonna just test pdf or html.
exportfinal.JPG
exporterror.JPG
0
Kent OlsenDBACommented:
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.
0
System AdminAuthor Commented:
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.
0
System AdminAuthor Commented:
No tables, maybe I'm in the wrong DB but there is only one?  PPROD...
notables.JPG
0
Kent OlsenDBACommented:
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;
0
Mark GeerlingsDatabase AdministratorCommented:
I'm glad you got the network connectivity working.  That is usually the most frustrating part of setting up a new Oracle database or configuring a new client to connect to an existing database (like you are doing).

I use SQL Developer, but usually only for testing and debugging Oracle stored procedures.  In the version of SQL Developer that I have (4.1.5.21) after I open a connection to the database (this will need a valid Oracle username and password, possibly the "exp_user" that I suggested a day or two ago) you will need to:
1.  Expand the "Connections" link in the "Connections" pane, likely in the upper, left corner of your SQL Developer screen.
2. Navigate to the "Other user" node and expand that,
3. Go to the name of your user that owns your application's tables and expand the link for that user.
4. Expand the "Tables" node to see a list of tables for that user.
5. Right-click on a table name and choose "Export"
6. Uncheck the "Export DDL" box at the top.
7. Click the "Format" drop-down in the middle of the screen, and choose 'csv' (or another option if that looks better)
8. In the "File:" box set a meaningful name for the file (I suggest using the table name).
9. Click: "Next", "Next" and "Finish"

Repeat steps 5-9 for each table that you need exported.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
System AdminAuthor Commented:
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.
0
System AdminAuthor Commented:
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!
0
System AdminAuthor Commented:
Great job!  EE Rocks!
0
Mark GeerlingsDatabase AdministratorCommented:
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.)
0
System AdminAuthor Commented:
HAHAHAH a vendor couldn't of figured that out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.