Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    29

    Unanswered: How do I extract data from views given a port, username, password and service name?

    Hi Guys,

    I'm trying to dump ~500k rows from ~20 views at a client's site. The server is running version Oracle 8.1.5 on SCO Unix and I don't have a terminal login to that box.

    I'm using SSH port forwarding to make TCP 1521 available here where I have a Slackware Linux 10.2 box and several Win2k boxes, alternatively there are WinXP boxes at the client site where I can install and run software.

    The information I've been provided with is a username, password and service name.

    The data data is to be migrated into a MSSQL DB. While I believe it will be possible to extract the data using ODBC drivers and Enterprise Manager I think it will be quicker to dump the data to some sort of text file at the client site first. What tools would you recommend to extract this data (this is my first time dealing with oracle)?

    Here's what I've tried already:

    Install SQL Developer: I can successfully connect to the DB on my fowarded port my select statements only return 50 rows in the tabular window or 5000 in the text results. I've got several hundred thousand rows to get so to do it in 5k row batches would be pretty awkward. The export DDL and data migration tools don't seem to work with the server version I'm talking to as SQL Developer warns that only version 9i and above are supported.

    Install Oracle Forms 6i: I grabbed this as it was a reasonable ('only' ~250meg as opposed to a 1.8Gb download for Oracle9i Database Release 2) size and comes with Net8 & SQLPlus. The problem I'm hitting here is that I've got a service name and Net8 wants an SID. I'm not sure how to resolve one from the other and the folk that maintain the server are being particularly unhelpful.

    Any suggestions for a more appropriate tool or solutions to the stumbling blocks I've hit would be appreciated.

    -Karl.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It depends.
    Words have meaning & computers can be picky.
    You said "views", I wonder if those are actually Oracle VIEWs.
    It depends upon the the data types which need to be retrieved.
    From my perspective you really have a two part problem.
    1st is to get the data off the old SCO & V8.1. box.
    2nd is to get data into MSSQL DB.
    If this were my challenge, I'd try to use EXPORT (exp) utility to get the data in datafiles which could be network copied onto a "local" box.
    This approach will require a function Oracle DB so the data could then be imported (imp).
    Then you worry about getting it to MSSQL.
    I hope you are getting top dollar for jumping thru hoop & saving their data.
    HTH & YMMV!

    http://asktom.oracle.com/pls/asktom/...ID:68212348056
    might get you closer to a solution
    Last edited by anacedent; 06-20-07 at 21:25.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2005
    Posts
    29
    Thanks for your reply anacedent,

    Quote Originally Posted by anacedent
    It depends.
    Words have meaning & computers can be picky.
    You said "views", I wonder if those are actually Oracle VIEWs.
    I believe what I'm selecting from are views as the SQL Developer package show the 'object type' to be 'view' for them.

    Quote Originally Posted by anacedent
    It depends upon the the data types which need to be retrieved.
    From my perspective you really have a two part problem.
    1st is to get the data off the old SCO & V8.1. box.
    2nd is to get data into MSSQL DB.
    I agree completely here it's just that I've done your step two many times before with a different step 1

    Quote Originally Posted by anacedent
    If this were my challenge, I'd try to use EXPORT (exp) utility to get the data in datafiles which could be network copied onto a "local" box.
    This approach will require a function Oracle DB so the data could then be imported (imp).
    Then you worry about getting it to MSSQL.
    I hope you are getting top dollar for jumping thru hoop & saving their data.
    HTH & YMMV!
    If I understand you correctly I'm not sure this approach is ideal because it requires me to set up another instance of Oracle. If I can dump straight out of the existing DB it would cut out this step. On that note, the url you gave me paid off nicely!

    Quote Originally Posted by anacedent
    lead me to: http://asktom.oracle.com/tkyte/flat/index.html. Which has some queries and shell scripts to dump data from a table or view to a character delimited text file.

    As for top dollar for the work, that'd be nice. It's currently just part of the service of taking on a new customer.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •