Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2004
    Posts
    60

    Unanswered: Checking if Session Is Alive

    Hi

    We have a requirement where in need to populate a table with unique_Session_ID using dbms_session.unique_Session_id.

    at a Later stage, We need to delete records from the table if the session is not active.


    Code:
    DELETE FROM session_table
    WHERE NOT dbms_session.is_session_alive(funique_session_id);
    
    
    Error : 
    
    ORA-00920: invalid relational operator
    00920. 00000 -  "invalid relational operator"
    *Cause:    
    *Action:
    Error at Line: 105 Column: 59
    Note : funique_session_id is the column in session table where the session id is stored using dbms_session.unique_Session_id.
    Last edited by edwin_fredrick; 07-05-12 at 01:53.

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    The problem would appear to be your use of 'NOT'

    Oracle/PLSQL: ORA-00920 Error
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Oct 2004
    Posts
    60
    Thanks for your reply.

    Yes, I am aware dbms_session.is_session_alive gives us a Boolean, I am trying different ways and still not able to make it work.

    Code:
    SELECT * FROM prdc_session_table
    WHERE bool_to_char(dbms_session.is_session_alive(funique_session_id)) = 'N';
    
    
    ORA-06552: PL/SQL: Statement ignored
    ORA-06553: PLS-382: expression is of wrong type
    06552. 00000 -  "PL/SQL: %s"
    Last edited by edwin_fredrick; 07-05-12 at 07:35.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Oct 2004
    Posts
    60
    We now have a small stored procedure to read each record and delete if the session is inactive.

    Thanks all for your valuable time.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    How did you fix it. did you call
    dbms_session.is_session_alive(funique_session_id) within the stored function?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Oct 2004
    Posts
    60
    Quote Originally Posted by beilstwh View Post
    How did you fix it. did you call
    dbms_session.is_session_alive(funique_session_id) within the stored function?
    Code:
    create or replace
    PROCEDURE DELETE_DEAD_SESSION_RECORD   AS 
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
      FOR rec IN (SELECT * FROM session_table)
      LOOP
        IF dbms_session.is_session_alive(rec.funique_session_id) THEN
           dbms_output.put_line('Active :  ' || rec.funique_session_id  );
        ELSE 
            DELETE FROM session_table WHERE funique_session_id = rec.funique_session_id AND fversion_id = rec.fversion_id;
            dbms_output.put_line('Inactive : Deleted  ' || rec.funique_session_id  );
        end if;
      END LOOP;
      COMMIT;
      
    END;

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Just curious, why not maintain your session table by using logon and logoff triggers?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Oct 2004
    Posts
    60
    Quote Originally Posted by beilstwh View Post
    Just curious, why not maintain your session table by using logon and logoff triggers?
    Basically, We are trying out Table versioning ...so at any give point of time, only one session should be editing a particular version, so we have a stored procedure to put the version in edit mode and a record is created in session table and once the editing (insert/delete/update) is done another stored procedure is excuted to remove the record from the session table. Till the second stored procedure is executed, no other session can edit the version.

    Since there might be changes of session getting killed before the 2nd stored procedure is executed, the version will be locked for ever not allowing other session to edit the locked version. So we though of having a procedure to clean up such rows from the session table.
    Last edited by edwin_fredrick; 07-06-12 at 01:35.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Even if you kill a session, the logoff trigger is fired. As for locking a table during update, simply use the LOCK TABLE command. Oracle takes care of everything and the lock stays until the session exits or a commit or rollback is issued.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Basically, We are trying out Table versioning ...
    I contend that you are trying to implement a technical solution for a problem that does not exist
    or can more easily be solved procedurally.

    DDL against Production DB should only occur rarely & during maintenance window using tested SQL.
    No two folks should ever be change the same object during application version upgrade.

    If this occurs in development, then this is why Source Code Control like Subversion exists.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •