Question about IMP/EXP in Oracle 9i

[eseabrook2008]

My company uses a system with an Oracle 9i backend. Each night, a full database export is run. The question I have is, how can I use the IMP statement to re-import that data back into the existing database (for whatever reason)? When I ran...
imp <user>/<password>@<tns> file=your.dmp full=y log=your.log ignore=y
...it just added the data to the existing tables. I'm not fluent with Oracle but from what I've been able to find, by searching on line, is that I need to drop/truncate the DB first and then run the imp statement? If this is correct, I have not had much success with it. We're running 9i on a Windows 2000 Server. Oh, and I'm doing this in an exact test enviro...so no worry of data loss! Thanks!

 

[eseabrook2008]

I just tried using the DESTROY=Y parameter but I get:

row rejected due to ORACLE error 1
ORACLE error 1 encountered
unique constraint <...> violated

Now, I thought that the DESTROY would allow for an overwrite but from what I've read, I still need to empty the tables before running the import? Yes/No? Thanks!

 

[SantaMufasa]

ESeabrook,

The quick, easy method that I use to clear out an Oracle user/schema (what you call a "database", I believe) is to do a:

DROP USER <username> CASCADE;

...then re-create the user, then perform the import.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[eseabrook2008]

Just so I know I've done this correctly, here are the steps I took..

Open SQLPlus
Log in as: username\password\tnsname
type "drop user username1 cascade;"
type "create user username1 identified by password1;"
close SQLPlus
open command prompt
type "imp username1\password1@tnsname file=<location of dump file> full=y ignore=y log=<name of new log file>"

Look correct? There are still a lot of errors (Oracle error 2264 "name already used by an existing contraint") that show up when the import firsts starts but the tables referenced in those errors are not tables that belong to the user that I deleted.

 

[SantaMufasa]

Close, ESeaBrook...After you "CREATE USER...", you must also GRANT enough privilege to the user to allow for the creation of tables. Typically, for such users, we:

GRANT CONNECT, RESOURCE to <username>;

The reason that you are receiving the "ORA-2264: Name already used..." is because of your import setting, "FULL=Y": this means import the entire contents of the dump file, which includes objects owned by other Oracle users (not what you want in this case). What you want, instead, are these "imp" settings:

FROMUSER=<username> TOUSER=<username>

Let us know how things proceed.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[eseabrook2008]

Boy, this thread is going to have some concise steps to running the imp command!

Here is exactly what I'll do...

1 - I ran this inside SQLPlus: "SELECT owner,TABLE_NAME FROM all_TABLES owner = ‘administrator';" and it returned all the table names of the DB I want to import the data for. So I know the schema is administrator.
2 - type "drop user administrator cascade;"
3 - type "create user administrator identified by password;"
4 - type "GRANT CONNECT, RESOURCE to administrator;"
5 - exit SQLPLus and open a command prompt
6 - type "imp administrator\password@tnsname FILE=<filename> IGNORE=y LOG=<logname> FROMUSER=administrator TOUSER=administrator"

How does that look?

 

[SantaMufasa]

That looks good to me, ESeaBrook.

I infer from the username "ADMINISTRATOR" that in addition to privileges from role membership "CONNECT" and "RESOURCE", that such user might need "DBA" role membership, as well...but I'll leave that up to you to decide.

Let us know how things turn out for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[eseabrook2008]

Well, I ran into some problems. First I couldn't sign in with an account from which to drop that user. After finally discovering one, I removed it. Then after running the import for quite some time, I ran into a bunch of errors and from what I can tell, I basically ran out of disk space. So I'm resizing the disk and am starting again. I'll let you know how I make out.

 

[eseabrook2008]

Oh, and when I tried with the parameters fromuser=administrator touser=administrator, it finished in about 10secs so I'm thinking that that didn't work as expected. So how can I do a full import and over write all the data for all schemas? Would I create a new user and delete all the users and then recreated them all and run the import?

 

[SantaMufasa]

Would I...delete all the users and then recreated them all and run the import?

That's what I do, provided I KNOW that the dump file contains all the data that I need to restore all of the users that I just DROPped. As you can imagine, if you have no dump-file contents (or other backup) for a user that you have DROPped, then that user's data is lost and gone forever.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[eseabrook2008]

Here are the users in the explog.log file:

SYSTEM
OUTLN
DBSNMP - no tables
ADMINISTRATOR - schema I really need
SYSTEMADMINISTRATOR - no tables
ARCHIVE - no tables
SYSMAN
GM

So I could go through and do a "drop user <username> cascade;" for each one? I'm guessing I need to add the appropriate privs for each user after I add them?

 

[SantaMufasa]

I suggest that you do not delete Oracle users that are not the focus of your import !

when I tried with the parameters fromuser=administrator touser=administrator, it finished in about 10secs

It sounds to me, ESeaBrook, that you and I are flying a bit blind:[ul][li]You sense that your import should take more than 10 seconds[/li][li]you sense that "administrator" is the owner of the tables that you want to import because you ran this query:

SELECT owner,TABLE_NAME FROM all_TABLES owner = 'administrator';

(That query, BTW, won't work syntactically since the WHERE clause is missing the keyword "WHERE". Secondly, that query should return "no rows selected" since the query looked for an owner matching 'administrator'...the query would only return rows if the query looked for an owner matching 'ADMINISTRATOR'...notice uppercase versus lowercase characters.)


Furthermore, just because the tables you were looking for appear under the owner named 'ADMINISTRATOR' doesn't mean that tables by those same names cannot also be under the ownership of one of the other users![/li][li]You seem to not be the person that created the export dump file, thus you are not really certain what is in the dump file.[/li][/ul]Before you attempt anything more destructive than may already have occurred, I proposed that you confirm the contents of the dump file by issuing the following 'imp' command:

imp administrator\password@tnsname FILE=<filename> IGNORE=y FULL=y INDEXFILE=<textfile>

'<textfile>' is some name that you devise on your machine; the file will contain the Oracle SQL code of all of the "CREATE TABLE..." and "CREATE INDEX..." statements that appear in your dump file, including the name of the owner of the tables and indexes. That information should be an excellent confirmation of who the FROMUSER and TOUSER should be and what to expect on import. (When you provide the "INDEXFILE=..." parameter Oracle makes no attempt to actually import objects and their data, so nothing destructive will occur on that run of 'imp'.)


Let us know your findings.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[eseabrook2008]

When I do as you say, the resultant file shows a lot of "create table" commands and "alter table" commands REM'd out and the commands that pertain to this my DB are referring "ADMINISTRATOR"."<tablename>".

Here is an example:

REM  CREATE TABLE "ADMINISTRATOR"."AR_TRANSACTION_AMOUNTS" 
REM  ("TRANSACTIONOBJECTID" NUMBER(15, 0), "TRANSACTIONNUMBER" NUMBER(4, 
REM  0), "LEDGEROBJECTID" NUMBER(15, 0), "LEDGERTYPE" VARCHAR2(1), "DEBIT" 
REM  NUMBER(18, 8) NOT NULL ENABLE, "CREDIT" NUMBER(18, 8) NOT NULL 
REM  ENABLE, "DESCRIPTION" VARCHAR2(80), "BUDGETCATEGORY" VARCHAR2(80), 
REM  "BUDGETDEPARTMENT" VARCHAR2(80)) PCTFREE 10 PCTUSED 40 INITRANS 1 
REM  MAXTRANS 255 STORAGE(INITIAL 54542336 NEXT 23592960 MINEXTENTS 1 
REM  MAXEXTENTS 249 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 
REM  BUFFER_POOL DEFAULT) TABLESPACE "GLDATA" LOGGING ;
REM  ... 7680031 rows
CREATE INDEX "ADMINISTRATOR"."IDX4AR_TRANSACTION_AMOUNTS" ON 
"AR_TRANSACTION_AMOUNTS" ("LEDGEROBJECTID" , "TRANSACTIONOBJECTID" ) 
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 43663360 NEXT 26542080 
MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT) TABLESPACE "GLINDEX" LOGGING ;
CREATE INDEX "ADMINISTRATOR"."IDX1AR_TRANSACTION_AMOUNTS" ON 
"AR_TRANSACTION_AMOUNTS" ("LEDGEROBJECTID" , "LEDGERTYPE" ) PCTFREE 10 
INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 43663360 NEXT 26542080 MINEXTENTS 
1 MAXEXTENTS 249 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL 
DEFAULT) TABLESPACE "GLINDEX" LOGGING ;
CREATE UNIQUE INDEX "ADMINISTRATOR"."C10_AR_TRANSACTION_AMOUNTS" ON 
"AR_TRANSACTION_AMOUNTS" ("TRANSACTIONOBJECTID" , "TRANSACTIONNUMBER" ) 
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 47841280 NEXT 26542080 
MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT) TABLESPACE "GLINDEX" LOGGING ;
REM  ALTER TABLE "ADMINISTRATOR"."AR_TRANSACTION_AMOUNTS" ADD CONSTRAINT 
REM  "C10_AR_TRANSACTION_AMOUNTS" PRIMARY KEY ("TRANSACTIONOBJECTID", 
REM  "TRANSACTIONNUMBER") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
REM  STORAGE(INITIAL 47841280 NEXT 26542080 MINEXTENTS 1 MAXEXTENTS 249 
REM  PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
REM  TABLESPACE "GLINDEX" ENABLE ;

but if I run "imp administrator/password@tns filename=filename log=logfile show=y ignore=y fromuser=administrator touser=administrator" I get:

 "CREATE TABLE "ACCOUNTINFORMATION" ("OBJECTID" NUMBER(15, 0), "TRANSITNUMBER"
 "" VARCHAR2(20), "ACCOUNTNUMBER" VARCHAR2(20), "BILLINGDAYPREFERRED" NUMBER("
 "2, 0), "ACCOUNTEXPIRYDATE" VARCHAR2(5), "INSTITUTIONNUMBER" VARCHAR2(20), ""
 "ACCOUNTTYPE" VARCHAR2(4), "WITHDRAWALTYPE" VARCHAR2(2), "ACCOUNTDESCRIPTION"
 "" VARCHAR2(60), "ACCOUNTHOLDERNAME" VARCHAR2(150), "CHEQUESPAYABLENAME" VAR"
 "CHAR2(150), "CHEQUESPAYABLEADDRESS1" VARCHAR2(75), "CHEQUESPAYABLEADDRESS2""
 " VARCHAR2(75), "WITHDRAWALCURRENCY" VARCHAR2(8), "LASTCHANGE" DATE)  PCTFRE"
 "E 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 3162112 NEXT 524288"
 "00 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1"
 " BUFFER_POOL DEFAULT) TABLESPACE "PYDATA" LOGGING"
. . skipping table "ACCOUNTINFORMATION"               

 "CREATE INDEX "IDX4ACCOUNTINFORMATION" ON "ACCOUNTINFORMATION" ("ACCOUNTNUMB"
 "ER" , "OBJECTID" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1064"
 "960 NEXT 31457280 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50 FREELISTS 1 FR"
 "EELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PYINDEX" LOGGING"
 "CREATE UNIQUE INDEX "C2_ACCOUNTINFORMATION" ON "ACCOUNTINFORMATION" ("OBJEC"
 "TID" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1064960 NEXT 314"
 "57280 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50 FREELISTS 1 FREELIST GROUP"
 "S 1 BUFFER_POOL DEFAULT) TABLESPACE "PYINDEX" LOGGING"
 "ALTER TABLE "ACCOUNTINFORMATION" ADD  CONSTRAINT "C2_ACCOUNTINFORMATION" PR"
 "IMARY KEY ("OBJECTID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORA"
 "GE(INITIAL 1064960 NEXT 31457280 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50"
 " FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PYINDEX" EN"
 "ABLE "

...and nothing is created.