Oracle in World: 7/6/08

Saturday, July 12, 2008

Data Pump Import fails with ORA-39000 and ORA-39140.

Error Description:
---------------------------------------------------
While I gave multiple file name in the dumpfile parameter of expdp it raised error ORA-39000 and ORA-39140.
$impdp prod16k/thword16k directory=d dumpfile=dump.dmp,dump2.dmp,dump3.dmp
Import: Release 10.2.0.2.0 - Production on Sunday, 13 July, 2008 1:56:14

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39140: dump file "/backup1/dump2.dmp" belongs to job "PROD72"."SYS_EXPORT_TABLE_02"

Cause of The Problem:
------------------------------------------
The error happened as data pump can't import several files at a time if they don't produce from a single export job. The files are produced by multiple export operation. At a time we can import dumps produced from a single export operation.

We can get the cause of the error from OS using oerr as follows,
bash-3.00$ oerr ora 39140
39140, 00000, "dump file \"%s\" belongs to job %s"
// *Cause: When a dump file set consists of multiple files, all files
// in the set must be specified for an import operation, and all
// files must have been produced by the same export job.
// One of the files provided does not belong to the original dump
// file set. For instance, it was created by a different export job
// than the other files.
// *Action: Remove the dump file indicated in the message and retry the
// import operation providing only the complete set of
// dump files created by a specific export job.

Solution of The Problem:
-----------------------------------
Remove the file name from dumpfile parameter if they are not created from a single export operation. If both of above three dumps are generated from three different export operation then to import it invoke three separate operation like,

$impdp prod16k/thword16k directory=d dumpfile=dump.dmp
$impdp prod16k/thword16k directory=d dumpfile=dump3.dmp
$impdp prod16k/thword16k directory=d dumpfile=dump2.dmp

ORA-28003, ORA-20002 while changing password

Error Description:
----------------------------
While changing user password ORA-28003 and ORA-20002 raised.
SQL> alter user arju identified by a;
alter user arju identified by a
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 4

Cause of The problem:
----------------------------------
There is a password verify function assigned on the profile of the user. The verify function does not allow to to change password to a and it specifies password length less than 4.

Solution of The Problem:
---------------------------------------
Way 1:
-----------
Change the password as it satisfy the password verfify function. Suppose if function impose restriction password to length at least 4 characters, must have one number and one alphanumeric character and one special character then change the password as following,

SQL> alter user arju identified by "b!12";
User altered.

Way 2:
----------------
If you don't care about password verification function then you may disable the function.
To do it first look for the user profile for which you get the error.
SQL> select profile from dba_users where username='ARJU';
PROFILE
------------------------------
DEFAULT

Then look for the assigned function of the intended profile.
SQL> select RESOURCE_NAME,RESOURCE_TYPE,LIMIT from dba_profiles where PROFILE='DEFAULT' and RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';

RESOURCE_NAME RESOURCE LIMIT
-------------------------------- -------- ----------------------------------------
PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION

Either assigned another function of disable the verification function. You can disable it by,
SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION NULL;
Profile altered.

Now change the password.
SQL> alter user arju identified by a;
User altered.

Related Documents

http://arjudba.blogspot.com/2008/05/password-verification-or-complexity.html

http://arjudba.blogspot.com/2008/05/playing-with-oracle-password-identified.html

http://arjudba.blogspot.com/2008/07/solution-of-ora-28002-password-will.html

http://arjudba.blogspot.com/2008/04/password-is-case-sensitive-in-oracle.html

ORA-02097, ORA-00384 while setting memory components

Error Description:
-----------------------------
While settings dynamic memory components ORA-00384: is raised.

SQL> alter system set db_4k_cache_size=800M;
alter system set db_4k_cache_size=800M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

Cause of The Problem:
--------------------------------------------
This problem can be raised for several reasons.
1)SGA_MAX_SIZE is not set
--------------------------------------------------
The initialization parameter SGA_MAX_SIZE is not set explicitly either in spfile or in pfile and hence its default value at the time of startup is set to sum of the total memory parameters plus some overhead.

You can check your current sga memory by issuing any one of the following in SQL*plus,
show sga;
show parameter sga_max_size;

So, whenever you use ALTER SYSTEM SET to set the value of dynamic memory components like DB_CACHE_SIZE , DB_nK_CACHE_SIZE this will fail as it cannot grow beyond SGA_MAX_SIZE . Thus, the errors which your are seeing is expected.

