ORA-06502: What Oracle Documentation says about this error?

Here is what Oracle documentation says about this error:

 

 

ORA-06502: PL/SQL: numeric or value error string

 

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER (2).

 

Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

 

 

More Information:

These could be the errors:

This error mainly occurs due to one of the following:
1. If you assign NULL value to a NOT NULL field.
2. If you assign number which is larger than the precision of the field.
3. If you assign character values greater than the field width.

Click here to read more on solving this error

 

ORA-04031: shared memory error

How to solve this error?

1. Find out which application is causing this error. Zero down on which package/procedure is loaded and try to keep it in shared pool by pinning it.
2. Sometimes, the application may not give the errors. In which case, set an event in init.ora, as follows and generate a trace file.

event = "4031 trace name errorstack level 2"

or with 9i and higher and spfiles you can issue

alter system set events='4031 trace name errorstack level 2';

What to look for in the trace?
The trace contains a dump of state objects, when the error occurs.

Look for 'load=X' a few lines below that 'name='[name of the object]. So, this error occurs at the time loading this object. Pin that object in the shared pool, thereby keeping it.

How to Pin objects to shared Pool?
Pinning objects to the shared pool is a key to tuning your shared pool. Having objects pinned will reduce fragmentation and changes of encountering the ORA-04031 error.

You must determine which objects to pin. These are particular to your own database, the application you are running, the size of your database, and the activity on your database.

