Oracle in World: 6/22/08

Saturday, June 28, 2008

Database Objects in Oracle

If we look for database objects in oracle then there comes two types of objects, one is schema objects that are associated with a particular schema or in other word they are owned by a database user. And the other is nonschema Objects that are not reside in a particular schema.

To know the object type in your database you can query,
SQL>select distinct object_type from dba_objects order by 1;

Schema Objects Lists
------------------------------------
APPLY
CAPTURE
CLUSTER
CONSTRAINT
CONSUMER GROUP
CONTEXT
DATABASE LINK
DIRECTORY
EVALUATION CONTEXT
FUNCTION
INDEX
INDEX PARTITION
INDEXTYPE
JAVA CLASS
JAVA DATA
JAVA RESOURCE
JOB
JOB CLASS
LIBRARY
LOB
LOB PARTITION
MATERIALIZED VIEW
OPERATOR
PACKAGE
PACKAGE BODY
PROCEDURE
PROGRAM
QUEUE
RESOURCE PLAN
RULE
RULE SET
SCHEDULE
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
TRIGGER
TYPE
TYPE BODY
UNDEFINED
VIEW
WINDOW
WINDOW GROUP
XML SCHEMA

Non-Schema Object Lists
--------------------------------------
Parameter file (PFILE) and server parameter files (SPFILEs)
Profile
Role
Rollback segment
Tablespace
User

History of SQL

What is SQL?

Thursday, June 26, 2008

Privileges and it's Category in Oracle

In your database if you are a dba then you possibly can do everything in your database like create a new user, create objects in another schema and etc. If you are a normal user then you can't create user or create objects in another schema. All these tasks are maintained by granting privilege to a user. We can define privilege in following ways,

"A privilege is a right to execute a particular type of SQL statement or to access another user's object."

Privilege can be divided into six major category.

1)System Privileges
2)Schema Object Privileges
3)Table Privileges
4)View Privileges
5)Procedure Privileges
6)Type Privileges

Wednesday, June 25, 2008

ORA-00903: Oracle Database Reserved Words

I will start this post with some experiment.

1)Create a Table named ACCESS but failed- ACCESS is Reserved Keyword.
SQL> CREATE TABLE ACCESS ( A NUMBER);
CREATE TABLE ACCESS ( A NUMBER)
*
ERROR at line 1:
ORA-00903: invalid table name

2)Select from a Table failed with ORA-00903:- Table spelling is not correct.
SQL> select * from as;
select * from as
*
ERROR at line 1:
ORA-00903: invalid table name

3)Create Table with named 1table failed with ORA-00903:- Invalid name specification.
SQL> create table 1table ( a number);
create table 1table ( a number)
*
ERROR at line 1:
ORA-00903: invalid table name

In above three cases we got the error ORA-00903. The summary of the expriment suggest that while table creation if I get ORA-00903 error then we have to look at,

•The specified table or cluster name is valid. The valid table name must be less than or equal to 30 characters.

•The table or cluster name must begin with a letter and may contain only alphanumeric characters and the special characters $, _, and #.

•The table name cannot be a reserved word.

Hopefully we already understood first two scenario. The third one need an special attention like which keywords are reserved in oracle? The reserved keyword can be found by querying V$RESERVED_WORDS data dictionary view.

We can see the always reserved keywords by following query,
select keyword from v$reserved_words where reserved='Y';

Let's see the table.
SQL> desc v$reserved_words;
Name Null? Type
----------------------------------------- -------- ----------------------------
KEYWORD VARCHAR2(30)
LENGTH NUMBER
RESERVED VARCHAR2(1)
RES_TYPE VARCHAR2(1)
RES_ATTR VARCHAR2(1)
RES_SEMI VARCHAR2(1)
DUPLICATE VARCHAR2(1)
•A value of column reserved Y means that the keyword cannot be used as an identifier. A value of N means that it is not reserved and hence can be used as an identifier.

