Exporting Tables from Oracle 9 instance 1

[bobby13]

Hi before i start i have to confess, i've never used the import and export utility in Oracle before. I'm basically trying to export a table called security_users from my live instance. This table contains all login information regarding our users, the table has 800 rows.

I successfully managed to export the table to file called users.expdat, the command i used was:

exp system/manager file=users.expdat tables=security_users log=users.log

but i noticed in the logfile it only exported 13 rows, not 800. Why is that?



Regards
Bobby

 

[Turkbear]

Hi,
Just for kicks, try:

select count(distinct user_id) from ems_connection_data.security_users;

 

[bobby13]

select count(distinct user_id) from ems_connection_data.security_users;

same result 866 rows!

Regards
Bobby

 

[hoinz]

Hi,
just to be sure:
Did you look in your alert log file?
Any error messages at times when you did an exp or an 'select *' ?
regards

 

[sem]

Bobby, are you sure you view correct log file (not an old one created by the first attempt? Would you check it's date?

Regards, Dima

http://seminihin.narod.ru

 

[bobby13]

Hi all,

I checked the alert log no error messages in there regarding the export.

Regards
Bobby

 

[hoinz]

Hi,

another question just to be sure:
Above you wrote:
I succesfully imported the security_users table to another schema i had exported earlier on, and all 14 rows were exported.

Should this read:
..., and all 14 rows were IMported. ?!

Have you got 14 rows in the new table in an other user's schema?

regards

 

[bobby13]

sorry about the miss-spell yes 14 rows were imported successfully, i've got the chaps from oracle coming over next week to sort this out for me, amongst other things.

Do i need to grant any rights or permissions to ems_connection_data user to export a table?

Regards
Bobby

 

[hoinz]

Hi,
I don't know what grants exactly you need to export tables.
But if you can export only some rows of a table, the reason for this cannot be missing grants.
(Your table is a table, isn't it? Not some weird kind of view?)
And for now, sorry, i haven't got any good ideas.
Lets hope the chaps from oracle will find out.
regards

 

[sandman99]

Hi, Sounds like the same problem I had today which I fixed by specifying the instance thus:
exp user/pwd@instance file=tbl.exp tables=user.tblname
- there was an old instance of the database I had forgotten about that I had fewer records in the exported table.
Hope this helps...

 

[bobby13]

Thanks sandman and everyone else, the problem has now been resolved!

Regards
Bobby

 

[hoinz]

sandman,
good point; we should have thought of that!

bobby,
fine to see that the problem has now been resolved!
But now I (and some others too, I presume) would like to know what was the reason. Could you tell us, please?

 

[bobby13]

We have two instances runnning a test instance and live instance, I was not specifying what instance to connect to so I think it was connecting to the test instance which must have only 13 users.

Regards
Bobby