2)SGA_MAX_SIZE is set but not enough to allow memory to allocate
--------------------------------------------------------------------------------
There may be explicitly set SGA_MAX_SIZE but it is not enough to allow the memory settings as it set by ALTER SYSTEM SET statement. Suppose total sga memory after setting sga dynamic components exceeds SGA_MAX_SIZE.

3)Oracle Bug
---------------------------
There is oracle Bug 4587117 and Bug 4919526 which is responsible to cause the above error.

Solution of the Problem:
----------------------------------
The solution is based on the cause of the error.
For Case 1)
Increase the SGA_MAX_SIZE parameter of the database and bounce the database. As SGA_MAX_SIZE is invoked only at startup so in order to affect the changes database need to be restarted. You can set it in spfile from sql*plus by,

ALTER SYSTEM SET SGA_MAX_SIZE=4G scope=spfile;
SHUTDOWN;
STARTUP;

For Case 2)
If you have limited memory in your system then set the lower value of the dynamic components. Suppose you tried to set 800M which raised error. You may set it lower value which will not raise error. Like,
alter system set db_4k_cache_size=300M;

For Case 3)
Install Server Patch Set from metalink.
Related Documents
http://arjudba.blogspot.com/2008/05/startup-fails-with-ora-27102-out-of.html
http://arjudba.blogspot.com/2008/12/expdp-fails-with-ora-39125-ora-04031.html
http://arjudba.blogspot.com/2009/05/ora-27100-shared-memory-realm-already.html
http://arjudba.blogspot.com/2008/05/startup-fails-with-oracle-error-ora.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-27302.html
http://arjudba.blogspot.com/2008/07/database-startup-fails-with-error-ora.html
http://arjudba.blogspot.com/2008/08/startup-fails-with-ora-01261-parameter.html

Data pump export fails with ORA-39000, ORA-31641,ORA-27038

Error Description:
---------------------------------------
Data pump export fails with following error.
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/backup1/d.dmp"
ORA-27038: created file already exists
Additional information: 1

Cause of the problem:
-------------------------------
Within the dumpfile parameter it was given the name as d.dmp. And directory parameter was set to /backup1. In the location of /backup1 there already exists d.dmp. Oracle data pump will generate error if the file name provided with dumpfile already exists in the destination.

Solution of The problem:
------------------------------------
Either rename the OS file name in the specified directory to a new name and perform export.
$mv /backup1/d.dmp /backup1/d2.dmp
Or, drop the existing file from the OS and perform export if OS file no longer necessary.
SQL> !rm /backup1/d.dmp

Or, rename the directory or dumpfile parameter to a new name so that OS file name does not conflict with the dumpfile+directory name.

$expdp dumpfile=d3.dmp

Related Documents:
--------------------------------

Data pump import fails with ORA-39125, ORA-00379, ORA-06512

Error Description:
-------------------------------
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 4K

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6235

Cause of the Problem:
---------------------------------------------
With using oracle message we get,
SQL> !oerr ora 00379
00379, 00000, "no free buffers available in buffer pool %s for block size %sK"
// *Cause: All buffers in the specified buffer pool for the specified block size
// are in use and no free buffers are available.
// *Action: Increase the number of buffers in the specified pool for the specified
// block size

So we get ORA-00379 when for the specified block size there is no free buffers are available. Here we see from error message we get block size 4K.

Solution of The problem:
----------------------------
As oracle recommends increase the size of the 4K buffer pool.
SQL> alter system set db_4k_cache_size=100M;
System altered.

Thursday, July 10, 2008

ORA-01264, ORA-19800 Permission denied

Error Description:
----------------------------------
Whenever you try to use Oracle Managed file systems while creating any objects like in this case tablespace it fails with error ORA-01264, ORA-19800 Permission denied. On Solaris the test is,
SQL> create tablespace tbs16k blocksize 16K;
create tablespace tbs16k blocksize 16K
*
ERROR at line 1:
ORA-01264: Unable to create datafile file name
ORA-19800: Unable to initialize Oracle Managed Destination
Solaris-AMD64 Error: 13: Permission denied

Or in linux the error stuck is,

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01264: Unable to create logfile file name
ORA-19800: Unable to initialize Oracle Managed Destination
Linux Error: 13: Permission denied

Cause of The problem:
-------------------------------------
The user don't have the permission to write in the specified location. This is Operating system permission.