•A value of column RES_TYPE Y means that the keyword cannot be used as a type name. A value of N means that it can be used as a type name.

•A value of column RES_ATTR Y means that the keyword cannot be used as an attribute name. A value of N means that it is not reserved as an attribute name.

•A value of column RES_SEMI Y means that the keyword is not allowed as an identifier in certain situations, such as in DML. A value of N means that it is not reserved.
In order words Y means the keyword is always reserved or N means it is reserved only for particular uses.

In order to know a complete list of oracle always reserved keywords you can query,
SQL> select keyword from v$reserved_words where reserved='Y' order by keyword;

KEYWORD
------------------------------
!
&
(
)
*
+
,
-
.
/
:
< = >
@
ALL
ALTER
AND
ANY
AS
ASC
BETWEEN
BY
CHAR
CHECK
CLUSTER
COMPRESS
CONNECT
CREATE
DATE
DECIMAL
DEFAULT
DELETE
DESC
DISTINCT
DROP
ELSE
EXCLUSIVE
EXISTS
FLOAT
FOR
FROM
GRANT
GROUP
HAVING
IDENTIFIED
IN
INDEX
INSERT
INTEGER
INTERSECT
INTO
IS
LIKE
LOCK
LONG
MINUS
MODE
NOCOMPRESS
NOT
NOWAIT
NULL
NUMBER
OF
ON
OPTION
OR
ORDER
PCTFREE
PRIOR
PUBLIC
RAW
RENAME
RESOURCE
REVOKE
SELECT
SET
SHARE
SIZE
SMALLINT
START
SYNONYM
TABLE
THEN
TO
TRIGGER
UNION
UNIQUE
UPDATE
VALUES
VARCHAR
VARCHAR2
VIEW
WHERE
WITH
[
]
^
|

99 rows selected.

See here ACCESS keyword does not appear beacuse ACCESS can't be used as an identifier.

SQL> select keyword,reserved from v$reserved_words where keyword='ACCESS';

KEYWORD R
------------------------------ -
ACCESS N

Related Documents:
Troubleshooting ORA-00942: ORA-04043:

List of Available Hints in Oracle

A)Optimization Goals and Approaches Category
--------------------------------------------------------------
ALL_ROWS
FIRST_ROWS
RULE

B)Access Path Hints Category
----------------------------------------------------
CLUSTER
FULL
HASH
INDEX
NO_INDEX
INDEX_ASC
INDEX_DESC
INDEX_COMBINE
INDEX_JOIN
INDEX_FFS
INDEX_SS
INDEX_SS_ASC
INDEX_SS_DESC
NO_INDEX_FFS
NO_INDEX_SS

C)Join Order Hints Category
---------------------------------------------
ORDERED
LEADING

D)Join Operation Hints Category
---------------------------------------------
USE_HASH
NO_USE_HASH
USE_MERGE
NO_USE_MERGE
USE_NL Hint
USE_NL_WITH_INDEX
NO_USE_NL

E)Parallel Execution Hints Category
-------------------------------------------------------------------
PARALLEL
NO_PARALLEL
PARALLEL_INDEX
NO_PARALLEL_INDEX
PQ_DISTRIBUTE

F)Query Transformation Hints Category
-------------------------------------------------------------
FACT
NO_FACT
MERGE
NO_MERGE
NO_EXPAND
USE_CONCAT
REWRITE
NO_REWRITE
UNNEST
NO_UNNEST
STAR_TRANSFORMATION
NO_STAR_TRANSFORMATION
NO_QUERY_TRANSFORMATION

Other Hints Category
-----------------------------------------------------
APPEND
NOAPPEND
CACHE
NOCACHE
CURSOR_SHARING_EXACT
DRIVING_SITE
DYNAMIC_SAMPLING
PUSH_PRED
NO_PUSH_PRED
PUSH_SUBQ
NO_PUSH_SUBQ
PX_JOIN_FILTER
NO_PX_JOIN_FILTER
NO_XML_QUERY_REWRITE
QB_NAME
MODEL_MIN_ANALYSIS

