Featured blog about application express from an ACE

This morning while loosing myself between the blogger links i found a nice blog which i wish i could find it 2 months ago while i was facing problems with Oracle XE installation. Blogs owner Sergio Leunissen is Oracle ACE and employee of Oracle as a Director of Linux Engineering Team.

If you are interested about Oracle, Application Express, and Linux there are good stuff to read on his blog.

Oracle Database 10g Express Edition (XE) Installation Issues Roundup entry helped me on the causes and solutions of the errors about installing Oracle Database 10g Express Edition.

you can find the solutions of

ERROR: ORA-01041: internal error. hostdef extension doesn't exist

ERROR: ORA-12638: Credential retrieval failed

errors from this blog.

have a nice reading.

Do Primary/Unique Keys need Index??

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

Using Datapump Restart Capability for ORA-39095

I couldn’t update my blog for 4 days because I was struggling with installing RAC on my new laptop with my colleague Orhan BIYIKLIOGLU who is the best technology man and sysadmin I have ever met (He is something like walking wikipedia of technology). If he didn’t help me I would be still working on the error messages of ASM and Linux etc. Thank you Orhan :). I will tell the story of installing RAC on my laptop soon but todays topic is about restart capability of datapump jobs. Yesterday night I had a call from the operator about failed export job messages on Open View console.

When I look at the export log of daily routine schema exports, I saw that only one table skipped with error code

ORA-31693: Table data object ****** failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-08103: object no longer exists

After a little search on Metalink I found only a bug which did not suit enough (Metalink Note:3853023.8).

I decided to take another data pump export for only the skipped table. After 30 seconds from expdp command data pump job took the error below

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Job “SYSTEM”.”SYS_EXPORT_TABLE_03″ stopped due to fatal error at 23:51

I checked the free space on data_pump_dir and saw that there was enough space.

The jobs descriptions says the action for the solution is to reattach the job and add additional dump files.
Error: ORA-39095 (ORA-39095)
Text: Dump file space has been exhausted: Unable to allocate %s bytes
—————————————————————————
Cause: The Export job ran out of dump file space before the job was
completed.
Action: Reattach to the job and add additional dump files to the job
restarting the job.

I looked to the manual and thought that this is something about the unsetted filesize parameter on parfile. My last decision about the problem; the table was huge (over 80GB) so the datapump couldn’t calculate an initial size and thought that there wasn’t enough space on the data pump directory.

I queried the dba_data_pump_jobs view about the status of the job

SQL> select job_name,state from dba_datapump_jobs;

JOB_NAME STATE
—————————— ——————————
SYS_EXPORT_TABLE_03 NOT RUNNING

To solve the problem I have to add another file to the job and restart it. But how ?? All the answer is in the online Oracle® Database Utilities 10g Release 1 (10.1) documentation . The commands attach , add_file,continue_client . (Do not put ; after the commands !!! )

so I connected to the data pump console and run the commands below

expdp system/***** attach=SYS_EXPORT_TABLE_03

Export: Release 10.1.0.4.0 – 64bit Production on Tuesday, 20 March, 2007 23:53

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

Job: SYS_EXPORT_TABLE_03
Owner: SYSTEM
Operation: EXPORT
Creator Privs: FALSE
GUID: 2C22B38216C04297E044000E7FEDD1FD
Start Time: Tuesday, 20 March, 2007 23:53
Mode: TABLE
Instance: *****
Max Parallelism: 4
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** parfile=/backup/****/PARFILES/userdata_tablename.dat
DATA_ACCESS_METHOD AUTOMATIC
ESTIMATE BLOCKS
INCLUDE_METADATA 1
LOG_FILE_DIRECTORY DATA_PUMP_DIR2
LOG_FILE_NAME expdp_tablename_20032007.log
TABLE_CONSISTENCY 0
State: IDLING
Bytes Processed: 0
Current Parallelism: 4
Job Error Count: 0
Dump File: /backup/****/expdp_tablename_20032007.dmp
bytes written: 180,224

Worker 1 Status:
State: UNDEFINED

Worker 2 Status:
State: UNDEFINED
Object Schema: *****
Object Name: *****
Object Type: TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 1

Worker 3 Status:
State: UNDEFINED

Worker 4 Status:
State: UNDEFINED

Export>add_file=expdp_tablename_20070320_2.dmp

Export> continue_client
Job SYS_EXPORT_TABLE_03 has been reopened at Tuesday, 20 March, 2007 23:53
Restarting “SYSTEM”.”SYS_EXPORT_TABLE_03″: system/******** parfile=/backup/******/PARFILES/userdata_tablename.dat
. . exported “********”.”*******” 72.46 GB 208475338 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_03″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_03 is:
/backup/*****/expdp_tablename_20070320.dmp
/backup/****/expdp_tablename_20070320_2.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_03″ completed with 1 error(s) at 00:47

References Used :

Oracle® Database Utilities 10g Release 1 (10.1)

Metalink Note:289404.1

Metalink Note:

Thomas Kyte Collections from Robert Vollman

Today I found a nice blog of a big Thomas Kyte Fan, Robert Vollman.

He listed his all time bests threads and Thomas Kyte style answering classics from the http://asktom.oracle.com I think you all must read those if you are a bit interested with Oracle World.

Fun With Tom Kyte

40 Tips From Tom

Viewing plans of old SQLs

Previously I wrote about how to view a plan of a sql. Today I will tell you about a good feature DBMS_XPLAN.DISPLAY_AWR function comes with Oracle 10G which helps you to view plan of an old sql. . If you have license for tuning pack and diagnostics pack you can get historical information about the old SQLs which ran on your database. For more info about licensing feature of these packs refer to the Oracle® Database Licensing Information 10g Release 1 (10.1) manual

