Coskan’s Approach to Oracle

March 27, 2009

Good reason to move from 10.2.0.2

Filed under: Bugs — coskan @ 11:31 am

If somebody ask me, which type of bug I prefer to live with from two options which are
1-Performance related bugs 2- wrong resultset bugs. I would prefer to live with performance related ones because wrong resultset is something I never want to see on any of my databases. It is hard to catch and can cause big problems especially on database with Finance data on it.

Today I am going to give you a good reason to move from 10.2.0.2 because of a wrong resultset issue. Issue we hit was on our Peoplesoft database running on HP-UX 11i on Oracle EE 10.2.0.2 . It looks like it is smilar to the
Bug 5843316 Wrong results with index join and function-based index.

Here is my test case for the issue.

My table

create table t1 as
select
mod(object_id, 11000) id,
trunc(dbms_random.value(0,9)) seq,
trunc(dbms_random.value(0,3)) rec,
trunc(created) created,
trunc(last_ddl_time) lastddl,
substr(owner,1,1) status,
object_type,
object_name,
generated,
subobject_name from all_objects;=

Two index on the table (both index have the same four column but second one has two more columns and the 4th column on the index is changed for the second one)


CREATE INDEX T1_IX2 ON T1
(id,rec, created DESC, seq DESC, object_type);


CREATE INDEX T1_IX1 ON T1
(id,rec, created DESC, seq DESC, STATUS, object_name,subobject_name);

Gather the stats

exec dbms_stats.gather_table_stats (ownname=>user,tabname=>'T1',cascade=>TRUE)

On our production case there wasn’t any hint but for simulating the test I am forcing to use indexes to see the different resultset behaviour

These are the SQLs I use for the tests.

wrong result set T1_IX1 hinted


SELECT /*+gather_plan_statistics INDEX (t1 t1_ix1)*/
lastddl
FROM t1
WHERE id = '19'
AND t1.status 'P'
AND t1.created =
(SELECT MAX (created)
FROM t1 x
WHERE t1.id = x.id AND t1.rec=x.rec)
AND t1.seq =
(SELECT MAX (seq)
FROM t1 x
WHERE t1.id = x.id
AND t1.rec=x.rec
AND t1.created = x.created);

right result set T1_IX2 hinted

SELECT /*+gather_plan_statistics INDEX (t1 t1_ix2)*/
lastddl
FROM t1
WHERE id = '19'
AND t1.status 'P'
AND t1.created =
(SELECT MAX (created)
FROM t1 x
WHERE t1.id = x.id AND t1.rec=x.rec)
AND t1.seq =
(SELECT MAX (seq)
FROM t1 x
WHERE t1.id = x.id
AND t1.rec=x.rec
AND t1.created = x.created);

These are the runtime behaviours of SQLs
FIRST CASE- Not working when ,resultset column LAST_DDL, is not included in the index T1_IX1. No rows returned

 SQL> SELECT /*+gather_plan_statistics  INDEX (t1 t1_ix1)*/
  2 <strong> lastddl</strong>
  3    FROM t1
  4   WHERE id = '19'
  5     AND t1.status <> 'P'
  6     AND t1.created =
  7                    (SELECT MAX (created)
  8                       FROM t1 x
  9                      WHERE t1.id = x.id AND t1.rec=x.rec)
 10     AND t1.seq =
 11            (SELECT MAX (seq)
 12               FROM t1 x
 13              WHERE t1.id = x.id
 14                AND t1.rec=x.rec
 15                AND t1.created = x.created);

no rows selected
SQL> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Plan hash value: 4001972719

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |     1 |   154 |    14   (8)| 00:00:01 |
|*  1 |  FILTER                        |        |       |       |            |          |
|   2 |   HASH GROUP BY                |        |     1 |   154 |    14   (8)| 00:00:01 |
|   3 |    NESTED LOOPS                |        |     1 |   154 |     7   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    71 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     3   (0)| 00:00:01 |
|   6 |       SORT AGGREGATE           |        |     1 |    35 |            |          |
|*  7 |        INDEX RANGE SCAN        | T1_IX1 |    12 |   420 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    83 |     3   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))=MAX("SEQ") AND
              SYS_OP_DESCEND("T1"."SEQ")=SYS_OP_DESCEND(MAX("SEQ")))
   5 - access("ID"=19)
       filter("T1"."STATUS"<>'P' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=
               (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B1 AND "X"."ID"=:B2) AND SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(
              (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B3 AND "X"."ID"=:B4)))
   7 - access("X"."ID"=:B1 AND "X"."REC"=:B2)
   9 - access("X"."ID"=19 AND "T1"."REC"="X"."REC" AND
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND("CREATED"))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=SYS_OP_UNDESCEND(SYS_OP
              _DESCEND("CREATED")))

SECOND CASE- working when resultset column ID which is IN the index T1_IX1.

 SQL> SELECT /*+gather_plan_statistics  INDEX (t1 T1_IX1)*/
  2  id
  3    FROM t1
  4   WHERE id = '19'
  5     AND t1.status <> 'P'
  6     AND t1.created =
  7                    (SELECT MAX (created)
  8                       FROM t1 x
  9                      WHERE t1.id = x.id AND t1.rec=x.rec)
 10     AND t1.seq =
 11            (SELECT MAX (seq)
 12               FROM t1 x
 13              WHERE t1.id = x.id
 14                AND t1.rec=x.rec
 15                AND t1.created = x.created);

       ID
----------
        19
        19
        19