Hints in Oracle

Automatic Load Generation tool in Database

Today I work with automatic and free load generation tool in my database. It is quite essential to experiment on test database performance by generating load on it before going to production database. There are many tools that may load workload. But I today work with tool named Swingbench and it is free to use.

If you need to download any load generation tool you can download swingbench from link,

http://www.dominicgiles.com/downloads.html

The main problem of this product it is free and has no support.

There is one user's manual of this software which can be downloaded from
http://www.dominicgiles.com/swingbench/swingbench22.pdf

You must have JVM installed on the client platform to run swingbench. It also requires an Oracle client. This can either be in the form of a full blown Oracle database install or the Oracle instant client downloadable from the OTN (Oracle Technology Network).

http://www.oracle.com/technology/software/tech/oci/instantclient/index.html

In order to use the product no need to install. Just download the zip file and save it in your computer. Then unzip it where you want to resides the program.

Before using this tool you have to at first modify swingbench.env file and change your JAVAHOME, SWINGHOME location according to your wish. On Windows the file is $SWINGHOME/swingbenchenv.bat. Note that java must be installed before using this product.

For unix based user use swingbench which is under $SWINGHOME/bin directory and for windows based user use $SWINGHOME/winbin directory.

The swingbench by default has a configuration file. Swingbench reads its configuration properties from a file called swingconfig.xml located in the $SWINGHOME/bin directory. In the file you can set general variable like host, user name, password etc.

It can be worked with the default swingbench environment ships with the source code for 4 benchmarks.
• CallingCircle
• OrderEntry (PL/SQL)
• OrderEntry (jdbc)
• PL/SQL stubs

The callingcircle schema can be loaded with ccwizrd and corresponding settings is in ccwizzard.xml
The order entry schema can be loaded with oewizard and corresponding settings in in oewizard.xml

After loading data you can run swingbench.

Related Documents:
------------------------------
How to develop own transactions load with Swingbench

Tuesday, June 24, 2008

ORA-12154: TNS:could not resolve the connect identifier specified

Problem Description:
--------------------------------------------
Whenever you try to connect your database it returns error ORA-12154.
SQL> conn prod/prod@jupi
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Cause of The Problem:
------------------------------------
The cause of the problem is depends on the which naming method you are using. Suppose if you use tnsnames.ora for naming method then there might be problem in it. I look for oerr command on my unix machine and got,

-bash-3.00$ oerr ora 12154
12154, 00000, "TNS:could not resolve the connect identifier specified"
// *Cause: A connection to a database or other service was requested using
// a connect identifier, and the connect identifier specified could not
// be resolved into a connect descriptor using one of the naming methods
// configured. For example, if the type of connect identifier used was a
// net service name then the net service name could not be found in a
// naming method repository, or the repository could not be
// located or reached.
// *Action:
// - If you are using local naming (TNSNAMES.ORA file):
// - Make sure that "TNSNAMES" is listed as one of the values of the
// NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA)
// - Verify that a TNSNAMES.ORA file exists and is in the proper
// directory and is accessible.
// - Check that the net service name used as the connect identifier
// exists in the TNSNAMES.ORA file.
// - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
// file. Look for unmatched parentheses or stray characters. Errors
// in a TNSNAMES.ORA file may make it unusable.
// - If you are using directory naming:
// - Verify that "LDAP" is listed as one of the values of the
// NAMES.DIRETORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA).
// - Verify that the LDAP directory server is up and that it is
// accessible.
// - Verify that the net service name or database name used as the
// connect identifier is configured in the directory.
// - Verify that the default context being used is correct by
// specifying a fully qualified net service name or a full LDAP DN
// as the connect identifier
// - If you are using easy connect naming:
// - Verify that "EZCONNECT" is listed as one of the values of the
// NAMES.DIRETORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA).
// - Make sure the host, port and service name specified
// are correct.
// - Try enclosing the connect identifier in quote marks.
//
// See the Oracle Net Services Administrators Guide or the Oracle
// operating system specific guide for more information on naming.

