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

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



&#91;/sourcecode&#93;



This is is the <strong>failed one,</strong> 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 <a href="http://forums.oracle.com/forums/thread.jspa?forumID=75&amp;threadID=873966" target="_blank">this link</a> 



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)

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.