Solution of The problem:
----------------------------------
Give the necessary permission to the user so that OS user who is running oracle can create file in the specified directory.
First Scenario:
------------------------------------------
In the first case, it is db_create_file_dest destination where create tablespace will try to create datafile.
We can see the location of db_create_file_dest by
SQL> show parameter db_create_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /oradata2/arju
So , now change permission of /oradata2/arju to 777 or change the ownership.

Log on as root and chnage ownership by following.
-bash-3.00$ su
Password:
# chown -R oracle /backup1
# -bash-3.00$ logout

Now try to create tablespace and hopefully it will work fine.

Second Scenario:
--------------------------------
In the second scenario user don't have permission on flash recovery area.
You can see flash recovery area location by,
SQL> show parameter db_recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oracle
Now either change permission(chmod 777) or change ownership of the specified location by,
-bash-3.00$ su
Password:
# chown -R oracle /oracle
# -bash-3.00$ logout

Now try to do operation and hopefully it will work.

Wednesday, July 9, 2008

Version Query Pseudocolumns

The version query pseudocolumns are only valid for flashback version query which are discussed on Flashback Version Query.

The available version query pseudocolumns are,

1)VERSIONS_STARTTIME: This pseudocolumn returns the timestamp of the first version of the rows returned by the query.

2)VERSIONS_STARTSCN: This pseudocolumn returns the SCN of the first version of the rows returned by the query.

3)VERSIONS_ENDTIME: This pseudocolumn returns the timestamp of the last version of the rows returned by the query.

4)VERSIONS_ENDSCN: This pseudocolumn returns the SCN of the last version of the rows returned by the query.

5)VERSIONS_XID: This pseudocolumn returns the transaction ID of the transaction that created that row version.

6)VERSIONS_OPERATION: This pseudocolumn returns a single character representing the operation that caused that row version.

It can have I-For Insert Operation.
U-For Update Operation.
D-For Delete Operation.

Job opportunity as Oracle DBA

Looking for an Oracle DBA in Bangladesh. The details are below.
Job Title:
----------------------
Database Engineer.

Educational Requirements
-------------------------------------
B.Sc in Computer Science or related subject. Educational qualification will be relaxed in case of highly experienced candidate.

Job Requirements
---------------------------------------
•Have clear concept about Oracle Server Administration, Oracle Backup+Recovery,Oracle Utilities(Export/Import,Data Pump Export,Import, SQL*Loader, Extenal Table+Log miner),Oracle Database Security, Oracle Streams Replication.

•Must have practical knowledge in the oracle database field and handling troubleshooting.

•More than 2 years experience will be preferred.

Job Location
-------------------------------
Dhaka

Salary Range:
--------------------
60k-70K in amount of BDT but negotiable for more experienced people.
Apply for the Job
-----------------------------------
If you feel you are suitable one then please e-mail your CV to arjuiut@gmail.com

Tuesday, July 8, 2008

How to Use Special Characters in Windows

In windows how you can insert special characters? In this post I will show two different ways to insert special character on windows operating system.

Method 1: Using Character Map:
---------------------------------------------
1. If Character Map is not installed, you must install Character Map. To install character map
•Click Start> point to Settings> click Control Panel> click Add/Remove Programs.
•Click the Windows Setup tab.
•Click System Tools (click the words only, not the check box), and then click Details.
•Click to select the Character Map check box, click OK, and then click OK.
Your character map will be installed.

2. By default character map is installed on your system. Run the character map.
You can run it by any of the following ways,
•Double click on browsing C\WINDOWS\SYSTEM32\charmap.exe
•In command prompt or in run box write charmap and press enter.
•Click Start> Programs >Accessories> System Tools> and then click Character Map.

3. After starting character map click font box, and select the font you want to use.

4. Click the special character that you want to insert, click Select, and then click select Copy.

5. Now go to the document or place where you insert this character.

6. On the Edit menu, click Paste if you have a document. You can also press ctrl+v from your keyboard. You can also copy characters by dragging them from Character Map directly into an open document.

Method 2: Using Unicode Value
---------------------------------------------
1. Move the cursor to the point where you want to put the special character.
2. Press and hold down the ALT key while you type the four number Unicode value for the character.

For example press and hold ALT key and then press 401. You will find special character text.

To determine what the Unicode value is for a particular character use the following method,