SQL> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
Plan hash value: 2740842560

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |   100 |    13   (8)| 00:00:01 |
|*  1 |  HASH JOIN          |         |     1 |   100 |     7  (15)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN  | T1_IX1  |     1 |    63 |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE   |         |     1 |    48 |            |          |
|*  4 |     INDEX RANGE SCAN| T1_IX1  |     1 |    48 |     3   (0)| 00:00:01 |
|   5 |   VIEW              | VW_SQ_1 |    11 |   407 |     3   (0)| 00:00:01 |
|   6 |    HASH GROUP BY    |         |    11 |   814 |     3   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN| T1_IX1  |    11 |   814 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))="VW_COL_1"
              AND SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND("VW_COL_1") AND
              "T1"."ID"="ID" AND "T1"."REC"="REC")
   2 - access("ID"=19)
       filter("T1"."STATUS"<>'P' AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))= (SELECT
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))) FROM "T1" "X" WHERE
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(:B1) AND "X"."REC"=:B2 AND
              "X"."ID"=:B3 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=:B4) AND
              SYS_OP_DESCEND("SEQ")=SYS_OP_DESCEND( (SELECT
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))) FROM "T1" "X" WHERE
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(:B5) AND "X"."REC"=:B6 AND
              "X"."ID"=:B7 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=:B8)))
   4 - access("X"."ID"=:B1 AND "X"."REC"=:B2 AND
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(:B3))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=:B1)
   7 - access("X"."ID"=19)
       filter("X"."ID"=19)

THIRD CASE- Still not working when one of the resultset columns, LAST_DDL is NOT in the index T1_IX1. No rows returned

 
SQL> SELECT /*+gather_plan_statistics  INDEX (t1 t1_ix1)*/
  2  lastddl,id
  3    FROM t1
  4   WHERE id = '19'
  5     AND t1.status <> 'P'
  6     AND t1.created =
  7                    (SELECT MAX (created)
  8                       FROM t1 x
  9                      WHERE t1.id = x.id AND t1.rec=x.rec)
 10     AND t1.seq =
 11            (SELECT MAX (seq)
 12               FROM t1 x
 13              WHERE t1.id = x.id
 14                AND t1.rec=x.rec
 15                AND t1.created = x.created);

no rows selected

SQL> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Plan hash value: 4001972719

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |     1 |   154 |    14   (8)| 00:00:01 |
|*  1 |  FILTER                        |        |       |       |            |          |
|   2 |   HASH GROUP BY                |        |     1 |   154 |    14   (8)| 00:00:01 |
|   3 |    NESTED LOOPS                |        |     1 |   154 |     7   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    71 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     3   (0)| 00:00:01 |
|   6 |       SORT AGGREGATE           |        |     1 |    35 |            |          |
|*  7 |        INDEX RANGE SCAN        | T1_IX1 |    12 |   420 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    83 |     3   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))=MAX("SEQ") AND
              SYS_OP_DESCEND("T1"."SEQ")=SYS_OP_DESCEND(MAX("SEQ")))
   5 - access("ID"=19)
       filter("T1"."STATUS"<>'P' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=
               (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B1 AND "X"."ID"=:B2) AND SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(
              (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B3 AND "X"."ID"=:B4)))
   7 - access("X"."ID"=:B1 AND "X"."REC"=:B2)
   9 - access("X"."ID"=19 AND "T1"."REC"="X"."REC" AND
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND("CREATED"))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=SYS_OP_UNDESCEND(SYS_OP
              _DESCEND("CREATED")))

FOURTH- It is working when we force the database use T1_IX2 for both cases (resultset IN or not IN)

 SQL> SELECT /*+gather_plan_statistics  INDEX (t1 t1_ix2) */
  2  lastddl,id
  3    FROM t1
  4   WHERE id = '19'
  5     AND t1.status <> 'P'
  6     AND t1.created =
  7                    (SELECT  MAX (created)
  8                       FROM t1 x
  9                      WHERE t1.id = x.id AND t1.rec=x.rec)
 10     AND t1.seq =
 11            (SELECT MAX (seq)
 12               FROM t1 x
 13              WHERE t1.id = x.id
 14                AND t1.rec=x.rec
 15                AND t1.created = x.created);

LASTDDL                   ID
----------------- ----------
20070125 00:00:00         19
20070128 00:00:00         19
20090327 00:00:00         19

SQL> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Plan hash value: 3946360691

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |     1 |   154 |    14   (8)| 00:00:01 |
|*  1 |  FILTER                        |        |       |       |            |          |
|   2 |   HASH GROUP BY                |        |     1 |   154 |    14   (8)| 00:00:01 |
|   3 |    NESTED LOOPS                |        |     1 |   154 |     7   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    71 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_IX2 |     1 |       |     3   (0)| 00:00:01 |
|   6 |       SORT AGGREGATE           |        |     1 |    35 |            |          |
|*  7 |        INDEX RANGE SCAN        | T1_IX1 |    12 |   420 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    83 |     3   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))=MAX("SEQ") AND
              SYS_OP_DESCEND("SEQ")=SYS_OP_DESCEND(MAX("SEQ")))
   4 - filter("T1"."STATUS"<>'P')
   5 - access("ID"=19)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))= (SELECT
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B1 AND "X"."ID"=:B2) AND SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(
              (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B3 AND "X"."ID"=:B4)))
   7 - access("X"."ID"=:B1 AND "X"."REC"=:B2)
   9 - access("X"."ID"=19 AND "T1"."REC"="X"."REC" AND
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND("CREATED"))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=SYS_OP_UNDESCEND(SYS_OP
              _DESCEND("CREATED")))

Same resultset on HP-UX-Linux 10.2.0.2. Don’t have 10.2.0.2 windows but works fine on 10.2.0.3 on Windows.

on 10.2.0.4 and 11G problem is gone

This case is enough reason that lead me to check every solved bug for the upper releases to see if our databases are facing the problem especially for wrong resultset. Another good part of looking for bugs, is that it is nice way to learn how to crash your database, a bit of oracle internals, interesting test cases etc.

March 23, 2009

SQL*Plus Bug or My own bug :)

Filed under: Bugs — coskan @ 5:00 pm

