Solved

Export and Import oracle dump script

Posted on 2006-06-22
11
5,220 Views
Last Modified: 2012-06-21
Can someone provide methe oracle export and import script. I just recently exported a oracle database to dmp file which is in 9i.
Now i like to import it into Oracle8 using the import script.
Please help.
0
Comment
Question by:netcool
11 Comments
 
LVL 47

Accepted Solution

by:
schwertner earned 125 total points
ID: 16966769
It is not possible to import downwards,
i.e. to import in 8i a 9i dump.

Set NLS_LANG parameter to a value that will
be appropriate to the character set of your DB. Otherwise possibly you will lose nonenglish letters
if any.

For export create a file with:
file=c:\box\05_11_2005_m.dmp
userid="sys/reks@test as sysdba"
log=c:\box\log_export.log
owner=sch1,sch2
rows=y
statistics=none
grants=y
compress=y

To do the export:
c:>exp parfile=the-file

For import:

USERID="sys/reks@test as sysdba"
FILE=d:\ora10g\m\main\imp\07_15_2005_m.dmp
SHOW=n
IGNORE=y
GRANTS=y
ROWS=y
FULL=n
FROMUSER=sch1,sch2
LOG=d:\ora10g\m\main\imp\imp.log

c:>imp parfile=the-file
0
 
LVL 5

Author Comment

by:netcool
ID: 16966893
If i export the table is call test1, when i import can i change it to test2 ?
0
 
LVL 8

Expert Comment

by:gvsbnarayana
ID: 16967193
Hi,
  No, it is not possible to change the names with Import. But with Oracle 9i, you have feature to rename a table. So, you can import normally and then issue a statement of Alter Table tablename Rename to .
HTH.
Regards,
Badri.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 16968476
Renaming a table has been around long before 9.

You cannot directly use import and change the table name.  However, there is a trick that should work, if I remember it correctly.  Create the table with the new name, then create a synonym with the old name pointing to the new name.  When you import, be sure to specify ignore=y.
0
 
LVL 1

Expert Comment

by:jim144
ID: 16969853
Hello,
I must disagree with swertner, because I have done and export/import from a higher level db into a lower level db in the past.....

In order to import a table into 8i that was exported from a 9i database, you must use the 8i export utility against the 9i database first, to create the dmp file. Just remember that you will be limited to the 8i export functionality.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 32

Expert Comment

by:awking00
ID: 16975341
Renaming syntax for a table is simply
rename oldtablename to newtablename;
0
 
LVL 47

Expert Comment

by:schwertner
ID: 16981694
To Jim144:
What you say is limited to export of objects that exist in 8i.
If you try to export with 8i objects that are new for 9i and unknown for 8i,
the export will fail.
Example is the new AL32UTF8 character set that is new for 9i.
So, cutting the edges, one not very happy day of your career
your transfer metodology will not work and you will need to
look for other methods to transfer objects - flat files, DDL's, etc.
0
 
LVL 1

Expert Comment

by:jim144
ID: 16983568
To Schwertner:
You stated "It is not possible to import downwards"  . .... I was simply stating it is possible. I also said you were limited to the 8i functionality .......

8^ )
0
 
LVL 47

Expert Comment

by:schwertner
ID: 16983894
Yes, I understand your point!
I think the risk in such solution is too high.
Oracle is used for big organization and the DBA has to
be very conservative  not to bring troubles.
As a rule 8i software works not good with 9i and higher versions.
0
 
LVL 1

Expert Comment

by:jim144
ID: 16984880
You're right (?)  I'll not answer anymore ..... can't compete with someone that HAS to be right at all costs ....... thats why i stopped 2 years ago ...
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17000037
No, no, these are emotions only!
I respect your contribution here!
Please excuse me, seems this are cultural differences
and an unwanted misunderstanding.
Excuse me for my remarks!
There is no competion at all! Points are nothing! Believe me!
I am very happy to know that Oracle 8i software
works against 9i. E.g. I failed to run Forms 6i
against 9i DB because of AL32UTF8 problem
and was very disapointed at that time.
I am sure we all be very happy if you
post here.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now