import failed
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: import failed

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    Here is a picular case of import export failure.

    One of the customers sent me oracle 8.0.5 export dmp.
    The user in his export dmp was XX and the tablespace was PRODUCTION. They did a FULL export dmp.

    I was trying to import it to here on my test machine where I have a PRODUCTION tablespace but it is used for some other purpose.

    I created a tablespace and user as follows:

    create tablespace TEST datafile 'C:\DATAFILES\TEST_userdata01.dbf' size 300M
    default storage(initial 500K next 500K);
    CREATE USER TEST identified by TEST
    DEFAULT TABLESPACE TEST
    temporary tablespace TEMPORARY1;
    GRANT DBA TO TEST;

    I did mention default tablespace for user as TEST when I created a user.

    Then on my test machine which has oracle 8.0.5 I was trying to import this file doing...at cmd prompt

    imp80 system/manager file=c:\temp\exp.dmp log=c:\temp\exp.log fromuser=XX touser=TEST ignore=Y

    It did insert rows in some tables..here is the part of log...
    . . importing table "MWEBATTRIBAMT" 0 rows imported

    . . importing table "MWEBAPPROVAL" 402 rows imported

    IMP-00058: ORACLE error 1653 encountered
    ORA-01653: unable to extend table TEST.MWEBATTRIBCOMMENT by 18 in tablespace PRODUCTION
    IMP-00028: partial import of previous table rolled back: 298 rows rolled back

    It was trying to insert this user in PRODUCTION tablespace even when I had default as TEST, why ?

    When I looked at free space in tablesapce TEST it was all free so it inserted 402 rows from the earlier table into PRODUCTION TABLESPACE which user did those rows went into ????

    I have had this problem couple of times before. But I still can't figure out why this would happen. Does this have to do with how they made a export dump. they did a full export



    Thanks
    Sonali





    Sonali

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    Revoke tablespace quota for this test user on production tablespace and then import or
    dump the import using indexfile to get the structure , edit that file and run the script and then run import with ignore=y.
    But i think first way is easier.

  3. #3
    Join Date
    Jan 2001
    Posts
    318
    I do I revoke tablespace for that user ?
    Where is the indexfile ?
    I am newbie plese help.

    thanks again
    Sonali

  4. #4
    Join Date
    Feb 2001
    Posts
    389
    1) Revoke unlimited tablespace privilege and revoke quota
    from tablespace production.
    Now import


    2) imp indexfile=

    It shall create the structure of tables and indexes in a file name mentioned.
    Edit this file , remove REM and rows....
    and run this file
    Then import with ignore=y

  5. #5
    Join Date
    Jun 2000
    Location
    Chennai/India
    Posts
    90
    Hi,
    Just log in as sys or system or internal and set the quota 0 on production tablespace as what he says and this will work.

    Regards
    Ganesh .R
    A Winner never Quits, A Quitter never Wins
    Ganesh .R

  6. #6
    Join Date
    Jan 2001
    Posts
    318
    If I revoke Revoke unlimited tablespace privilege and revoke quota
    from tablespace production.

    What all things users on PRODUCTION tablespace will not be able to do ?

    thanks
    Sonali

  7. #7
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    limiting tablespace quota will not affect the user's privs. however, if any segment needs another extent when the quota is restricted, that activity will be stopped. after the import, re-grant the quota(s). just make sure you have ample space during the restricted quota period. it shouldn't be too much of a problem unless you have bad luck.

    d.

  8. #8
    Join Date
    Jan 2001
    Posts
    318
    It still doesn't work.

    I did following before import.


    Revoke unlimited tablespace from PROD;
    THIS PROD USER IS ON PRODUCTION

    ALTER USER TEST
    QUOTA 0 M ON PRODUCTION;

    THEN I did import and I was watching storage manager it still puts stuffs in production tablespace along with test tablespace.
    How do I get this to work ?

    What all things did it put in production tablespace ?

    thanks
    sonali
    Sonali

  9. #9
    Join Date
    Jan 2001
    Posts
    318

    Unhappy

    Oh, ya, I also tried to make PRODUCTION TABLESPACE OFFLINE FOR THIS IMPORT AFTER REVOKING PRIVILEGES.

    When I tried to do import it complained giving error..
    Cannot assign objects to tablespace PRODUCTION and my import failed.

    So looks like it still tries to put something in production tablespace.

    thanks for all you help

    Sonali
    Sonali

  10. #10
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    if you are using << fromuser=XX touser=TEST>>, you don't need to do anything to user PROD. be sure that TEST doesn't have unlimited ts: Revoke unlimited tablespace from TEST;

    the only user you have to fiddle with is TEST, as this is the affected user. check that out.

    d.

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