You need to pin objects when any of these happen:
1. If you have encountered the ORA-04031 already and need to resolve it or
2. You can also pin large packages frequently used by the users. (It is better to pin necessary objects while startup of database.

Pinning a package to shared pool
1. Oracle automatically loads SYS.STANDARD, SYS.DBMS_STANDARD and SYS.DIUTIL.  Here is an example: 
 
pk1 is a package with a variable called dummy.  Assigning dummy to a value and then executing the package will load it into the shared pool: 

Example:-                                                                   
begin  
pk1.dummy := 0 ; /* THIS ASSIGNMENT TO THE DUMMY VARIABLE IS BY */  
end;               /*    EXECUTING THE PACKAGE. */   
                                                                            
2.Then you must pin the package. Here is an example: 
execute dbms_shared_pool.keep(owner.pk1);

Pinning stored procedure/function to shared pool
You can pin procedures and triggers with the dbms_shared_pool procedure. Either procedures or packages can be pinned with the 'P' flag, which is the default value (so you can leave it out). Triggers are pinned with 'R' and anonymous plsql blocks need any letter other than [p,P,r,R] as a flag.  Refer to dbmspool.sql for more documentation. 

Here is an example: 
execute dbms_shared_pool.keep(owner.trigger, 'R');

What objects should I pin?
You can check the x$ksmlru fixed table.  This table keeps track of the objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm. 
             
Because this is a fixed table, once you query the table, Oracle will automatically reset the table, thus, you can only query the table once. Suggestion for workaround: spool the output to a file so you can capture the output for analysis. 
       
describe x$ksmlru        
    
Table or View x$ksmlru          
Name                            Null?    Type             
------------------------------- -------- --------------            ADDR                                     RAW(4) 
INDX                                     NUMBER             
KSMLRCOM                                 VARCHAR2(20)             
KSMLRSIZ                                 NUMBER                     
KSMLRNUM                                 NUMBER            
 
KSMLRNUM stores the number of objects that were flushed to load the large object. KSMLRISZ stores the size of the object that was loaded (contiguous memory allocated) 

We do not need the other columns. 
 
Here is an example of a query you issue to find all the objects that are larger than size 5k which you may want to pin:    

select * from x$ksmlru where ksmlrsiz > 5000;    
                      
In general, pinning SYS.STANDARD ,SYS.DBMS_STANDARD & SYS.DIUTIL which are large packages used by Oracle, should help.   
 
SYS.DIUTIL is used only during generations of SQL*forms so it may not be necessary to pin this package in your production database. 

A Basic Tutorial on Oracle9i Forms and Reports

Covering Developer version 9i for Windows NT/2000/XP By Richard Holowczak


This tutorial introduces the Oracle9i Developer Suite Release 2 (August, 2002) that includes Oracle Forms 9.0 and Oracle Reports 9.0. The main objectives are to demonstrate and provide hands-on instructions on creating and modifying data entry and query forms in various configurations, reports and graphics.

Caveats: Please note that Oracle tends to change things like menu items, prompts and other small things between each major (certainly) and often minor release. Depending on the exact release of Oracle9i Developer Suite you have, you may find some small discrepencies between what is shown in this tutorial and what you see on your screen.

Prerequisites
Before following this tutorial, a student must have a valid user account in an Oracle server or a local installation of an Oracle database. Contact your DBA or systems administrator to learn the details of how Oracle server is set up in your organization.

If you have a local instance of the Oracle database, please do not use the SYSTEM (DBA) account to practice development. There is a good chance you can corrupt the entire database by mistake. You are much better off making use of the SCOTT/TIGER schema or (even better) creating a separate user (schema) for this tutorial work and for practicing.

One more word of caution. Oracle has designed Oracle9i as a three tier architecture. This means that the database (Oracle9i server) should run on one server, the application Server (oracle9i Application Server or Oracle9iAS Containers for Java (OC4J) server as used here) runs on a second server and the client application (written in Oracle Forms and Reports for example) runs on a third machine.


The tutorial is available here.



About the Author: Richard Holowczak is an Associate Professor of Computer Information Systems at the Zicklin School of Business in Baruch College, City University of New York.

ORA-25153-Temporary Tablespace is Empty-Error solved

*** SOLVED ***

We have a linux server in our office, and yesterday the machine got restarted (power failure). We were able to bring up our Oracle database server afterwards but there was some problems with temporary tablespace.

We were running a procedure when we encountered this error:

ORA-25153: Temporary Tablespace is Empty

Once querying dba_temp_files view, there were no temp files being listed. Though the temporary file was residing in the oradata folder and the dba_tablespaces where the tablespace was present. 

The Solution:
As it was a temporary tablespace, I could add a tempfile to the same and made the procedure working:

ALTER TABLESPACE temp ADD tempfile '/fullpath.dbf' SIZE 50M;

The error ORA 25153 got solved.

NOTE: Do not give the same name as the existing file in oradata folder. Let the file REST IN PEACE, give a new name. For example if you have temp01.dbf existing create with temp02.dbf.

Installing Oracle silently

Oracle Universal Installer by default installs any oracle products in its GUI mode. In situations where the GUI could not be started, it is not possible to install Oracle, such as in Linux environments if X Server could not be started it is difficult to install any Oracle products.

Moreover for a DBA, it is very cumbersome to sit and click buttons for installation. For automating purposes, Oracle Universal Installer (OUI) provides a silent mode of installation.

The runInstaller script which is used for calling the OUI has some switches which can be used to achieve this functionality.

./runInstaller -record -destinationFile  
./runInstaller -silent -responseFile

Here is how:

First Step
./runInstaller -record -destinationFile ResponseFile.txt

The record parameter tells the installer to write to the response file and the destinationFile parameter defines the name of the response file. Once the response file is created you can run the installer in silent mode using the following command:

Second Step
./runInstaller -silent -responseFile ResponseFile.txt

The silent parameter indicates the OUI to install the product using the parameters found in the file passed throught responseFile parameter.

Once the installer stops for executing root.sh as root user, the installation will be completed.

Take your 6i Certification exams soon

The Oracle Internet Application Developer Release 6i Certified Professional (OCP) certification and its related exams will be retired effective April 30, 2009. Exams for this certification may taken up to this date, after which time they will no longer be available through Prometric or any other avenue.

Those working with Oracle PL/SQL and Forms should consider the newer Oracle PL/SQL Developer Certified Associate (OCA), Oracle Advanced PL/SQL Developer Certified Professional (OCP), and Oracle Forms Developer Certified Professional (OCP) certifications.

EXAM RETIREMENTS:

Oracle will retire the following four related exams:

Exams 1Z0-001 and 1Z0-101 are recognized toward several Oracle certifications that are not being retired. Exams passed prior to April 30, 2009 will be accepted and will count towards any track not retired.

ALTERNATES FOR EXAM 1Z0-001:

Exam 1Z0-001 is just one of four SQL-related exam options. The three other exams that may be taken in lieu of this exam are:

These exam options are available now. To find out more, visit the appropriate track page in the Quick Links below.

6i CERTIFICATION VALIDITY :

Note that the Internet Application Developer Rel. 6i OCP certification credential remains valid indefinitely. This retirement therefore has no effect on those currently holding the certification as well as those who complete the certification requirements and earn the certification before April 30.

Fastest spool: SQL Plus Vs UTL_FILE

I got a following query from Suvankar Das.

"I need to unload few Oracle tables with average data volume of 15M records to flat files. Is the SQL* Plus spool a good option for this volume of data from performance point of view OR is it better to do through PL/SQL UTL_FILE option."

And this is how I responded:

"SQL* Plus will have the fastest result to the flat file. Only use UTL_FILE if you need some more programmatic control in between the spooling.

To make sure first take a sample set of data (some thousands of records) for spooling using SQL *Plus and do the same using UTL_FILE. Compare both the timings.

Also based on the sample estimate the time for the entire 15M records."

The Verdict:
There is no hard rule that says either SQL *Plus or UTL_FILE will perform faster. It varies from environment to environment.

Where SQL *Plus/UTL_FILE could perform faster
SQL *Plus is a client program which is accessing Oracle database. Therefore it highly depends on the network. If the network traffic to the server is slow, then SQL *Plus will perform slower. No doubt. If the server and client are in same machine expect SQL *Plus to perform faster.

UTL_FILE is a server-side program residing within the Oracle database server. Use this procedure if you need to spool something in the server. This will perform faster in such cases. In client machine it always depends on the network load. Whether you are using SQL *Plus or UTL_FILE the network load is always a factor to check with.

Oracle XE-Data Uploading from CSV file

This is a step-by-step guide on the simplest of the simplest ways by which data can be uploaded from CSV file to Oracle table. I have done uploading using this way for as many as 10,000 records without any performance issues. It went very swiftly.

Login to OracleXE first and follow the instructions below:

Step 1 - Click Utilities


Step 2 - Click Data Load/Unload


Step 3 - Click Load


Step 4


Step 5 - Click Load Spreadsheet Data


Step 6 - Follow the screen


Step 7 - Follow the screen


Step 8 - Follow the screen


Step 9 - Follow the screen


Step 10 - Follow the screen


Unloading oracle data to flat files

Today I was answering one of the queries, this question seriously made my interest. The question was

"I have 15 million records in my table. What do you suggest a best method for unloading them from Oracle table to a flat file".

Seriously till that time I had never done this. The need had not come. I clearly know two ways of doing this:
  • Using SQL Plus and SPOOL command
  • Using UTL_FILE built-in package
UTL_FILE vs SPOOL
I know that SPOOL command will be faster in execution than UTL_FILE package, but also I reminded myself that this may not be the case always. SPOOL commands operates in the client machine and depending upon the network traffic it might take time for the SPOOL operation than UTL_FILE. 

SO I ADVICE TO TEST BOTH OPTIONS IN YOUR ENVIRONMENT BEFORE FINALISING ON ONE OF THE ABOVE.

I will soon publish routines for this purpose. Your patience is appreciated.

More resources for your inquisitive mind
A little surfing did a wonderful job in putting this article up. In asktom I found a beautiful document that should be the starting page for such exploration. Here is the link for you. I am not going to discuss how tom reviews this anymore. Check the link for yourself.

If you are not having Oracle client in your machine and still you need to unload the data to flat file, follow this link and introduce yourself to a tool OraCmd. I have not personally used this tool, but from the description in the website it promises to be one. I will test the performance issues with this as well in the near future. Here is the link for unloading using OraCmd.

Making Oracle Case sensitive, again

This article is a continuation on Making Oracle Case Insensitive. Read that first to understand the context discussed here.


Oracle is always Case Sensitive while querying data. Now we have learned how to make Oracle Case Insensitive for queries. Let us ask some questions to get going.

What is the purpose of making Oracle Case Insensitive?
By making Oracle Case Insensitive, you need not any more use Case mixed words. For example Test, TEST, tESt are all same once you have made Oracle Case Insensitive. Its use could be in Data warehousing applications.

How to make Oracle Case Sensitive back again after this?
You have to alter two session paramaters NLS_COMP and NLS_SORT.

First set NLS_COMP to binary.

ALTER SESSION SET nls_comp=binary;

Secondly set NLS_SORT to generic.

ALTER SESSION SET nls_sort=GENERIC_M_CI;

Once these variables are altered Oracle is back in business as case sensitive.

SQL Developer for Linux FREE Download





Download Now 68.4 MB





Oracle's description of SQL Developer



Oracle SQL Developer is a free and fully supported graphical tool for database development. With SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own.

SQL Developer enhances productivity and simplifies your database development tasks.

Screen shot

Click on the image to view


Documentation


Click here to access SQL Developer Documentation

Compile full schema in Oracle

To compile an entire schema in Oracle, there are two utilities provided by Oracle.

  • DBMS_UTILITY.COMPILE_SCHEMA
  • Two procedures in UTL_RECOMP

DBMS_UTILITY.COMPILE_SCHEMA
This package prior to 10g would recompile all objects (prior to Oracle 10g) and optionally recompile all Invalid Objects (starting from Oracle 10g). This procedure is available with all schemas (there is no need for giving additional privileges).

I am using Oracle 10g (10.2.0.3.0) for the following examples.

Syntax:
DBMS_UTILITY.COMPILE_SCHEMA( schema VARCHAR2, compile_all BOOLEAN, reuse_settings BOOLEAN);

Example (from SQL *Plus):
EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'SCOTT', compile_all => FALSE);