Solution of The Problem:
-----------------------------------------------
Actually above solution already depicted what to do if you get the problem. My suggest after getting ORA-12154 immediately go as the steps mentioned below.

Step 1: Look for tnsnames.ora and sqlnet.ora.
-----------------------------------------------------------------------
Look for your tnsnames.ora and sqlnet.ora file that you are using. On unix the default location is $ORACLE_HOME/network/admin. You better avoid to use tnsnames.ora. By using easy naming service you can easily avoid this error. Easy naming service is discussed on See the solution part of this post

Step 2: Check both file
---------------------------------------------------------------------------
•After locating both file open it with any viewer like on windows with notepad and on unix with less or more or cat.

•Check within the files whether any illegal character or any unnecessary space exists. If have then remove that.

•Check whether the exact entry exist on the tnsnames.ora that you used in connection identifer. Like if you use conn a/a@jupi then within tnsnames.ora search for only jupi alias.

•You can check your whether there is error or not in the tnsnames alias inside tnsnames.ora by using tnsping. In order to check alias jupi we issue,

bash-3.00$ tnsping jupi

TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 12-OCT-2008 03:25:58

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

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = ((ADDRESS = (PROTOCOL = TCP)(HOST = neptune)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ARJU)))
TNS-12533: TNS:illegal ADDRESS parameters
So there is illegal ADDRESS parameter which we see an extra ( before ADDRESS parameter.

If our tnsalias was good, then it would result below with how many miliseconds.
bash-3.00$ tnsping jupi

TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 12-OCT-2008 03:28:26

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

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = neptune)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ARJU)))
OK (10 msec)

•Note that with name alias checking tnsnames also tell us which location parameter files it used. Here the location is /oracle/app/oracle/product/10.2.0/db_1/network/admin. So /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora location is used in order to resolve name. However if your tnsnames.ora is in other location then you have to set TNS_ADMIN variable.

•If you see tnsnames.ora is most likely accurate, echo the TNS_ADMIN environment variable.

% echo $TNS_ADMIN

•If nothing is returned, try to set the TNS_ADMIN environment variable to explicitly point to the location of the TNSNAMES.ORA file.

In C Shell, the syntax is:
% setenv TNS_ADMIN full_path_of_tnsnames.ora_file

In K Shell or bash, the syntax is:
% export TNS_ADMIN=full_path_of_tnsnames.ora_file

In windows the syntax is:
set TNS_ADMIN=full_path_of_tnsnames.ora_file

•Now try and see whether error remains. If still you get error then in the SQLNET.ORA file, add the parameter AUTOMATIC_IPC = OFF. If AUTOMATIC_IPC is already set to ON, then change the value to OFF. And try to connect. If still you get error then check the permission of tnsnames.ora and sqlnet.ora. For workaround you can set chmod 777 of these files and try to connect. At this point it is expected you have solved your problem.

Related Documents
ORA-12560: TNS:protocol adapter error on windows machine while starting oracle
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Startup fails with oracle error ORA-00119, ORA-00132

ORA-12541: TNS:no listener

For newbie users they found ORA-12541 and search here and there to solve the problem. But it is one word about the error which it says no listener and think next what to do. Never mix this error with another error like ORA-12514: which are different and mutually exclusive.

Error Description
---------------------------------------------
-bash-3.00$ sqlplus arju/a@neptune/saturn

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 24 06:39:15 2008

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

ERROR:
ORA-12541: TNS:no listener

Cause of the Problem:
--------------------------------------
Listener service is not running in the database to which you are requested to connect. Here we see that is neptune database. If you use tnsnames.ora then look at host parameter value in it.