update:(5 hours later after Nicolas Gasparotto’s comment) the post below is a good explanation for invalid dba exception I got for my own actions. You need to read the post together with first two comments.

I have 2 excuses for my dodgy conclusion below:)
1- I am on diet and halved the calorie intake, so my brain is not fed enough.
2- The way it gives the same result on every different/client machine combination lead me wrong idea and to be honest, I wasnt focusing on the content of trigger because of a successful case.

At the end of the day I am %100 guilty because I act like a developer and blamed ORacle at first place:)

Moral of the story for me ; check before publishing, especially your NLS settings

I think I found a bug which is available for the sqlplus clients above 10.2.0.1 .

What I was doing was an insert on a table. Table had a trigger which inserts a non-default value for a column that wasn’t in the insert list.
Insert statement wasn’t working on my 11.1.0.6 client but works fine on TOAD or SQLPLUSW exe.

This is the test case I prepared for this issue; (exactly the same trigger on our production database)

Table is on 10.2.0.3 database (DB version doesnt matter the problem is client)

SQL> drop table test_c purge;

Table dropped.

SQL> create table test_c (test_count number,tr_date date);

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER TRG_TST
2 BEFORE INSERT OR UPDATE ON TEST_c
3 FOR EACH ROW
4 WHEN (
5 new.TR_DATE is NULL
6 )
7 BEGIN
8 :new.TR_DATE := '31-DEC-2049';
9 :new.TEST_COUNT := :new.TEST_COUNT + 1;
10 END;
11 /

Trigger created.

This is the behaviour for the insert on different client versions for sqlplus.exe (Windows) and sqlplus (HP-UX)

On 10.2.0.1 client the tests case works.


SQL> insert into test_c (TEST_COUNT) values (0);

1 row created.

On 10.2.0.2 client test case fails.
windows

SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'

Unix

SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'

On 10.2.0.4
windows

SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'

Unix

On 11.1.0.6
Windows
SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'

I don’t have a unix 11G client, but I think it will fail on it as well.

I think its time to raise an SR to Oracle Support.

Query Result Cache on Encrypted Tablespace on 11G

Filed under: Performance — coskan @ 10:45 am

Question: Will query result cache work on Encrypted tablespaces.

I asked this question to Tom Kyte after his All About Encryption presentation at UKOUG 2008. His answer was ” I think it will work but you need to try”

After 4 months, here is the answer to my question;

 
SQL> ---Encrypted tablespace creation
SQL> CREATE TABLESPACE encryptedtst
  2  DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\ORACOS\encryptedtst_01.dbf' SIZE 128K
  3  AUTOEXTEND ON NEXT 64K
  4  ENCRYPTION USING 'AES256'
  5  DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL> connect hr/hr;
Connected.

SQL> alter table employees enable row movement;

Table altered.

SQL> alter table employees
  2  move tablespace encryptedtst;

Table altered.

SQL> select manager_id,sum(salary) from employees group by manager_id;

MANAGER_ID SUM(SALARY)
---------- -----------
       100      155400
                 27000
       123       25900
       120       22100
	..	...

19 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    18 |   144 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |    18 |   144 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |   856 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

SQL> select /*+ result_cache*/manager_id,sum(salary) from employees group by manager_id;

MANAGER_ID SUM(SALARY)
---------- -----------
       100      155400
                 27000
       123       25900
       120       22100
	..	...

19 rows selected.

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    18 |   144 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 1u45jnmw74kd60brbww9gr7ss8 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    18 |   144 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   856 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id"

SQL> select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id;

MANAGER_ID SUM(SALARY)
---------- -----------
       100      155400
                 27000
       123       25900
       120       22100
	..	...

19 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    18 |   144 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 1u45jnmw74kd60brbww9gr7ss8 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    18 |   144 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   856 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id"

SQL>


SQL>--Check if it is really cached
SQL> select name from v$result_cache_objects;

NAME
-----------------------------------------------------------------------------------------------
HR.EMPLOYEES
select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id


As you see on the explain plan and v$result_cache_table, the result is cached

Conclusion is; Oracle did not forget to combine two new features together by the help of keeping objects of encrypted tablespaces, unencrypted in SGA for better performance

March 18, 2009

What I learned during Oracle SQL Expert Exam Study Part-2

Filed under: Basics, Certification, PL/SQL — coskan @ 9:31 pm

I wrote Part-1 of this series for covering what I learned from SQL Fundamentals I while studying for SQL Expert exam. Part-2 is for covering what I learned from SQL Fundamentals II.

Here are the things I should have learned before; (There are questions on 6 and 9 and answers will be prized with guinness if you can catch me (try my pony tail when you see me at a conference))

1- session_privs / session_roles views; never need, so never heard, these views can be very useful for normal database users to see what privileges they have what roles are enabled for them.

SQL> connect hr/hr;
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
...
...
...

14 rows selected.

-----ON SYS SESSION GRANT
-----SELECT ANY TABLE TO HR

SQL> grant select any table to hr;

Grant succeeded.

---ON HR SESSION
---SELECT ANY TABLE is shown 

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
SELECT ANY TABLE
....
....

15 rows selected.

—SESSION ROLES

SQL> select * from session_roles;

ROLE
------------------------------
RESOURCE

---ON SYS SESSION GIVE DBA ROLE to HR

SQL> grant dba to hr;

Grant succeeded.
---on HR Session set Role DBA

SQL> set role dba;

Role set.

SQL> select * from session_roles;

ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
...
...

18 rows selected.

2- PASSWORD command of SQLPLUS I was always using alter user command to change my password or somebody else’s password but this command is available and working as it works on Unix. If you want to change your password when you are a normal user it asks old password but if you are DBA and want to change some other users password, it lets you to set the password.


SQL> connect hr/hr
Connected.
---for SCOTT by HR
SQL> password scott
Changing password for scott
New password:
Retype new password:
ERROR:
ORA-01031: insufficient privileges