1 Start Character Map which is discussed in this post.
2. In the Font box, click the font you want to use.
3. Click the special character that you want to use. The Unicode number that corresponds to the character you select appears in the lower right corner of the window.

How to know process ID or PID on windows

LRM-00116: ORA-39001 ORA-39071 ORA-00907 in impdp

Problem Description:
------------------------------
While using special character without escape character causes error like LRM-00116: ORA-39001 ORA-39071 ORA-00907.

-bash-3.00$ expdp dba/dba exclude=table:"in('ARJU_DATA','INDIA_ACTIVITY','USA_IMAGE')"
LRM-00116: syntax error at ')' following 'USA_IMAGE'

-bash-3.00$ expdp dba/dba exclude=table:"in$'ARJU_DATA','INDIA_ACTIVITY','USA_IMAGE'$"

Export: Release 10.2.0.2.0 - Production on Tuesday, 08 July, 2008 23:54:13

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00907: missing right parenthesis

Cause of The Problem:
------------------------------------------------
double quotes ("), single quotes (') parentheses () are termed as special character on unix system. To know the list of special character on unix please have a look at Special Character on Unix. In order to use specual character on unix you have to use escape characters. How you can use escape character are discussed on How does one escape special characters.
If you don't use escape characters while using special character above error will arise.

Solution of The problem:
---------------------------------
Use escape character before using special character. Like in stead of using
expdp dba/dba exclude=table:"in('ARJU_DATA','INDIA_ACTIVITY','USA_IMAGE')"

Use,
expdp prod/prod exclude=table:\"in$\'ARJU_DATA\',\'INDIA_ACTIVITY\',\'USA_IMAGE\'$\"

The details of the exclude and include parameters of datapump are described in DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects

Monday, July 7, 2008

Literals and literal Types in Oracle

The word literals in oracle just point to a fixed data value. Suppose in a table dept(id, name) we insert value as (1,'CIT') then both 1 and 'CIT' are termed as literals. Now we we want to insert just CIT data value without single quote into name column then it will generate error.

So numeric values should be entered without quota and character values should be within single quotes.
Based on the data values inserted into a table literals can be classified into four categories.

1)Text Literals
------------------------------
Use the text literal notation to specify values of strings. The format to specify text literal forms any one of the following,
-[N]{'}{character}{'}
-[N][Q]{'}{quote delimeter}{character}{quote delimeter}{'}

Note that {} /braces indicates the mandatory and brackets [] indicate optional.
In the second format of text literals,
•Q or q indicates that the alternative quoting mechanism will be used.
•C or c is any member of the user's character set. You can include quotation marks (") in the text literal made up of c characters. You can also include the quote_delimiter, as long as it is not immediately followed by a single quotation mark.

Using first format method example of valid literals are,
'testing'
N'testing'
'test''s'

Using second format method example of valid literals are,
q'!name LIKE '%ARJU_%%'!'
q'<'So,' I said 'It's going.'>'
q'{SELECT * FROM dept where name = 'ARJU';}'
q'"dept like '['"'

SQL> select q'<'So,' I said 'It's going.'>' from dual;
Q'<'SO,'ISAID'IT'SGOING.'>
--------------------------
'So,' I said 'It's going.'

2)Numeric Literals
------------------------------
•The interger literals form [+|-]{digit} format like +10,9. -100 etc.
•The number and floating point literals hold any one of the following format,
-[+|-]{digit}[.][digit][e][+|-][digit][f|d]
-[+|-][.]{digit}[digit][e][+|-][digit][f|d]
-f or F indicates that the number is a 32-bit binary floating point number (of type BINARY_FLOAT).
-d or D indicates that the number is a 64-bit binary floating point number (of type BINARY_DOUBLE)
The example of valid unmber and floating point literals are +1.23F,.123,1e-9 -1D etc.

3)Datetime Literals
------------------------------------
4)Interval Literals
-----------------------------------------
Both of these are dicussed in
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html

SwingBenchException: Oracle jdbc classes not found

Problem Description:
---------------------------------------
After running swingbench product you get the following message,

11:18:57 07/08 [EROR] UserSession com.dom.benchmarking.swingbench.kernel.SwingBenchException: Oracle jdbc classes not found

Reason of The Problem:
-----------------------------------------
The Oracle jdbc class is not found. Possibly there is wrong settings of CLASSPATH in the $SWINGHONE/swingbench.env file.

