SQL Operators

Technorati Tags: Oracle SQL operator,SQL comparison operator

There are two type of Operators, namely Comparison Operators and Logical Operators. These operators are used mainly in the WHERE clause, HAVING clause to filter the data to be selected.

Comparison Operators:

Comparison operators are used to compare the column data with specific values in a condition.
Comparison Operators are also used along with the SELECT statement to filter data based on specific conditions.
The below table describes each comparison operator.

 

Logical Operators:

There are three Logical Operators namely AND, OR and NOT.
Logical operators are discussed in detail in the next section.

SQL WHERE Clause

Technorati Tags: Oracle SQL,SQL WHERE,Oracle WHERE,Oracle SQL Where clause

The WHERE Clause is used when you want to retrieve specific information from a table excluding other irrelevant data. For example, when you want to see the information about students in class 10th only then you do need the information about the students in other class. Retrieving information about all the students would increase the processing time for the query.
So SQL offers a feature called WHERE clause, which we can use to restrict the data that is retrieved. The condition you provide in the WHERE clause filters the rows retrieved from the table and gives you only those rows which you expected to see. WHERE clause can be used along with SELECT, DELETE, UPDATE statements.

Syntax of SQL WHERE Clause:

WHERE {column or expression} comparison-operator value
Syntax for a WHERE clause with Select statement is:
SELECT column_list FROM table-name
WHERE condition;


• column or expression - Is the column of a table or a expression
  
• comparison-operator - operators like = < > etc.
  
• value - Any user value or a column name for comparison

For Example: To find the name of a student with id 100, the query would be like:
SELECT first_name, last_name FROM student_details WHERE id = 100;
Comparison Operators and Logical Operators are used in WHERE Clause. These operators are discussed in the next chapter.
NOTE: Aliases defined for the columns in the SELECT statement cannot be used in the WHERE clause to set conditions. Only aliases created for tables can be used to reference the columns in the table.

How to use expressions in the WHERE Clause?

Expressions can also be used in the WHERE clause of the SELECT statement.
For example: Lets consider the employee table. If you want to display employee name, current salary, and a 20% increase in the salary for only those products where the percentage increase in salary is greater than 30000, the SELECT statement can be written as shown below
SELECT name, salary, salary*1.2 AS new_salary FROM employee WHERE salary*1.2 > 30000;
Output:

name	salary	new_salary
Khurram	35000	37000
Faraz	35000	37000
Ayaz	30000	360000

Setting up Oracle DataGuard for 10g

Technorati Tags: DataGuard for oracle 10g

[courtesy: avdeo.com]
Introduction
Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
Data Guard Configurations:
A Data Guard configuration consists of one production database and one or more standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided they can communicate with each other. For example, you can have a standby database on the same system as the production database, along with two standby databases on other systems at remote locations.
You can manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager.
Primary Database
A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role. This is the database that is accessed by most of your applications.
The primary database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.
Standby Database
A standby database is a transactionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to nine standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database.
Similar to a primary database, a standby database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.
A standby database can be either a physical standby database or a logical standby database:

Physical standby database

Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database by recovering the redo data received from the primary database.

Logical standby database

Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database by transforming the data in the redo received from the primary database into SQL statements and then executing the SQL statements on the standby database. A logical standby database can be used for other business purposes in addition to disaster recovery requirements. This allows users to access a logical standby database for queries and reporting purposes at any time. Also, using a logical standby database, you can upgrade Oracle Database software and patch sets with almost no downtime. Thus, a logical standby database can be used concurrently for data protection, reporting, and database upgrades.

For Detailed tutorial please click here

ORA-12547: TNS:lost contact

I get this error while installing Oracle 10g R2 on Redhat Enterprise Linux 5. So I install the following package and problem resolved:
libaio-0.3.105-2.i386.rpm

Technorati Tags: ORA-12547: TNS:lost contact,ORA-12547 linux,libaio-0.3.105-2.1386.rpm

Download it from : http://rpm.pbone.net/index.php3/stat/4/idpl/5184007/com/libaio-0.3.105-2.i386.rpm.html

Exception in thread "main" java.lang.InternalError: Can't connect to X11 window server using ':0' as the value of the DISPLAY variable.

Technorati Tags: Exception in thread "main" java.lang.InternalError: Can't connect to X11 window server using ':0' as the value of the DISPLAY variable,Linux Exception in thread "main" java.lang

