6 Replies Latest reply: Feb 26, 2013 10:40 AM by ursusca RSS

    ORA-28 in alert.log file

    Ratnesh Sharma
      please help.. getting ORA-28 in alert.log file.

      Mon Feb 25 17:46:05 2013
      ORA-28 signalled during: ALTER TABLESPACE DATA_APP_199903 READ ONLY...
      Mon Feb 25 17:48:54 2013
      ALTER TABLESPACE DATA_APP_199903 READ ONLY

      please let me know if you need any other info to help me.
        • 1. Re: ORA-28 in alert.log file
          sb92075
          Ratnesh Sharma wrote:
          please help.. getting ORA-28 in alert.log file.

          Mon Feb 25 17:46:05 2013
          ORA-28 signalled during: ALTER TABLESPACE DATA_APP_199903 READ ONLY...
          Mon Feb 25 17:48:54 2013
          ALTER TABLESPACE DATA_APP_199903 READ ONLY

          please let me know if you need any other info to help me.
          [oracle@localhost ~]$ oerr ora 28
          00028, 00000, "your session has been killed"
          // *Cause:  A privileged user has killed your session and you are no longer
          //          logged on to the database.
          // *Action: Login again if you wish to continue working.
          [oracle@localhost ~]$ 
          I suspect a trigger is "guarding" this tablespace
          • 2. Re: ORA-28 in alert.log file
            Ratnesh Sharma
            thanks. but what kind of trigger that could be and how could I find that.
            this is not normal ora-00028 but ORA-28 which I am getting in the alert.log file.
            the session is still active and wait is increasing for event "enq: TX - contention"
            • 3. Re: ORA-28 in alert.log file
              ursusca
              It looks like that the session attempting to make the tablespace read-only must wait until active DML transactions in the database have completed. If you can try to shutdown the database and startup it in restricted mode to ensure no active DML transactions are there in the whole database by any user. Then, try to make the tablespace in read-only again.
              • 4. Re: ORA-28 in alert.log file
                Ratnesh Sharma
                There is not any active transactions.
                "select * from dba_dml_locks" doesnt return any rows.
                Would you still recommend DB restart ?
                • 5. Re: ORA-28 in alert.log file
                  Girish Sharma
                  Error: ORA 28
                  Text: your session has been killed
                  -------------------------------------------------------------------------------
                  Cause: A privileged user killed the session and it is no longer logged in to the database.
                  Action: Contact the database administrator.
                  The administrator may be attempting to perform an operation that requires users to be logged out.
                  When the database administrator announces that the database is available, log in and resume work.
                  So, its a message only not exactly an error. It happens when you or other DBA said alter tablespace tbs read only; and at the same time privileged user killed your session. So, nothing to worry/to do, just relogin and resume work.

                  Regards
                  Girish Sharma
                  • 6. Re: ORA-28 in alert.log file
                    ursusca
                    Try to find currently waiting process:
                    select s.sid, s.serial#, s.username, s.state,
                    s.wait_class, s.event, t.sql_text
                    from v$session s, v$sql t
                    where sid in (select sid
                    from v$session
                    where s.state in ('WAITING')
                    and event like '%enq: TX%'
                    and (t.sql_id = s.sql_id
                    or t.sql_id = s.prev_sql_id));