Cant figure out what import is doing...wrong

[shandj]

One solution for your problem is

1. Open the export (dump) file using any textpad (if it too big don't use notepad). You can download textpad from net also.
2. Replace the string "TABLESPACE USERS" with your string "TABLESPACE yourtablespace".
3. Save the file
4. Now import the dump file into your database.

Note: If you are having database on Unix box, ftp it to NT box through Binary mode. Change the dump file and copy back to Unix box and import it.

By doing this way, your objects will import into your specified tablespace without any problem.

Thanks,

[anandkl]

Grant import_full_Database privilege to the new user and try to import using this user.

regards
anandkl

[Edited by anandkl on 10-01-2002 at 06:56 AM]

[nagarjuna]

Originally posted by shandj
One solution for your problem is

1. Open the export (dump) file using any textpad (if it too big don't use notepad). You can download textpad from net also.
2. Replace the string "TABLESPACE USERS" with your string "TABLESPACE yourtablespace".
3. Save the file
4. Now import the dump file into your database.

Note: If you are having database on Unix box, ftp it to NT box through Binary mode. Change the dump file and copy back to Unix box and import it.

By doing this way, your objects will import into your specified tablespace without any problem.

Thanks,

Noway you can import the data using this method. I have used such method (opening the dump using wordpad and editing) to import the structure. But, it does not work with complete data(rows=y).

[coubs]

I can maunally add the keys after the import but that is not the long term solution. I need to know why it is overriding the users defaults and trying to put objects in USERS. And ,like I said before the output from show = y shows me this:

"CREATE TABLE "ACTIVITY" ("ACTIVITY_ID" NUMBER(10, 0) NOT NULL ENABLE, "CROP"
"_ID" NUMBER(10, 0) NOT NULL ENABLE, "ACTIVITY_CLASS" VARCHAR2(20), "ACTIVIT"
"Y_TYPE" VARCHAR2(20), "PLANNED_EARLY_START_DATE" DATE, "PLANNED_LATEST_END_"
"DATE" DATE, "START_DATE" DATE, "END_DATE" DATE, "START_DESC" VARCHAR2(20), "
""ACTIVITY_STATUS" VARCHAR2(30), "TECHNOLOGY" VARCHAR2(15), "COMMENTS" VARCH"
"AR2(2000), "AREA" NUMBER(15, 5), "AIR_TEMPERATURE" NUMBER(15, 5), "AIR_TEMP"
"ERATURE_UNIT" VARCHAR2(20), "WIND_VELOCITY" NUMBER(15, 5), "WIND_VELOCITY_U"
"NIT" VARCHAR2(20), "WIND_DIRECTION" VARCHAR2(10), "TIME_OF_DAY" VARCHAR2(30"
"), "DELETED" VARCHAR2(3), "PRODUCT_PRICE" NUMBER(15, 5), "SERVICE_PRICE" NU"
"MBER(15, 5), "DELIVERY_CHARGE" VARCHAR2(3), "PRODUCT_PURCHASED" VARCHAR2(3)"
", "SERVICE_PURCHASED" VARCHAR2(3), "START_TIME" VARCHAR2(10), "END_TIME" VA"
"RCHAR2(10), "SOIL_TEMPERATURE" NUMBER(15, 5), "CUSTOM_APPLICATION" VARCHAR2"
"(3), "CANCEL_REASON" VARCHAR2(255), "ALERT" VARCHAR2(3), "PRIORITY_ID" NUMB"
"ER(10, 0), "GROWTH_STAGE_ID" NUMBER(10, 0), "REQUESTOR_RESOURCE_ID" NUMBER("
"10, 0), "PARENT_ACTIVITY_ID" NUMBER(10, 0), "RESOURCED" VARCHAR2(3), "SEQUE"
"NCE_NUMBER" NUMBER(10, 0), "PERCENT_COMPLETE" NUMBER(15, 5), "CUSTOM_DATA_E"
"NTERED" VARCHAR2(3), "PORTION_RESOURCED" NUMBER(15, 5)) PCTFREE 10 PCTUSED"
" 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 131072 NEXT 131072 MINE"
"XTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER"
"_POOL DEFAULT) TABLESPACE "USERS""
. . skipping table "ACTIVITY"

"CREATE UNIQUE INDEX "PK_ACTIVITY" ON "ACTIVITY" ("ACTIVITY_ID" ) PCTFREE 1"
"0 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 M"
"AXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFA"
"ULT) TABLESPACE "USERS" LOGGING"
"ALTER TABLE "ACTIVITY" ADD CONSTRAINT "PK_ACTIVITY" PRIMARY KEY ("ACTIVITY"
"_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072"
" NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIS"
"T GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE "

Note the USERS table space in the create table, index and primary key. BUT when I do a select * from tabs as this user it says that the ACTIVITY table resides on the woodlands_data tablespace and not the USERS table space???

[nagarjuna]

Originally posted by coubs


Note the USERS table space in the create table, index and primary key. BUT when I do a select * from tabs as this user it says that the ACTIVITY table resides on the woodlands_data tablespace and not the USERS table space???

mmmm, how can you see the tablespace name from "tab" synonym???

and, now I doubt... what is your exact problem?? can you post/explain it again???

[philmer]

One thing to remember when doing an import. If you do a SHOW=Y one can obtain the DDL statement necessary to the objects without data in the preferred tablespace. Then using the IGNORE=Y the import will detect the object exists and just bring in the data. Once the inserts have completed the import will proceed to build the constraints or indexes. If you have trouble with indexes and constraints then export using INDEXES=N and CONSTRAINTS=N, also be advised if the export is done with COMPRESS=Y the import will try to place the data in one initial extent upon load (in some cases this can cause problems). Hope this helps.

[philmer]

From reviewing all the threads this problem is escalating farther than necessary. Export/Imports are powerful tools that are not the complex beast many DBAs envision them to be, so with that said ---
1) If you drop and recreate the schema prior to importing you run the risk of object dependency errors (rare but possible). A possible solution is to build a drop table cascade script.

select 'drop table '||owner||'.'||table_name||' cascade constraints;'
from dba_tables
where owner = 'USERNAME';

2) Export just the DDL. This is done using the ROWS=N option.

3) Export with the following options: CONSTRAINTS=N, INDEXES=N, ROWS=Y

4) Import with SHOW=Y to obtain object creation DDL. Then precreate tables in thier appropriate tablespaces.

5) Import with IGNORE=Y, ROWS=Y and INDEXES=N. Promise no errors, unless export is corrupt. Specify the USERID or FROMUSER, TOUSER parameters. FULL=Y generally causes errors from the SYSTEM user.

6) Import the DDL export using IGNORE=Y. This will put the indexes and constraints, as well as the other necessary objects, in place.

[philmer]

P.S. Step 3 in the previous reply is a separate export from the DDL export.

[coubs]

Thanks so much everyone...

I do know how to get the import in..like the manual steps in the last post.

But I can't for the life of me figue out WHY it is doing this...

[patnams]

I'm on 8.1.7.2. I've noticed this problem too. But when I abort the import, dropped the schema and tried the import again, it worked fine. I always use the option Import_Full_File = 'Y'.

Sridhar