Solution of The Problem:
------------------------------------------
For example if you just install oracle instant client then change the CLASSPATH location to the point of ojdbc14.jar.
There was the settings of ,
# The following is needed for 10g environments
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/jdbc/lib/ojdbc14.jar

Now you point to your instant client and fing ojdbc14.jar. Locate the location to the CLASSPATH variable in the swingbench.env, change it to correct location and start the swingbench.

export CLASSPATH=$CLASSPATH:$ORACLE_HOME/ojdbc14.jar

Solution of ORA-28002: the password will expire within 10 days

Error Description:
-----------------------------------
Whenever a user try to connect to database it raise ORA-28002: error.

-bash-3.00$ sqlplus arju/a

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 7 23:58:46 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-28002: the password will expire within 10 days

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Cause of The Problem:
-----------------------------------
Lets know the user profile.
SQL> select profile from dba_users where username='ARJU';

PROFILE
------------------------------
DEFAULT

Now lets know the resource settings 'PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME' of default profile.

SQL> select LIMIT, RESOURCE_NAME from dba_profiles where RESOURCE_NAME in ('PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME','PASSWORD_REUSE_MAX','PASSWORD_REUSE_TIME') and PROFILE=(select profile from dba_users where username='ARJU');

LIMIT RESOURCE_NAME
---------------------------------------- --------------------------------
60 PASSWORD_LIFE_TIME
1800 PASSWORD_REUSE_TIME
UNLIMITED PASSWORD_REUSE_MAX
10 PASSWORD_GRACE_TIME

The resource PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused.

In this case our interested resource is PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME.

The resource of Default profile PASSWORD_LIFE_TIME specify the number of days the same password can be used for authentication.

The resource PASSWORD_GRACE_TIME specify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.

Here in the profile of ARJU user the value of PASSWORD_GRACE_TIME is set to 10. So it just arises a warning ORA-28002 but still allow users to logon to database. The password will expire if it is not changed within the grace period, and further connections are rejected. If you do not set a value for PASSWORD_GRACE_TIME, its default of UNLIMITED will cause the database to issue a warning but let the user continue to connect indefinitely.

One may interpret wrongly of parameter PASSWORD_LIFE_TIME with account creation time. Actually the PASSWORD_LIFE_TIME limit of a profile is measured from the last time an account's password was changed or the account creation time if the password has never been changed.

The account creation time and password change time can be seen from USER$.CTIME and USER$.PTIME respectively. Like,
SQL> select ctime, ptime from sys.user$ where name='ARJU';

CTIME PTIME
--------- ---------
08-MAY-08 08-MAY-08

You can also get the account creation time from dba_users view.
SQL> SELECT CREATED FROM DBA_USERS WHERE USERNAME = 'ARJU';

CREATED
---------
08-MAY-08

Now let's look current time which is 08-JUL-08
SQL> select sysdate from dual;

SYSDATE
---------
08-JUL-08

So between the password change time and current time there it is passed 60 which is equal to PASSWORD_LIFE_TIME. Now the setting of PASSWORD_GRACE_TIME to 10 allow the user ARJU to connect to database 10 days more but will issue a warning.

Solution of The Problem:
--------------------------------------
A)Change the user password.
------------------------------------
If you just want to avoid the error temporary then change the user password.
SQL> conn arju/a
ERROR:
ORA-28002: the password will expire within 10 days
SQL> password
Changing password for ARJU
Old password:
New password:
Retype new password:
Password changed
SQL> conn arju/a!12
Connected.

Now you can see the change time by .
SQL> select ctime, ptime from sys.user$ where name='ARJU';

CTIME PTIME
--------- ---------
08-MAY-08 08-JUL-08

This is a temporary solution. After 60 days the user will again see the warning message.

B)Change PASSWORD_LIFE_TIME resource of profile assigned to user.
----------------------------------------------------------------------------------------

The permanent solution is to change PASSWORD_LIFE_TIME resource of profile DEFAULT which is assigned to user ARJU.

SQL> select profile from dba_users where username='ARJU';

PROFILE
------------------------------
DEFAULT
If you make it unlimited then user never will see above error. Like,
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

Related Documents

http://arjudba.blogspot.com/2008/05/password-verification-or-complexity.html

http://arjudba.blogspot.com/2008/05/playing-with-oracle-password-identified.html

http://arjudba.blogspot.com/2008/04/password-is-case-sensitive-in-oracle.html

