Coskan’s Approach to Oracle

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
&#91;/sourcecode&#93;

<strong>2- null with logical operators . </strong>( I felt really shamed that I didnt know (null&amp;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
&#91;/sourcecode&#93;

<strong>3- Escape clause to avoid wildcard characters. </strong>

To use wildcard characters as normal character you can use Escape clause with Like</pre>


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 

&#91;/sourcecode&#93;

<strong>12-ANY/ALL operator for subqueries </strong>
My face is going red for not using these two before. I think I never need them ūüôā

<strong><a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/conditions002.htm#sthref2792" target="_blank">Document </a></strong> 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 =, !=, &gt;, &lt;, =. 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


&#91;/sourcecode&#93;

<strong>13- Using NOT IN with subqueries with NULL values</strong> 
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.

November 12, 2008

How to downgrade EM Repository

Filed under: Basics, Tips — coskan @ 3:05 pm

Nowadays, I am studying on to upgrade my OCP certificate to 11G. Despite that  none of the excellent data guard enhancements covered,  exam topics are still very satisfactory to cover new features for a DBA of Oracle Database 11G. If you try what you read you can go deep new features too. One of them is the necessity of separate EM Repository backup for a possible downgrade. There is a new tool, under 11gORACLE_HOME/bin directory,  called emdwgrd (enterprise manager downgrade) . It can backup your EM Repository before you upgrade and then restore it to the downgrade if you rollback the upgrade process. If you dont use this tool upgraded EM Repository cannot be used.

Here is how it works.

Before you start the actual upgrade (manual or DBUA) you ran the emdwgrd from new 11gORACLE_HOME/bin directory like below;

emdwgrd -save -sid old_SID -path save_directory

-sid is the SID of the database being upgraded
-path is the directory for the backup of em respository.

Path you choose must be emty otherwise you will get the error I got ūüôā

C:\oracle\product\product\11.1.0\db_1\BIN>emdwgrd -save -sid ORACOS -path C:\oracle\product\10.2.0\flash_recovery_area
Enter sys password for database ORACOS?
********
Error: C:\oracle\product\10.2.0\flash_recovery_area must be an empty directory.

Output of the command ran  is like below

C:\oracle\product\product\11.1.0\db_1\BIN>mkdir C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade
C:\oracle\product\product\11.1.0\db_1\BIN>emdwgrd -save -sid ORACOS -path C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade
Enter sys password for database ORACOS?
********
Tue Nov 11 11:19:44 2008 – Verify EM DB Control files … pass
Tue Nov 11 11:19:44 2008 – Validating DB Connection to ORACOS … pass
shared = 0
Tue Nov 11 11:19:49 2008 – Creating directory … created
Tue Nov 11 11:19:50 2008 – Stopping DB Control … stopped
Tue Nov 11 11:20:10 2008 – Saving DB Control files
… saved
Tue Nov 11 11:21:46 2008 – Recompiling invalid objects … recompiled
Tue Nov 11 11:22:46 2008 – Exporting sysman schema for ORACOS … exported
Tue Nov 11 11:25:48 2008 – DB Control was saved successfully.
Tue Nov 11 11:25:48 2008 – Starting DB Control … started
Tue Nov 11 11:26:28 2008 – Dump directory was dropped successfully.

As you see it creates a temporary database directory in the database and dump the file using this directory including sysman schema export. After everything is finished it drops the temp database directory.

Directory structure of the backup  is like below. It has backup and logs directories

C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade>dir
Directory of C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade12/11/2008 11:39 backup
12/11/2008 11:38 logs
0 File(s) 0 bytes
4 Dir(s) 8,887,455,744 bytes free

In backup directory there are backups files including sysman schema export and copy of sysman and servername_ORACLESID directories related with EM under ORACLE_HOME directory

C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade\backup>dir
Directory of C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade\backup11/11/2008 11:25 13,000,704 EXPORT.DMP
11/11/2008 11:25 30,461 export.log

11/11/2008 11:21 sysman
11/11/2008 11:20 XXXXXXXXXXXXXXXXXX_ORACOS

In the logs directory you can view the logs of the backup proces

C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade\logs>dir
Directory of C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade\logs11/11/2008 11:21 115,427 copysave.log
11/11/2008 11:26 132 emctl_startsave.log
11/11/2008 11:20 124 emctl_stopsave.log
11/11/2008 11:22 0 export_dmp.log
11/11/2008 11:26 2,251 sqlsave.log

After this finish you can do your upgrade. Lets assume that you did your upgrade and decide to go back. After you finish the database downgrade steps you can now do post downgrade process for EM Repository. To do this you must first run the emca utility from 11gORACLE_HOME to copy the respository files to new oracle home.

C:\oracle\product\product\11.1.0\db_1\BIN>emca -restore dbSTARTED EMCA at 12-Nov-2008 11:57:06
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
ORACLE_HOME for the database to be restored: C:\oracle\product\10.2.0\db_1
Database SID: ORACOS
Listener port number: 1522
Password for SYS user:
Password for SYS user:
Do you wish to continue? [yes(Y)/no(N)]: coskan12
Invalid input.
Do you wish to continue? [yes(Y)/no(N)]: Y
12-Nov-2008 11:59:42 oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\product\cfgtoollogs\emca\oracos\emca_2008_11_12_11_57_04.log.
12-Nov-2008 11:59:43 oracle.sysman.emcp.EMDBPreConfig checkRestoreParams
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Enterprise Manager configuration completed successfully
FINISHED EMCA at 12-Nov-2008 11:59:51

As you see on the warning EM version must still be downgraded. When I connected to EM I can only be able to manage non EM specific actions as mentioned (sorry I couldnt upload the file ūüė¶¬† proxy issues )

Its time to finish EM Downgrade operation. Command is similar with two difference we need to use -restore option instead of -save and we need to give default temporary tablespace name for sysman schema. If we don’t give temp tablespace it will fail.

emdwgrd -save -sid old_SID -path save_directory -tempTablespace temptablespace

C:\oracle\product\product\11.1.0\db_1\BIN>emdwgrd -restore -sid ORACOS -path C:\oracle\product\10.2.0\flash_recovery_area\em_before
_upgrade -tempTablespace TEMP
Enter sys password for database ORACOS?********
Enter sysman password for database ORACOS?

2*******
Wed Nov 12 11:59:59 2008 – Verify EM DB Control files … pass
Wed Nov 12 11:59:59 2008 – Validating DB Connection to ORACOS … pass
Wed Nov 12 12:00:06 2008 – Validating TEMP tablespace in ORACOS … pass
shared = 0
Wed Nov 12 12:00:08 2008 – Creating directory … created
Wed Nov 12 12:00:12 2008 – Stopping DB Control … stopped
Wed Nov 12 12:01:00 2008 – dropping sysman schema … dropped
Wed Nov 12 12:03:51 2008 – Recreating sysman user … recreated
Wed Nov 12 12:03:54 2008 – Restoring DB Control files
… restored
Wed Nov 12 12:03:54 2008 – Importing sysman schema … imported
Wed Nov 12 12:11:17 2008 – Recompiling invalid objects … recompiled
Wed Nov 12 12:11:46 2008 – restoring dbms_jobs … restored
Wed Nov 12 12:11:47 2008 – restoring dbms_registry … restored
Wed Nov 12 12:13:14 2008 – DB Control was restored successfully.
Wed Nov 12 12:13:14 2008 – Starting DB Control … started
Wed Nov 12 12:13:57 2008 – Dump directory was dropped successfully.

As you see sysman schema is dropped and recreated again. All your scheduler jobs will be restored so you don’t need to create them again.

I hope you wont need to downgrade but its nice to know how to.

Upgraded DB: 10.2.0.4 Windows 32
Upgraded DB: 11.1.0.6 Windows 32


References used :
Oracle¬ģ Database Upgrade Guide 11g Release 1 (11.1)

FYI: Manual is for linux only but nothing special to linux is on the doc for this operation

October 23, 2008

Dictionary views which are not listed in Dictionary View

Filed under: Basics, Tips — coskan @ 4:20 pm

I usually check view called dictionary if I want to learn the name of the dictionary table which I need to query for specific info.

For example if I want to learn which views are available to view SCHEDULER related info I use query below ,

select * from dict where table_name like ‘%SCHED%’;

Then I do my research by querying  the table in the result set.

Today I was looking for which table I need to look for running scheduled jobs I realized that documented

DBA_SCHEDULER_RUNNING_JOBS view is not listed in Dictionary view, I crosschecked DBA_OBJECTS view to see if it is listed there. It was listed under DBA_OBJECTS as both  view and synonym. So what was the problem ?

In the definition of Dictionary view what you see is a union query. First part of the union is like below.

SELECT o.NAME, c.comment$
FROM SYS.obj$ o, SYS.com$ c
WHERE o.obj# = c.obj#(+)
AND c.col# IS NULL
AND o.owner# = 0
AND o.type# = 4
AND (¬†¬† o.NAME LIKE ‘USER%’
OR o.NAME LIKE ‘ALL%’
OR (¬†¬†¬† o.NAME LIKE ‘DBA%’
AND EXISTS (SELECT NULL
FROM SYS.v$enabledprivs
WHERE priv_number = -47 /* SELECT ANY TABLE */)
)
)

UNION_ALL

..
..
..

DBA_SCHEDULER_RUNNING_JOBS view is definitely listed under SYS.obj$ so there must be a problem on SYS.com$ table which holds the comments of both tables and columns.

When I query SYS.com$ with the obj# of DBA_SCHEDULER_RUNNING_JOBS view, I realized that, there isn’t any row where c.col# IS NULL ( I assume the row that holds table comment. )

I think Oracle developers forgot to put a comment on this table. Further investigation was to see which dictionary tables were missed by dictionary table. Basic minus operation lists 36 tables that are missed by Dictionary view on 10.2.0.4.

As a workaround You can still query DBA_OBJECTS but it doesn’t have an indicator that the listed is a dictionary table so you might miss the ones you look for.

To solve the problem I created a DICT_MISSED table and put it as another UNION_ALL statement at the and of the DICTIONARY view source and created MYDICT view. The only problem is, you must refresh DICT_MISSED on every upgrade to catch new missed tables (I hope there will be none if someone from oracle reads this).

Full demonstration scripts

MYDICT view Script.

Note:  WordPressed changed a lot since I wrote for the last time Its a shame that they dont support txt upload anymore pufff

January 16, 2008

If you access a database link in a session, then the link remains open until you close the session (ORA-02020)

Filed under: Basics — coskan @ 5:11 pm

I hit this ORA-02020 error for the first time today and it helped me to learn the truth behind the DB_LINK (I have to confess that I did not read before ). The truth is,” if you access a database link in a session, then the link remains open until you close the session. A link is open in the sense that a process is active on each of the remote databases accessed through the link.”

My case was;

I created a cursor and run a statement which uses all the DB_LINKS on the server. The number of db_links I used was more than 30. The code was getting the ORA-02020:too many database links in use error.

The definition and the solution of the error was simple

Cause: The current session has exceeded the INIT.ORA open_links maximum.
Action: Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling open cursors that reference remote databases.

I checked the open_links parameter (I was unaware that this parameter was existed) and saw that parameter had the default value which was 4 and cannot be modified online. I took the advised action and added commit clause before the cursor gets the new value for the db_link. This solution worked very well.

By the way Document says that, I can close a database link session with “alter session close database link XXXX” clause but it did not worked as I expected. You still need to do commit or rollback before closing a db_link session to do that.¬† Also¬† after commit, the session¬† stays¬† on the remote machine but the sessions are put on list to be closed when a new db_link opened. (list works last in first out (before commit) fashion as can be seen from demonstration ).

Cole Listing 9

The only benefit of alter session close database link is closing a link with knowledge otherwise LIFO will work after commit.

 References Used;

¬†Oracle¬ģ Database Administrator’s Guide 10g Release 2 (10.2)

December 6, 2007

Altering Without Privileges

Filed under: Basics — coskan @ 4:54 pm

There was a mail in Turkish Oracle Users group mailing list about “why a users can still alter a table despite all privileges has been revoked (only connect privilege is left). My answer was to double check if there is any privilege was left by selecting DBA_TAB_PRIVS for that user, but the best answer send by Yasin Baskan (Oracle Today). He claimed and proved that, if the objects are created/owned by that user he can still alter the objects without any privilege. Below is how he proved his claim and a little addition from me (privileges on different schema test).

First create a user with connect and dba privileges.

usercreate

Create tables with that user under his own schema;

create table

Create tables with that user under HR schema;

create table on hr

Revoke DBA privilage from user; (the costst user is must disconnect after revoke to connect with new privs)

revoke

Test ddl on table ;

ddl

He is still altering but can he create new ?

new

What about the table he created on HR schema, can he alter it ?He couldn’t as I expected because he doesn’t have access to that schema.

hrddl

What privileges user has ? (Checked by find_all_privs.sql script written by Pete Finnegan ). CONNECT and CREATE SESSION

privs

What if other one creates a table on coststs schema. He still has access  as you might guess

otheruser

The privileges comes from schema owning and this is the expected behaviour. You can not disable this option, so  you must think different ways  such as creating different user or moving tables under another schema, to prevent a user from  accessing tablesunder its schem.

Special thanks goes to Yasin  for this entry.

December 5, 2007

Temp Segments in Normal Datafile (ORA-1652)

Filed under: Basics, Tips, Uncategorized — coskan @ 6:44 pm

It has been over 2 months since my last entry, but i have reasons to be offline, like major location and job changes. I moved to UK (London) 2 months ago and joined to a huge organization. I am not just an Oracle DBA anymore, so you should see some SQL Server posts in this blog, please do not panic when you see them ūüôā I am still in an endless love with Oracle.

First of all, I am sorry that I can’t write any adventures about UKOUG because one of my colleagues already arranged his attendance when I joined the company. I hope I will write my own adventures next year.

Now its time to write technical stuff. Today we faced “ORA-1652: unable to extend temp segment”. One of my team members asked my opinion about “why the user getting this error on a normal tablespace instead of TEMP tablespace”. He said, the user was trying to create a table by using “create table xxx as select” clause. At first I thought that this should not happen, but when I re-think, I realized that, this is behaviour is like index rebuild. “Create table as select” must be different then “insert into” clause. In my opinion, It first uses temp segments on a permanent tablespace and if it is successful then it converts the segments as permanent. (Updated: Metalink note 181132.1 Thanks YAS) Lets see how

User HR using USERS tablespace as default tablespace and TEMP as temporary tablespace;

tablespace

Size of the source table T1 is 264MB;

tablesize

Available free space on USERS tablespace is;

free space

Auto Extend option on USERS tablespace is off;

autoextend

So I don’t have enough space for new table T2. Lets see what error is raised with different clauses.

When I try to create table T2 from T1 I got error 01652 which is about using temp segment on default tablespace USERS.

unable to extent temp segment

When I try to insert data to T2 from T1 I got error 01653 which is about cannot allocate normal extent on default tablespace. (where 1=2 syntax is just for creating the same table structure without data)
normal segments

Lets try it about with index rebuild. As you might know you need free space with the same size of index when you to rebuld it.

First set auto extend on with 16MB extents;

Then create an index on T1;

create index

Size of the index is 43MB;

index size

Set auto extent off while free space on USERS tablespace is 6 MB;


autoext

When I tried to rebuild the index I expect to get temp segment error ORA-10252 on normal USERS tablespace, because it is really a temp usage.

rebuild

Moral of the story is, temp segments are not always located on TEMP tablespaces.

updated (11/12/2007): Taken from metalink note id 181132.1

4. Temporary Segments for Permanent Segments Creation
-----------------------------------------------------
Besides sort operations, there are other SQL operations, which also require 
temporary segments: 
--> CREATE PRIMARY/UNIQUE KEY CONSTRAINT
--> ALTER TABLE ... ENABLE PRIMARY/UNIQUE CONSTRAINT
--> CREATE TABLE STORAGE (MINEXTENTS>1)
--> CREATE TABLE AS SELECT
    --> The CTAS creates a data segment in the target tablespace and marks this 
        segment as temporary in dictionary. On completion, the dictionary type 
        is changed from temporary to table. In addition, if the SELECT performs 
        a SORT operation, temporary space may be used as for a standard select.
    --> For a Parallel CTAS statement, each slave builds its own data segment 
        (marked as temporary in the dictionary) from the row source which feeds
        it. 
        Similarly, for Parallel Direct Load or Parallel Insert, each slave 
        process creates its own single temporary segment to load data into.
--> CREATE PARTITION TABLE
--> ALTER TABLE ... SPLIT PARTITION
--> CREATE SNAPSHOT
--> CREATE INDEX
    The CREATE INDEX statement, after sorting the index values, builds a 
    temporary segment in the INDEX tablespace; once the index is completely
    built, the segment type is changed to INDEX.
--> ALTER INDEX REBUILD
    During an index rebuild, besides the temporary segments used to store 
    partial sort (segments built in the user's default TEMPORARY tablespace), 
    Oracle uses a segment which is defined as a temporary segment until the 
    rebuild is complete. 
    Once this segment is fully populated, the old index can be dropped and the 
    temporary segment is redefined as a permanent segment with the index name. 
    The new version of the index, currently a temporary segment, resides in the 
    tablespace where the index is required. Note that the old index segment that
    is to be dropped is itself converted to a temporary segment first (like drop
    ping a table). Therefore, an index rebuild involves three temporary segments,
    one of which is a sort segment, that all may be located in different tablespaces.
--> DROP TABLE
    Oracle first converts the segment to a temporary segment, and starts 
    cleaning up the now temporary segments extents. If the drop is interrupted, 
    the temporary segment is cleaned up by SMON. If the SMON is interrupted by a
    shutdown abort, this may cause serious problem, and the total time to
    cleanup is increased.

April 16, 2007

Take the control of TIME Part1 (Introduction to Date/Time)

Filed under: Basics — coskan @ 2:46 pm

In todays world data is controlled by time and if you don’t know how to control the time, you can’t have enough control over your data. Thanks Larry Ellison and his employees for building Oracle with powerful time manipulating functions and datatypes which you have to know to administer your Oracle DB. On the next lines I’m going to try to explain Date Time datatypes and functions related them within Oracle as in a nutshell paper. At second part of this issue I will try to cover more complex operations using the functions below over date time values.

First of all I want to tell about date time data types of Oracle then I will explain how to use this data types with Oracle functions.

Oracle Date Time Datatypes


DATE Data type: Stores date and time information ( century, year, month, date, hour, minute, and second). width is fixed (7-bytes).

TIMESTAMP Data type: Extended date datatype. It differs from date by ability to store fractional seconds. Fixed width (7 or 11 bytes).

TIMESTAMP [(fractional_seconds_precision)]

–up to 9 digits precision with default 6

TIMESTAMP WITH TIME ZONE Data type: We can call it timestamp with time zone support. It includes time zone offset (the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time‚ÄĒformerly Greenwich Mean Time)). Fixed width (13 Bytes)

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

–up to 9 digits precision with default 6

TIMESTAMP WITH LOCAL TIME ZONE Data type: Another variant of timestamp that includes a time zone offset but it differs from the timestamp with time zone datatype by not storing the time zone offset as a column data. It gets time zone offset information from user’s local session time zone. (this is useful for displaying data-time info of the client in a two tier application). Fixed width (7 or 11 bytes)

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

–up to 9 digits precision with default 6

INTERVAL YEAR TO MONTH Data type: Stores a duration between a period of time by means of year and month. It is usually used for math operations (addition+ subtraction). Fixed width (5 bytes)

INTERVAL YEAR [(year_precision)] TO MONTH

–default precision 2

INTERVAL DAY TO SECONDS Data type: Stores a duration between a period of time by means of days,hours, minutes,seconds.

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

–precision for date is up to 9 with default 2

–precision for fractional seconds is up to 9 with default 6

(more…)

April 2, 2007

Using Virtual Indexes

Filed under: Basics, Tips — coskan @ 2:28 pm

While looking at Mr Julian Dyke’s presentation about index internals I saw a new (at least new for me) feature Virtual Indexes. It is there since the 8.1.5 release but i was unaware ūüė¶ .

These indexes are not physically located on a segment but they have a data dictionary definition (you can’t add a real index with same name of virtual index). Virtual indexes allow user to test a potential advantages or disadvantages of a new index prior to actually building the new index in the database . For example you want an index on a table with 10 million rows and you do not sure about using an index then you can use virtual indexes for having idea about new execution plans.

CBO do not use virtual indexes by default without setting the undocumented parameter “_use_nosegment_indexes” to true and this is something you must consider before deciding to use virtual indexes. Since it is undocumented then it is not supported, unless the Oracle say that you can set. In my tests with virtual index i got similar cost results and same access paths from CBO comparing to real index usage.

=>Index Creation Syntax (you add nosegment clause to instead of storage clauses)

CREATE INDEX INDEX_NAME
ON TABLE_NAME (INDEX_COLUMN)
NOSEGMENT;

You have to generate statistics for the virtual index to get realistic costs. Creation of the index is not enough, it is called virtual but it acts like if it is real so if you want a good index simulation don’t forget the statistics. You can use the syntax below to generate statistics;

SQL>EXECUTE DBMS_STATS.GENERATE_STATS (user,’index_name’)

Don’t forget to set the session wide “_use_nosegment_indexes” parameter .

SQL> ALTER SESSION SET “_use_nosegment_indexes” = TRUE;

=>Sample Demonstration (Tests are done on 10.2.0.1 XE);

code_listing_8

References Used:

Index Internals Presentation of Julian Dyke (www.juliandyke.com)

http://www.idevelopment.info/data/Oracle/DBA_tips/Tuning/TUNING_15.shtml

March 26, 2007

Do Primary/Unique Keys need Index??

Filed under: Basics — coskan @ 7:33 pm

Till 10 minutes ago I was thinking that primary key or unique key constraints always created with their indexes by default. But after reading Tables chapter of Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solution (book of Mr Kyte) all my thoughts changed.

He noted “A unique or primary key constraint may or may not create a new index. If there is an existing index on the constrained columns, and these columns are on the leading edge of the index, the constraint can and will use them.

Here is the proof of the note;

code_listing 7

References Used :

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solution
(Pg 341)

All Test are done on 10.2.0.1 XE

March 14, 2007

Connect Role without Alter Session privilege (ORA-01031: insufficient privileges)

Filed under: Basics — coskan @ 11:54 am

When i am trying to trace an event from user Scott who has connect and resource role privileges i have faced with ORA-01031 error when running the command “alter session set events ‘10132 trace name context forever, level 8’;”. After a bit googling i saw that the connect role has changed for 10G R2 and does not have alter session sys privilege. Official Document says “beginning in Oracle Database 10g Release 2 (10.2), the CONNECT role has only the CREATE SESSION privilege, all other privileges are removed.”


The error stack
(code ran on 10.2.0.1 Express Edition)

SQL> connect scott/tiger;
Connected.

scott@XE> alter session set sql_trace=true;
alter session set sql_trace=true
*
ERROR at line 1:
ORA-01031: insufficient privileges

scott@XE> alter session set events ‘10132 trace name context forever, level 8’;

ERROR:
ORA-01031: insufficient privileges

scott@XE> select * from USER_ROLE_PRIVS;

USERNAME GRANTED_ROLE ADM DEF OS_
—————————— —————————— — — —
SCOTT CONNECT NO YES NO
SCOTT RESOURCE NO YES NO
scott@XE> select * from USER_SYS_PRIVS;

USERNAME PRIVILEGE ADM
—————————— —————————————- —
SCOTT CREATE VIEW NO
SCOTT UNLIMITED TABLESPACE NO
SCOTT CREATE SESSION NO


Investigation for the source of the error

In 10.2.0.1

SQL> select version from v$instance;

VERSION
—————–
10.2.0.1.0

SQL> select * from DBA_SYS_PRIVS where privilege=’ALTER SESSION’ ;

GRANTEE PRIVILEGE ADM
—————————— —————————————- —
DBA ALTER SESSION YES
RECOVERY_CATALOG_OWNER ALTER SESSION NO
CTXSYS ALTER SESSION NO
HR ALTER SESSION NO
FLOWS_020100 ALTER SESSION NO
XDB ALTER SESSION NO

6 rows selected.

SQL> select * from ROLE_SYS_PRIVS where role=’CONNECT’;

no rows selected

In 10.1.0.2

sys@XE> select version from v$instance;

VERSION
—————–
10.1.0.2.0

sys@XE> select * from DBA_SYS_PRIVS where privilege=’ALTER SESSION’ ;

GRANTEE PRIVILEGE ADM
—————————— —————————————- —
IX ALTER SESSION NO
DBA ALTER SESSION YES
SYS ALTER SESSION NO
XDB ALTER SESSION NO
DMSYS ALTER SESSION NO
WKSYS ALTER SESSION NO
CONNECT ALTER SESSION NO
PERFSTAT ALTER SESSION NO
OLAP_USER ALTER SESSION NO
RECOVERY_CATALOG_OWNER ALTER SESSION NO

10 rows selected.

sys@XE> select * from ROLE_SYS_PRIVS where role=’CONNECT’;

ROLE PRIVILEGE ADM
—————————— —————————————- —
CONNECT CREATE VIEW NO
CONNECT CREATE TABLE NO
CONNECT ALTER SESSION NO
CONNECT CREATE CLUSTER NO
CONNECT CREATE SESSION NO
CONNECT CREATE SYNONYM NO
CONNECT CREATE SEQUENCE NO
CONNECT CREATE DATABASE LINK NO

8 rows selected.

After giving alter session privilage to scott the error resolved;
sys@XE> GRANT ALTER SESSION TO SCOTT;

Grant succeeded.

sys@XE>connect scott/tiger

Connected.
scott@XE> alter session set sql_trace=true;

Session altered.

scott@XE> alter session set events ‘10132 trace name context forever, level 8’;

Session altered.
scott@XE> select * from USER_SYS_PRIVS;

USERNAME PRIVILEGE ADM
—————————— —————————————- —
SCOTT CREATE VIEW NO
SCOTT UNLIMITED TABLESPACE NO
SCOTT ALTER SESSION NO
SCOTT CREATE SESSION NO

You can look the official document linked below for the effects of this important change on connect privilege .

References Used :

Oracle¬ģ Database Security Guide 10g Release 2 (10.2)

Oracle¬ģ Database Security Guide 10g Release 1 (10.1)

« Newer PostsOlder Posts »

Blog at WordPress.com.