Solution of the Problem:
----------------------------------
Solution of the problem is straightforward which is start the listener. It is done by lsnrctl start in the server machine to which you connect.
Step1:
---------
First check status by
$lsnrctl status

If you have default named listener which is LISTENER then lsnrctl status is fine. However if you don't have default listener then from listerner.ora see the available listener. And use that name after status. If your listener name is TEST_LISTERNER then use,
$lsnrctl status TEST_LISTERNER
LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 06:55:08

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Solaris Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=neptune)(PORT=1522)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Solaris Error: 146: Connection refused

If you get TNS-00511 then go to step 2 otherwise if you get listener status then go to step 3.

Step 2:
---------------
Start the listener.
If you have default listener then use
-bash-3.00$ lsnrctl start

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 06:53:50

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

Starting /oracle/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
.
.

If you have other than default then use that name like,
$ lsnrctl start NON_DEFUALT_LISTENER
Step 3:
--------------
It is natural after starting listener service it takes minute to register the service of the listener. You can dynamically service of the listner by issuing following command in SQL*Plus,
SQL> alter system register;
System altered.

Step 4:
------------------
Try to connect to database using connection identifier. If you still get error ORA-12541 then check the addition settings of the connection identifier. If you use tnsnames.ora then also check settings listener post. This all can be seen after issuing lsnrctl status command

Like in this example my listener port is 1522 and if I use 1521 in my connection identifier error will come.
-bash-3.00$ lsnrctl status

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 07:02:18

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date 24-JUN-2008 07:01:52
Uptime 0 days 0 hr. 0 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC2)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=neptune)(PORT=1522)))
.
.

-bash-3.00$ sqlplus arju/a@neptune:1521/dbase

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 24 07:06:00 2008

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

ERROR:
ORA-12541: TNS:no listener

As listener is running on port 1522 so connect to port 1521 will fail.
-bash-3.00$ sqlplus arju/a@neptune:1522/dbase

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 24 07:06:15 2008

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

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

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

In this post I will try to show how efficiently we can avoid error ORA-12514. My suggestion is whenever you get this error forget about tnsnames.ora and other stuff related to it. Start fixing problem step by step.

Problem Description:
Whenever you try to connect to database the following error comes.
-bash-3.00$ sqlplus arju/a:1522/dba

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jun 24 06:35:02 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Cause of The problem:
The services named issued with the connection identifier has not been registered yet.

Solution of The Problem:
After getting above error forget any tnsnames.ora file. Issue lsnrctl status command on the server to which you try to connect like,
-bash-3.00$ lsnrctl status

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 07:17:56

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date 24-JUN-2008 07:01:52
Uptime 0 days 0 hr. 16 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC2)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=neptune)(PORT=1522)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dbase" has 1 instance(s).
Instance dbase, status READY, has 1 handler(s) for this service...
Service "dbaseXDB" has 1 instance(s).
Instance "dbase", status READY, has 1 handler(s) for this service...
Service "dbase_XPT" has 1 instance(s).
Instance "dbase", status READY, has 1 handler(s) for this service...
The command completed successfully

Now closely look at the bolded items above , it is host, port, service and optionally instance.
Now use it in the connection descriptor as follows.

sqlplus user_id/password@host:port/service

Like here,
$sqlplus arju/a@neptune:1522/dbase
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 24 07:21:19 2008

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

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

This method of connecting to database is called Easy Connect Naming Method.

Related Documents:
ORA-12541: TNS:no listener

ORA-12641: Authentication service failed to initialize

Error Description:
---------------------------------------
Whenever I tried to logon to my database it failed me with error ORA-12641.
-bash-3.00$ sqlplus arju/arju@jupiter/ARJU.ARJU.BANGLADESH.NET

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jun 24 05:55:52 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

ERROR:
ORA-12641: Authentication service failed to initialize