http://arjudba.blogspot.com/2008/07/ora-28003-ora-20002-while-changing.html

Pseudocolumns in Oracle

The word pseudo means flase. Pseudocolumn means false column which indicate the column is not stored within a table but they behaves as they are table columns.

You can only do select operation on pseudocolumns but you can't do insert, update, or delete their values.

You can compare pseudocolumn to a function which takes no argument.

The difference between pseudocolumn and function without argument is pseudocolumns typically return a different value for each row but functions without arguments typically return the same value for every row in the result set.

Based on the functions pseudocolumn can be divided into several types.

1)Hierarchical Query Pseudocolumns: Discussed in
2)Sequence Pseudocolumns: Discussed in
3)Version Query Pseudocolumns: Discussed in http://arjudba.blogspot.com/2008/07/version-query-pseudocolumns.html
4)COLUMN_VALUE Pseudocolumn: Discussed in
5)OBJECT_ID Pseudocolumn: Discussed in
6)OBJECT_VALUE Pseudocolumn: Discussed in
7)ORA_ROWSCN Pseudocolumn: Discussed in http://arjudba.blogspot.com/2008/07/can-i-get-idea-when-row-is-created.html
8)ROWID Pseudocolumn: Discussed in
9)ROWNUM Pseudocolumn: Discussed in http://arjudba.blogspot.com/2008/07/rownum-pseudocolumn-in-oracle.html
10)XMLDATA Pseudocolumn: Discussed in

Null value Concept in Oracle

•A column in a row contains Null value in oracle is treated as special one which indicates the column in that row contains no value.

•Null is not same as zero as zero has a value but null represent no value.

•Setting a null value is appropriate when the actual value is not known or when a value would not be meaningful.

•Currently oracle Database treats a character value with a length of zero as null which is exampled in section below. But according to oracle this behavior may change in future.

•If we do any arithmetic operation of a NULL value then it evaluates to null.

•All operators (except concatenation) return null when given a null operand.

•All scalar functions (except REPLACE, NVL, and CONCAT) return null when given a null argument.

•Most aggregate functions ignore nulls. For example in a column I have 11,null,12 values. Now if I do an average of the column it will ignore the null value and will result average with other column as (11+12)/2.

SQL> select avg(sal) from sal;
AVG(SAL)
----------
11.5
•To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL.

•A null cannot be equal or unequal to any value or to another null as it represents lack of data. Though in DECODE function and two null appeared in compound function treated as same.

•If Nulls are used in Conditions the condition that evaluates to UNKNOWN acts almost like FALSE.
Like if value of a is NULL and if we use where a=NULL then it evaluates to unknown and returns no rows(act like false).

The following example will make you clear.

SQL> create table test_null(a number, b varchar2(10));
Table created.

SQL> insert into test_null (a) values(1);
1 row created.

SQL> insert into test_null values(2, null);
1 row created.

SQL> insert into test_null values(3,'');
1 row created.

SQL> select * from test_null;
A B
---------- ----------
1
2
3
So in three ways we inserted null.

SQL> select * from test_null where b=NULL;
no rows selected
Though all b columns hold null values but use of b=NULL return no rows as NULL!=NULL.

Sunday, July 6, 2008

How to develop own transactions load with Swingbench

Swingbench is a free tool to generate load on database. This product has four benchmark and you can test those benchmark easily. Almost no extra work needed. Just import schema tablespaces and load data and test load with predefined transaction.

But how you can test load with your transaction based on your data?

While configuring swingbench you may have troubles to load your own defined transaction into it and then test your load. In this topic I will write how easily you can proceed with swingbench to test load with your defined transaction.

With swingbench to test your own defined transaction there are two approaches. Either by writing PL/SQL procedures or by writing JAVA programs. The steps are discussed below using stored procedures.

1. Unzip swingbench product.
2. Open the file $SWINGHONE/sql/storedprocedures.sql with an vi editor and edit the procedures. There are six stored procedures by defaults. You can add more if you wish. For example here I have edited storedprocedure3 to add a delete statement like below.

function storedprocedure3(min_sleep integer, max_sleep integer) return integer_return_array is
begin
init_dml_array();
execute immediate 'DELETE FROM TEST where name= ''1''';
increment_deletes(1);
sleep(min_sleep, max_sleep);
return dml_array;
end storedprocedure3;

