asked on
Export and Import Warnings
Hi
I am trying to do export and import from one database to another database. And See warning messages in the log file
EXP-00056: ORACLE error 4031 encountered
ORA-04031: unable to allocate 18020 bytes of shared memory ("shared pool","KU$_NT_LIST_T","typ
ORA-06512: at "SYS.DBMS_METADATA", line 1546
ORA-06512: at "SYS.DBMS_METADATA", line 1583
ORA-06512: at "SYS.DBMS_METADATA", line 1901
ORA-06512: at "SYS.DBMS_METADATA", line 3806
ORA-06512: at "SYS.DBMS_METADATA", line 3784
ORA-06512: at line 1
The destination database is already having data when I try to Import full database at destiation it gives me whole bunch or warning saying object already exist
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file 'D:\ORACLE\PRODUCT\10.2.0\
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists
IMP-00017: following statement failed with ORACLE error 1119:
I thought something wrong with the process or the way I am trying to import.
Do I need to remove all objects from destination database?
Can you please help with steps I need to fallow If I want to make two database same. And when I do export and import I don't want any warnings. Can yuo help me with steps I need to fallow for export and import?
Thanks
I am trying to do export and import from one database to another database. And See warning messages in the log file
EXP-00056: ORACLE error 4031 encountered
ORA-04031: unable to allocate 18020 bytes of shared memory ("shared pool","KU$_NT_LIST_T","typ
ORA-06512: at "SYS.DBMS_METADATA", line 1546
ORA-06512: at "SYS.DBMS_METADATA", line 1583
ORA-06512: at "SYS.DBMS_METADATA", line 1901
ORA-06512: at "SYS.DBMS_METADATA", line 3806
ORA-06512: at "SYS.DBMS_METADATA", line 3784
ORA-06512: at line 1
The destination database is already having data when I try to Import full database at destiation it gives me whole bunch or warning saying object already exist
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file 'D:\ORACLE\PRODUCT\10.2.0\
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists
IMP-00017: following statement failed with ORACLE error 1119:
I thought something wrong with the process or the way I am trying to import.
Do I need to remove all objects from destination database?
Can you please help with steps I need to fallow If I want to make two database same. And when I do export and import I don't want any warnings. Can yuo help me with steps I need to fallow for export and import?
Thanks
exp / full=y file=c:\backup\expdat.dmp consistent=y grants=y constraints=y direct=y log= c:\backup\expdat.log.
imp / file= c:\backup\expdat.dmp ignore=Y log= c:\backup\impdat.log analyze=N full=Y commit=Y rows=Y
Can you pls provide step by instructions?
Oracle Database
Last Comment
dbmullen
WolfgangKoenig is right, in that you can ignore the "file already exists" errors; those warnings are generated because the export dump file has the information to create the tablespaces as well as the data.
The first errors, though, might be a real problem. I'd take a look at the this page to see if it's helpful:
http://www.dba-oracle.com/sf_ora_04031_unable_to_allocate_string_bytes_of_shared_memory.htm
The first errors, though, might be a real problem. I'd take a look at the this page to see if it's helpful:
http://www.dba-oracle.com/sf_ora_04031_unable_to_allocate_string_bytes_of_shared_memory.htm
ASKER
Does import create any tablespaces if there are no tablespaces?
If there is no tablespace and datafile how it will create?
Do we need to create tablespace manual on other machine. Can you provide with steps.
The two database are 10.2.0.1.0 so there is no problem if we have Higher version at destination. If I am right.
Do I need to drop existing object at destination if they already exist?
The destination database is having objects.
Thanks
If there is no tablespace and datafile how it will create?
Do we need to create tablespace manual on other machine. Can you provide with steps.
The two database are 10.2.0.1.0 so there is no problem if we have Higher version at destination. If I am right.
Do I need to drop existing object at destination if they already exist?
The destination database is having objects.
Thanks
Yes tablespaces will be created at the import ...
You don't need to drop existing objects at destination db, but you must set the IGNORE=Y parameter!
You don't need to drop existing objects at destination db, but you must set the IGNORE=Y parameter!
Import will create tablespaces if they do not exist (in the same locations as on the source), and it's safe to ignore the warning if they do exist.
As for whether or not you need to drop the existing objects, that depends on a number of factors, primarily: what do you wish to accomplish?
If you want the imported database to mirror the original source, then yes: you do need to drop the objects. Since you've already got the tablespaces created, it's probably easier just to drop the tables in them, perhaps building a script at runtime:
SELECT 'DROP ' || object_type || ' ' || owner || '.' || object_name || ';'
from dba_objects where owner in ('<put schema name here>');
This will build a drop script that will drop all of the objects (should that be what you want to do) that are in a given schema. You can either spool this to a file or just select its output and paste it back to the terminal.
The other instance in which you'd need to drop tables, in particular, is if there are unique constraints within the target database that would be violated by the imported data.
That is: if a field that is supposed to be unique in the target database has a value of "1", and that same field in the table in the source DB also has a value of "1", that record won't be imported.
I hope that helps!
As for whether or not you need to drop the existing objects, that depends on a number of factors, primarily: what do you wish to accomplish?
If you want the imported database to mirror the original source, then yes: you do need to drop the objects. Since you've already got the tablespaces created, it's probably easier just to drop the tables in them, perhaps building a script at runtime:
SELECT 'DROP ' || object_type || ' ' || owner || '.' || object_name || ';'
from dba_objects where owner in ('<put schema name here>');
This will build a drop script that will drop all of the objects (should that be what you want to do) that are in a given schema. You can either spool this to a file or just select its output and paste it back to the terminal.
The other instance in which you'd need to drop tables, in particular, is if there are unique constraints within the target database that would be violated by the imported data.
That is: if a field that is supposed to be unique in the target database has a value of "1", and that same field in the table in the source DB also has a value of "1", that record won't be imported.
I hope that helps!
Dropping all the objects in a database is not quite that simple.
For example... You cannot drop a table that has refernetial integrity constraints that point an index on the table. You cannot drop an index that is used to enforce a primary key.
One way would be to drop all the tables with cascade contraints, then go back and drop everything else. A 2 step approach.
select 'drop table ' || table_name || ' cascade constraints;'
from dba_tables
where owner in (....);
Once all of the tables are dropped then you can use:
SELECT 'DROP ' || object_type || ' ' || owner || '.' || object_name || ';'
from dba_objects where owner in ('<put schema name here>');
The second drop should only be getting any procedures/functions/packa
For example... You cannot drop a table that has refernetial integrity constraints that point an index on the table. You cannot drop an index that is used to enforce a primary key.
One way would be to drop all the tables with cascade contraints, then go back and drop everything else. A 2 step approach.
select 'drop table ' || table_name || ' cascade constraints;'
from dba_tables
where owner in (....);
Once all of the tables are dropped then you can use:
SELECT 'DROP ' || object_type || ' ' || owner || '.' || object_name || ';'
from dba_objects where owner in ('<put schema name here>');
The second drop should only be getting any procedures/functions/packa
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I meant to say:
if the "source" can be shutdown, there is a much faster ways to do this.
if the "source" can be shutdown, there is a much faster ways to do this.
first check at least that the version of the destination database is higher or better same version as the source database.
Second check that the version of the imp tool exact matches the version of destination database and
version of exp tool exact matches the version of source database.
(with exp -help you can see it)
When your destination database has already db objects of the source db then you can supress warnings from imp with:
IGNORE=Y