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
  • 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.


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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Sub-Query Help 22 62
Create Index on a Materialized View 5 24
pl/sql - query very slow 26 61
export sql results to csv 6 36
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

821 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