DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2001
    Here is what has happened -

    Server 1- JUPITER
    Tablespace - KERMIT, PRODUCTION etc
    Users - Only one user TEST on KERMIT tablespace with DBA priviledge. Tablespace PRODUCTION is
    used for some other users but not TEST

    Server 2- EARTH
    Tablespace - PRODUCTION, CYCLONE etc
    Users - User UPPROD on PRODUCTION tablespace with DBA priviledge

    So when the export was done from Server 2- EARTH, Tablespace - PRODUCTION, User - UPPROD
    and then was imported to Server 1- JUPITER, User - TEST with DEFAULT Tablespace - KERMIT,
    Oracle started to insert something in Tablespace - PRODUCTION on Server 1- JUPITER for user TEST
    even though the default tablespace for user TEST was KERMIT and I had used fromuser touser in import
    and also it had enough space in it for the import.
    After the import was done I saw tablespace PRODUCTION being used instead of KERMIT as KERMIT was empty.
    I even tried taking out the DBA priviledge but nothing seems to work.
    So I imported user TEST with show = y and editing the INDEX file for tablespace ( changing it from PRODUCTION
    to KERMIT) and then doing the import.
    This worked for me. This time it did use KERMIT tablespace and not PRODUCTION on server1.
    Similar thing happened with some other users with tablespace PRODUCTION being imported to server1 to some other tablespaces.

    My questions are :
    1. Why would this happen, what can I do other than creating an INDEX file everytime this happens and PRODUCTION
    tablespace getting filled up with something.
    2. Is there any query or way to know what all users have used PRODUCTION tablespace on server1 by mistake ?
    In other words to see whats in that PRODUCTION tablespace ?


  2. #2
    Join Date
    Nov 2000
    1. That's the way I would do it, with INDEXFILE.

    2. select owner, segment_name, tablespace_name
    from dba_segments
    where tablespace_name = 'PRODUCTION'
    order by owner


    select owner, tablespace_name, segment_type, count(*)
    from dba_segments
    group by owner, tablespace_name, segment_type
    order by tablespace_name, owner
    Jeff Hunter
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Apr 2001
    Louisville KY
    Depending on the release of Oracle, prior to import you can revoke all privileges on Production tablespace from the test id. Make sure KERMIT is the default tablespace for the test id. Then when the import fails to create the table in Production it will fall back to Kermit for it's creates.

    At least I have seen this solution posted elsewhere.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  4. #4
    Join Date
    Jan 2001
    Thanks marist, I will try to see whats in that tablespace now.
    Jrpm- I am using Oracle 8.0.6. I also have Oracle 8.1.7 but I think the same thing happens with these 2 releases.
    I have tried revoking all the privileges for TEST user on production like I said before that did not work either.


  5. #5
    Join Date
    Nov 2001


    Hi, Sonali.

    Why donīt you try setting the PRODUCTION tablespace offline and repeat all the steps? Maybe an error will occur and it's gonna be easier to understand why that happens.

    Best regards,

    Luis Nobre, from Brazil.

  6. #6
    Join Date
    Nov 2001
    Central U.S.
    The TEST user is granted DBA privileges, which, I believe, also grants RESOURCE. Revoke RESOURCE from TEST, then grant CREATE SESSION (since CREATE SESSION is part of the RESOURCE privileges) to TEST. THEN try your import again after revoking all privileges to PRODUCTION. You'll find, I believe, that the import will finally use the KERMIT tablespace to store the objects imported into TEST.
    David D. Fitzjarrell
    Oracle Certified DBA

  7. #7
    Join Date
    Dec 2000
    Ljubljana, Slovenia
    I belive the above post deserve some correction as almost every advice above is wrong (and please, take this intervention in a positive spirit, my intentions here are nothing but good).

    Originally posted by oratune
    The TEST user is granted DBA privileges, which, I believe, also grants RESOURCE.
    DBA role has some other roles granted, but RESOURCE is not among them. Of course it has all the RESOURCE privileges, but they have been granted to DBA explicitely, not through RESOURCE role.

    Originally posted by oratune
    Revoke RESOURCE from TEST, then grant CREATE SESSION (since CREATE SESSION is part of the RESOURCE privileges) to TEST.
    A couple of incorrectnes in this statement. CREATE SESSION is not a part of RESORCE role. Even if it was, there will be no point in granting explicit CREATE SESSION after revoking RESORCE, because if iser test has DBA role then he has CREATE SESSION allreday granted through DBA role. And in the context of the original problem there is no point in revoking any ROLE (even DBA) from that user, as roles have nothing to do with the problem.

    Now back to the original problem/question: "Why import utility insist in creating segments in the same tablespace (actually in the tablespace with the same name) as they were when they were exporting, even though the destination user has some other tablespace designated as the default tablespace? Only if there is no tablespace with the same name the segments are created in user's default tablespace."

    It is funny, how many times this problem has been discussed in this forum (if I'm not mistaken the same question has allready been answered to sonaliak some time ago), yet there is still so much confusion about that. There are a couple of ways how to got around this problem (hacking the dmp file; creating imp indexfile and manually changing the tablespace in CREATE statements; manualy creating segments; etc...), but if you simply want to do it using imp utility, you only have to make sure one thing: DESTINATION USER MUST HAVE NO QUOTA IN THE "ORIGINAL" TABLESPACE WHEN IMPORTING IS PERFORMED. And of course, one additional requirement - it must have sufficient quota on his default tablespace for imp to create all the segments there.

    Basicaly the root of the confusion is in one tiny system privilege: UNLIMITED TABLESPACE. This privilege is not part of any role (unles some wise DBA has manualy granted it to some role), but Oracle "silently" grant it as an explict privilege to any user that has been given DBA or RESOURCE role. Irronicaly enough, even if you later revoke any/both of those roles from that user, Oracle does not implicitely revoke this privilege from that user in the same "silent" way. As a result of this, many DBAs don't even know that most of their users have *unlimited* quota on all tablespaces in the database!

    So once again the instrunctions for sonaliak's case:

    1. Revoke UNLIMITED TABLESPACE system privilege from user TEST:

    2. Just in case user TEST has any explicit quota set on tablespace PRODUCTION, set it to 0:
    ALTER USER test QUOTA 0 ON production;

    3. Make sure TEST has sufficient quota on his default tablespace KERMIT - if needed raise him that quota or even set it to UNLIMITED:

    Now perform your import and *all* of the imported objects will be created in tablespace KERMIT.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.