For recompiling all objects irrespective or VALID or INVALID use the following example (simply omit the compile_all parameter):

EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'SCOTT');

UTL_RECOMP Package
This package has been introduced from Oracle 10g Release 2, which is specially designed for recompiling invalid objects. It recompiles PLSQL modules, Java procedures etc. Only a SYSDBA account can run the subprograms in this package. But always a DBA can grant EXECUTE privilege to your schema (or PUBLIC).

There are two ways in which invalid objects are recompiled if you choose to use this package.
  1. Sequentially
  2. Parallelly

By using the subprogram RECOMP_SERIAL for compiling invalid objects sequentially one by one. The syntax of the subprogram is as follows:

UTL_RECOMP.RECOMP_SERIAL( schema VARCHAR2, flags BINARY_INTEGER);

Simply call the program as follows:

EXEC UTL_RECOMP.RECOMP_SERIAL (schema => 'SCOTT');

The subprogram RECOMP_PARALLEL is available within this package UTL_RECOMP to enable multiple threads for recompiling invalid objects. The syntax of the procedure is as follows:

UTL_RECOMP.RECOMP_PARALLEL (threads BINARY_INTEGER, schema VARCHAR2, flags BINARY_INTEGER);

Note: By using more number of threads the recompiling could be slow taken to consideration the I/O intensive nature of recompiling invalid objects. There is no optimized number of threads to be used while recompiling, but as a rule of thumb use minimum number of threads as possible.

Example of usage of RECOMP_PARALLEL procedure:
EXEC UTL_RECOMP.RECOMP_SERIAL (schema => 'SCOTT');

Coming soon: Article on comparison of these procedures.

How to rename a table in Oracle?

There are two ways of renaming a table in Oracle:

Method 1: Simple

rename {old_table_name} to {new_table_name}

Example:

rename CUSTOMER to CUSTOMER_BACKUP

Method 2: Not so Complex

alter table {old_table_name} rename to {new_table_name};

Example:

alter table CUSTOMER rename to CUSTOMER_BACKUP;

The minimum version that supports table renaming is Oracle 8i. All the dependencies of the table will automatically updated. No need of updating them after wards.