DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: drop user

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    drop user

    what is the easiest way to drop database user with many tables worth 40 GB tables and 50 GB indexes .

    Thanks .

  2. #2
    Join Date
    Jan 2001
    Posts
    3,134
    SQL> Drop user slimdave cascade;
    I remember when this place was cool.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by Mr.Hanky
    SQL> Drop user slimdave cascade;
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    this is taking lot fo time may be several hours ..
    also it gave message cannot allocate memory in sharedpool ...

    can i do nologging ... something like that .

  5. #5
    Join Date
    Oct 2002
    Posts
    807
    LMT or DMT? If DMT - you better phase it out. Drop individual objects separately during offhours. Eventually drop user. Else your smon will go crazy like you're probably seeing anyway. If LMT, the drop shouldn't take long anyway.

  6. #6
    Join Date
    May 2001
    Posts
    73
    Truncate all the tables and drop the user.
    OCP 7.3,8.0,8i,9i

  7. #7
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    It is LMT but still doing since last 2 hours , ofcourse i could see freespace from dba_freespace releasing slowly ...

    but this is a time taking ...

  8. #8
    Join Date
    Oct 2002
    Posts
    807
    From metalink -

    "You'll only see 'issues' with dropping/truncate etc on massive extent numbers (in an LMT) if you are using tablespace quotas (because the quotas are still managed via a standard dictionary table).

    In 9.2 the quota management is still there but a little smarter - if you drop 1,000 extents, there is only a single update to the tsq$ table not 1000"

  9. #9
    Join Date
    Jul 2000
    Posts
    521
    Drop objects owned by this user throuhg multiple SQL*Plus sessions that run in parallel and then drop the user.

    Oracle has to do the work of cleaning up objects worth @ 100GB. If you do it in parallel, it will feel faster.
    svk

  10. #10
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    Thanks all ,

    i did same thing ... Now i am done.

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