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

 

[JtheRipper]

Hi,

Is it not possible that you have more than one "security_users" table in your database, just different owners?

Try doing the export again but login with the owner of the security_users table (If it is not system).

J.

 

[bobby13]

Hi J

sorry i made a misttake in writing the thread above i had used the following to export the table:

exp ems_connection_data/manager file=users.expdat tables=(security_users) log=users.log

The table SECURITY_USERS belongs to EMS_CONNECTION_DATA Schema, which is the owner and cannot be accessed by user system

Regards
Bobby

 

[Turkbear]

Hi, any warnings or errors in the log or when running the export?

 

[bobby13]

No warnings what so ever, export terminated successfully without any warnings.

Regards
Bobby

 

[Turkbear]

Hi,
Odd....

To be sure I understand,
if you log in as
EMS_CONNECTION_DATA and do a
select count(*) from SECURITY_USERS
you get 800 but the export ( using the same credentials) reports only 16 rows exported?

Try adding the OWNER=EMS_CONNECTION_DATA to the export command line ( just insurance as to what table gets exported)..
It also could be a bad log file I suppose;try importing the table ( into another schema to avoid confusion) and seeing how may rows are actually in the export file.

 

[bobby13]

HI guys,

I've been off sick so i couldn't try any of the ideas mentioned above. To make things worse the motherboard on our Live server needs replacing!

I want to try to import the table into a different schema, how do you do it? Is it a command as simple as SCHEMA=xyz?



Regards
Bobby

 

[hoinz]

TOUSER=xyz

 

[bobby13]

So I need to run the following:

exp ems_connection_data/manager file=users.expdat tables=(security_users) touser=emsmain owner=ems_connection_data log=users.log

Do you think there are too many parameters involved? Owner parameter is not relevant here is it?



Regards
Bobby

 

[hoinz]

Hi,
there seems to be a misunderstanding;
touser is used with imp, not with exp.
(And btw you may have to use fromuser with imp as well.)
And I don't think that there are too many parameters involved.
And I don't know about owner parameter. Maybe it should be replaced with fromuser parameter?
So, if you already have got an export file, just try to import it.
regards

 

[bobby13]

HI guys,

I tried exporting the table security_users by adding the parameter owner as follows:

So I need to run the following:

exp ems_connection_data/manager file=users.expdat tables=(security_users) owner=ems_connection_data log=users.log

I got the following message:
EXP-00026: Conflicting modes specified
EXP-00000: Export terminated unsuccessfully

Any idea what the conflicting modes are?

Regards
Bobby

 

[hoinz]

Hi,
a quick look in the docs tells us:
owner: export all objects of 'owner'
tables: export these tables

so: which of those two options do you want?
I suggest you omit 'owner'

hth

 

[hoinz]

another idea:
If your problem is how to export one user's table as some other user, system or whatever:
Of course you have to specify the owner of the table,
but not with 'owner=...'
Try this:
exp ... tables=(owner.table) ..
hth

 

[bobby13]

Hi, hoinz

I want to export the table security_users only, i originally tried exporting it without the owner parameter but it only exports 14 rows instead of 800 rows.

I just don't understand why its not exporting the entire table called security_users

Regards
Bobby

 

[bobby13]

I tried the following exp ems_connection_data/manager file=users.expdat tables=(ems_connection.security_users) log=users.log

Again it only exported 14 rows. When i log on to the dataset via SQLplus and run a query to count number of user_ids:

SQL*Plus: Release 9.2.0.1.0 - Production on Tue May 4 13:15:24 2004

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select count(user_id) from security_users;

COUNT(USER_ID)
--------------
866

So shouldn't the export function export 866 rows instead of 14 rows! or am i doing something wrong?

Regards
Bobby

 

[hoinz]

Hm,
strange, ...
two ideas:
1) Now try to import your exportfile to some other user, as Turkbear suggested above.
2) To be sure, in sqlplus, replace your
select count(user_id) from security_users;
by
select count(user_id) from ems_connection.security_users;
hth

 

[bobby13]

I ran the following:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select count(user_id) from ems_connection_data.security_users;

COUNT(USER_ID)
--------------
866

I have never used the Oracle import facility before, what parameters do i need to use? something like this:

imp system/oracle file=users.expdat tables=users


Regards
Bobby

 

[hoinz]

Hi

one other possible reason came to my mind, it could be corrupt blocks, usually caused by hardware problems.
In this case you should find error messages, at least in your alert log file.
But to check this immediately:
select * from your_table;
What do you get?
(I know, this approach will not work, if you have columns of type RAW or CLOB in your table.)
Reason for this check:
If user_id is a primary key, then 'select count(user_id)' will read the index only, not the data itself. But 'select *' will.

And as for imp:
I used it a long time ago.
And I don't want to reread the docs now.
But this could give you a start:
imp system/... file=... tables=... fromuser=ems_connection_data touser=other_user

hth

 

[bobby13]

I ran select * from security_users and it returned 866 rows! I have to read some documentation before i run the import routine!

Regards
Bobby

 

[bobby13]

I succesfully imported the security_users table to another schema i had exported earlier on, and all 14 rows were exported.

Regards
Bobby