Oracle - import of dmp-files change the table name

[Matthias Maschke]

Hi!

I have a problem importing a dmp-file back to the database.
I have only one database available and have a set of dmp-files with
backuped data (only one table in the file). To make some research on
the data I have to reimport some of the backup files to the database
and add some data from other tables (the actual data and the one in
the backupped which is not yet sored in a dmp-file).
Because there is new data coming to the system I am not able to use
the table the dmp-file was created from. So I want to import the data
to another table for example:
The table DATAARCHIVE was exported to a dmp-file.
I need data from the actual table DATAARCHIVE and the table DATA.
I want to restore the dmp-file to the table DATASEARCH so that I can
add the information from the other tables without "NULL in column xyz
is not allowed" or making the data in TFPLARCHIVE inconsistent or
rebackup the data with the next creation of a dmp-file (which I don't
control).

The environment is too large and with too many dependecies nobody
knows, so I am not allowed to change the structure of the database or
a table or removing Indexes. It is also not possible to stop the
database. It's an Oracle 8.1.7 .

Is there a solution? I tried to rename the table name in the dmp-file
but after that I got errors while importing (header not valid or
problems with the character set). I read the oracle documentation for
"import" but I was not able to find a possibility to choose another
table name (maybe I am just blind).

Thanks a lot in advance.

Greetings
Matthias

[Turkbear]

forum@maschke.biz (Matthias Maschke) wrote:
Hi!I have a problem importing a dmp-file back to the database.I have only one database available and have a set of dmp-files withbackuped data (only one table in the file). To make some research onthe data I have to reimport some of the backup files to the databaseand add some data from other tables (the actual data and the one inthe backupped which is not yet sored in a dmp-file).Because there is new data coming to the system I am not able to usethe table the dmp-file was created from. So I want to import the datato another table for example:The table DATAARCHIVE was exported to a dmp-file.I need data from the actual table DATAARCHIVE and the table DATA.I want to restore the dmp-file to the table DATASEARCH so that I canadd the information from the other tables without "NULL in column xyzis not allowed" or making the data in TFPLARCHIVE inconsistent orrebackup the data with the next creation of a dmp-file (which I don'tcontrol).The environment is too large and with too many dependecies nobodyknows, so I am not allowed to change the structure of the database ora table or removing Indexes. It is also not possible to stop thedatabase. It's an Oracle 8.1.7 .Is there a solution? I tried to rename the table name in the dmp-filebut after that I got errors while importing (header not valid orproblems with the character set). I read the oracle documentation for"import" but I was not able to find a possibility to choose anothertable name (maybe I am just blind).Thanks a lot in advance.GreetingsMatthias

If allowed, create a new user then use the TO=newuser FROM=olduser IMP command line options to import the tables into the
new user's schema..

By granting the correct permissions you can 'see' both tables ...

[Thomas Jensen]

Matthias Maschke wrote: Hi! I have a problem importing a dmp-file back to the database. I have only one database available and have a set of dmp-files with backuped data (only one table in the file). To make some research on the data I have to reimport some of the backup files to the database and add some data from other tables (the actual data and the one in the backupped which is not yet sored in a dmp-file). Because there is new data coming to the system I am not able to use the table the dmp-file was created from. So I want to import the data to another table for example: The table DATAARCHIVE was exported to a dmp-file. I need data from the actual table DATAARCHIVE and the table DATA. I want to restore the dmp-file to the table DATASEARCH so that I can add the information from the other tables without "NULL in column xyz is not allowed" or making the data in TFPLARCHIVE inconsistent or rebackup the data with the next creation of a dmp-file (which I don't control). The environment is too large and with too many dependecies nobody knows, so I am not allowed to change the structure of the database or a table or removing Indexes. It is also not possible to stop the database. It's an Oracle 8.1.7 . Is there a solution? I tried to rename the table name in the dmp-file but after that I got errors while importing (header not valid or problems with the character set). I read the oracle documentation for "import" but I was not able to find a possibility to choose another table name (maybe I am just blind). Thanks a lot in advance. Greetings Matthias

If i read correcty between the lines you don't need the data from an
exact date, so you could create a copy of the table using

create table xyz as select * from orig_table

then create the indexes you need on the new table, if you use cost based
optimizer donøt forget to analyze the table.


best regards

Thomas Jensen

[Matthias Maschke]

> If allowed, create a new user then use the TO=newuser FROM=olduser IMP command line options to import the tables into the new user's schema.. By granting the correct permissions you can 'see' both tables ...

Looks like the perfect way. A little bit tricky with the rights needed
but exactly providing the possibilities I need.
Thank you very much.

Greetings
Matthias

[Mark Bole]

Matthias Maschke wrote:
Hi! I have a problem importing a dmp-file back to the database. I have only one database available and have a set of dmp-files with backuped data (only one table in the file). To make some research on the data I have to reimport some of the backup files to the database
[...] Because there is new data coming to the system I am not able to use the table the dmp-file was created from.
[...] The environment is too large and with too many dependecies nobody knows, so I am not allowed to change the structure of the database or a table or removing Indexes. It is also not possible to stop the database. It's an Oracle 8.1.7 .
[...] Greetings Matthias

The other advice posted is fine as far as it goes, but I see a much
bigger problem here if I infer correctly that this is a production system.

Be assured that it is indeed possible to "stop the database" -- maybe
not in a way that you planned. How can it be that you only have one
database available? Use a test database, create it on the same server
if you must or put it on your workstation using Personal Edition.

If you have "too many dependencies nobody knows", it is inevitable that
you will break something or make a mistake that you can't easily recover
from.

If the database is too big to clone a complete copy for test purposes,
then just import your one table into a new database and use a dblink to
have access to all the tables you need concurrently.

As a side note, if you have a "set" of dmp files that only contain one
table, this would mean the export used the FILESIZE option to split the
output files, and I'm pretty sure you can't re-import only some of them.

--Mark Bole