oracle command

Posted on 2014-07-31
Last Modified: 2014-08-03
Dear all,

What is the use of the following command:

1) export ORACLE_SID=xxxx
2)  !ps =eaf | grep -1 pmon
3) select <text> from dual;
4) . oraenv
        -> after that we need to input the sid of a named sid

any failover guide of RAC from one node to the other node and from site to site in case of DR ? I am not saying DataGuard
Question by:marrowyung
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3

Assisted Solution

by:venkatesh packiyanathan
venkatesh packiyanathan earned 100 total points
ID: 40231437
Setting oracle environment variables if there is no database indentification is shown
List the number database instance running on the server
select text from a dual table in oracle
.oraenv is a file in oracle which holds certain
LVL 22

Expert Comment

by:Steve Wales
ID: 40231878
1) export ORACLE_SID=xxxx

Setting ORACLE_SID sets an environment variable so that if you just type "sqlplus" on your database server, it will make a local connection to the database using the SID defined in /etc/oratab (or /var/opt/oracle/oratab on Solaris - where ever it is, or the service name as defined on Windows)

2)  !ps =eaf | grep -1 pmon

Done at the Unix shell, I would think that this is going to give you an error.  Done from within sqlplus (or any other process) it will shell out to the OS (that's what the ! does) and return a process list (do man ps to see what the options e, a and f do for ps) and then pipe the output to grep where it is pattern matching for the string pmon.

(I will also assume that =eaf is a typo, it should be -eaf)

Since every database should have pmon as a mandatory process, it can give you an idea of what databases you have running.  Presence of a pmon process does not mean that your database is operational though, just be aware.  Databases started but not mounted, or mounted but not open will still have a pmon process.

The -1 passed to grep shows the one line either side in the text you're matching.  -2 would show two lines either side.

For example I have databases dev, dev2 and tst on a server:
root      2099     1  0 Apr03 ?        00:00:00 /usr/sbin/mcelog --daemon
oracle    2133     1  0 Jul23 ?        00:02:40 ora_pmon_dev
oracle    2135     1  0 Jul23 ?        00:02:38 ora_psp0_dev
oracle    2208     1  0 Jul23 ?        00:00:08 ora_q001_dev
oracle    2252     1  0 Jul23 ?        00:02:32 ora_pmon_dev2
oracle    2254     1  0 Jul23 ?        00:02:34 ora_psp0_dev2
oracle    2337     1  0 Jul23 ?        00:00:28 ora_smco_dev
oracle    2381     1  0 Jul23 ?        00:02:36 ora_pmon_tst
oracle    2383     1  0 Jul23 ?        00:02:37 ora_psp0_tst
root      7159  6741  0 09:06 pts/0    00:00:00 ps -eaf
root      7160  6741  0 09:06 pts/0    00:00:00 grep -1 pmon
gdm      28845  3419  0 Apr21 ?        00:04:44 /usr/libexec/gdm-simple-greeter

Open in new window

It found 3 pmon processes, the grep for pmon and returned the 1 line either side of what it found in the process list.  Not very useful in my mind for the output of a process list, but there you go.

3) select <text> from dual;

The DUAL table is a special 1 row / 1 column table.  Tom Kyte and the Oracle documentation explain what it is in detail:

4) . oraenv
        -> after that we need to input the sid of a named sid

Oraenv is a script that is provided by Oracle to set up your shell environment to point to a particular database.  It sets appropriate environment variables so you can just type sqlplus or rman or whatever and connect directly to the database  you specified.

You can even do this:

export ORACLE_SID=orcl
. oraenv

Then it will take the value assigned to ORACLE_SID and set the environment without further prompting because you set ASK to no.

That is particularly useful for setting the environment in a crontab script, for example.

Author Comment

ID: 40233665

very god answer.

from that link:
DUAL exists solely as a means to have a 1 row table
we can reliably select from.  Thats all."

then I dont understnad why we need that and it seems that the rest of the talbe is not reliably ? or dual is a old style Oracle stuff hasn't been remove yet ?

it seems if we do this :

 . oraenv

we don't even need this:

export ORACLE_SID=orcl

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.


Author Comment

ID: 40233803
also, what is :

1) after we do .oraenv and set an environment,

we can do this

crsctl stat res -t

Open in new window

LVL 22

Expert Comment

by:Steve Wales
ID: 40234576
Yes, if you do ". oraenv", you don't need to export ORACLE_SID (unless, as I mentioned, you're trying to script it in which case you can set ORACLE_SID and ORAENV_ASK and then run . oraenv).

crsctl apparently is the main controlling program for Oracle Clusterware.  I have never used that product so can't comment any further on what crsctl is doing with any experience backing me.

However, the crsctl utility is documented here:

Author Comment

ID: 40237211

but any more reasonable explanation about the dual table one why 1 row is the return and how important Is it?

"crsctl apparently is the main controlling program for Oracle Clusterware.  I have never used that product so can't comment any further on what crsctl is doing with any experience backing me."

then what program you use for that ?
LVL 22

Accepted Solution

Steve Wales earned 400 total points
ID: 40238226
We're not using that part of Oracle functionality at my site.  No RAC in use here.

I don't know that I can give you more than what's in to documentation.

However, a few other places have articles on it - what it is, what it does etc

Oracle docs:
Wikipedia Article:
ORAFAQ site article:

I like the description from the original developer of the table: "I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one."

It's for use in a place where you need to query something but not from a table.

Oracle SELECT syntax REQUIRES a FROM be used..  You can't just go SELECT SYSDATE (SQL server supports that kind of syntax, Oracle requires the FROM).

In Oracle you need to go SELECT SYSDATE FROM DUAL;

This allows you to select from this dummy, pseudo table.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question