This discussion is archived
2 Replies Latest reply: Dec 3, 2012 6:13 AM by user480768 RSS

Copy Access Table(s) to Oracle

user480768 Newbie
Currently Being Moderated
I use Oracle SQL Developer version 3.2.20.09 "Copy to Oracle..." to copy table(s) from MS Access 2007 to Oracle 11g R2 database instance. All the tables from Access will go to the system schema, even if I choose a connection which connects to a specific user. I wonder if I can let the MS Access table(s) to be copied to a specific schema instead of system schema? Thank you for your help

Kevin
  • 1. Re: Copy Access Table(s) to Oracle
    rp0428 Guru
    Currently Being Moderated
    >
    I use Oracle SQL Developer version 3.2.20.09 "Copy to Oracle..." to copy table(s) from MS Access 2007 to Oracle 11g R2 database instance. All the tables from Access will go to the system schema, even if I choose a connection which connects to a specific user.
    >
    You need to explain that. What steps are you using to select 'Copy to Oracle'? Oracle won't create tables in the system schema unless you are connected as user SYSTEM or a user that has privileges as a DBA.

    So if you only have one connection open (e.g. SCOTT) that is where the objects will get created since there is no other connection sql developer could use and SCOTT is not a DBA or have privileges in the SYSTEM schema.

    Close all connections. Open the connection you want the tables to go to and try it. Post the results.
  • 2. Re: Copy Access Table(s) to Oracle
    user480768 Newbie
    Currently Being Moderated
    I delete all the local cached files from my windows 7 related to SQL developer. Open the SQL developer again and recreate two connections, one for Access Database, another for the Oracle schema I would like to create all the tables there. Do a Copy Table(s) to Oracle again, it works. All the Access tables can be copied to the specific schema instead of system. Thanks for your help.

    Kevin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points