Cause of The problem:
---------------------------------------------------
In the $ORACLE_HOME/network/admin/sqlnet.ora file SQLNET.AUTHENTICATION_SERVICES is set to ALL and Secure Network Services has not been used.

Whenever Secure Network Services is not being used, that is you try to logon to database using SQL*Net from database host computer using above command, then it explicitly disallow applications from initiating SQL*Net connect requests with authentication services. So to allow connection through SQL*Net it is needed to disable authentication services while still allowing normal SQL*Net connections.

Soltion of The Problem:
---------------------------------
Issuing the above command from another computer rather than host will not produce the error because in that case secure network services will be used.

To disable SQL*Net authentication services while still allowing normal SQL*Net connections you can set in $ORACLE_HOME/network/admin/sqlnet.ora the following line,

SQLNET.AUTHENTICATION_SERVICES =(NONE)

Now try to connect and it will be ok.
-bash-3.00$ sqlplus arju/arju@jupiter/ARJU.ARJU.BANGLADESH.NET
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 24 06:10:32 2008

Copyright (c) 1982, 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

How to zip and unzip a file or folder under linux

Perhaps you have downloaded a file from internet with .zip extension. Or
You want to send a list of files to your friends. Or
your folder /file size is too large that you want to reduced.
In all of these cases you have to be familiar with the zip and unzip command.

Zip is used to compress a file or folder and unzip is used to decompress/extract.

On windows it is quitely easy to do it. Windows by default have winzip program which is used to do the task. You can manually in windows can install winrar software and do the same task more efficiently.

On linux there is command line tool and also various ways to do it. The command line tool zip and unzip is used to do the task.

If you don't have any unzip tool then yet you can do unzip. To do it just enter to the .zip extention file and copy the contents inside it to another location outside zip file.

The zip and unzip tool is by default not installed on linuz system. You have to install it manually.

If you are in debian linux distribution then as a root user install it as,

# apt-get install zip
# apt-get install unzip

If you are Red Hat Linux or Fedora Core user then use yum command to install zip and unzip program like,

# yum install zip
# yum install unzip

Now you have both zip and unzip utility by which you can do the task.

Here is some example of zipping and unzipping process in linux.

Example zip 1:
-----------------------------------
To make archive named as myzip.zip with all the files in the current directory issue,
$ zip myzip *
The archive name will be automatically to .zip extension. So no need to give extension.

Example zip 2:
-------------------------------------------
To make a zip file of the entire directory and subdirectory under it issue,
$ zip -r myzip *

