Cant figure out what import is doing...wrong - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 3 FirstFirst 123
Results 21 to 30 of 30

Thread: Cant figure out what import is doing...wrong

  1. #21
    Join Date
    Feb 2002
    Posts
    70
    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,
    ________________
    ShanDJ

  2. #22
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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]
    anandkl

  3. #23
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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).
    -nagarjuna

  4. #24
    Join Date
    Sep 2002
    Posts
    13
    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???

  5. #25
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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???
    -nagarjuna

  6. #26
    Join Date
    Nov 2001
    Posts
    11
    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.
    Learning something new everyday
    Forgetting something useful every minute!

  7. #27
    Join Date
    Nov 2001
    Posts
    11
    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.


    Learning something new everyday
    Forgetting something useful every minute!

  8. #28
    Join Date
    Nov 2001
    Posts
    11
    P.S. Step 3 in the previous reply is a separate export from the DDL export.
    Learning something new everyday
    Forgetting something useful every minute!

  9. #29
    Join Date
    Sep 2002
    Posts
    13
    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...

  10. #30
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    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
    Sridhar R Patnam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width