Forum Stats

  • 3,814,850 Users
  • 2,258,920 Discussions
  • 7,892,875 Comments

Discussions

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

656701
656701 Member Posts: 9
edited Aug 28, 2008 12:47AM in General Database Discussions
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!

Answers

  • 247514
    247514 Member Posts: 10,875 Bronze Trophy
    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
    347756 Member Posts: 130
    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
    567269 Member Posts: 213
    edited Aug 25, 2008 7:10PM
    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
    656701 Member Posts: 9
    I thought you could not use SQL Developer for anything less than 9i?


    Thanks.
  • 656701
    656701 Member Posts: 9
    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
    656701 Member Posts: 9
    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
    347756 Member Posts: 130
    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
    656701 Member Posts: 9
    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
    347756 Member Posts: 130
    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
    247514 Member Posts: 10,875 Bronze Trophy
    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
This discussion has been closed.