I get the following Exception before start installation of Oracle 10g R2 on Redhat Enterprise Linux 5 :
Exception in thread "main" java.lang.InternalError: Can't connect to X11 window server using ':0.0' as the value of the DISPLAY variable.
at sun.awt.X11GraphicsEnvironment.initDisplay(Native Method)
at sun.awt.X11GraphicsEnvironment.access$000(X11GraphicsEnvironment.java:53)
at sun.awt.X11GraphicsEnvironment$1.run(X11GraphicsEnvironment.java:142)
at java.security.AccessController.doPrivileged(Native Method)
at sun.awt.X11GraphicsEnvironment.<clinit>(X11GraphicsEnvironment.java:131)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:164)
at java.awt.GraphicsEnvironment.getLocalGraphicsEnvironment(GraphicsEnvironment.java:68)
at sun.awt.motif.MToolkit.<clinit>(MToolkit.java:93)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:164)
at java.awt.Toolkit$2.run(Toolkit.java:821)
at java.security.AccessController.doPrivileged(Native Method)
at java.awt.Toolkit.getDefaultToolkit(Toolkit.java:804)
at javax.swing.UIManager.initialize(UIManager.java:1262)
at javax.swing.UIManager.maybeInitialize(UIManager.java:1245)
at javax.swing.UIManager.getDefaults(UIManager.java:556)
at com.tibco.administrator.wizard.WizardDialog.setUIFont(WizardDialog.java:208)
at com.tibco.administrator.du.DomainUtility.a(DomainUtility.java:593)
at com.tibco.administrator.du.DomainUtility.main(DomainUtility.java:427)
 
 
So I just Issue the following command:
export DISPLAY=:0
and problem resolved

Uninstalling VMware Tools To remove VMware Tools from your Linux guest operating system

Uninstalling VMware Tools

To remove VMware Tools from your Linux guest operating system, log on as root (su -) and enter the following co

Technorati Tags: Uninstalling VMware Tools,Uninstalling VMware Tools Linux

mmand:

From an RPM install
$ rpm -e VMwareTools

Failed to auto-start Oracle Net Listener using /ade/vikrkuma_new/oracle/bin/tnslsnr

Technorati Tags: Failed to auto-start Oracle Net Listene using /ade/vikrkuma_new/oracle/bin/tnslsnr,/ade/vikrkuma_new/oracle/bin/tnslsnr

Edit the file $ORACLE_HOME/bin/dbstart to change this line. Issue following command:
$ vi $ORACLE_HOME/bin/dbstart
Change the following line:
ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle
With the following:
ORACLE_HOME_LISTNER=$ORACLE_HOME

Set ORACLE_SID in Linux

Technorati Tags: ORACLE_SID in linux,ORACLE_SID

First check the oralce sid by:
$ echo $ORACLE_SID
If you want to change the SID then use following command:
$ export ORACLE_SID=<database sid>

Installing VMware Tools from the Command Line with the RPM Installer on Linux

Technorati Tags: Install VMware tools,Linux VMware tools,WMware RMP install

1- Power-On the virtual machine.
2- From toolbar click VM -- >Install VMware Tools
3- Right click on desktop and Open Terminal Window.
4- Isuue the following command to mount the CD-Rom
$ mount /dev/cdrom /mnt/cdrom
Now there should be a CD-Rom Icon on desktop.
Note: If you have a previous installation, delete the previous vmware-distrib directory before installing. The default location of this directory is
/tmp/vmware-tools-distrib.
5- Issue the following command
$ cd /mnt/cdrom
$ rpm –Uhv VMwareTools-5.0.0-<xxxx>.i386.rpm
$ vmware-config-tools.pl
Now VMware tool should start installation.
6- Now at the end, unmount the CD-Rom
$ umount /dev/cdrom
 
Thats It..!!

How to get the DBCA in Linux Environment

Technorati Tags: Oracle 10g,DBCA,Oracle Linux

$ cd $ORACLE_HOME/bin
$ dbca

Setting up Oracle DataGuard for 10g

Setting up Oracle DataGuard for 10g


[courtesy: avdeo.com]

Introduction
Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
Data Guard Configurations:
A Data Guard configuration consists of one production database and one or more standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided they can communicate with each other. For example, you can have a standby database on the same system as the production database, along with two standby databases on other systems at remote locations.
You can manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager.
Primary Database
A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role. This is the database that is accessed by most of your applications.
The primary database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.
Standby Database
A standby database is a transactionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to nine standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database.
Similar to a primary database, a standby database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.
A standby database can be either a physical standby database or a logical standby database:

Physical standby database

Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database by recovering the redo data received from the primary database.

Logical standby database

Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database by transforming the data in the redo received from the primary database into SQL statements and then executing the SQL statements on the standby database. A logical standby database can be used for other business purposes in addition to disaster recovery requirements. This allows users to access a logical standby database for queries and reporting purposes at any time. Also, using a logical standby database, you can upgrade Oracle Database software and patch sets with almost no downtime. Thus, a logical standby database can be used concurrently for data protection, reporting, and database upgrades.

For Detail plz click here