Showing posts with label Troubleshooting. Show all posts
Showing posts with label Troubleshooting. Show all posts

ORA-01658: Unable to create INITIAL extent for segment in tablespace %s

** Solved **

You will encounter this error when a tablespace is full or cannot extend automatically.

These are the possible solutions (if there are any other ways add a comment):

Solution 1: Resize Tablespace

alter database datafile '' resize M;

Solution 2: Add Datafile

alter tablespace tablespace_name add datafile '' size M autoextend M maxsize M|off>;

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.

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.

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

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.

Simply stating the value being assigned is not a valid value which can be assigned to the string/numeric field.

For example:

declare
test varchar2(1);
begin
test := 'I am here';
end;

The above block gives the error:
ORA-06502: PL/SQL: numeric or value error: character buffer too small
ORA-06512: at line 4

Of course in this you are getting more information as to the character variable is too small to hold the value you are assigning.

Now consider the following example:

declare
test number(2);
begin
test := 100;
end;

The above block gives this error:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4

So now you know more about this frequently occuring error.

Some tips to avoid this error:
1. If you are assigning some values from a table to a variable always use the %TYPE declaration.
For Ex:
declare
test my_table.my_field%TYPE;
begin
select my_field from my_table where id=1;
end;

The above declaration methodology is a very efficient one in handling ORA-06502 error.

2. If you are not assigning the values from a table, but rather from some calculation, then use proper validation methodology.
For Ex:
declare
sum_value number(2);
begin
sum_value := 10 + 90;
exception
when value_error then
dbms_output.put_line('Raise your error here');
end;

3. If you are concatenating two strings together also use the above WHEN VALUE_ERROR in exception block to validate your data.
4. It is better method to put a value assigning code inside a seperate BEGIN END block and validate it using EXCEPTION block.

Hope you are solving this error. Best of luck. For more information, comment in this post with your email id and I will reply back.

Converting date to a date

In this article we will discuss what people end up doing erroneously. As data type Oracle stores DATE in a separate format then Character. But when passing values between two programs, the format may cease to be a Date anymore.

For example there are two independent systems, one which is a Oracle system (which we are going to see) and another foreign system which uses some other technology.

In this case let us also assume that the foreign system is our front-end to the user and Oracle system is the back-end part of the system. Say the front-end system is passing some date value which has been input from user to the database. The value will be interpreted as a Character by the Oracle system.

To convert the Character value to a Oracle-specific date data type, we will be using the function to_date(). The function to_date() accepts a Character value and will convert it into a Date (as understood by Oracle). We also needs to tell to the function what is the format in which the date value is coming. For example the character value 12-FEB-2007 can be converted to Date data type by using to_date('12-FEB-2007','DD-MON-YYY') function call.

Now what normally people do is that they do a conversion of a date data type to a date. That is a call to to_date() with date as input. For example: to_date(sysdate). What such a call will do is that it will implicitly convert the date parameter passed to a Character value, and then will pass it to to_date() function to be converted to a date again. The result is loss of time information. The time part will be truncated in this implicit conversion to Character.

Proof:

SQL> select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
05.07.2007 13:42:00

SQL> select to_char(to_date(sysdate),'dd.mm.yyyy hh24.mi.ss') from dual;

TO_CHAR(TO_DATE(SYS
-------------------
05.07.2007 00.00.00

The moral learned is that never go for an implicit conversion. It is safe to use explicit data type conversions

Cannot directly access remote package variable or cursor

Have you ever encountered error "Implementation Restriction: 'XX.XX': Cannot directly access remote package variable or cursor" while compiling oracle forms? If yes read below to find a method of creating a generic way of assigning values to variables from oracle forms.

Here in this article we will see how to avoid the error "Cannot directly access remote package variable or cursor" from Oracle forms through an example.

We have created a package for test purposes. The name of the package is testpck. Assume it has a lot of variables, say some 200 and you need to assign values to each of the variables from front-end. One way is to create a procedure which assigns values using a rowtype variable. But in this approach we would be writing unnecessary code to attain the logic.

In this example listed below we have used only one variable in the package specification. The variable name is l_variable. For simplicity of discussion I am restricting the variables of type VARCHAR2. This can be further enhanced to provide a logic for all of the datatypes.

Find below the code for package specification:
create or replace package testpck as
l_variable varchar2(255);

procedure l_set_variable_value(l_variable_name varchar2, l_variable_value varchar2);
end;
/

Find below the code for package body:
create or replace package body testpck as
procedure l_set_variable_value(l_variable_name varchar2, l_variable_value varchar2) as
l_sql varchar2(4000);
begin
l_sql := 'begin testpck.'||l_variable_name ||':='||chr(39)||l_variable_value||chr(39)||'; end;';
execute immediate l_sql;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
end;
/

Now to test this procedure:
begin
testpck.l_set_variable_value('l_variable',
'variables value');
end;
/

That's all. Now you can use this procedure to assign value to any of the variables present in the package specification. If an invalid variable name is passed then the following error will pop out:
ORA-06550: line 1, column 15:
PLS-00302: component 'L_VARIABLE1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

PL SQL ERROR 201: SRW.MESSAGE must be declared

If you are unable to compile your reports or plls in Oracle 10g Version, due to the error like

PL SQL ERROR 201: SRW.___ must be declared, then check the existence of the following file and its privilege:

rwrun.jar in /Oracle10gAS/reports/jlib folder.

Where Oracle10gAS is the folder where 10g Application server is installed.

You will normally get this error only in UNIX/Linux flavors, because this is related to privilege rights. If you are getting this error in Windows also, then it is quietly possible that the file is missing.

The file must have the privilege 755 or -rwx-r-x-r-x.