Database Solutions

UNDO MANAGEMENT ------------------------------------------------------------------------------------------ Undo The Undo tablespace is used for several features: ROLLBACK, READ CONSISTENCY and FLASHBACK technology. Rollback Rollback is easy to understand, if you are not happy with some data modifications, you want to ‘undo’ it: Rollback. The original (non modified) information within a transaction is stored in a separate Undo tablespace, because the database is designed for COMMIT to be fast, not rolling back. Read Consistency Another mechanism Undo information is used for is Read Consistency, which means if you run a query at 9:00 for 10 minutes, you want all the data to be from 9:00. You don’t want it to read data that has been modified at 9:02 and 9:06 or data that hasn’t been committed yet. So, to support Read Consistency, Oracle must keep the original data (committed or not) for these 10 minutes until the query is finished. The problem is, you actually don’t k...

Troubleshooting Open Cursor Issues - ORA-01000: maximum open cursors exceeded Find out the session that is causing the error by using the following SQL statement: select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null; The output displays the details of all sessions. You can see the maxed out session IDs. To display which queries are causing maxing out of open cursors, run the following SQL statement: set lines 200 col USER_NAME FOR A40 select  sid ,USER_NAME,sql_text, count(*) as "OPEN CURSORS" from v$open_cursor where sid in (&SID) group by sid ,USER_NAME,sql_text ; set lines 200 col USER_NAME FOR A40 select  sid ,USER_NAME,sql_text,sql_id, count(*) as "OPEN CURSORS" from v$open_cursor where sid in (&SID) group by sid ,USER_NAME,sql_text,sql_id ; The top queries that are opening maximum cursors and ar...