Surprising to many people, but “In some cases select statement generates redo too?? “ Background: I was working on one scenario regarding Oracle Statistics (Tracing statistics like sorts, consistent gets etc). So I created one table Please Note for testing purpose:  Db Block Size is 8k                 & ..read more

After a long  time am writing something and this write-up is based on live performance critical issue which i faced. During these critical issue we don’t have much time to ponder on; so need to be quick; so for sharing the same I have prepared on test case. Teams complained that their query(more specific execution of package) behaved very badly; earlier it used to take 10-15 mins max but per ..read more

One special behavior I found in Oracle (11.2.0.3) only on Sun-Solaris platform. In 11.2. + There is a bug 13549808 which deals with improving performance of all_synonyms queries. But on Solaris system even after applying the bug fix; the query related to ALL_SYNONYMS didn't improved though it is fine on Linux Box without even gathering statistics. After deployment of this bug fix; tried ..read more

Real Scenario: Development team ran the Job and the job failed with below error ORA-01031: insufficient privileges Logs were created for the failed job; but there were no trace for which object it failed. By looking at the error we cant say which object is lacking grants so I started my journey as doing simple check(Checking Grants on Objects) but since schema was big and the job logs didn't ..read more

SHM is key feature for any software on any of the host server. Oracle is doing the same. When Oracle initiates; Shared memory segments are created and the allocation of shared memory depends on Kernel parameter (i.e. Host server dependent). Just to give you brief how Oracle and Host server do the same  SHARED MEMORY ALLOCATION 1. One-segment 2. Contiguous multi-segment 3 ..read more

Recently faced an issue where an Application developer as per one request asked to revert dml permissions from the table; they just wanted "select only privilege"(Only for read purpose.). Did the same; removed the dml privileges from the public role as well as from the schema which was using that table via synonym; but still they complained that they are able to update the table from other ..read more

Understanding Joins Joins are statements that retrieve data from more than one table. A join is characterized by multiple tables in the FROM clause, and the relationship between the tables is defined through the existence of a join condition in the WHERE clause. In a join, one row set is called inner, and the other is called outer. This section discusses: ·         Nested Loop ..read more

Background: Whenever a session seems stuck or doesn’t comes out many DBA’s /Developers have a habit to cancel their session (CTRL C) or kill the session. My bad, I did the same when I was testing rebuilding of an index. Sql_Sesssion1> Select object_name,object_id,object_type From user_objects; OBJECT_NAME                                OBJECT_ID                           ..read more

IN v/s Exists:  Which one is Appropriate/Faster? Background: IN: It’s a multiple-row operator used in a query. <!--[if !supportLineBreakNewLine]--> <!--[endif]-->Select * From table_name Where column_name IN( value1,Value2) EXISTS: Operator is frequently used with correlated sub-queries to test whether a value retrieved by the outer query exists in the results set of the values retrieved by the ..read more

In today’s article I will discuss two securities issues: A) How to prevent user with ALTER USER privileges from changing password of sys and system.B) How to prevent the user to alter his/her password. I'll start with discussing “How to prevent user with alter user privileges from changing password of sys and system” If suppose one of the users in Db has “ALTER USER” privilege and he is ..read more