Home » RDBMS Server » Server Utilities » export/import (Oracle8i Release 8.1.5.0.0 ,win-xp)
export/import [message #282127] Tue, 20 November 2007 23:42 Go to next message
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
Re: export/import [message #282129 is a reply to message #282127] Tue, 20 November 2007 23:45 Go to previous messageGo to next message
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.

Re: export/import [message #287040 is a reply to message #282129] Tue, 11 December 2007 00:07 Go to previous messageGo to next message
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 1024 times)
Re: export/import [message #287042 is a reply to message #287040] Tue, 11 December 2007 00:18 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

You should add tempfile in your temp tablespace.
Re: export/import [message #287047 is a reply to message #287040] Tue, 11 December 2007 00:24 Go to previous messageGo to next message
jitender.sadh
Messages: 86
Registered: May 2007
Member
error screen snapshots.

regards jitender.

  • Attachment: untitled4.bmp
    (Size: 1.37MB, Downloaded 1017 times)
Re: export/import [message #287051 is a reply to message #287042] Tue, 11 December 2007 00:37 Go to previous messageGo to next message
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.
Re: export/import [message #287055 is a reply to message #287051] Tue, 11 December 2007 00:41 Go to previous message
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.

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


Current Time: Tue Dec 24 23:14:39 CST 2024