How to re-import data in Oracle 11g on Oracle Linux 6.5

What do you mean by "re-import"?

If you mean:
Get rid of everything currently in the database and do the import again:
Drop and recreate the schemas you want imported, then run the import for those schemas.

You can also take a look at:
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL936

TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]

REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

slightwv,

I mean to import and replace all the data from previous imported dmp files.
Can I use the oracle em feature(Data Movement->Import from export file to do it?
Thanks.

Mark

>>I mean to import and replace all the data from previous imported dmp files.

I've never tried TABLE_EXISTS_ACTION=REPLACE but from the docs, it should do it.

When I've needed to do something like this, I drop/recreate the user then import.  This way I know I'm getting everything in the import file and not leaving any potential new stuff around.

>>Can I use the oracle em feature(Data Movement->Import from export file to do it?

Don't know.  Don't use the GUI for much of anything.  The GUI should have all the options available that the command line has but I cannot say for sure.

Can I use TABLE_EXISTS_ACTION to replace all the tables?
Thanks.

>>Can I use TABLE_EXISTS_ACTION to replace all the tables?

That is what the documentation says it does.  I have to believe the docs since I've never personally done it.

Also to add TABLE_EXISTS_ACTION=REPLACE will very much do replace the table data, but the only exception to use this option is you must not have any referential integrity (constraints) on the your tables as it simply deletes and inserts the data again, in case of referential integrity constraints might prevent the deletion in case the child-parent order is not followed(and as far as i remember the import of table data will happen in alphabetical order)

>> on the your tables as it simply deletes and inserts the data again

Small correction:  REPLACE drops the table.  TRUNCATE deletes the data.

The comment on referential integrity constraints is correct and also in the docs:

When you use TRUNCATE or REPLACE, ensure that rows in the affected tables are not targets of any referential constraints.

What sql stmt do I need to run to drop the schema?
The user is root. Do I have to drop the root user in order to drop the schema?
Thanks.

I need to drop and then import ROOT schema.
Thanks.

Just to clarify:  You have a database user named 'ROOT'?

If so, the steps are the same as posted above.

Yes, ROOT is a database user.

Please let me know if any mistakes from the steps below.
1. spool the output from get_ddl into rebuild_root.sql file
2. drop the users/schema by: drop user SOMEUSER cascade;
3. re-create the users by running rebuild_root.sql
4. reimport the ROOT Schema and import the data again.

Thanks.

Step 3: not users plural since there is only the one user:
drop user root cascade;

Step 4 should be able to be done in one step as long as the export file only contains that user.

Other than that it looks good with the one exception I noted above:

If the schemas have a lot of grants, the import might not get them all depending on who granted them.

If you drop a user, all grants are removed.  Depending on the export, you might need to re-grant them.  Again, depends on the export.

I will try slightwv's suggestion and update this case soon. thanks.

slightwv,
Below are what I did on the Oracle demo server. Please let me know if you see any problems. Thanks.

1. SELECT DBMS_METADATA.GET_DDL('USER','ROOT') FROM dual;


DBMS_METADATA.GET_DDL('USER','ROOT')
--------------------------------------------------------------------------------

   CREATE USER "ROOT" IDENTIFIED BY VALUES 'S:FA1C56EED0DDECDC3E3FD2320F53518451
C10B980B9C63AC96AE35BC8AB0;34754AB434AE34CE'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

2. drop user ROOT cascade

3. Use the dll in step1 to recreate ROOT user

4. Use oem datapump to create schema and data import. Below is the sql if I click 'show sql' from oem
declare
  h1   NUMBER;

begin
     
h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'SCHEMA', job_name => 'IMPORT ', version => 'COMPATIBLE');
   
dbms_datapump.set_parallel(handle => h1, degree => 1);
   
dbms_datapump.add_file(handle => h1, filename => 'IMPORT.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);
   
dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
   
dbms_datapump.add_file(handle => h1, filename => 'expAiMfull.dmp', directory => 'DATA_PUMP_DIR', filetype => 1);
   
dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_TABLESPACE', old_value => '/ORACLE/ORADATA/MAX/UD1/DATAMAX_01.DBF', value => '+DATA/MAX/DATAFILE/MAX01.266.860253321.DBF');
   
dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''ROOT'')');
   
dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
   
dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
   
dbms_datapump.set_parameter(handle => h1, name => 'TABLE_EXISTS_ACTION', value => 'REPLACE');
   
dbms_datapump.set_parameter(handle => h1, name => 'SKIP_UNUSABLE_INDEXES', value => 0);
   
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
   
dbms_datapump.detach(handle => h1);
end;

/
5. Ran the following after the import completed.
Exec sys.utl_recomp.recomp_serial(‘ROOT’)
Select object_name from dba_objects where status <> ‘VALID’ and owner = ‘ROOT’;
No row return.

the import job said failed, but the tables look fine. Below is the import.log file. It is just a warning caused user ROOT was already created in step 3 above ?

Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"ROOT" already exists

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "ROOT"."AE_X"                       287.8 MB  641774 rows

xxxxxxx imported all other tablesXXXXXXX

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT

Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION

Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE

Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION

Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER

Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/MATERIALIZED_VIEW_LOG

Job "oracle"."11-16-2014 DATA IMPORT" completed with 1 error(s) at 08:36:10

Looks like the import tried to recreate the ROOT user.  Looks like you could have skipped that step.

Should I let the import to recreate the user instead of using dll to do it? Thanks.

>>Should I let the import to recreate the user instead of using dll to do it?

If you want no errors, then let the import do it.

The benefit to doing it manually is you have the opportunity to change things like the password.

If you are reloading something like a test database from a production export, you typically don't have the same passwords.