Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

I need to get data out of Oracle 8i??!!

656701Aug 25 2008 — edited Aug 28 2008
I am working for a company that is moving from one old application to a new application.

The Old application is using Oracle 8i for a backend. I know SQL Server very well. However, I don't know Oracle.

1) One guy on the forum helped me to find the SIDs and see the databases being run.
2) I have also changed the password file and can now get in as internal.

Great, but I need data.

1) How can I get the data out of Oracle 8i?
2) Is there a way to export the data to CSV or spreadsheet?
3) There are 1400 tables with cryptic table names. How can I see the tables and their column names?

(this is easy to do with SQL Server, but again, I am just starting to use Oracle).

Thanks in advance for any and all help!!! This is crazy!

Comments

247514
To easy browse tables and data you can download SQL Developer
http://www.oracle.com/technology/products/database/sql_developer/index.html

When you get data out of Oracle 8i, what do you plan to do with them? If you only want to put them into new Oracle database, you can upgrade the existing server or use exp/imp utility.

some sample here http://www.orafaq.com/wiki/Import_Export_FAQ
347756
Do you need all table data to be exported to CSV format or just a couple. I know SQL developer has an option to export data to csv format (edit -actually i think SQL Developer can not connect to ORacle 8, you might want to try TOAD).

You can also write sql or plsql to do this as well. Read the asktom thread:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:88212348059

to see all columns of the database, you can query the DBA_TAB_COLUMNS view:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch2238.htm#79797
567269
Are you migrating to SQL Server?

If so you're going the wrong direction (Oracle -> MSSQL Server ), but how about using Micro$oft's SQL Server Migration Assistant for Oracle. It's fun to watch these attempt TSQL -> PL/SQL ( or vice versa ), but you usually can get scalar data migrated pretty easily:

http://www.microsoft.com/sql/solutions/migration/oracle/default.mspx

Edited by: JoeC on Aug 25, 2008 4:09 PM
656701
I thought you could not use SQL Developer for anything less than 9i?


Thanks.
656701
I only need the customer data. However, because the tables are not so cryptically, I can't tell what is customer tables and what is not.

That is why I want to someone see the column names in the tables. Then I choose which tables I needed.


Thanks.
656701
Joe,

I don't need to go from t-SQL to pl/SQL.

I only need the tables themselves. So if the migration assistant can just get me the tables...that would be awesome.


I will take a look at it. I only need tables and then only a portion of the tables. I just wish they were named in such a way as to tell me what they might contain. There are over 1400 tables.

Thanks.
347756
You can not use SQL Developer for 8i databases, I mentioned in my post you can try TOAD by Quest. To find the target table you can query the view I mentioned DBA_TAB_COLUMNS or USER_TAB_COLUMNS ie.

select * from USER_TAB_COLUMNS
where column_name like '%CUST%';

you can use sql and spool to create the csv file in sqlplus:

SET COLSEP ","
spool customer_data.csv
select * from CUSTOMER_TABLE;
spool off
set colsep " "
656701
Hello,

No one likes a thread that goes on and on. I really appreciate those that try to help.

I now this database is being used. However, we are trying to read the database and create reports before switching over to a new application.

The database is in use.

I've tried

select table_name, column_name from user_tab_columns;

I get over 15,000 rows of this

TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
EXU81REFC ROWNER
EXU81REFC RTNAME
EXU81REFC CNAME
EXU81REFC CNO
EXU81REFC RCNO
EXU81REFC ACTION
EXU81REFC ENABLED
EXU81REFC DEFER
EXU81REFC PROPERTY
EXU81REFC ROBJID
EXU81REFC ROWNERID
EXU81REFC REFTYPE
EXU81REFI OBJID
EXU81REFI OWNER
EXU81REFI OWNERID
EXU81REFI TNAME
EXU81REFI ROWNER
EXU81REFI RTNAME
EXU81REFI CNAME
EXU81REFI CNO
EXU81REFI RCNO

(none of which looks like user tables, but rather system tables)

when I tried

SELECT TABLE_NAME, NUM_ROWS FROM ALL_TABLES;

I get over 1400 rows of this:

TABLE_NAME NUM_ROWS
------------------------------ ---------
CNBIL1
CNBIL2
CNBJR
CNBJT
CNBLC
CNBLDE
CNBLES
CNBLK1
CNBLK4
CNBLLOG
CNBLNET
CNBLQ
CNBLR
CNBLR1
CNBLRD
CNBLRR
CNBLRT
CNBLRW
CNBLTF5
CNBLX
CNBPC

I've tried to do a select on a few table_names and I get "no rows selected".

Isn't there a way in Oracle to see what tables have a rowcount > 0 and then get the table_names and column_names from those tables?

I found the free download of TOAD, but not sure how much more that would help me than using SQL Plus at this stage of the game when I don't even see data. Maybe TOAD would help getting the data once I know where to look...I guess.

Thanks for helping to straighten out with the confusion.
347756
Hi Webtechie44,

You can find all tables that have rows/data in them using this query:

select TABLE_NAME
from USER_TABLES
where NUM_ROWS > 0

You can join this query to get all the corresponding columns using the view I mentioned in my previous post, USER_TAB_COLUMNS.

select t.TABLE_NAME, c.COLUMN_NAME
from USER_TABLES t, USER_TAB_COLUMNS c
where t.TABLE_NAME = c.TABLE_NAME
and t.NUM_ROWS > 0
247514

webtechie44 wrote:
I thought you could not use SQL Developer for anything less than 9i?

Thanks.

Oh. yes it's only working with 9iR2 and above.

Still you can do an export with ROWS=N

and import with SHOW=Y or INDEXFILE=filename

It will be a lot easier if you know the schema holder username.

Try to find out all users in the database first.

select username from dba_users
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 25 2008
Added on Aug 25 2008
10 comments
822 views