How do you find Oracle SID and ORACLE_HOME?

ps -aef | grep smon

How do you select random rows in Oracle?

SELECT *
FROM
( SELECT *
 FROM <TableName>
ORDER BY dbms_random.value )
WHERE rownum <= 10;

How do you execute a UNIX command within the SQLPlus prompt?

Use "Osexec()".
For example:
select OSexec('/usr/bin/date') as STDOUT from dual;

How do you ensure the listener for a service on Oracle Network is up?

Use "tnsping" utility available in the "HOME/bin" directory of ORACLE to test if a remote listener is up (checks if the socket is reachable). This utility only ensures the availability of a listener; but not about the databases behind the listener.

What is the difference between ROWNUM and ROWID in Oracle?

ROWID is the unique physical address of every row of a table maintained by database automatically. ROWNUM is the sequential number allocated to each row in the result set object during query execution.

ROWID is permanent whereas ROWNUM is temporary. ROWID is 16-bit hexadecimal whereas ROWNUM is numeric.

How do you insert data into the table of a database in Oracle in Perl?

use DBI;

$oracle_server = 'localhost';
$oracle_user = '<UserName>';
$oracle_passwd = '<Password>';
$oracle_sid = 'ORCL';
$oracle_table = 'PerlDB';
$oracle_port = '1521';

$dbh = DBI->connect("dbi:Oracle:host=$oracle_server; port=$oracle_port; sid=$oracle_sid", $oracle_user, $oracle_passwd, { RaiseError => 1, AutoCommit => 0}) or die "$DBI::errstr\n";

my $sql = "INSERT INTO $oracle_table (EMP_NUM, EMP_NM, JOB, MNGR_ID, SAL) VALUES (?, ?, ?, ?, ?)";

my $rv = $dbh->do($sql, undef, 21295, 'RABINDRA', 'PROGRAMMER', 7839, 8000) or die "$DBI::errstr";
$dbh->disconnect();

How do you connect Perl and Oracle on Windows?

#!C:/Perl/bin/perl

use DBI;

$oracle_server = 'localhost';
$oracle_user = '<UserName>';
$oracle_passwd = '<Password>';
$oracle_sid = '<SID>';
$oracle_table = 'EMP';
$oracle_port = '1521';

# Get a database handle by connecting to the database
$dbh = DBI->connect("dbi:Oracle:host=$oracle_server; port=$oracle_port; sid=$oracle_sid", $oracle_user, $oracle_passwd, { RaiseError => 1, AutoCommit => 1}) or die "$DBI::errstr\n";

my $sql = "SELECT E.EMPNO AS EMPLOYEE_ID,
   E.ENAME AS EMPLOYEE_NM,
   M.ENAME AS MNGR_NM
   FROM   $oracle_table E
   LEFT OUTER JOIN $oracle_table M
            ON E.MGR = M.EMPNO
   ORDER  BY E.EMPNO";

my $sth = $dbh->prepare($sql) or die "$DBI::errstr";
$sth->execute() or die "$! $?\n";

my $COL_NM = join("\t", @{$sth->{NAME}});
print "$COL_NM\n";

while (my @row = $sth->fetchrow_array())
{
    print join("\t", @row), "\n";
}

$sth->finish();
$dbh->disconnect();

How can you get column names of a table in Oracle?

SELECT column_name
FROM USER_TAB_COLUMNS
WHERE table_name = '<TableName>';

How could you find out all the table names having a specific column name in Oracle?

SELECT TABLE_NAME
FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME = '<ColumnName>';

SELECT TABLE_NAME
FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME = '<ColumnName>';

SELECT TABLE_NAME
FROM DBA_TAB_COLUMNS
WHERE COLUMN_NAME = '<ColumnName>';

- USER_TAB_COLS for tables owned by the current user and ALL_TAB_COLS or DBA_TAB_COLS for tables owned by all users.

How can you get the First/Top N rows in Oracle?

select *
from <TableName>
where rownum <= n;

How do you create temporary tables in Oracle?

Oracle database allows us to create Temporary tables of two types:

1- Transaction Specific Temporary Table
2- Session Specific Temporary Table

A transaction specific temporary table holds data when a transaction begins with execution of first SQL statement and ends either by commit or rollback. The following command creates a transaction specific temporary table.

CREATE global TEMPORARY TABLE temp_table
  (
     emp_id   NUMBER,
     emp_name VARCHAR2(50)
  )
ON COMMIT DELETE ROWS;

Note :- If "ON COMMIT" clause is omitted, by default oracle creates a transaction specific temporary table.

A session specific temporary table holds data until session lasts. When a commit is performed on session specific temporary table, data is preserved in table. But the data is not visible to others session. The data is lost when session ends.

Session specific temporary table can be created using “PRESERVE ROWS” in ON COMMIT clause.

CREATE global TEMPORARY TABLE temp_table
  (
     emp_id   NUMBER,
     emp_name VARCHAR2(50)
  )
ON COMMIT preserve ROWS;

The definition of temporary table is visible to all the sessions. Unlike permanent table, segment for temporary table is allocated when first INSERT statement is executed on temporary table. Indexes can also be created on temporary tables. The scope and life time of these indexes is similar to temporary tables.

How can we load bulk data into Oracle database from a flat file?

SQL*Loader (sqlldr or sqlload) is a bulk loader utility used for moving data from external files into the Oracle database.

The synatx of command is as follows:

sqlldr userid=scott/tiger control=user.ctl log=user.log direct=y

This sample control file (user.ctl) will load an external data file containing comma (by default) delimited data:

LOAD DATA
INFILE '/home/nayakr/User_Permission.csv'
BADFILE ‘/home/nayakr/User_Permission.bad’
DISCARDFILE ‘/home/nayakr/User_Permission.bad’
INSERT INTO TABLE User_Apps
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'   
( USER, APPS, PERMS )

The 'User_Permission.csv' contains following data:

"NayakR", "Oracle", 2
"NayakR", "DB2", 99
"NayakR", "Sybase", 1

How can we execute a '.sql' file from shell script with parameters?

#!/usr/bin/sh

sqlplus -S  <User Id>/<Password> @<path>/script.sql  $param1 $param2 <<ENDOFSQL > $0.$$.`date "+%Y%m%d"`.log
ENDOFSQL