Password unchanged
SQL>---FOR HR itself
SQL> password
Changing password for HR
Old password:
New password:
Retype new password:
Password changed

—with sys user

SQL> password SCOTT
Changing password for SCOTT
New password:
Retype new password:
Password changed

3- References Object Privilege; Document says it lets the given user to create references on the table or view.

SQL> create user coskan identified by coskan ;

User created.

SQL> grant resource,create session to coskan;

Grant succeeded.

SQL> grant create table to coskan;

Grant succeeded.

SQL> connect coskan/coskan;
Connected.

SQL> create table t1 (id number CONSTRAINT t1_empid_fk REFERENCES hr.employees(employee_id));
create table t1 (id number CONSTRAINT t1_empid_fk REFERENCES hr.employees(employee_id))
                                                                *
ERROR at line 1:
ORA-00942: table or view does not exist

----GRANT REFERENCES priv
----on HR.EMPLOYEES by HR user

SQL> grant references on employees to coskan;

Grant succeeded.

---on COSKAN user
---it lets to user the table for ref.
SQL> create table t1 (id number CONSTRAINT t1_empid_fk REFERENCES hr.employees(employee_id));

Table created.

---PRIV is just for reference
---it is not for selecting
SQL> select count(*) from hr.employees;
select count(*) from hr.employees
                        *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>

—on HR session, you cant revoke privilege without cascade option

SQL> revoke references on employees from coskan;
revoke references on employees from coskan
*
ERROR at line 1:
ORA-01981: CASCADE CONSTRAINTS must be specified to perform this revoke

SQL> revoke references on employees from coskan cascade constraints;

Revoke succeeded.

----on COSKAN session constraint is droppped

SQL> @ddl t1

PL/SQL procedure successfully completed.

DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
-------------------------------------------------------------------------
-------------------------------------------------------------------------

  CREATE TABLE "COSKAN"."T1"
   (    "ID" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;

4- system and object privileges cannot be given in one sql statement.

SQL> grant create table,select on hr.employees to coskan;
grant create table,select on hr.employees to coskan
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege

SQL> grant create table to coskan;

Grant succeeded.

SQL> grant select on hr.employees to coskan;

Grant succeeded.

5-on delete set null Another shame for me. I think this option is better than on delete cascade for constraints

SQL> create table emp_2 (id number,
  2  emp_name varchar2(32),
  3  emp_id number
  4  CONSTRAINT t1_empid_fk
  5  REFERENCES hr.employees(employee_id)
  6  on delete set null);

Table created.

SQL> insert into emp_2 select employee_id,last_name
  2  ,employee_id from hr.employees where rownum<10;

9 rows created.

SQL> commit;

Commit complete.

SQL> select * from emp_2;

        ID EMP_NAME                             EMP_ID
---------- -------------------------------- ----------
       198 OConnell                                198
       199 Grant                                   199
       200 Whalen                                  200
       201 Hartstein                               201
       202 Fay                                     202
       203 Mavris                                  203
       204 Baer                                    204
       205 Higgins                                 205
       206 Gietz                                   206

9 rows selected.

SQL> delete from hr.employees where employee_id=198;

1 row deleted.

SQL> select * from emp_2;

        ID EMP_NAME                             EMP_ID
---------- -------------------------------- ----------
       198 OConnell
       199 Grant                                   199
       200 Whalen                                  200
       201 Hartstein                               201
       202 Fay                                     202
       203 Mavris                                  203
       204 Baer                                    204
       205 Higgins                                 205
       206 Gietz                                   206

9 rows selected.

SQL>

6- Inserting Using a Subquery as a Target This is the first time I came across an insert like this. When you do an insert using a subquery I think it is better to use it with check otherwise I cant think of a situation to use it

Here is the working example. When I use WITH CHECK I got the expected error



SQL> INSERT INTO
  2  (SELECT department_id, department_name, d.location_id
  3  FROM departments d,locations l
  4  WHERE d.location_id=l.location_id and
  5  d.location_id < 2000)
  6  VALUES (9999, 'Entertainment', 2500);

1 row created.

SQL> rollback;

Rollback complete.

SQL> INSERT INTO
  2  (SELECT department_id, department_name, d.location_id
  3  FROM departments d,locations l
  4  WHERE d.location_id=l.location_id and
  5  d.location_id < 2000 WITH CHECK OPTION)
  6  VALUES (9999, 'Entertainment', 2500);
FROM departments d,locations l
     *
ERROR at line 3:
ORA-01402: view WITH CHECK OPTION where-clause violation



This is is the failed one, when I use WITH CHECK OPTION, I got ORA-01733: instead of ORA-01402: and I really dont know, why it fails. I asked it on Oracle forums too and if you need points at the forum ,you can answer it at this link


SQL> INSERT INTO
  2  (SELECT
  3  location_id,
  4  city,
  5  l.country_id
  6  FROM countries c, locations l,regions r
  7  where l.country_id = c.country_id
  8  and r.region_id=c.region_id
  9  and  r.region_name = 'Asia'  )
 10  VALUES (5500, 'Wansdworth Common', 'UK');

1 row created.

SQL> rollback;

Rollback complete.

SQL> INSERT INTO
  2  (SELECT
  3  location_id,
  4  city,
  5  l.country_id
  6  FROM countries c, locations l,regions r
  7  where l.country_id = c.country_id
  8  and r.region_id=c.region_id
  9  and  r.region_name = 'Asia' WITH CHECK OPTION )
 10  VALUES (5500, 'Wansdworth Common', 'UK');
INSERT INTO
*
ERROR at line 1:
ORA-01733: virtual column not allowed here


7- Multiple inserts I heard about multiple inserts probably when I got DBA Workshop-1 , 4 years ago, but I never needed them after that date. Here is how you can use them

—unconditional INSERT ALL. As you might guess there is no condition and all inserts will run

create table sal_history as
select employee_id,hire_date,salary
from employees
where 1=2;

SQL> create table MGR_HISTORY as
  2  select EMPLOYEE_ID,MANAGER_ID,SALARY
  3  from employees
  4  where 1=2;

Table created.

SQL> create table SAL_HISTORY as
  2  select EMPLOYEE_ID,HIRE_DATE,SALARY
  3  from employees
  4  where 1=2;

Table created.

SQL>
SQL> INSERT ALL
  2  INTO sal_history VALUES(EMPID,HIREDATE,SAL)
  3  INTO mgr_history VALUES(EMPID,MGR,SAL)
  4  SELECT employee_id EMPID, hire_date HIREDATE,
  5  salary SAL, manager_id MGR
  6  FROM employees
  7  WHERE department_id > 20;

206 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from sal_history;

  COUNT(*)
----------
       103

SQL> select count(*) from mgr_history;

  COUNT(*)
----------
       103

—conditional INSERT ALL. Only insert statements those fit to the condition will be inserted


SQL> truncate table sal_history;

Table truncated.

SQL> truncate table mgr_history;

Table truncated.

SQL>
SQL> select count(*) from employees
  2  where department_id<20;

  COUNT(*)
----------
         1

SQL>
SQL> select count(*) from employees
  2  where manager_id is null;

  COUNT(*)
----------
         2

SQL> INSERT ALL
  2  WHEN DEPARTMENT_ID <20 THEN
  3  INTO sal_history VALUES(EMPID,HIREDATE,SAL)
  4  WHEN MGR is null THEN
  5  INTO mgr_history VALUES(EMPID,MGR,SAL)
  6  SELECT employee_id EMPID, hire_date HIREDATE,
  7  salary SAL, manager_id MGR,DEPARTMENT_ID
  8  FROM employees;

3 rows created.

SQL> select count(*) from sal_history;

  COUNT(*)
----------
         1

SQL> select count(*) from mgr_history;

  COUNT(*)
----------
         2

—conditional INSERT FIRST Only first insert whose condition is TRUE will run if it is not true the second and continue.


SQL> truncate table sal_history;

Table truncated.

SQL> truncate table mgr_history;

Table truncated.

SQL> truncate table emp_history;

Table truncated.

SQL>
SQL> select count(*) from employees
  2  where manager_id is null;

  COUNT(*)
----------
         2

SQL>
SQL> select count(*) from employees
  2  where department_id is null;

  COUNT(*)
----------
         1

SQL>
SQL>
SQL> select count(*) from employees
  2  where department_id is null and manager_id is null;

  COUNT(*)
----------
         1

SQL> select count(*) from (
  2  SELECT employee_id EMPID, hire_date HIREDATE,
  3  salary SAL, manager_id MGR,DEPARTMENT_ID
  4  FROM employees) t;

  COUNT(*)
----------
       106

SQL>
SQL>
SQL> ---When it finds first row with manager_id is null
SQL> ---it will skip mgr_history insert and will do
SQL> ---only sal_history insert and
SQL> ---there is no other department_id is null
SQL> ---so second insert will never run
SQL> ---at the end of this transaction
SQL> ---sal_history 2 row
SQL> ---mgr_history 0 row
SQL> ---emp_history 104 row
SQL>
SQL> INSERT FIRST
  2  WHEN MGR is null THEN
  3  INTO sal_history VALUES(EMPID,HIREDATE,SAL)
  4  WHEN DEPARTMENT_ID is null THEN
  5  INTO mgr_history VALUES(EMPID,MGR,SAL)
  6  ELSE INTO emp_history VALUES(EMPID,SAL,MGR,DEPARTMENT_ID)
  7  SELECT employee_id EMPID, hire_date HIREDATE,
  8  salary SAL, manager_id MGR,DEPARTMENT_ID
  9  FROM employees;

106 rows created.

SQL> select count(*) from sal_history;

  COUNT(*)
----------
         2

SQL> select count(*) from mgr_history;

  COUNT(*)
----------
         0

SQL> select count(*) from emp_history;

  COUNT(*)
----------
       104

—pivoting INSERT- The one I really like. It does pivoting :)

