Pages

Thursday, July 23, 2015

Virtual Columns in oracle 11g

Virtual Columns:

Oracle 11g introduced the concept of ‘Virtual Column’ within a table. Virtual Columns are similar to normal table’s columns but with the following differences:

They are defined by an expression. The result of evaluation of this expression becomes the value of the column. The values of the virtual column are not stored in the database. Rather, it’s computed at run-time when you query the data. You can’t update (in SET clause of update statement) the values of virtual column. These are read only values, that are computed dynamically and any attempt to modify them will result into oracle error.

The syntax for defining a virtual column is:

column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]

where the parameters within [ ] are optional and can be omitted. If you don’t mention the datatype, Oracle will decide it based on the result of the expression.

Excepting the above points, a virtual column, exists just like any other column of a normal table and the following points apply to it:

Virtual columns can be used in the WHERE clause of UPDATE and DELETE statement but they can’t be modified by DML.
Statistics can be collected on them.
They can be used as a partition key in virtual column based partitioning.
Indexes can be created on them. As you might have guessed, oracle would create


  • Function based indexes as we create on normal tables. 
  • Constraints can be created on them.


For creating a virtual column, use the syntax mentioned above. Consider the following example:

CREATE TABLE VTEST
(
    emp_id        NUMBER,
    emp_no        VARCHAR2(50),
    monthly_sal    NUMBER(10,2),
    bonus          NUMBER(10,2),
    tot_sal      NUMBER(10,2) GENERATED ALWAYS AS (monthly_sal*10 + bonus)
);

Here we have defined a virtual column “total_sal” whose value would be dynamically calculated using the expression provided after the “generated always as” clause. Please note that this declaration is different than using “default” clause for a normal column as you can’t refer column names with “default” clause.

Lets check the data dictionary view:

SELECT column_name, data_type, data_length, data_default, virtual_column
  FROM user_tab_cols
 WHERE table_name = 'VTEST';

COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_DEFAULT | VIRTUAL_COLUMN
EMP_ID                   | NUMBER      | 22         | null                     | NO           
EMP_NO                 | VARCHAR2  | 50         | null                     | NO           
MONTHLY_SAL   | NUMBER       | 22         | null                     | NO           
BONUS                   | NUMBER       | 22         | null                     | NO           
TOT_SAL           | NUMBER      | 22         | "MONTHLY_SAL"*12+"BONUS" | YES 


Lets test this :

CREATE TABLE VTEST
(
    emp_id        NUMBER,
    emp_no        VARCHAR2(50),
    monthly_sal    NUMBER(10,2),
    bonus          NUMBER(10,2),
    tot_sal      NUMBER(10,2) GENERATED ALWAYS AS (monthly_sal*10 + bonus)
);

Table created.

when you insert data into table you only insert for non virtual columns. See below


SQL> INSERT INTO VTEST (emp_id, emp_no, monthly_sal, bonus) values (1,'arvind',10000,1000);

1 row created.

SQL> INSERT INTO VTEST (emp_id, emp_no, monthly_sal, bonus) values (2,'reddy',20000,2000);

1 row created.

SQL> commit;

Commit complete.


SQL> select * from vtest;

   EMPL_ID EMPL_NM     MONTHLY_SAL BONUS   TOT_SAL
---------- ---------- ---------- ------------ ----------
         1 arvind              10000 1000     121000
         2 reddy   20000 2000     242000

Configure Putty tunnel

WinSCP Config

The WinSCP Config is quite simple and utilizes its “tunnel” feature. Open WinSCP and configure a saved session for the final destination host as follows:

On the Session page, fill in the hostname and user name for the final destination host. Leave the password blank.
Check the “Advanced options” box in the login dialog.
Select the Connection –> Tunnel page.
Check the “Connect through SSH tunnel” box.
Fill in the Host name and user name of the intermediate host. Leave the password blank.
Save the session using the button in the lower right-hand corner of the window.

When you log in using the new profile, you will be prompted for two passwords. The first is for your account on the intermediate host, and the second is for your account on the final-destination host. After login, the bounce is entirely transparent and WinSCP works as if you had connected directly to the final-destination host. The connection process can be made even more transparent and secure by using public key authentication with Pageant instead of passwords.


