Why do I get "table or view no exist" error, after export from MS-SQL

[lydiattc]

Hi,

I have exported one table from MS SQL server to Oracle 9.2.0.1 on XP.

The table creation was successful shown by the Export Wizard in SQL server. When I view the table in Oracle's Enterprise Manager Console, it's there with all the data. However, when I query from SQL*PLUS, it gives me a error of "table or view does not exist."

Does anybody know why?

Thanks.

 

[sybaseguru]

Have you checked to make sure that which schema or user the migrated table belongs to when you look at it using OEM. Use the same user login as you are using for OEM to log in via SQL*Plus and have a look at user_tables or all_tables (select owner, table_name from all_tables). My guess is that although you have migrated the table OK, you are using an SQL*PLus loggin which does not have the privileges to see the migrated table. If you can log in as sysdba in SQL*Plus you should see the table in dba_tables.

I hope this helps

 

[sem]

Another guess is that your table name contains lowercase (or some other unsuitable) characters. In this case you should use doubleqoutes in sql*plus.

Regards, Dima

 

[lydiattc]

Solved. It was because of the lower-cases. I had to use double quotes to query.

Thanks!

Lydia

 

[Turkbear]

Hi,
Just to reinforce a very important point..
When moving from Access to Oracle using the Access export function, it is vital to pre-configure the Access data structure to meet Oracle standards..
Upper case for all tables, fields, etc
No MEMO fields - convert them to TEXT first or thay will become almost unmanagable LONG data types in Oracle.

No 'special characters' in the table or field names..

In other words, the Export from Access to Oracle needs to be thought thru before actually moving the data.

( The Oracle Migration Workbench tools are better at doing this type of move and, if you are doing a lot of mixed database work, is well worth learning)