suppose that you have a table like below


SQL> create table HALF_YEAR_BUDGET
  2  (DEPARTMENT varchar2(2),
  3  YEAR VARCHAR2(4),
  4  JAN number,
  5  FEB number,
  6  MAR number,
  7  APR number,
  8  MAY number,
  9  JUN number);

Table created.

SQL> insert into HALF_YEAR_BUDGET
  2  values ('IT','2009',5000,6000,2000,4000,7000,9000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from HALF_YEAR_BUDGET;

DE YEAR        JAN        FEB        MAR        APR        MAY        JUN
-- ---- ---------- ---------- ---------- ---------- ---------- ----------
IT 2009       5000       6000       2000       4000       7000       9000

and you want to store it in 3 column 6 row table

all you need to do is pivoting the insert

usage is same with INSERT ALL

SQL> create table DEP_BUDGET
  2  (DEPARTMENT varchar2(2),
  3  YEAR VARCHAR2(4),
  4  BUDGET number);

Table created.

SQL> INSERT ALL
  2     INTO DEP_BUDGET VALUES (department,year,jan)
  3     INTO DEP_BUDGET VALUES (department,year,feb)
  4     INTO DEP_BUDGET VALUES (department,year,mar)
  5     INTO DEP_BUDGET VALUES (department,year,apr)
  6     INTO DEP_BUDGET VALUES (department,year,may)
  7     INTO DEP_BUDGET VALUES (department,year,jun)
  8     SELECT department, year, jan,feb,mar,apr,
  9     may,jun
 10     FROM HALF_YEAR_BUDGET;

6 rows created.

SQL> select * from DEP_BUDGET;

DE YEAR     BUDGET
-- ---- ----------
IT 2009       5000
IT 2009       6000
IT 2009       2000
IT 2009       4000
IT 2009       7000
IT 2009       9000

6 rows selected.

8- Explicit Defaults This is nice way to set a column to a default value. Just use the word DEFAULT

SQL> create table t1 (id number,last_name varchar2(32) default 'coskan');

Table created.

SQL> insert into t1 values (1,'oracle');

1 row created.

SQL> insert into t1 values (2,'larry');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID LAST_NAME
---------- --------------------------------
         1 oracle
         2 larry

SQL> update t1 set last_name=DEFAULT where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID LAST_NAME
---------- --------------------------------
         1 coskan
         2 larry

9- Subquery in order by You need to show all the employees ordered by their department names.

FIRST WAY I thought was joining the tables


SQL> select employee_id,last_name
  2  from hr.employees e,hr.departments d
  3  where e.department_id=d.department_id
  4  order by d.department_name;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        206 Gietz
        205 Higgins
        200 Whalen
        202 Fay
	..
	..
	..

105 rows selected

SQL> @x

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Plan hash value: 3011238288

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |   105 |  3255 |     7  (29)| 00:00:01 |
|   1 |  SORT ORDER BY                |             |   105 |  3255 |     7  (29)| 00:00:01 |
|   2 |   MERGE JOIN                  |             |   105 |  3255 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
       filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

19 rows selected.

SECOND WAY I LEARNED- using corrolated subquery in order by

SQL> select employee_id,last_name from hr.employees e
  2  order by
  3  (select department_name
  4  from hr.departments d
  5  where e.department_id=d.department_id);

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        205 Higgins
        206 Gietz
        200 Whalen
	..
	..
	..
106 Rows selected

SQL> @x

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
Plan hash value: 1231663137

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   3 |  SORT ORDER BY              |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   4 |   TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPARTMENT_ID"=:B1)

If you are carefull enough you would have seen that plans are different and rownumbers are different ?

What is the difference between 2?

Second one doesn’t care if there isnt any matching row on employees table. So it is something like outer join
So if you want the same result with the first method you need outer join

SQL> select employee_id,last_name
  2  from hr.employees e,hr.departments d
  3  where e.department_id=d.department_id(+)
  4  order by d.department_name;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        206 Gietz
        205 Higgins
        200 Whalen
        202 Fay
	...
	...
106 rows selected

SQL> @x

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
Plan hash value: 3271769410

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   107 |  3317 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |             |   107 |  3317 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |             |   107 |  3317 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))