DBMS_XPLAN.DISPLAY_AWR displays the contents of an execution plan stored in the AWR.

Syntax is;

DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);

If db_id paramater is not specified the function will use the id of the local db.

If you don’t specify plan_hash_value parameter, function will bring all the stored execution plans for the given sql_id

Format parameter have so many capabilities you can get the list from the manual.

Simple demonstration ; (all tests are done with 10.2.0.1 Express Edition)

code listing 6a

You can also use DBA_HIST_SQL_PLAN table for viewing the historic plan info.

code listing 6b

References Used ;

Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)

Oracle® Database Reference 10g Release 2 (10.2)

Changing the Current Schema

It is  time consuming process for me to put the schema name in front of the objects from that schema when i am doing maintenance processes on the objects of the schema. DBA ‘s usually work with system or sys users and they have to do this alias process for not to face with ORA-00942 error (table or view does not exist). But if the connected user doesn’t have permission on the current schema object you will still get ORA-00942.

To solve this problem you can use current_schema session parameter.

alter session set current_schema=SCHEMA_NAME /*without quotes*/

Usage;

idle> connect scott/tiger@xe;
Connected.
scott@XE> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

scott@XE> create table t1 as select * from user_sys_privs;

Table created.

scott@XE> select * from t1;

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

scott@XE> grant select on t1 to hr; –for testing with hr

Grant succeeded.

scott@XE> connect hr/hr

Connected.

hr@XE> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

hr@XE> create table t1 as select table_name from user_tables;

Table created.

hr@XE> select * from t1; –its own table

TABLE_NAME
——————————
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
MUNEVVER
COSKAN
FLASHBACK_TEST
COUNTRIES

10 rows selected.

hr@XE> alter session set current_schema=scott;

Session altered.

hr@XE> select * from t1; –t1 from the scott schema

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

hr@XE> connect system/*****
Connected.
system@XE> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

system@XE> alter session set current_schema=scott;

Session altered.

system@XE> select * from t1;

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

system@XE> alter session set current_schema=hr;

Session altered.

system@XE> select * from t1;

TABLE_NAME
——————————
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
MUNEVVER
COSKAN
FLASHBACK_TEST
COUNTRIES

10 rows selected.

!!!!! Don’t forget to change your current schema settings after your operations on other schemas.

Use the query below to get your current_schema info

system@XE> SELECT SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’) from DUAL;

SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’)

—————————————————————————————————-

HR

Autocommit with JDBC connections

I hate software development all my IT life. I don’t know why but writing code (not query or simple pl/sql block) never satisfy me. For this reason i try to be far away from programming languages so i can never feel myself free about using code technics in this blog but now i have to give you an important clue about JDBC connections for the developers who works with Oracle (especially for the new ones).

Oracle never uses autocommit for transactions because of its optimistic locking mechanism which is the best point it differs from other RDBMS s which are autocommit by default. But when you use APIs ODBC and JDBC the connection is autocommit by default and this is against the  nature of Oracle.

suppose you have balance table and you are trying to transfer money from account 10 to account 20.

t1 => update balance set balance=balance-1000000 where account_id=10;

t2=> update balance set balance=balance+1000000 where account_id=20;

If you do this update with autocommit feature you take the risk of loosing 1 million dollars at a system fail between the t1 and t2.

normally in Oracle you do this two staments together and commit or rollback after all the transaction is done or fail.

To avoid this situation you must set to off the autocommit option of your JDBC connection

connection conn= DriveManager.getConnection (“jdbc:oracle:oci:database”,”hr”,”hr”);

conn.setAutoCommit (false);

References Used:

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solution
(Mr Thomas Kytes Book Pg=272)

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

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)

converting scn to a timestamp

I saw the nice function below while i was reading about locking and latches section of Mr Thomas Kytes Book (Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions) and i said to myself i have to share it for the ones like me 🙂

If you know the SCN (system change number) you can get its timestamp value (within about +/–3 seconds) by the function scn_to_timestamp. After looking to the manual for more info i saw two other nice functions about scn. They are all under DBMS_FLASHBACK package and not available for the releases prior to 10g. I found these functions useful for dataguard issues recovery issues and flashback issues.

GET_SYSTEM_CHANGE_NUMBER: for getting the current system change number of the database.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
884871
SCN_TO_TIMESTAMP: for converting given scn to timestamp value ;

SQL> select scn_to_timestamp(884871) as timestamp from dual;

TIMESTAMP
—————————————————————————
09/03/2007 14:52:02,000000000

TIMESTAMP_TO_SCN : For getting SCN equivalent of the given timestamp value. You must do to_timestamp convertion for the character value.

SQL> select timestamp_to_scn(to_timestamp(’08/03/2007 14:24:54′,’DD/MM/YYYY HH24:MI:SS’)) as scn from dual;

SCN
———-
845396

References Used:

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solution Mr Thomas Kytes Book Pg 244

Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

How to grant on v$ views

Today i was faced with an error when I try to give permission on a v$view

SQL> grant select on v$session to hr;
grant select on v$session to hr
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
I wonder why i couldn’t give select privilage to a v$ view. I tried to give to permission to another v$ views but the error was same. Action for this error on error codes manual was meaningless to me (Action: You may only select rows from fixed tables/views.)

From a little googling effort i saw that the problem is caused because of trying to give select privilage on a synonym. Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym.

If you want to give permission to a V$ view you must give it like below

SQL> grant select on v_$session to hr;

Grant succeeded.