http://oracletoday.blogspot.com/This blog is about cases I see in my day-to-day Oracle DBA job. Mostly anything related to Oracle RDBMS will be the subject.ennoreply@blogger.com (yas)Wed, 11 Nov 2009 18:46:14 PSTBlogger http://www.blogger.com56125This is an XML content feed. It is intended to be viewed in a newsreader or syndicated to another site, subject to copyright and fair use.http://feedproxy.google.com/~r/OracleToday/~3/x4kwwkVSlks/opatch-problem-on-windows.htmlnoreply@blogger.com (yas)Fri, 19 Jun 2009 06:30:38 PDTtag:blogger.com,1999:blog-15574215.post-2484166438532664876There is a note in Metalink that explains that on Windows having space characters in your ORACLE_HOME variable, the patch location or JDK location causes an error when running opatch. Yesterday I saw a strange problem that is similar to the above case.If your opatch directory contains space characters you get a strange error. Even if the above conditions were not present we got an error like this2009-06-19T16:30:38.230+03:00http://oracletoday.blogspot.com/2009/06/opatch-problem-on-windows.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/_iMTyHPP1Y8/dbfs.htmlnoreply@blogger.com (yas)Fri, 19 Jun 2009 03:32:02 PDTtag:blogger.com,1999:blog-15574215.post-9093342669721345584Yesterday I attended Kevin Closson's Exadata technical deep dive webcast series part 4. It is now available to download here. In there he talks about DBFS which is a filesystem on top of the Oracle database which can store normal files like text files. DBFS is provided with Exadata and is used to store staging files for the ETL/ELT process. This looks very promising, he sites several tests he 2009-06-19T13:32:02.954+03:00http://oracletoday.blogspot.com/2009/06/dbfs.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/m8UytOGz1mw/tablespace-selection-in-interval.htmlnoreply@blogger.com (yas)Mon, 08 Jun 2009 23:58:48 PDTtag:blogger.com,1999:blog-15574215.post-91891799079772828811G brought interval partitioning which is a new partitioning method to ease the maintenance burden of adding new partitions manually. The interval partition clause in the create table statement has an option to list tablespace names to be used for interval partitioning. The documentation states that the tablespaces in the list you provide are used in a round-robin manner for new partitions:2009-06-09T09:58:48.199+03:00http://oracletoday.blogspot.com/2009/06/tablespace-selection-in-interval.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/DznWoEwHQ5Y/different-plans-for-sql-with-rule-hint.htmlnoreply@blogger.com (yas)Wed, 04 Mar 2009 04:08:52 PSTtag:blogger.com,1999:blog-15574215.post-8085494524433390083I was trying to find out why a query with the RULE hint produces different plans. I got stuck so I posted the problem to the OTN database forum and Randolf Geist provided a good answer and starting point for it. A second eye on the problem can remove the mind blockage and clear your way.2009-03-04T14:08:52.089+02:00http://oracletoday.blogspot.com/2009/03/different-plans-for-sql-with-rule-hint.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/ODiGh2gxnbE/remote-dependencies.htmlnoreply@blogger.com (yas)Mon, 23 Feb 2009 06:58:15 PSTtag:blogger.com,1999:blog-15574215.post-3962593140818653487When changing plsql code in a production system the dependencies between objects can cause some programs to be invalidated. Making sure all programs are valid before opening the system to users is very important for application availability. Oracle automatically compiles invalid programs on their first execution, but a successfull compilation may not be possible because the error may need code 2009-02-23T16:58:15.568+02:00http://oracletoday.blogspot.com/2009/02/remote-dependencies.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/WmLk4xGlAsc/index-block-split-bug-in-9i.htmlnoreply@blogger.com (yas)Wed, 06 Aug 2008 00:18:32 PDTtag:blogger.com,1999:blog-15574215.post-1600434712897461388In his famous index internals presentation Richard Foote mentions a bug in 9i about index block splits when rows are inserted in the order of the index columns. Depending on when you commit your inserts the index size changes dramatically.While I was trying to find out why a 3-column primary key index takes more space than its table I recalled that bug and it turned out that was the reason of the2008-08-06T10:18:32.172+03:00http://oracletoday.blogspot.com/2008/08/index-block-split-bug-in-9i.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/mUVKCouKg-I/otn-members-dont-change-your-e-mail.htmlnoreply@blogger.com (yas)Fri, 01 Aug 2008 01:44:36 PDTtag:blogger.com,1999:blog-15574215.post-445149049972074108I am regular user of OTN and its forums. Last week I was trying to login to OTN from a public computer and I got the "invalid login" error everytime I tried. I was sure I was typing my password correct but I could not get in anyway. So, I tried to get my password reset and sent to my e-mail address. Then I remembered that the e-mail address I used to register for OTN was from my previous employer2008-08-01T11:44:36.598+03:00http://oracletoday.blogspot.com/2008/08/otn-members-dont-change-your-e-mail.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/inVc-eLE8LE/materialized-view-refresh-change-in-10g.htmlnoreply@blogger.com (yas)Thu, 17 Apr 2008 01:33:50 PDTtag:blogger.com,1999:blog-15574215.post-3387298164914338357Complete refresh of a single materialized view used to do a truncate and insert on the mview table until 10G. Starting with 10G the refresh does a delete and insert on the mview table. This guarantees that the table is never empty in case of an error, the refresh process became an atomic operation.There is another difference between 9.2 and 10G in the refresh process, which I have realized when 2008-04-17T11:33:50.810+03:00http://oracletoday.blogspot.com/2008/04/materialized-view-refresh-change-in-10g.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/7wx0qItx_0w/disqus.htmlnoreply@blogger.com (yas)Fri, 11 Apr 2008 10:26:03 PDTtag:blogger.com,1999:blog-15574215.post-1614736685947846268Last week I read a post in Andy C's blog about the service called Disqus. It is a service to keep track of comments in blogs. Later he made another post about it.I have been looking for a solution to keep track of blog comments, both mine and other people's. Not all blogs have the option to subscribe to the comments, when you comment on a post you need to check later if someone commented further.2008-04-11T20:26:03.627+03:00http://oracletoday.blogspot.com/2008/04/disqus.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/1foMurECmmc/collect-in-10g.htmlnoreply@blogger.com (yas)Fri, 11 Apr 2008 02:29:18 PDTtag:blogger.com,1999:blog-15574215.post-725683734989902516Collections can be a great help in speeding up the PL/SQL programs. By using bulk collect operations it is possible to get great performance improvements.In 9.2 we needed to use the bulk collect clause to fetch rows into a collection. 10G brings a new function called COLLECT, which takes a column as a parameter and returns a nested table containing the column values. Using this we can get the 2008-04-11T12:29:18.142+03:00http://oracletoday.blogspot.com/2008/04/collect-in-10g.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/EPN55pNroIo/relational-algebra-division-in-sql.htmlnoreply@blogger.com (yas)Wed, 09 Apr 2008 02:42:49 PDTtag:blogger.com,1999:blog-15574215.post-4010525411196162728There was a question in one of the Turkish Oracle mailing lists which got me interested. The question simply was:I have a table holding the parts of a product and another table holding the suppliers of these parts. How can I find the suppliers which supply all the parts?Someone suggested using the division operation of relational algebra but did not provide how to do it in Oracle. So I started 2008-04-09T12:42:49.079+03:00http://oracletoday.blogspot.com/2008/04/relational-algebra-division-in-sql.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/z4a94glHYvQ/local-vs-remote-connection-performance.htmlnoreply@blogger.com (yas)Tue, 05 Feb 2008 02:13:58 PSTtag:blogger.com,1999:blog-15574215.post-2965975543579215255It has been asked several times in several places; is there a performance difference between running a query locally in a client on the server and running the same query in a remote client? The obvious answer given by the respondents including myself is: "if you do not return thousands of rows through the network, there must not be any difference". This type of response is opposed to what I 2008-02-05T12:13:58.775+02:001http://oracletoday.blogspot.com/2008/02/local-vs-remote-connection-performance.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/HMDAK0fz3W0/database-version-control.htmlnoreply@blogger.com (yas)Mon, 04 Feb 2008 05:33:50 PSTtag:blogger.com,1999:blog-15574215.post-2890883540261532732Coding horror is one of the software development blogs I keep a close eye on. Jeff Atwood posted a nice piece about database version control recently. Database version control is maybe one of the most important and unfortunately most overlooked things in software development. The post is a good read including the links he provides.2008-02-04T15:33:50.377+02:002http://oracletoday.blogspot.com/2008/02/database-version-control.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/u2irKYMcg3w/blog-tagging-thing.htmlnoreply@blogger.com (yas)Fri, 11 Jan 2008 04:50:38 PSTtag:blogger.com,1999:blog-15574215.post-6665675999922688977During the last few days lots of Oracle bloggers have been busy tagging each other and posting eight unknown things about themselves. I was also tagged by some friends and was asked to post eight things about myself. I have never forwarded any chain e-mails or messages to anyone and in parallel to that I have not written anything about myself after this either.What I think about this blog 2008-01-11T14:50:38.899+02:000http://oracletoday.blogspot.com/2008/01/blog-tagging-thing.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/u8lHwWlomxU/hiring-dbas-based-on-certification.htmlnoreply@blogger.com (yas)Sun, 09 Dec 2007 05:49:50 PSTtag:blogger.com,1999:blog-15574215.post-3618165530190358770There was a question in the OTN database forum recently which said:" Can any one advise me? I have joined a company as new senior dba. i am not understanding what shall be done at beginning? Can anybody advice me how to check all the database and what to do in the beginning ?I have been reading all the stuff,documents fr. a week?"The same poster also said:" I have all th knowledge of oracle,I 2007-12-09T15:49:50.434+02:008http://oracletoday.blogspot.com/2007/12/hiring-dbas-based-on-certification.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/Gi__ceccXTE/consolidating-orainventories.htmlnoreply@blogger.com (yas)Thu, 06 Dec 2007 05:05:48 PSTtag:blogger.com,1999:blog-15574215.post-3728320809927727116I have always had trouble with dealing with multiple oraInventories and multiple oraInst.loc files in a server. If there are lots of people installing products in a server or if you are handed an environment which is in such a mess it is a big trouble to get things tidied up (if the installer people used a central inventory instead of each one using their own inventories, you a re lucky). You 2007-12-06T15:05:48.441+02:000http://oracletoday.blogspot.com/2007/12/consolidating-orainventories.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/ZgvPO7Nxlno/optimizer-blog.htmlnoreply@blogger.com (yas)Thu, 06 Dec 2007 02:32:44 PSTtag:blogger.com,1999:blog-15574215.post-7286048775071558844As I have learnt from Greg Rahn's blog Structured Data, the people behind the optimizer started blogging. In their first technical post they talk about bind peeking in 11G. I think we will see lots of examples and explanations about the optimizer in there. Their blog is here.2007-12-06T12:32:44.146+02:000http://oracletoday.blogspot.com/2007/12/optimizer-blog.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/h7cSUZwCu5c/hard-disk-reference.htmlnoreply@blogger.com (yas)Wed, 05 Dec 2007 05:35:37 PSTtag:blogger.com,1999:blog-15574215.post-2465392577716239915There was a reference to a site about the hard disk a few days ago in the oracle-l list from Ranko Mosic. I have gone through half of it for now, I can say the referenced pages are a great guide for understanding the basics of the hard disk, its parts, inner workings, etc... Trying to tune I/O without knowing about the hard disk basics can lead a DBA to the wrong path easily, this site is a must 2007-12-05T15:35:37.146+02:000http://oracletoday.blogspot.com/2007/12/hard-disk-reference.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/qNyG3RA4MwM/rotate-your-logs.htmlnoreply@blogger.com (yas)Fri, 30 Nov 2007 06:53:35 PSTtag:blogger.com,1999:blog-15574215.post-5828457099088318261If you are using Linux and not rotating your alert logs, listener logs, any log actually, or rotating them with your own scripts, check out logrotate. I did not know this existed. From Dizwell.2007-11-30T16:53:35.570+02:000http://oracletoday.blogspot.com/2007/11/rotate-your-logs.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/VF_OdWIm8UI/bind-peeking-change-in-10g.htmlnoreply@blogger.com (yas)Wed, 28 Nov 2007 09:55:14 PSTtag:blogger.com,1999:blog-15574215.post-7068276233959324532A new note called "10g Upgrade Companion" has been published in Metalink recently. The note talks about the upgrade from 9i to 10g and lists recommended patches, behavior changes and best practices. While I was going through it I saw something I was not aware of before. In the "behavior changes" section it says: "Bind peeking has been extended to binds buried inside expressions.".I have talked 2007-11-28T19:55:14.181+02:001http://oracletoday.blogspot.com/2007/11/bind-peeking-change-in-10g.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/kpePIEE4nw4/fasttrue-for-adding-columns-in-11g.htmlnoreply@blogger.com (yas)Wed, 31 Oct 2007 08:40:29 PDTtag:blogger.com,1999:blog-15574215.post-6751900322645972115Before 11G adding new columns with default values to tables with millions of rows was a very time consuming and annoying task. Oracle had to update all the rows with the default value when adding the column.11G introduced a fast=true feature for adding columns with default values. In 11G when you want to add a not null column with a default value, the operation completes in the blink of an eye (2007-10-31T17:40:29.103+02:003http://oracletoday.blogspot.com/2007/10/fasttrue-for-adding-columns-in-11g.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/3QTYh4LdjQQ/ora-04043-in-mount-mode.htmlnoreply@blogger.com (yas)Thu, 20 Sep 2007 02:09:55 PDTtag:blogger.com,1999:blog-15574215.post-3344556675433873740There was a question at the OTN Database-General forum today about a problem when trying to describe the view dba_tablespaces.The poster was getting an ORA-04043 error.SQL> desc dba_tablespacesERROR:ORA-04043: object dba_tablespaces does not existThe first thing I thought about this was that the instance might have been in mount mode. I tried it on a database in mount stage and I got the 2007-09-20T12:09:55.168+03:007http://oracletoday.blogspot.com/2007/09/ora-04043-in-mount-mode.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/FD9MgUNEu1g/unique-indexes-on-materialized-views.htmlnoreply@blogger.com (yas)Wed, 29 Aug 2007 06:46:55 PDTtag:blogger.com,1999:blog-15574215.post-8525876916955279618One of the uses of materialized views is replication. Mviews can be used to replicate a table to another database to prevent users from accessing several databases through database links. This can improve the performance of queries which frequently access that table by removing the latency of the database link.Today the refresh job of one of the mviews we use for this purpose started getting "ORA2007-08-29T16:46:55.954+03:006http://oracletoday.blogspot.com/2007/08/unique-indexes-on-materialized-views.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/7Xh6U-2jiD4/invisible-indexes-in-11g.htmlnoreply@blogger.com (yas)Wed, 15 Aug 2007 02:20:28 PDTtag:blogger.com,1999:blog-15574215.post-804992605106177843711G has a new feature called Invisible Indexes. An invisible index is invisible to the optimizer as default. Using this feature we can test a new index without effecting the execution plans of the existing sql statements or we can test the effect of dropping an index without dropping it.We can create an index as invisible or we can alter an index to become invisible.YAS@11G>drop table t;Table 2007-08-15T12:20:28.665+03:002http://oracletoday.blogspot.com/2007/08/invisible-indexes-in-11g.htmlhttp://feedproxy.google.com/~r/OracleToday/~3/oC4sdqoBKuI/bind-variable-peeking-in-11g.htmlnoreply@blogger.com (yas)Tue, 14 Aug 2007 06:49:52 PDTtag:blogger.com,1999:blog-15574215.post-6498390083778679375In one of my recent posts I mentioned about Gregory Guillou's posts about bind variable peeking in 11G. I did a simple test and research on this.First here is the test case.YAS@11G>create table t(id number,name varchar2(30)); Table created.YAS@11G>insert into t select mod(rownum,2),object_name from all_objects;64901 rows created.YAS@11G>commit;Commit complete.YAS@11G>create index t_ind on t(id);2007-08-14T16:49:52.237+03:000http://oracletoday.blogspot.com/2007/08/bind-variable-peeking-in-11g.html