Putty Config

The Putty setup is slightly more complicated and requires that public key authentication be used on the intermediate host. It utilizes Putty’s “local proxy” feature, which allows you to specify an arbitrary command on the local machine to act as a proxy. Instead of creating a TCP connection, PuTTY will communicate using the proxy program’s standard input and output streams. Our local proxy will be plink, which is a command-line ssh connection program included in the Putty default installation. Plink’s -nc option provides functionality similar to the ProxyCommand/netcat approach, but does so using the ssh server’s native port-forwarding interface and does not require that netcat be installed on the intermediate system. To set things up, configure a saved session for the final destination host:

Configure public key authentication for the intermediate host and make sure it works.

Start putty and on the “Session” page of the “Putty Configuration Dialog” that appears, fill in the host name and user name for the final destination host.

Switch to the Connection –> Proxy page, select “Local” as the proxy type enter the following as the local proxy command: plink.exe intermediate.proxy.host -l username -agent -nc %host:%port

Save the session.

C:\Program Files (x86)\PuTTY\plink.exe 10.194.0.0210 -l username -agent -nc %host:%port


connect %host %port\n

PRVF-7532 : Package "libaio-0.3.105 (i386)" is missing - Oracle installation requires old rpm versions, what to do?

Below packages can usually be ignored as oracle installer thinks they are not available and are still needed while they already exists.



[root@rac2 Desktop]# yum install libaio-0.3.105-2.i386.rpm

Loaded plugins: refresh-packagekit, security

Setting up Install Process

No package libaio-0.3.105-2.i386.rpm available.

Error: Nothing to do

[root@rac2 Desktop]# rpm -ivh compat-libstdc++*

error: File not found by glob: compat-libstdc++*

[root@rac2 Desktop]# ps -ef |grep compat-libstdc++*

root     11837  7817  0 19:27 pts/0    00:00:00 grep compat-libstdc++*

[root@rac2 Desktop]# rpm -qa|grep compat-libstdc++*

compat-libstdc++-296-2.96-144.el6.i686

compat-libstdc++-33-3.2.3-69.el6.x86_64


Reference :

You can also see below notes from this link:

Installing Oracle Software

Install Oracle software without clicking through Oracle Universal Installer (OUI). Use instead smart swInst action of install Manager which installs Oracle SW with OUI silently. Add your own response files if you prefer another ORACLE_HOME destination.
$ bash
$ cd $INSTALL_CONF
$ cp sample/swInstEeSrv1-linux-x86_64.cfg .
$ installManager swInst swInstEeSrv1-linux-x86_64.cfg
$ su -
# /opt/oracle/eesrv/11.2.0/db1/root.sh
# exit
Oracle Universal Installer prints beginning with 11g R2 also warnings for optional requirements. Identify the errors and decide whether they can be ignored or not. The next example shows errors which can usually be ignored since OUI does not recognize newer packages.
20101205_185530: Info: Grepping for PRVF- pattern in /opt/oracle/oraInventory/logs/installActions2010-12-05_06-40-15PM.log
INFO: Error Message:PRVF-7532 : Package "libaio-0.3.105 (i386)" is missing on node "<node_name>"
INFO: Error Message:PRVF-7532 : Package "compat-libstdc++-33-3.2.3 (i386)" is missing on node "<node_name>"
INFO: Error Message:PRVF-7532 : Package "libaio-devel-0.3.105 (i386)" is missing on node "<node_name>"
INFO: Error Message:PRVF-7532 : Package "libgcc-3.4.6 (i386)" is missing on node "<node_name>"
INFO: Error Message:PRVF-7532 : Package "libstdc++-3.4.6 (i386)" is missing on node "<node_name>"
INFO: Error Message:PRVF-7532 : Package "unixODBC-2.2.11 (i386)" is missing on node "<node_name>"
INFO: Error Message:PRVF-7532 : Package "unixODBC-devel-2.2.11 (i386)" is missing on node "<node_name>"
INFO: Error Message:PRVF-7532 : Package "pdksh-5.2.14" is missing on node "<node_name>"