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 [/sourcecode] 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&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.
Thanks for the input.
Comment by B. Polarski — March 19, 2009 @ 3:03 pm
Hi Coskan,
you are right – there are so many SQL statements not used in an ordinary DBA/Developer day…
I passed 1Z0-047 too, but some questions were strange to me. 🙂
I learned some new powerful SQL thingies…
Comment by Dejan Topalovic — March 26, 2009 @ 1:00 pm
[…] Edit (26.03.2009.): Naletio sam na još par korisnih informacija o sadržaju sa ovog, pa koga zanima, neka baci pogled. What I learned during Oracle SQL Expert Exam Study Part-1 What I learned during Oracle SQL Expert Exam Study Part-2 […]
Pingback by » Oracle Database: SQL Certified Expert (1Z0-047) — March 26, 2009 @ 1:10 pm
Hehehe, I knew most of that( won’t say all 😉 ) .
But, I agree, SQL , at least for me, is the hardest nut to crack. I still remember when I wrote my 9i sql exam, almost all the time I was asking myself, heck, is it really possible or oracle people are just making fun of me :-). I shall take some time to write the SQL Expert exam, not in a hurry to be expert in it 😀
Regards
Aman….
Comment by Aman.... — March 31, 2009 @ 12:02 pm