3. In this way you can add several procedures like insert , update, delete etc. and save the file. Like save it as stored.sql
4. Log on to the user on which you want to test load and then execute the script. For example,
SQL>conn arju/a
SQL>@stored.sql
A typical output is,
SQL> @/export/home/oracle/stored.sql

Type created.

Package created.

Package body created.

If you get any error then try to fix the error.

5.Now edit the $SWINGHONE/bin/swingconfig.xml file and along with username, passowrd etc entry edit the transaction entry to include the StoredProceduren class where n may be from 1 to ... as you ran script in to your database. Like here I added storedprocedure4 in the configuration file.

[Transaction]
[Id]UpdateCount[/Id]
[ShortName]Update Count Table[/ShortName] [ClassName]com.dom.benchmarking.swingbench.storedprocedures.
StoredProcedure4[/ClassName]
[Weight]20[/Weight]
[Enabled]true[/Enabled]
[/Transaction]

Use <> instead of [].

Along with class name you can edit ID and shortname for better understanding.

6. Now save the swingconfig.xml file and run the swingbench. If you have syntactically correct in your swingconfig.xml swingbench will run and test your load.

7.You can edit the configuration as your wish. You can give different load in the configuration tab.

Related Documents:

Automatic Load Generation tool in Database
Swingbench Exception: Oracle jdbc classes not found

ORA-1000 Maximum Number of Cursors Exceeded

Error Description:
----------------------------------
Whenever you run any procedures or functions or any other application or transaction either database level or application level you get the following error,

ORA-01000: "maximum open cursors exceeded"

Cause of The Problem:
--------------------------------
The initialization parameter OPEN_CURSORS specifies the maximum number of open cursors a session can have at once. To run a application the program needs to open more cursor in a session than the OPEN_CURSORS and error arises.

To process a SQL statement, Oracle opens a work area called a private SQL area. This private SQL area stores information needed to execute a SQL statement. Cursors are stored in this area to keep track of information.

There are two types of cursor named implicit cursor and explicit cursor.

An IMPLICIT cursor is declared for all data definition and data manipulation statements. They are internal to Oracle and is handled by oracle. They are harder to tune because they are internal to Oracle.

But in order to handle the queries that return more than one row, you must declare an EXPLICIT cursor to retrieve all the information. They can be tuned by determining when you will open or close them.

Solution of The Problem:
--------------------------------------
You can solve the ORA-01000 error by tuning cursor usage at the database level and at the application level.

1. Tuning at the DATABASE LEVEL
-------------------------------------------------------
The initialization parameter OPEN_CURSORS specifies the maximum number of open cursors a session can have at once. The default value of it is 50 and the value ranges from 0 to 65535. By setting the highest value of it likely solve the ORA-01000 error. Like you can issue,
SQL> ALTER SYSTEM SET open_cursors=5000;
System altered.

SQL> show parameter open_curs
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 5000

2. Tuning at the APPLICATION LEVEL
----------------------------------------------------------------------
The three parameters that affect handling cursors at the application level are RELEASE_CURSOR, HOLD_CURSOR, MAXOPENCURSORS.

The default value of HOLD_CURSOR is NO which means that after Oracle executes a SQL statement the links to the cursor cache, memory, and parse locks are released and marked for reuse.

The default value of RELEASE_CURSOR is NO which means that after Oracle executes a SQL statement, the links to the cursor cache is maintained and not released.

To resolve the ORA-01000 error, you should set HOLD_CURSOR=NO and RELEASE_CURSOR=YES.

ORA-01722: invalid number

Error Description:
-----------------------------------
Any procedure, function or statements failed with ORA-01722. Like,
SQL> select * from a where a='d';
select * from a where a='d'
*
ERROR at line 1:
ORA-01722: invalid number

Cause of The Problem:
-------------------------------------------
There error comes as either implicitly or explicitly the attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Oracle tried to convert a value to a number and found an illegal value.

Solution of The Problem:
-------------------------------------
Check the SQL statement that raises the error. Check the table definitions. Fix the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.

Like
SQL> select * from a where a='d';
select * from a where a='d'
*
ERROR at line 1:
ORA-01722: invalid number

SQL> desc a;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER

As a is number data type and 'd' can't be converted to number , hence error arises. So use numeric value instead of 'd'.

SQL> select * from a where a=1;
A
----------
1
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html

ORA-00936: missing expression

ORA-01756: quoted string not properly terminated

Oracle in World

Most Visited Recently

Categories

Popular Posts

Which OS you like best for your oracle