OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » export/import (Oracle8i Release 8.1.5.0.0 ,win-xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

export/import [message #282127]

Tue, 20 November 2007 23:42

jitender.sadh
Messages: 86
Registered: May 2007

Member

hi

i m taking logical backup of the database by

exp scott/tiger@oracle file ='scott.dmp'

but when i import that file to another database ,it import sucessfully but during importing it gives error unable to create indexes & during query the execution of query is vewy slow.
when i count total number of indexes from database from which i take backup by querying user_indexes it gives.

SQL> SELECT COUNT(*) FROM USER_INDEXES;
COUNT(*)
---------
549

but when i count total number of indexes from database to which i import that file it gives.

SQL> SELECT COUNT(*) FROM USER_INDEXES;
COUNT(*)
---------
322

regards
jitender

Report message to a moderator

Re: export/import [message #282129 is a reply to message #282127]

Tue, 20 November 2007 23:45

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

error unable to create indexes &
AFAIK, there is tablespace space issue...because of this index is not created.
or you should post exact error message.

during query the execution of query is vewy slow.
did you check tkprof/execution plan/statistics for executed query.

Report message to a moderator

Re: export/import [message #287040 is a reply to message #282129]

Tue, 11 December 2007 00:07

jitender.sadh
Messages: 86
Registered: May 2007

Member

ok , i am sending all the quries with result which i am using to create a user and tablespace.

SQL> select * from all_users;

USERNAME                         USER_ID CREATED
------------------------------ --------- ---------
SYS                                    0 01-MAR-99
SYSTEM                                 5 01-MAR-99
OUTLN                                 11 01-MAR-99
DBSNMP                                20 01-MAR-99
MTSSYS                                28 01-MAR-99
AURORA$ORB$UNAUTHENTICATED            25 01-MAR-99
SCOTT                                 26 01-MAR-99
DEMO                                  27 01-MAR-99
ORDSYS                                30 01-MAR-99
ORDPLUGINS                            31 01-MAR-99
MDSYS                                 32 01-MAR-99
CTXSYS                                35 01-MAR-99

12 rows selected.

SQL> create user nds identified by nds;

User created.

SQL> grant dba to nds;

Grant succeeded.

SQL> create tablespace nds datafile 'nds.dbf' size 4500m;

Tablespace created.

SQL> alter user nds quota 0 on system;

User altered.

SQL> alter user nds quota unlimited on nds;

User altered.

SQL> alter user nds default tablespace nds;

User altered.

SQL> select * from all_users;

USERNAME                         USER_ID CREATED
------------------------------ --------- ---------
SYS                                    0 01-MAR-99
SYSTEM                                 5 01-MAR-99
OUTLN                                 11 01-MAR-99
DBSNMP                                20 01-MAR-99
MTSSYS                                28 01-MAR-99
AURORA$ORB$UNAUTHENTICATED            25 01-MAR-99
SCOTT                                 26 01-MAR-99
DEMO                                  27 01-MAR-99
ORDSYS                                30 01-MAR-99
ORDPLUGINS                            31 01-MAR-99
MDSYS                                 32 01-MAR-99
CTXSYS                                35 01-MAR-99
NDS                                   38 07-DEC-07

13 rows selected.

now find the attachments , in this i am sending the snapshots of the error screens which i am getting when i import the .dbf file to the new user NDS.

Attachment: untitled3.bmp
(Size: 1.37MB, Downloaded 911 times)

Report message to a moderator

Re: export/import [message #287042 is a reply to message #287040]

Tue, 11 December 2007 00:18

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

You should add tempfile in your temp tablespace.

Report message to a moderator

Re: export/import [message #287047 is a reply to message #287040]

Tue, 11 December 2007 00:24

jitender.sadh
Messages: 86
Registered: May 2007

Member

error screen snapshots.

regards jitender.

Attachment: untitled4.bmp
(Size: 1.37MB, Downloaded 900 times)

Report message to a moderator

Re: export/import [message #287051 is a reply to message #287042]

Tue, 11 December 2007 00:37

jitender.sadh
Messages: 86
Registered: May 2007

Member

you mean by using the following query

CREATE USER sidney
IDENTIFIED BY out_standing1
DEFAULT TABLESPACE example
QUOTA 10M ON example

TEMPORARY TABLESPACE temp

QUOTA 5M ON system;

regards
jitender.

Report message to a moderator

Re: export/import [message #287055 is a reply to message #287051]

Tue, 11 December 2007 00:41

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

>you mean by using the following query
No,
Before import.
add tempfile in your current temporary tablespace.

alter tablespace "temptbs"
add tempfile 'OS path\filename.dbf' size x m;

thenimport your data.

Report message to a moderator

Previous Topic:	imp utility
Next Topic:	import/export query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 30 01:24:34 CDT 2024