Import issue Oracle 8

roy · Post by **roy** » Thu Oct 30, 2003 5:45 am

Hi Yall,
I have a wierd behaviour:
importing table definition of user a's tables works; importing for user b's tables fails,
the wierd thing is that even with user b login import fails
(or I would say: permission issue)
using sqlplus describe works fine with either user as login
with sqlnav seeing columns of both user's tables
only import of tables owned by user b fails to import thru DS
the error I get is:
No columns ... for table ... not imported

Anyone had this / knows what to look for???

Thanks in advance,

kcbland · Post by **kcbland** » Thu Oct 30, 2003 8:53 am

Are you using OCI8 plugin or ODBC? What are the names of the tables, anything weird about them? Do some of the tables in user b make it, or are all bad? Are they physical tables, or synonyms? If using the plugin, are you specifying that user's schema?

Help!

roy · Post by **roy** » Thu Oct 30, 2003 3:10 pm

Hi,
I'm using OCI8 plugin
example for table name: user_name.tname_900_fk
the table/s is/are physical, some are partitioned.
all tables owned by this user fail to import via plugin table definition import
regardless of the user I put in the import process.

any lead would be apreciated

This is still open any leads would be apreciated

roy · Post by **roy** » Mon Nov 03, 2003 7:52 am

Hi All,
this is still an issue,
any Ideas?

kcbland · Post by **kcbland** » Mon Nov 03, 2003 10:01 am

Still drawing a blank. You're using OCI8 plugin, but does the tnsnames.ora instance you're using point to an Oracle 8 instance, or is in an 7.3, 9, etc? Maybe the discrepancy is somewhere in the client software?

degraciavg · Post by **degraciavg** » Mon Nov 03, 2003 9:26 pm

roy,

the only time i encountered this problem was when the userid i used had no SELECT privilege on the table... but your userid is the table owner so it's very strange

anyway, just in case you've overlooked it, can you confirm that your userid can SELECT the data in your table? and, are you importing the table from the correct SCHEMA name?

try this to see the actual owner and privileges granted on the table...

select * from sys.table_privileges
where table_name = '<tab_name>'

regards,
vladimir

roy · Post by **roy** » Wed Nov 05, 2003 9:07 am

Hi,
I'll try that Vladimir.
BTW even with DBA user this import failes

.
the puzzle is what could it be since from the win oracle clients like sqlnav or sql plus describe works fine and I get the scheme of the table, just from DS for those specific tables which ore ORACLE 8 (keneth, I hope I spell it right).
The DBA says that the other user's tables reside on the same DB and in the same repository so I think something is fishi with the ones belonging to that user.
we will try to capture the sql DS send to query the DB and maybe this will help to figure this out.

Amos.Rosmarin · Post by **Amos.Rosmarin** » Wed Nov 05, 2003 9:29 am

Roy,

Just some ideas :

Login as user B create a table, give grant all to public and try to import it .... it is still failes.

what do you mean import failes ... you can select the table name from the list but when you do the import then nothing?? any error message ?

What is the schema name .... look for DS reserved words ?

Are there any rare data types ... I'm not sure DS handles blobs

roy · Post by **roy** » Thu Nov 06, 2003 4:18 am

Hi,
were migrating db schemes for type changes,
therefor have a new DB, all columns are conventional.
import worked fine in the old DB ( both oracle version 8 ).

as you can see in my original post the only error I get is that DS says:
no columns in table xxx import aborted (or similar to that).

does anyone know what DS needs in privilages on repository/system tables, which tables are concerned in ORACLE for the import of table definition?

Thanks in advance,