Coskan’s Approach to Oracle

April 2, 2007

ORA-12838 after direct load

Filed under: Tips — coskan @ 3:02 pm

When I was testing the virtual indexes for the previous post, I got ORA-12838 from the subsequent select after inserting 4 million rows without commit (I mean I forgot committing). The error was

ORA-12838: cannot read/modify an object after modifying it in parallel

The error documentation says ;

Cause: Within the same transaction, an attempt was made to add read or modification statements on a table after it had been modified in parallel or with direct load. This is not permitted.
Action: Rewrite the transaction, or break it up into two transactions: one containing the initial modification and the second containing the parallel modification operation.

Action was not clear for me. When I look at the code I saw that there wasnt any commit when i committed the transaction the error solved.

As a main idea for the blog entry “look twice to your code before taking an action” :)

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

April 1, 2007

Viewing Error Definitions from SQL*plus or Command Line

Filed under: Tips — coskan @ 1:05 pm

Suppose that you faced with an error when you execute a command. What you do to first is usually to look at the error codes documentation of Oracle and read the action you can take against the error. There is an another way to do it in Unix environments using OERR utility. This useful utility could lower the steps of your error code solution research. It is not an executable, it is a shell script and it is flexible for customizing. OERR is located under $ORACLE_HOME/bin directory with a name oerr.ksh. It reads the file $ORACLE_HOME/lib/facility.lis to show the messages. This file contains the paths to messages for the given facalities. (for example ORA errors are related with rdbms folder and the facility.lis files directs the oerr command to the rdbms/mesg/* folder to get the definition of the error code). You can add your own message files to the facility.lis for customized errors or change the definitions of the predefined error codes.

The syntax to call the oerr is below;

#oerr <facility> <error number>

    

     
Usage Samples; (! is used to call the command from the OS command line while you are in sql*plus)
SQL> !oerr ora 07400
07400, 00000, “slemtr: translated name for the message file is too long.”
// *Cause: The name for the message file overflows internal buffer.
// *Action: Try making the complete path-name of the message file shorter
// by reorganizing the directory hierarchy.

=>default ora-01652

SQL> !oerr ora 01652
01652, 00000, “unable to extend temp segment by %s in tablespace %s”
// *Cause: Failed to allocate an extent for temp segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.

=>custimized ora 01652 (I have edited the $ORACLE_HOME/rdbms/mesg/oraus.msg file )

SQL> !oerr ora 01652

// *Cause: Failed to allocate an extent for temp segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.Please be carefull when you are creating TEMPFILES!!!!!

Note: OERR comes with oracle installation on Unix environments and is not implemented for windows by default because of its nature which depends on the awk command however you can install freeware programs for windows too (look at the references)

References Used:

http://www.oracleutilities.com/OSUtil/oerr.html

March 21, 2007

Using Datapump Restart Capability for ORA-39095

Filed under: Tips — coskan @ 8:28 am

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:

March 15, 2007

Changing the Current Schema

Filed under: Tips — coskan @ 8:32 am

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

« Newer Posts

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

Follow

Get every new post delivered to your Inbox.

Join 202 other followers