As you see they have different plan. Cost of corrolated one is better then the outer join version
but consistent gets are higher for corrolated one. 

<strong>I think this needs more  investigation if I find something I will write as a new blog post</strong>  (to be honest I just realised. One more benefit of blogging; paying more attention to what you learn/write)
1
SQL> set autotrace traceonly;
SQL> select employee_id,last_name from hr.employees e
  2  order by
  3  (select department_name
  4  from hr.departments d
  5  where e.department_id=d.department_id);

106 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1231663137

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   3 |  SORT ORDER BY              |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   4 |   TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPARTMENT_ID"=:B1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
       2200  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        106  rows processed

SQL> select employee_id,last_name
  2  from hr.employees e,hr.departments d
  3  where e.department_id=d.department_id(+)
  4  order by d.department_name;

106 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3271769410

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   107 |  3317 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |             |   107 |  3317 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |             |   107 |  3317 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
       2200  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        106  rows processed

SQL>

This is the end of this series as I promised.Please check the SQL Reference for remaining interesting things I bet you will find one no matter how much you think you know.

March 11, 2009

Alter user identified by values on 11G without using SYS.USER$

Filed under: Security, Tips — coskan @ 11:13 am

You probably know that on Oracle 11G, hash values of passwords, are no longer shown in dba_users table, so to change the password temporarily, it is not possible to use the way I explained here before.

On Oracle 11G, there is one method posted by Laurent Schneider which is, to use SYS.USER$ table to extract hash value of the password.

Another method, I tried and got success, is using DBMS_METADATA.GET_DDL function without going to any other dictionary tables.

DBA Session — check the output of DBMS_METADATA.GET_DDL

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> drop user coskan cascade;

User dropped.

SQL> create user coskan identified by oracle;

User created.

SQL> grant create session to coskan;

Grant succeeded.

SQL>  select dbms_metadata.get_ddl('USER','COSKAN') from dual;

DBMS_METADATA.GET_DDL('USER','COSKAN')
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------

   CREATE USER "COSKAN" IDENTIFIED BY 
VALUES 'S:1F0648E7E665F0A0EE44B1E9BD4B626A77CA25B376A49177F9E97DF98BFA;26EB15F771A78542'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


As you see gathering DDL of the user gives us the hash value

On the User session —try connection

SQL> connect coskan/oracle
Connected.
SQL>

DBA Session —change user password

SQL> alter user coskan identified by msssql;

User altered.

User Session —connect with new password

SQL> connect coskan/msssql
Connected.

DBA Session — Update the user password with the value you gathered by DBMS_METADATA.GET_DDL

SQL> alter user coskan identified by
  2  VALUES 'S:1F0648E7E665F0A0EE44B1E9BD4B626A77CA25B376A49177F9E97DF98BFA;26EB15F771A78542';

User altered.

User Session – Try the old password

SQL> connect coskan/oracle
Connected.
SQL>

I think this option is a bit easier than extracting from table approach.

Update-01/12/11 After I read the question on this oracle-l question http://www.freelists.org/post/oracle-l/11202-setting-password I think I need to mention that you need to have “SET LONG 999999″ setting for your sqlplus env

March 5, 2009

What I learned during Oracle SQL Expert Exam Study Part-1

Filed under: Basics, Certification, PL/SQL, Tips — coskan @ 9:36 am

