How to Install and Configure Oracle on Linux

Oracle RDBMS is a full-featured relational database management system from Oracle Corporation. It includes a set of administration tools, and precompilers for most programming languages. This article will cover how to install and configure an Oracle database on Linux.

The installation file for Oracle 8.0.5 Enterprise Edition is named Oracle8051EE_Intel.tgz. It can be downloaded from ftp://technet.oracle.com/. If needed, the glibc patch file glibcpatch.tgz can be downloaded from ftp://ftp.oracle.com/pub/www/otn/linux/.

A multitude of directories are created during the installation process. Some of the more important ones are:

• $ORACLE_HOME/bin contains the executables for the database and administrative software.
• $ORACLE_HOME/rdbms/admin contains the SQL scripts used to create the catalog, and other useful scripts too numerous to cover here. Refer to the Oracle Database Administration Manual for an explanation of the scripts contained in this directory.
• $ORACLE_BASE/admin/SID/bdump contains the alert log for the instance. The name of the alert log is alert_SID.log, where SID is the system identifier for the instance (i.e., alert_greg.log for this installation). This file is extremely important in determining where problems lie. Any time you have an error or database crash, this is the first place to look for information on what might have happened to cause the problem.
• $ORACLE_HOME/network/admin contains the tnsnames.ora and listener.ora files. Both of these files are described in the section on modifying configuration files.
• $ORACLE_HOME/precomp/demo/proc contains several Pro*C example programs.
  
  For More details Check Install and Configure Oracle on Linux

How to set home solaris private network?

Question:

I just purchased 2 Ultra 10 servers and I want to practice with home networking. I want to create a private network where I can connect both boxes to the internet via broadband using my four port adsl modem/router. My ISP assigns me 192.168 addresses via DHCP and I use the ISP's DNS but what I'm interested in is:

1. Configuring my own internal DNS

2. Creating my own internal network with different ip addresses.

I will be adding some windows pc to the network soon but I just need guidance or tips on where to start, ipaddressing, firewalls...routing, setting up internal mail etc. I'm new to this and I'll be glad with all the help I can get. By the way, OS on both boxes is Solaris 10.

How to set ORACLE_HOME Path in Unix and Linux?

ORACLE_HOME refers to either:

1. a directory where the Oracle software is installed; or
2. an environment variable pointing to the directory where the Oracle software is installed. 

Check current value:

echo The current ORACLE_HOME is %ORACLE_HOME%

Set the ORACLE_HOME environment variable:

set ORACLE_HOME=C:\oracle\ora10.2

Unix & Linux

Check current value:

env | grep ORACLE_HOME

Change the ORACLE_HOME environment variable (valid for bash and ksh):

export ORACLE_HOME=/app/oracle/product/10.2.0/db_1

How to Check What Version of Microsoft .NET Framework is Installed using Registry Editor

Method 1 : Using Registry Editor:
f you are familiar with Windows registry editor (by using regedit command), this should be the easier way to find the version of .NET installed.You just have to navigate to the following registry path and check the value present for the key entry “Version” (which is 3.5.30729… in my case) as shown below.
Path: HKEY_LOCAL_MACHINE\ SOFTWARE\ MICROSOFT\ NET Framework Setup\ NDP\ v3.5

Oracle Pseudocoloumns : LEVEL | Oracle PL/SQL Interview Questions

LEVEL

For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A root row is the highest row within an inverted tree. A child row is any nonroot row. A parent row is any row that has children. A leaf row is any row without children. Figure 2-1 shows the nodes of an inverted tree with their LEVEL values.

 

To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses.

Oracle CURRVAL and NEXTVAL Pseudocoloumns

You can use CURRVAL and NEXTVAL in:

* The SELECT list of a SELECT statement that is not contained in a subquery, materialized view, or view

* The SELECT list of a subquery in an INSERT statement

* The VALUES clause of an INSERT statement

* The SET clause of an UPDATE statement

