temp table query issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: temp table query issue

  1. #1
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110

    temp table query issue

    I have some temp tables. Somtimes I need to modify it to add a column.
    But usually this table has a session open on it and would give below error. I resolve it by just killing sessions. But I would like to find just that session or sessions that are the culprits rather than blindly killing all the sessions.

    -Question - How i do find the session/s that has the temp table open now?

    Thanks

    ERROR at line 1:
    ORA-14450: attempt to access a transactional temp table already in use
    Looking for the greatest evil in the world? Look in the mirror.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Tuma View Post
    I have some temp tables. Somtimes I need to modify it to add a column.
    But usually this table has a session open on it and would give below error. I resolve it by just killing sessions. But I would like to find just that session or sessions that are the culprits rather than blindly killing all the sessions.

    -Question - How i do find the session/s that has the temp table open now?

    Thanks

    ERROR at line 1:
    ORA-14450: attempt to access a transactional temp table already in use
    This one may come handy...
    Code:
    SELECT 
           o.object_name
         , s.sid, s.serial#
         , s.username
         , s.osuser, s.machine 
         , 'alter system kill session '''||to_char(s.sid)||','||to_char(s.serial#)||''';' ks  
    FROM 
           user_objects o
         , v$lock a
         , v$session s  
    WHERE 
         o.object_name = 'table_name_here'     
    AND  a.id1 = o.object_id    
    AND  a.type = 'TO'    
    AND  a.sid = s.sid
    ;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110
    Thanks....i'll try that....
    Looking for the greatest evil in the world? Look in the mirror.

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