Yesterday, I passed the Oracle SQL Expert Exam. After 4 full years of working with Oracle, it took 2.5 weeks to study for this exam. I have studied from the SQL Fundementals 1-2 course material from Oracle University for focusing the exam topics and I used Oracle official documentation to support what I have learned from the course material. During my studies, I can say I learned a lot of small things and sometimes I felt shamed about not knowing the things that I should have learned as a junior DBA. At the end of the day, I can easily say that I can not be a SQL expert before writing that much SQL code as a database developer nevertheless, I am glad that, I spent time on learning new things by the help of certification. After this brief history, lets talk about something technical. (Warning!!! Post will be long and if you are already good with sql, just review the headers to se if there is something new for you. )

1- q Operator for using character literals with qoutation marks I never heard about it before. Document says its good for readibility but I am not sure :) Usage is simple if you want to say “coskan’s” all you need to do is put it in the braclets q’[]‘ or q’{}’ or q’()’ or q’<>’

SQL> select 'coskan''s approach to oracle' from dual;

'COSKAN''SAPPROACHTOORACLE'
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q[''s approach to oracle'] from dual;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> select 'coskan'||q'['s approach to oracle]' from dual;

'COSKAN'||Q'['SAPPROACHTOOR
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q'('s approach to oracle)' from dual;

'COSKAN'||Q'('SAPPROACHTOOR
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q'{'s approach to oracle}' from dual;

'COSKAN'||Q'{'SAPPROACHTOOR
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q'<'s approach to oracle>' from dual;

'COSKAN'||Q'<'SAPPROACHTOOR
---------------------------
coskan's approach to oracle

2- null with logical operators . ( I felt really shamed that I didnt know (null&false=false) and (null or true=true) ) table is like below null and true = null null and false = false null OR true = true null OR false = null

SQL> select * from hr.departments where (1=1 and department_id not in (select null from dual)) and rownum<2;

no rows selected

SQL> select * from hr.departments where (1=2 and department_id not in (select null from dual)) and rownum<2;

no rows selected

SQL> select * from hr.departments where (1=1 or department_id not in (select null from dual)) and rownum<2;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700

SQL> select * from hr.departments where (1=2 or department_id not in (select null from dual)) and rownum<2;

no rows selected

3- Escape clause to avoid wildcard characters.

To use wildcard characters as normal character you can use Escape clause with Like

SQL> select first_name from hr.employees
     2  where first_name like '%E_%';

FIRST_NAME
--------------------
Ellen
Elizabeth
E_leni

SQL> select first_name from hr.employees 
     2 where first_name like '%E\_%' escape '\';

FIRST_NAME
--------------------
E_leni

SQL> select first_name from hr.employees 
    2   where first_name like '%E?_%' escape '?';

FIRST_NAME
--------------------
E_leni

4 Operator Precedence
You need to know these precedence to make sure your query is doing the right thing. (simple things can cause big problems)
1 Arithmetic operators
2 Concatenation operator
3 Comparison conditions
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 Not equal to
7 NOT logical condition
8 AND logical condition
9 OR logical condition

5-Order by Nulls FIRST/LAST Clause If you want to put nulls in the beginning or end of you resultset all you need to do is add

order by ..... nulls first
or
order by ..... nulls last

 
SQL> select * from hr.departments 
   2 order by manager_id nulls last;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           90 Executive                             100        1700
           60 IT                                    103        1400
          100 Finance                               108        1700
          230 IT Helpdesk                                      1700
          240 Government Sales                                 1700

5 rows selected.

SQL> select * from hr.departments 
   2 order by manager_id nulls first;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
          240 Government Sales                                 1700
          230 IT Helpdesk                                      1700
           90 Executive                             100        1700
           60 IT                                    103        1400
          100 Finance                               108        1700

5 rows selected.

6- How to see seconds past midnight (dont know how I missed this when I prepare take control of time post)

SQL> select to_char(sysdate,'sssss') seconds_pass_midnight
  2  from dual;

SECON
-----
61546

7- nullif function
Document says NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1.

Say you want to put null for the lenght_diff for employees whose first_name lenght is equal to last_name lenght (dont ask why :) )

SQL> select  first_name,last_name
  2  ,nullif (length(first_name),length(last_name)) lengt_diff
  3  from employees;

FIRST_NAME LAST_NAME  LENGT_DIFF
---------- ---------- ----------
MARKO      JANKO
JOE        JANKO               3

8- Coalesce Function
Document says; COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.

Lets see how ;


SQL> SELECT last_name, employee_id,manager_id,department_id,
  2  COALESCE(to_char(department_id),TO_CHAR(manager_id), 'No manager no department who are you ???')
  3  FROM hr.employees
  4  ;