Restrictions: You cannot use CURRVAL and NEXTVAL:

* A subquery in a DELETE, SELECT, or UPDATE statement

* A query of a view or of a materialized view

* A SELECT statement with the DISTINCT operator

* A SELECT statement with a GROUP BY clause or ORDER BY clause

* A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator

* The WHERE clause of a SELECT statement

* DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement

* The condition of a CHECK constraint

Also, within a single sql statement that uses CURRVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.

Oracle Pseudocolumns : CURRVAL and NEXTVAL

CURRVAL and NEXTVAL

A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in sql statements with these pseudocolumns:

CURRVAL

The CURRVAL pseudocolumn returns the current value of a sequence.

NEXTVAL

The NEXTVAL pseudocolumn increments the sequence and returns the next value.

You must qualify CURRVAL and NEXTVAL with the name of the sequence:

sequence.CURRVAL
sequence.NEXTVAL

To refer to the current or next value of a sequence in the schema of another user, you must have been granted either SELECT object privilege on the sequence or SELECT ANY SEQUENCE system privilege, and you must qualify the sequence with the schema containing it:

schema.sequence.CURRVAL
schema.sequence.NEXTVAL

To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:

schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink

Oracle Pseudocolumns CURRVAL ,NEXTVAL,LEVEL,ROWID,ROWNUM

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:

* CURRVAL and NEXTVAL

* LEVEL

* ROWID

* ROWNUM

ROWNUM and ORDER BY in Oracle PL/SQL

From the Oracle7 Server SQL Reference Manual: ROWNUM

Oracle7 assigns a ROWNUM value to each row as it is retrieved, before rows are sorted for an ORDER BY clause, so an ORDER BY clause normally does not affect the ROWNUM of each row. However, if an ORDER BY clause causes Oracle7 to use an index to access the data, Oracle7 may retrieve the rows in a different order than without the index, so the ROWNUMs may differ than without the ORDER BY clause.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM emp WHERE ROWNUM < 10;

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE tabx SET col1 = ROWNUM;

Note that conditions testing for ROWNUM values greater than a positive integer are always false.

For example, this query returns no rows:

SELECT * FROM emp WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row
to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition
false. All rows subsequently fail to satisfy the condition, so no rows are returned.

Sleep function in Oralce PL / SQL

The following small function attempts to obtain a system resource, eg if an index should be created on a "busy" table. You wait a while and tried until the counter has reached the maximum.
CREATE OR REPLACE PROCEDURE Sleep_function IS
  GotIt  BOOLEAN := FALSE;
  Count  NUMBER  := 0;
BEGIN
  WHILE (NOT GotIt AND NOT (Count > 10)) LOOP
    BEGIN
      -- Try to get free slot, if OK, set GotIt = TRUE
      -- else EXCEPTION will automatically fire.
      (Insert Code here)
      GotIt := TRUE;
    EXCEPTION
      WHEN OTHERS THEN
        GotIt := FALSE;
        DBMS_LOCK.SLEEP(10);
        Count := Count + 1;
    END;
  END LOOP;
END;
/ 

Where are the default parameters defined NLS in Oracle

SELECT * FROM nls_session_parameters;
SELECT * FROM nls_instance_parameters;
SELECT * FROM nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_DATE_FORMAT                DD-MON-YY
NLS_DATE_LANGUAGE              AMERICAN
NLS_CHARACTERSET               WE8ISO8859
NLS_SORT                       BINARY
NLS_CALENDAR                   GREGORIAN
NLS_RDBMS_VERSION              7.3.4.4.0

Oralce Pseudocolumns ROWNUM |SELECT...where ROWNUM > 1 doesn't work??

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM employees WHERE ROWNUM < 10;

If an ORDER BY clause follows ROWNUM in the same query, the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:

SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query":

SELECT * FROM
(SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;

In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by employee_id in the subquery.

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM employees
WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE my_table
SET column1 = ROWNUM;