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

Thread: drop user cascade

  1. #1
    Join Date
    Jan 2005
    Posts
    7

    drop user cascade

    i have a small db (about 2gb) , i need to refresh it and make "drop user cippalippa cascade;" but the drop still running after 4 hours..
    i see in trace that the session makes :
    SELECT file#, block#, LENGTH
    FROM uet$
    WHERE ts# = :1 AND segfile# = :2 AND segblock# = :3 AND ext# = :4

    and

    DELETE FROM uet$
    WHERE ts# = :1 AND segfile# = :2 AND segblock# = :3 AND ext# = :4

    tablespace usage (data and idex) going down.. but the schema is about 20tables and some procedure..
    why it takes so long?
    the server is a sun solaris 8 whith 1 cpu and 1gbram (i know that it's poor..) , top says that server is 0 idle and ram utlization is full (swamping)..

    P.S. oracle is 9.2.0.6 versione 64bit

    thanks
    marco

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    shame on you for not using LMt's,

    Most liekly it is deallocating extents. There is a chance it has got stuck though

  3. #3
    Join Date
    Jan 2005
    Posts
    7
    thanks for explanation, but i don't know what LMt's(or i don't know what's the italian for..) is ..

    thanks
    marco

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    Locally Managed Tablespaces.

    If you had them you would find no queries against uet$ / fet$. Thats what takes most of the time, updating those two system tables and with LMT's it isn't needed

  5. #5
    Join Date
    Jan 2005
    Posts
    7
    ok thanks for explanation.
    For reason that i don't know this db is dictionary manage.(it still exist before i come here).


    thanks
    marco

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    select tablespace_name, EXTENT_MANAGEMENT from dba_tablespaces;


    if they say dictionary you really want to look to migrating them to locally managed ones

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    But since you are dropping the schema, now would be a great time to use LMT's. If the tablespaces are empty, just drop them and create LMT tablespaces.
    Last edited by gandolf989; 01-31-2005 at 01:43 PM.

  8. #8
    Join Date
    Jan 2005
    Posts
    7
    just do it!

  9. #9
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    If your RAM utilisation is "swamping", ie paging, then you might want to look at the size of your SGA. Is it too big, therefore leaving little free memory for processing?

    You might want to look at pga_aggregate_target to limit the amount of allocated process memory.

    If your system is paging, your database performance is doomed. Dooooomed I say!!

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