LAST_NAME                 EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID COALESCE(TO_CHAR(DEPARTMENT_ID),TO_CHAR(
------------------------- ----------- ---------- ------------- ----------------------------------------
OConnell                          198        124            50 50
Grant                             199        124            50 50
Whalen                            200        101            10 10
Hartstein                         201        100            20 20
Fay                               202        201           160 160
Mavris                            203        101            40 40
Baer                              204        101            70 70
Higgins                           205        101           110 110
Gietz                             206        205           110 110
King                              100                       90 90	******************
Kochhar                           101        100            90 90
....................................................................................
....................................................................................
....................................................................................
....................................................................................
Feeney                            197                          No manager no department who are you ??? ****************

107 rows selected.

9- Need of group by clause for nested group by functions.

---single aggregate function

SQL> select avg(salary) from employees;

AVG(SALARY)
-----------
 6461.68224


---nested aggregate functions
SQL> select max(avg(salary)) from employees;
select max(avg(salary)) from employees
           *
ERROR at line 1:
ORA-00978: nested group function without GROUP BY

SQL> select max(avg(salary)) from employees
  2  group by department_id;

MAX(AVG(SALARY))
----------------
      19333.3333

10-Natural Join
Despite coming from a SQL Server DBA background with computer engineering diploma I never heard about natural joins or I heard at university but never cared about it ....

Document says:
A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.

SQL> select employee_id,department_name from employees natural join departments;

32 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2052257371

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    11 |   330 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |             |    11 |   330 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPARTMENTS |    11 |   209 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMPLOYEES   |   108 |  1188 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID"
              AND "EMPLOYEES"."MANAGER_ID"="DEPARTMENTS"."MANAGER_ID")
   2 - filter("DEPARTMENTS"."MANAGER_ID" IS NOT NULL)


11-When joining with JOIN...USING syntax table aliases are not allowed for the joining column

SQL> ---WRONG
SQL> select e.first_name,e.department_id
  2  from employees e join departments d
  3  using (department_id)
  4  where d.department_id=10 and rownum<2;
where d.department_id=10 and rownum<2
      *
ERROR at line 4:
ORA-25154: column part of USING clause cannot have qualifier


SQL> ---WRONG after removing from where
SQL> select e.first_name,e.department_id
  2  from employees e join departments d
  3  using (department_id)
  4  where department_id=10 and rownum<2;
select e.first_name,e.department_id
                    *
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier


SQL> --RIGHT
SQL> select e.first_name,department_id
  2  from employees e join departments d
  3  using (department_id)
  4  where department_id=10 and rownum<2;

FIRST_NAME           DEPARTMENT_ID
-------------------- -------------
Jennifer                        10 

12-ANY/ALL operator for subqueries
My face is going red for not using these two before. I think I never need them :)

Document says same thin for both of them;
Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, =. Can be followed by any expression or subquery that returns one or more values.

The only difference is ANY Evaluates to FALSE if the query returns no rows. ALL Evaluates to TRUE if the query returns no rows.

I won't go deep into but if you not already know them its good practice to play with them

SQL> ---ANY
SQL> SELECT employee_id, last_name, job_id, salary
  2  FROM employees
  3  WHERE  salary < ANY
  4  (SELECT salary
  5  FROM employees
  6  WHERE job_id = 'ST_MAN')
  7  AND job_id <> 'ST_MAN' and rownum<2;

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        132 Olson                     ST_CLERK         2100

SQL>
SQL> ----USING ANY WITH NOT CHECK THE SYNTAX
SQL> SELECT employee_id, last_name, job_id, salary
  2  FROM employees
  3  WHERE  NOT salary < ANY
  4  (SELECT salary
  5  FROM employees
  6  WHERE job_id = 'IT_PROG')
  7  AND job_id <> 'IT_PROG' and rownum<2;

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        158 McEwen                    SA_REP           9000

SQL> ---ALL
SQL> SELECT employee_id, last_name, job_id, salary
  2  FROM employees
  3  WHERE  salary < ALL
  4  (SELECT salary
  5  FROM employees
  6  WHERE job_id = 'ST_MAN')
  7  AND job_id <> 'ST_MAN' and rownum<2;

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        105 Austin                    IT_PROG          4800

SQL>
SQL> ----USING ALL WITH NOT CHECK THE SYNTAX
SQL> SELECT employee_id, last_name, job_id, salary
  2  FROM employees
  3  WHERE  NOT salary < ALL
  4  (SELECT salary
  5  FROM employees
  6  WHERE job_id = 'IT_PROG')
  7  AND job_id <> 'IT_PROG' and rownum<2;

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        100 King                      AD_PRES         24000


13- Using NOT IN with subqueries with NULL values
I knew this but its nice to remind for the ones who doesnt know already. If you use NOT IN for a subquery with possible NULL clause it wont bring resultset because comparing null as you know equals to null. To workaround you can use NVL function.

Look what happens because of 2 NULL values


SQL> select count(*) from departments
  2  where department_id
  3  not in (select department_id
  4  from employees);

  COUNT(*)
----------
         0

SQL> select count(*) from employees
  2  where department_id is null;

  COUNT(*)
----------
         2
SQL>--change nulls with 999999

SQL> select count(*) from departments
  2  where department_id
  3  not in (select nvl(department_id,99999)
  4  from employees);

  COUNT(*)
----------
        16



14- Creating views with check option

To be honest I was totally unfamiliar to "with check option". Document says; Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.


SQL> CREATE OR REPLACE VIEW empv
  2  AS SELECT *
  3  FROM employees
  4  WHERE department_id = 10
  5  WITH CHECK OPTION CONSTRAINT empv10_ck ;

View created.

SQL> update empv set department_id=15 where Last_name='Whalen';
update empv set department_id=15 where Last_name='Whalen'
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

15- Sequences restart from MINVALUE(thanks Radino for the correction) with CYCLE option regardless from the START WITH value on the definition
I never used a sequence with cyle option before but I was thinking that it will re-use the already used ones when you recycle the values but it won't. If you set MINVALUE it will use MINVALUE, not the START WITH value and if you dont set MINVALUE it will use 1 which is default MINVALUE like below.


SQL> CREATE SEQUENCE cos_seq
  2  INCREMENT BY 10
  3  START WITH 10
  4  MAXVALUE 30
  5  NOCACHE
  6  CYCLE;

Sequence created.

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        10

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        20

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        30

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        11

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        21

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
         1

16- NEXTVAL must be used before the initial usage of CURRVAL in a session.

Never tried, never got the error so never heard.


SQL> ---Brand New session
SQL> select cos_seq.currval from dual;
select cos_seq.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence COS_SEQ.CURRVAL is not yet defined in this session


SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        11
SQL> select cos_seq.currval from dual;

   CURRVAL
----------
        11

This is the end of PART-1. I hope I can complete the PART-2 of this series.

Warning: This post is not about what is being asked in Exam. It is about what I learned during my studies. If you want the questions just study and take the exam. Try to think what would you expect from a SQL Expert and study the things you think you would expect.

Update : Dont forget to check the links in Comment of Tonguc for better understanding of SQL Fundementals.

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers