I got a question regarding Metalink note 296235.1 about a describe bug which causes objects to “disappear” when they are described when database is not open.
It was an interesting case involving a bug, so I wrote a quite long analysis with test cases today. However when posting the entry to wordpress, it managed to completely mess up the formatting. After wasting half an hour trying to get the formatting correct I gave up and saved the article into a PDF instead.
You can access it here: http://www.tanelpoder.com/files/oracle_rowcache.pdf
By the way, I welcome more questions about Oracle internals or performance tuning.
Often when starting to explain something, I realize that I don’t really know the answer well enough, which is a valuable experience as it makes me figure it out and learn!
So, if you have a question, write it into a comment here or drop me an email to tanel@tanelpoder.com
Very good read. Thanks for all the effort.
Thanks Tanel !!!
Thanks Marco & special thanks to Alessandro for asking this interesting question :)
Wow, great explanation Tanel. Now, lets explain why running “alter/**/session/**/set/**/nls_date_format=’dd/mm/yyyy hh24:mi:ss’” segfaults on me :-) Did you notice that you can run most commands without using whitespaces at all thus defeating a lot of security tools using pattern matching?
Slavik
Tanel,
very interesting, thanx.
there is another bug: When you describe dual when database mounted, you won’t be able to open it afterwards !
SQL> startup mount
ORACLE instance started.
Total System Global Area 922746880 bytes
Fixed Size 1222624 bytes
Variable Size 146802720 bytes
Database Buffers 767557632 bytes
Redo Buffers 7163904 bytes
Database mounted.
SQL> describe dual
ERROR:
ORA-04043: object dual does not exist
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
It’s definitely the
describe dual
which causes the ORA-01092
Could you shed some light on this ?
Hi Slavik! This is a good one. I took a quick look and it looks sqlplus crashes when receiving the reply from the server, not during sending it. This makes the case even more interesting :) I think i need to write another blog entry ;)
Btw, I once blogged about writing SQL without whitespace, including
the /**/ approach..
http://blog.tanelpoder.com/2008/01/14/can-you-write-a-working-sql-statement-without-using-any-whitespace/
…yep it may break a lot of bad apps ;)
Sokrates, good finding :)
This crash seems to be because the database is opened by your current server process, which can not access DUAL table due the describe bug again. But there are some SQL statements which select from DUAL when opening the db, so if some of them unexpectedly fail, the server process has no other choice than abort he instance.
Here’s a tail piece of my alert.log after this DUAL crash happened:
Errors in file /u01/app/oracle/admin/LIN10G/udump/lin10g1_ora_3193.trc:
ORA-00942: table or view does not exist
Thu Aug 21 08:37:30 2008
Error 942 happened during db open, shutting down database
USER: terminating instance due to error 942
Instance terminated by USER, pid = 3193
ORA-1092 signalled during: alter database open…
Tanel,
Could you shed some light on when a session might get an “ORA-08103: object no longer exists” error when selecting from a table that is truncated by another session?
I have blogged (http://freekdhooge.wordpress.com/2007/12/25/can-a-select-block-a-truncate/) about this in the past and I understand that it is the increase of the dataobj# column in obj$ that causes this, but I have not been able to discover when a session get this error.
regards,
Freek
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2144824 bytes
Variable Size 922748360 bytes
Database Buffers 671088640 bytes
Redo Buffers 7430144 bytes
Database mounted.
SQL> desc dual;
ERROR:
ORA-04043: object dual does not exist
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
Process ID: 17765
Session ID: 170 Serial number: 5
let’s try again:
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2144824 bytes
Variable Size 922748360 bytes
Database Buffers 671088640 bytes
Redo Buffers 7430144 bytes
Database mounted.
SQL> desc dual
ERROR:
ORA-04043: object dual does not exist
now execute
SQL> alter system flush shared_pool;
System altered.
SQL> alter database open;
Database altered.
SQL>
Yes, this is because a shared pool flush flushes out also the “non-existent markers” from rowcache (I also demoed this in my paper).
Another way is to try to create the “lost” table again, this creation will fail due a primary key violation on data dictionary tables, the recursive data dictionary transaction will be rolled back and corresponding rowcache entries invalidated. Flush shared pool is safer though.