To make zip of all files under /root/Image issue ,
zip myzip /root/Image/*
which all make archive named myzip.zip and save the archive in current directory. To save the archive in another location like /home/Arju issue,
zip /home/Arju/myzip /root/Image/*

Example unzip
------------------------------------------------
1)To list the available files with size inside zip archive issue,
unzip -l myzip.zip

2)To extract all files into the /tmp directory:
$ unzip myzip.zip -d /tmp

3)You can test whether zip file is ok or not and then printing summary by,
$ unzip -tq myzip.zip

4)To unzip all files in the current directory use,
$ unzip /home/Arju/myzip.zip

Monday, June 23, 2008

Examples of Usage of Composite Index

If I create an index on column (a,b,c) then
a, ab, abc combination of columns in where clause will use the index.
bc, b, and c combinations of columns in where clause will not use the index.

The example is given below.

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

SQL> begin
for i in 1 .. 10000
loop
insert into comp_tab values(i,'pc-'||round(dbms_random.value(1,20000),0),'pc-'||round(dbms_random.value(1,20000),0));
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> create index comp_tab_I on comp_tab(a,b,c);
Index created.

SQL> analyze table comp_tab estimate statistics;
Table analyzed.

SQL> set autot trace

a in where cluase will use the index as a is leading portion of index.
SQL> select a,c,b from comp_tab where a=565;
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------

ac combination will use the index.
SQL> select a,c,b from comp_tab where a=565 and c='pc-3722';
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------

bc combination is used in where clause so will not use index.
SQL> select b,c,a from comp_tab where b='pc-5895' and c='pc-2893';
Execution Plan
----------------------------------------------------------
Plan hash value: 3767570742
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| COMP_TAB | 1 | 19 | 11 (0)| 00:00:01 |
------------------------------------------------------------------------------

Only b combination is used so will not use the index.
SQL> select a,c,b from comp_tab where b='pc-6735';
Execution Plan
----------------------------------------------------------
Plan hash value: 3767570742
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| COMP_TAB | 1 | 19 | 11 (0)| 00:00:01 |
------------------------------------------------------------------------------

abc combination is used in where clause and so will use the index.
SQL> select b,c,a from comp_tab where b='pc-5895' and c='pc-2893' and a=564;
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

ab combination in where clause will use the index.
SQL> select c from comp_tab where b='pc-5895' and a=564;
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
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

Choice to create composite index

Whenever you create index on multiple columns in a table then that index is called composite index.

Advantage of Using Composite Index:
----------------------------------------------------------------
Improved selectivity: As we all know index is created for the column that has higher selectivity means there are a few duplicate column values of the indexing columns. Sometimes two or more columns each with poor selectivity, can be combined to form a composite index with higher selectivity.

Reduced I/O: If composite index is created with all the columns selected by the query, then oracle can return the values from the index itself instead of accessing values from the table using ROWID scan unlike single key index.

To know more about ROWID scan and explain plan search within my blog.

Composite Key Usage in a Query
----------------------------------------------------------
It is important to maintain order of the index while creating composite key index. For example create index on column(x,y) is not same as of create index on column(y,x). So while creating composite index take special care of the order of the columns inside index. More specifically, a SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. Order of columns inside index creation make the leading constructs.

An example will make you clear.
If I create index as

CREATE INDEX comp_indx ON tabl(a, b, c);
Then,
a, ab, and abc combinations of columns in where clause will use the index.
bc, b, and c combinations of columns in where clause will not use the composite index.

Th ordering of keys of composite index is given below.

Ordering Keys for Composite Indexes
-----------------------------------------------------------
•Create the composite index so the keys used in WHERE clauses make up a leading portion.

•If some keys are used in WHERE clauses more frequently, then while creating composite index make sure that the more frequently selected keys make up a leading portion to allow the statements that use only these keys to use the index.

•If all keys are used in the WHERE clauses equally often but the data is physically ordered on one of the keys, then place that key first in the composite index.

•The example is given in Examples of Usage of Composite Index

Choosing Keys for Composite Indexes
----------------------------------------------------------------------
•Consider to create of composite index on columns that is used frequently in WHERE clause condition combined with AND operation.

•If several queries select a combination of columns based on one or more columns then consider to create composite index based on these columns.

Related Documents:
---------------------------------------------
Examples of Usage of Composite Index

Sunday, June 22, 2008

Choose Indexing Column

It is common to ask on which column of a table I will create index. In this topic I want to give an idea about the criteria based on which you can decide on which column you will make an index.

•Consider to create an index for the column that is used frequently in WHERE clause. For example, if SELECT * FROM test WHERE A=2; query is executed frequently then consider to create an index on column A.

•Consider to create an index for the columns that are used frequently to join tables in SQL statements. For example if select a.n, b.b from table_a a join table_b b on a.k=b.k; query is executed frequently then consider to create index on column k of table b and column k on table a.

•Choose to create index on the columns of a table that have high selectivity. High selectivity index that the column has few duplicate values. If columns are defined as unique and primary keys then oracle automatically create index on these columns. Lower selectivity means that many row of the column have the same value. Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values.

•Don't create standard B-tree indexes on column having few distinct values. If there are many duplicate values in a column, consider to create bitmap index unless the index is modified frequently, as in a high concurrency OLTP application.

•Don't create index on a column that is modified highly. Update statement that modify indexed column and delete or insert statement that modify indexed table take longer time than if there is no index. Such statement also modify data highly on index along will highly generate redo and undo.

•Don't create index on columns that appear in WHERE clause and uses a function other than MAX , MIN. In these case consider using function-based indexes.

•Consider to create index on foreign keys columns in which a large number of concurrent INSERT, UPDATE, and DELETE statements access the parent and child tables. Such an index allows UPDATEs and DELETEs on the parent table without share locking the child table.

•While creating a new index always consider whether performance gain for queries is worth the performance loss for INSERTs, UPDATEs, and DELETEs.

Playing with Oracle data block size

In this example I have experiment of oracle data block size with 8k and 16K. I performed DML operation against both 8k and 16k data block size. I created two tables table_8k under 8k tablespace tbs_8k and table_16k under 16k tablespace tbs_16k. The summary of the experiment is bigger data block cause more time while update but less time while insert operation.

A)I used OMF file system. So I set db_create_file_dest.
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /oradata2

B)Create 8k and 16K blocksize tablespace
SQL> create tablespace tbs_8k blocksize 8k;
Tablespace created.

SQL> alter system set db_16k_cache_size=20M;
System altered.

SQL> create tablespace tbs_16k blocksize 16k;
Tablespace created.

C)create table_8k under 8k blocksize tablespace and table_16k under 16k blocksize tablespace and insert random data into them.
SQL> create table table_8k (n number ,k varchar2(15)) tablespace tbs_8K;
Table created.

SQL> begin
for i in 1 .. 100000
loop
insert into table_8k values(i,'pc-'||round(dbms_random.value(1,20000),0));
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:12.51

SQL> create table table_16K (n number ,k varchar2(15)) tablespace tbs_16k;
Table created.

SQL> begin
for i in 1 .. 100000
loop
insert into table_16k values(i,'pc-'||round(dbms_random.value(1,20000),0));
end loop;
end;
/

PL/SQL procedure successfully completed.
Elapsed: 00:00:09.42

D)I created one script named flush will will flush data from buffer cache and shared pool so that we get actual result to complete operation.
SQL> !vi flush.sql
alter system flush buffer_cache;
alter system flush shared_pool;

SQL> @flush
System altered.
Elapsed: 00:00:00.03

System altered.
Elapsed: 00:00:00.03

SQL> select * from table_8k where k like '%888%';
176 rows selected.
Elapsed: 00:00:00.14

SQL> select * from table_16k where k like '%888%';
195 rows selected.
Elapsed: 00:00:00.04

So we see for 16k select is faster.
E) Now test for update operation.
SQL> @flush
SQL> update table_16k set k='Testing';
100000 rows updated.

Elapsed: 00:00:06.88

SQL> @flush
System altered.
Elapsed: 00:00:00.24
System altered.
Elapsed: 00:00:00.02

SQL> update table_8k set k='Testing';
100000 rows updated.
Elapsed: 00:00:01.91

We see update is fairly faster in 8k tablespace.
SQL> delete from table_8k;
100000 rows deleted.
Elapsed: 00:00:09.53

SQL> delete from table_16K;
100000 rows deleted.
Elapsed: 00:00:09.27

Related Documents:
-------------------------
General Idea of Database Block Size and BLOCKSIZE
Choose an optimal Data block size in Oracle
Advantage and Disadvantage of small and bigger data block
Data Block Format in Oracle

Most Visited Recently

Categories

Popular Posts

Which OS you like best for your oracle

Saturday, June 28, 2008

Thursday, June 26, 2008

Wednesday, June 25, 2008

Tuesday, June 24, 2008

Monday, June 23, 2008

Sunday, June 22, 2008

Tag Cloud

Blog Archive

Which Oracle database version you use?