Archive

Archive for June, 2011

DDL_LOCK_TIMEOUT — new 11g wait parameter

June 27, 2011 Leave a comment

DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.

If a lock is not acquired before the timeout period expires, then an error is returned.

SQL> alter session set ddl_lock_timeout=1000001;
ERROR:
ORA-00068: invalid value 1000001 for parameter ddl_lock_timeout, must be
between 0 and 1000000

Elapsed: 00:00:00.00

<oracle:ora11gr21>:/home/oracle=>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 26 20:19:28 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter ddl_lock_timeout

NAME                                 TYPE        VALUE
———————————— ———– ——————————
ddl_lock_timeout                     integer     0

As of now DDL lock wait option is not set.

 

SQL> create table ddl_lock_timeout_test (CUSTOMER_ID number(10),
2  CUSTOMER_NAME varchar2(20));

Table created.

SQL> select * from ddl_lock_timeout_test;

CUSTOMER_ID CUSTOMER_NAME
———– ——————–
1 UMA

open a one more session and update the existing row.

SQL> update ddl_lock_timeout_test set CUSTOMER_NAME=’UMA KATRU’ where CUSTOMER_ID=1;

1 row updated.

alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20);

 

SQL> alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20);
alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

SQL> set timing on
SQL> alter session set ddl_lock_timeout=60;

Session altered.

Elapsed: 00:00:00.02

If we issue alter table statement again, it will not fail immediately instead it will keep trying to get an exclusive lock and execute the command for next 60 seconds and if it fails to obtain lock it will return an error.

SQL> alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20);
alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Elapsed: 00:00:59.02

Now let us issue the same alter statement again. After few seconds or so, go to previous session from which we fired the update statement and commit the change. Once the change is committed, alter table command will go through fine from other session.

SQL> commit;

Commit complete.

SQL> alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20);

Table altered.

Elapsed: 00:00:16.34

 

Oracle High Availability Solutions for Unplanned Downtime

June 26, 2011 Leave a comment

Oracle High Availability Solutions for Unplanned Downtime

Outage Type Oracle Solution Benefits Recovery Time
Computer failures Fast-Start Fault Recovery Tunable and predictable cache recovery Minutes to hoursFoot 1 
RAC Automatic recovery of failed nodes and instances, fast connection failover, and service failover No downtimeFoot 2 
Data Guard Fast Start Failover and fast connection failover Seconds to 5 minutes
Oracle Streams Online replica database No downtime2
Storage failures ASM Mirroring and online automatic rebalance No downtime
RMAN with flash recovery area Fully managed database recovery and managed disk-based backups Minutes to hours
Data Guard Fast Start Failover and fast connection failover Seconds to 5 minutes
Oracle Streams Online replica database No downtime2
Human errors Oracle security features Restrict user access as prevention No downtime
Oracle Flashback technology Fine-grained and database-wide rewind capability < 30 minutesFoot 3 
LogMiner Log analysis Minutes to hours
Data corruption HARD Corruption prevention within a storage array No downtime
RMAN with flash recovery area Online block media recovery and managed disk-based backups Minutes to hours
Data Guard Automatic validation of redo blocks before they are applied, execute fast failover to an uncorrupted standby database Seconds to 5 minutes
Oracle Streams Online replica database No downtime2
Site failures RMAN Fully managed database recovery and integration with tape management vendors Hours to days
Data Guard Fast Start Failover and fast connection failover Seconds to 5 minutesFoot 4 
Oracle Streams Online replica database Seconds to 5 minutes4
Categories: oracle streams

Linux commands for DBA’s and Developers

June 26, 2011 Leave a comment

uptime is a standard Linux command that reports the amount of time that a system has been
running.

sanl001.ukatru.com:/home/ukatru>uptime
17:12:07 up  4:40,  2 users,  load average: 0.00, 0.02, 0.00

uptime provides node availability information and is useful in diagnosing and troubleshooting node evictions across a RAC cluster.it also reports system load over intervals of 1,5 and 15 minutes.

last command is useful to check detailed log of system shutdown and change run levels.

sanl001.ukatru.com:/home/ukatru>last
ukatru   pts/1        76.91.238.138    Sat Jun 25 17:11   still logged in
ukatru   pts/0        76.91.238.138    Sat Jun 25 17:11   still logged in
ukatru   pts/0        76.91.238.138    Sat Jun 25 14:41 – 15:12  (00:30)
ukatru   pts/1        76.91.238.138    Sat Jun 25 13:44 – 14:15  (00:30)
ukatru   pts/0        76.91.238.138    Sat Jun 25 13:20 – 14:19  (00:59)
reboot   system boot  2.6.26.8-1.0.11. Sat Jun 25 12:32          (04:42)
ukatru   pts/1        192.168.2.162    Thu Jun 23 17:32 – 17:35  (00:03)
ukatru   pts/0        192.168.2.162    Thu Jun 23 17:08 – 23:15  (06:06)
ukatru   pts/1        192.168.2.162    Wed Jun 22 20:29 – 08:38  (12:09)
ukatru   pts/0        192.168.2.162    Wed Jun 22 20:15 – 20:32  (00:16)
ukatru   pts/0        192.168.2.162    Tue Jun 21 23:20 – 23:48  (00:27)
ukatru   pts/0        192.168.2.162    Tue Jun 21 21:38 – 22:52  (01:14)
ukatru   pts/0        192.168.2.162    Tue Jun 21 16:02 – 19:21  (03:19)
ukatru   pts/0        192.168.2.162    Sun Jun 19 22:37 – 01:49  (03:11)
ukatru   pts/1        192.168.2.163    Sun Jun 19 22:32 – 22:32  (00:00)
ukatru   pts/0        192.168.2.163    Sun Jun 19 22:28 – 22:34  (00:05)
ukatru   pts/0        192.168.2.163    Sat Jun 18 17:05 – 17:05  (00:00)
ukatru   pts/0        76.91.238.138    Fri Jun 17 00:04 – 00:39  (00:35)
ukatru   pts/0        76.91.238.138    Thu Jun 16 23:45 – 23:53  (00:08)
ukatru   pts/0        76.91.238.138    Thu Jun 16 20:59 – 21:06  (00:07)
ukatru   pts/0        192.168.2.155    Tue Jun 14 22:37 – 23:03  (00:25)
ukatru   pts/0        192.168.2.155    Tue Jun 14 17:03 – 20:39  (03:36)
reboot   system boot  2.6.26.8-1.0.11. Tue Jun 14 10:36         (11+06:38)
ukatru   pts/0        192.168.2.151    Sun Jun 12 20:41 – 21:43  (01:02)
ukatru   pts/0        192.168.2.151    Sat Jun 11 12:33 – 15:02  (02:28)
ukatru   pts/0        192.168.2.155    Wed Jun  8 20:37 – 21:29  (00:51)
ukatru   pts/1        ibmdsl1.ukatru.c Wed Jun  8 20:06 – 20:09  (00:03)
ukatru   pts/1        192.168.2.155    Wed Jun  8 19:58 – 20:04  (00:06)
ukatru   pts/0        192.168.2.155    Wed Jun  8 19:54 – 20:08  (00:14)
ukatru   pts/0        dbsl0001.ukatru. Sun Jun  5 13:56 – 13:59  (00:03)
ukatru   pts/0        dbsl0001.ukatru. Sun Jun  5 12:28 – 12:28  (00:00)
ukatru   pts/0        192.168.2.151    Sat Jun  4 19:36 – 20:12  (00:36)
ukatru   pts/0        192.168.2.151    Sat Jun  4 11:38 – 18:24  (06:45)
ukatru   pts/1        rmanl001.ukatru. Thu Jun  2 22:13 – 22:14  (00:01)
ukatru   pts/0        192.168.2.151    Thu Jun  2 21:02 – 07:34  (10:32)
ukatru   pts/0        192.168.2.151    Thu Jun  2 17:54 – 19:02  (01:07)
ukatru   pts/0        76.91.238.138    Thu Jun  2 09:13 – 09:20  (00:06)
ukatru   pts/0        76.91.238.138    Wed Jun  1 20:30 – 22:14  (01:44)

ps Command:

sanl001.ukatru.com:/home/ukatru>ps -ef | more
UID        PID  PPID  C STIME TTY          TIME CMD
root         1     0  0 12:31 ?        00:00:01 init [3]
root         2     0  0 12:31 ?        00:00:00 [kthreadd]
root         3     2  0 12:31 ?        00:00:00 [migration/0]
root         4     2  0 12:31 ?        00:00:00 [ksoftirqd/0]
root         5     2  0 12:31 ?        00:00:00 [watchdog/0]
root         6     2  0 12:31 ?        00:00:13 [events/0]
root         7     2  0 12:31 ?        00:00:00 [khelper]
root        59     2  0 12:31 ?        00:00:00 [kblockd/0]
root        61     2  0 12:31 ?        00:00:00 [kacpid]
root        62     2  0 12:31 ?        00:00:00 [kacpi_notify]
root       189     2  0 12:31 ?        00:00:00 [ksuspend_usbd]
root       195     2  0 12:31 ?        00:00:00 [khubd]
root       198     2  0 12:31 ?        00:00:00 [kseriod]
root       242     2  0 12:31 ?        00:00:00 [pdflush]
root       243     2  0 12:31 ?        00:00:00 [pdflush]
root       244     2  0 12:31 ?        00:00:00 [kswapd0]
root       322     2  0 12:31 ?        00:00:00 [aio/0]
root       594     2  0 12:31 ?        00:00:00 [kpsmoused]
root       773     2  0 12:31 ?        00:00:00 [ata/0]
root       774     2  0 12:31 ?        00:00:00 [ata_aux]
root      1103     2  0 12:31 ?        00:00:00 [scsi_eh_0]
root      1356     2  0 12:31 ?        00:00:00 [kjournald]
root      2003     1  0 12:31 ?        00:00:00 udevd
root      2718     2  0 12:31 ?        00:00:00 [kstriped]
root      2739     2  0 12:31 ?        00:00:00 [ksnapd]
root      2746     2  0 12:31 ?        00:00:00 [kmpathd/0]
root      2753     2  0 12:32 ?        00:00:00 [kdmflush]
root      2763     2  0 12:32 ?        00:00:00 [kdmflush]
root      2768     2  0 12:32 ?        00:00:00 [kdmflush]
root      2777     2  0 12:32 ?        00:00:00 [kdmflush]
root      2787     2  0 12:32 ?        00:00:00 [kdmflush]
root      2825     2  0 12:32 ?        00:00:00 [kdmflush]
root      2836     2  0 12:32 ?        00:00:00 [kdmflush]
root      2849     2  0 12:32 ?        00:00:00 [kdmflush]
root      2855     2  0 12:32 ?        00:00:00 [kdmflush]
root      2860     2  0 12:32 ?        00:00:00 [kdmflush]
root      2896     2  0 12:32 ?        00:00:00 [kdmflush]
root      2907     2  0 12:32 ?        00:00:00 [kdmflush]
root      2914     2  0 12:32 ?        00:00:00 [kdmflush]
root      2925     2  0 12:32 ?        00:00:00 [kdmflush]
root      2961     2  0 12:32 ?        00:00:00 [kdmflush]
root      2971     2  0 12:32 ?        00:00:00 [kdmflush]

<oracle:>:/home/oracle=>ps -ef | grep pmon
oracle    3974     1  0 13:24 ?        00:00:00 asm_pmon_+ASM1
oracle    4613     1  0 13:26 ?        00:00:00 ora_pmon_ora10gr21
oracle    4727     1  0 13:26 ?        00:00:00 ora_pmon_ora11gr21
oracle    6100     1  0 13:44 ?        00:00:00 ora_pmon_ora11gr11

 

 pgrep, look up or signal processes based on name

<oracle:>:/home/oracle=>pgrep -flu oracle
3613 /u01/app/root/product/11.2.0/grid/bin/oraagent.bin
3625 /u01/app/root/product/11.2.0/grid/bin/mdnsd.bin
3634 /u01/app/root/product/11.2.0/grid/bin/gpnpd.bin
3647 /u01/app/root/product/11.2.0/grid/bin/gipcd.bin
3693 /u01/app/root/product/11.2.0/grid/bin/diskmon.bin -d -f
3708 /u01/app/root/product/11.2.0/grid/bin/ocssd.bin
3796 /u01/app/root/product/11.2.0/grid/bin/evmd.bin
3919 oracle+ASM1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
3974 asm_pmon_+ASM1
3976 asm_psp0_+ASM1
3978 asm_vktm_+ASM1
3982 asm_gen0_+ASM1
3984 asm_diag_+ASM1
3986 asm_ping_+ASM1
3988 asm_dia0_+ASM1
3990 asm_lmon_+ASM1
3992 asm_lmd0_+ASM1
3994 asm_lms0_+ASM1
3998 asm_lmhb_+ASM1
4000 asm_mman_+ASM1
4002 asm_dbw0_+ASM1
4004 asm_lgwr_+ASM1
4006 asm_ckpt_+ASM1
4008 asm_smon_+ASM1
4010 asm_rbal_+ASM1
4012 asm_gmon_+ASM1
4014 asm_mmon_+ASM1
4016 asm_mmnl_+ASM1
4018 asm_lck0_+ASM1
4027 oracle+ASM1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
4040 oracle+ASM1_ocr (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
4042 asm_asmb_+ASM1
4044 oracle+ASM1_asmb_+asm1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
4099 /u01/app/root/product/11.2.0/grid/bin/evmlogger.bin -o /u01/app/root/product/11.2.0/grid/evm/log/evmlogger.info -l /u01/app/root/product/11.2.0/grid/evm/log/evmlogger.log
4139 /u01/app/root/product/11.2.0/grid/bin/oraagent.bin
4141 /u01/app/root/product/11.2.0/grid/bin/scriptagent.bin
4143 /u01/app/root/product/11.2.0/grid/bin/appagent.bin
4196 oracle+ASM1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
4471 /u01/app/root/product/11.2.0/grid/opmn/bin/ons -d
4472 /u01/app/root/product/11.2.0/grid/opmn/bin/ons -d
4485 /u01/app/oracle/product/10.2.0.5/db_1/bin/racgimon startd ora10gr2
4488 /u01/app/root/product/11.2.0/grid/jdk/jre//bin/java -server -Xcheck:jni -Xms128M -Xmx384M -Djava.awt.headless=true -Ddisable.checkForUpdate=true -Dstdstream.filesize=100 -Dstdstream.filenumber=10 -DTRACING.ENABLED=false -Doracle.wlm.dbwlmlogger.logging.level=INFO -Dport.rmi=23792 -jar /u01/app/root/product/11.2.0/grid/oc4j/j2ee/home/oc4j.jar -config /u01/app/root/product/11.2.0/grid/oc4j/j2ee/home/OC4J_DBWLM_config/server.xml -out /u01/app/root/product/11.2.0/grid/oc4j/j2ee/home/log/oc4j.out -err /u01/app/root/product/11.2.0/grid/oc4j/j2ee/home/log/oc4j.err
4551 /u01/app/root/product/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
4553 /u01/app/root/product/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit
4555 /u01/app/root/product/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
4557 /u01/app/root/product/11.2.0/grid/bin/tnslsnr LISTENER -inherit
4613 ora_pmon_ora10gr21
4615 ora_diag_ora10gr21
4617 ora_psp0_ora10gr21
4619 ora_lmon_ora10gr21
4621 ora_lmd0_ora10gr21
4623 ora_lms0_ora10gr21
4627 ora_lms1_ora10gr21
4631 ora_mman_ora10gr21
4633 ora_dbw0_ora10gr21
4635 ora_lgwr_ora10gr21
4637 ora_ckpt_ora10gr21
4639 ora_smon_ora10gr21
4641 ora_reco_ora10gr21
4643 ora_cjq0_ora10gr21
4645 ora_mmon_ora10gr21
4648 ora_mmnl_ora10gr21
4650 ora_d000_ora10gr21
4652 ora_s000_ora10gr21
4654 ora_lck0_ora10gr21
4664 oracleora11gr21 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
4667 ora_asmb_ora10gr21
4670 oracle+ASM1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
4675 ora_rbal_ora10gr21
4712 ora_o000_ora10gr21
4727 ora_pmon_ora11gr21
4729 ora_psp0_ora11gr21
4732 ora_vktm_ora11gr21
4738 ora_gen0_ora11gr21
4740 ora_diag_ora11gr21
4742 ora_dbrm_ora11gr21
4744 ora_ping_ora11gr21
4746 ora_acms_ora11gr21
4750 ora_dia0_ora11gr21
4752 ora_lmon_ora11gr21
4754 ora_lmd0_ora11gr21
4756 ora_lms0_ora11gr21
4760 ora_rms0_ora11gr21
4762 ora_lmhb_ora11gr21
4764 ora_mman_ora11gr21
4766 ora_dbw0_ora11gr21
4770 ora_lgwr_ora11gr21
4774 ora_ckpt_ora11gr21
4776 ora_smon_ora11gr21
4778 ora_reco_ora11gr21
4780 ora_rbal_ora11gr21
4782 ora_asmb_ora11gr21
4784 ora_mmon_ora11gr21
4786 ora_mmnl_ora11gr21
4788 oracle+ASM1_asmb_ora11gr21 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
4790 ora_d000_ora11gr21
4792 ora_mark_ora11gr21
4794 ora_s000_ora11gr21
4800 ora_lck0_ora11gr21
4802 ora_rsmn_ora11gr21
4819 ora_qmnc_ora10gr21
4870 oracleora10gr21 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
4873 ora_gtx0_ora11gr21
4875 ora_rcbg_ora11gr21
4877 ora_q000_ora10gr21
4879 ora_q001_ora10gr21
4885 ora_qmnc_ora11gr21
4895 oracleora11gr21 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
4911 oracleora10gr21 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
4924 oracleora11gr21 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
4936 ora_cjq0_ora11gr21
4954 ora_vkrm_ora11gr21
4962 ora_q000_ora11gr21
4964 ora_q001_ora11gr21
5047 oracleora10gr21 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
5575 ora_smco_ora11gr21
6100 ora_pmon_ora11gr11
6102 ora_vktm_ora11gr11
6106 ora_diag_ora11gr11
6108 ora_dbrm_ora11gr11
6110 ora_ping_ora11gr11
6112 ora_psp0_ora11gr11
6114 ora_acms_ora11gr11
6116 ora_dia0_ora11gr11
6118 ora_lmon_ora11gr11
6120 ora_lmd0_ora11gr11
6122 ora_lms0_ora11gr11
6126 ora_rms0_ora11gr11
6128 ora_mman_ora11gr11
6130 ora_dbw0_ora11gr11
6132 ora_lgwr_ora11gr11
6134 ora_ckpt_ora11gr11
6136 ora_smon_ora11gr11
6138 ora_reco_ora11gr11
6140 ora_rbal_ora11gr11
6142 ora_asmb_ora11gr11
6144 ora_mmon_ora11gr11
6146 ora_mmnl_ora11gr11
6148 oracle+ASM1_asmb_ora11gr11 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
6150 ora_d000_ora11gr11
6152 ora_s000_ora11gr11
6157 ora_lck0_ora11gr11
6159 ora_rsmn_ora11gr11
6196 ora_fbda_ora11gr11
6198 ora_gtx0_ora11gr11
6202 ora_qmnc_ora11gr11
6218 ora_rcbg_ora11gr11
6220 ora_cjq0_ora11gr11
6243 ora_q000_ora11gr11
6245 ora_q001_ora11gr11
6841 ora_smco_ora11gr11
8723 ora_o000_ora11gr21
8725 oracle+ASM1_o000_ora11gr21 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
18474 ora_w000_ora11gr11
18626 sshd: oracle@pts/0
18627 -ksh
18650 ora_w000_ora11gr21

pidof — find the process ID of a running program

pidof ora_cjq0_ora11gr11
6220

free command:

free command displays the system virtual memory.

<oracle:>:/home/oracle=>free
total       used       free     shared    buffers     cached
Mem:       3090732    2997904      92828          0      45060    1487364
-/+ buffers/cache:    1465480    1625252
Swap:      2752504          0    2752504

/proc file system:

<oracle:>:/home/oracle=>cat /proc/meminfo
MemTotal:      3090732 kB
MemFree:         86084 kB
Buffers:         45232 kB
Cached:        1487512 kB
SwapCached:          0 kB
Active:        2209004 kB
Inactive:       623068 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:      3090732 kB
LowFree:         86084 kB
SwapTotal:     2752504 kB
SwapFree:      2752504 kB
Dirty:              76 kB
Writeback:           0 kB
AnonPages:     1299228 kB
Mapped:         662096 kB
Slab:            45932 kB
PageTables:      79908 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:   4297868 kB
Committed_AS:  6954712 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    276804 kB
VmallocChunk: 34359460847 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

 

ipcs command :

The ipcs command with the –m argument can be used to display the configured shared
memory segments on the system.

<oracle:>:/home/oracle=>ipcs -m

—— Shared Memory Segments ——–
key        shmid      owner      perms      bytes      nattch     status
0x00000000 0          root      644        80         2
0x00000000 32769      root      644        16384      2
0x00000000 65538      root      644        280        2
0x79e140f0 229379     oracle    660        4096       0
0x6024b7b0 294916     oracle    660        283115520  28
0x54e90348 360453     oracle    660        404750336  41
0x3f1a94e0 425990     oracle    660        387973120  35

lsof Command;lsof is a command that lists the open files on the system

[root@dbsl0001 ~]# lsof -u oracle | more
COMMAND     PID   USER   FD   TYPE             DEVICE      SIZE                 NODE NAME
oraagent.  3613 oracle  cwd    DIR              253,2      4096              1556948 /u01/app/root/product/11.2.0/grid/log/dbsl0001/agent/ohasd/oraagent_orac
le
oraagent.  3613 oracle  rtd    DIR              253,0      4096                    2 /
oraagent.  3613 oracle  txt    REG              253,2  26905268              1230627 /u01/app/root/product/11.2.0/grid/bin/oraagent.bin
oraagent.  3613 oracle  mem    REG              253,0    139416               983378 /lib64/ld-2.5.so
oraagent.  3613 oracle  mem    REG              253,0   1717800               983379 /lib64/libc-2.5.so
oraagent.  3613 oracle  mem    REG              253,0    615136               983089 /lib64/libm-2.5.so
oraagent.  3613 oracle  mem    REG              253,0     23360               983093 /lib64/libdl-2.5.so
oraagent.  3613 oracle  mem    REG              253,0    145824               983380 /lib64/libpthread-2.5.so
oraagent.  3613 oracle  mem    REG              253,0     58400               983333 /lib64/libgcc_s-4.1.2-20080825.so.1
oraagent.  3613 oracle  mem    REG              253,0    114352               983246 /lib64/libnsl-2.5.so
oraagent.  3613 oracle  mem    REG              253,0     17384               983238 /lib64/libcap.so.1.10
oraagent.  3613 oracle  mem    REG              253,0    976312              1908950 /usr/lib64/libstdc++.so.6.0.8
oraagent.  3613 oracle  mem    REG              253,2     59085              1233804 /u01/app/root/product/11.2.0/grid/lib/libnque11.so
oraagent.  3613 oracle  mem    REG              253,2    791430              1237972 /u01/app/root/product/11.2.0/grid/oracore/zoneinfo/timezlrg_14.dat
oraagent.  3613 oracle  mem    REG              253,0     21752              1907194 /usr/lib64/libnuma.so.1
oraagent.  3613 oracle  mem    REG              253,2      1544              1239471 /u01/app/root/product/11.2.0/grid/dbs/hc_+ASM1.dat
oraagent.  3613 oracle  mem    REG              253,2   1559829              1232431 /u01/app/root/product/11.2.0/grid/lib/libocr11.so
oraagent.  3613 oracle  mem    REG              253,2   3295575              1232432 /u01/app/root/product/11.2.0/grid/lib/libocrb11.so
oraagent.  3613 oracle  mem    REG              253,2    150599              1232433 /u01/app/root/product/11.2.0/grid/lib/libocrutl11.so
oraagent.  3613 oracle  mem    REG              253,2  16067428              1232399 /u01/app/root/product/11.2.0/grid/lib/libhasgen11.so
oraagent.  3613 oracle  mem    REG              253,2  52364845              1233777 /u01/app/root/product/11.2.0/grid/lib/libclntsh.so.11.1
oraagent.  3613 oracle  mem    REG              253,2     12763              1233179 /u01/app/root/product/11.2.0/grid/lib/libskgxn2.so
oraagent.  3613 oracle  mem    REG              253,2    891110              1237940 /u01/app/root/product/11.2.0/grid/lib/libasmclntsh11.so
oraagent.  3613 oracle  mem    REG              253,2    532417              1237939 /u01/app/root/product/11.2.0/grid/lib/libcell11.so
oraagent.  3613 oracle  mem    REG              253,2    993144              1230119 /u01/app/root/product/11.2.0/grid/lib/libskgxp11.so
oraagent.  3613 oracle  mem    REG              253,2   7898628              1233839 /u01/app/root/product/11.2.0/grid/lib/libnnz11.so
oraagent.  3613 oracle  mem    REG              253,2   4739109              1231885 /u01/app/root/product/11.2.0/grid/lib/libgns11.so
oraagent.  3613 oracle  mem    REG              253,2    190837              1231614 /u01/app/root/product/11.2.0/grid/lib/libeons.so
oraagent.  3613 oracle  mem    REG              253,2    141441              1231472 /u01/app/root/product/11.2.0/grid/lib/libonsx.so
oraagent.  3613 oracle  mem    REG              253,2    100962              1230579 /u01/app/root/product/11.2.0/grid/lib/libeonsserver.so
oraagent.  3613 oracle  mem    REG              253,0      3768              1908602 /usr/lib64/libaio.so.1.0.1
oraagent.  3613 oracle  mem    REG              253,0     53880               983065 /lib64/libnss_files-2.5.so
oraagent.  3613 oracle    0u   CHR                1,3                           1563 /dev/null
oraagent.  3613 oracle    1u   REG              253,2        50              1557141 /u01/app/root/product/11.2.0/grid/log/dbsl0001/agent/ohasd/oraagent_orac
le/oraagent_oracleOUT.log
oraagent.  3613 oracle    2u   REG              253,2        50              1557141 /u01/app/root/product/11.2.0/grid/log/dbsl0001/agent/ohasd/oraagent_orac
le/oraagent_oracleOUT.log
oraagent.  3613 oracle    3u   REG              253,2        50              1557141 /u01/app/root/product/11.2.0/grid/log/dbsl0001/agent/ohasd/oraagent_orac
le/oraagent_oracleOUT.log
oraagent.  3613 oracle    4w   REG              253,2   5286512              1557968 /u01/app/root/product/11.2.0/grid/log/dbsl0001/agent/ohasd/oraagent_orac
le/oraagent_oracle.log

top command:

top – 17:25:26 up  4:03,  1 user,  load average: 0.85, 0.73, 0.58
Tasks: 257 total,   2 running, 255 sleeping,   0 stopped,   0 zombie
Cpu(s):  2.2%us,  0.5%sy,  0.0%ni, 96.4%id,  0.8%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:   3090732k total,  3006576k used,    84156k free,    45740k buffers
Swap:  2752504k total,        0k used,  2752504k free,  1488304k cached

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
3660 root      RT   0  244m  84m  54m S  1.0  2.8   2:44.36 osysmond.bin
4135 root      15   0  654m  24m  13m S  1.0  0.8   2:25.62 orarootagent.bi
3644 root      15   0  294m  35m  14m S  0.7  1.2   1:50.04 orarootagent.bi
3613 oracle    15   0  307m  35m  15m S  0.3  1.2   0:32.85 oraagent.bin
3708 oracle    RT   0  197m 112m  52m S  0.3  3.7   1:09.78 ocssd.bin
3988 oracle    15   0  492m  29m  18m S  0.3  1.0   0:06.81 oracle
4139 oracle    15   0 1021m  38m  16m S  0.3  1.3   1:03.36 oraagent.bin
4911 oracle    16   0  418m  36m  30m S  0.3  1.2   0:01.10 oracle
19196 oracle    15   0 12872 1224  816 R  0.3  0.0   0:00.08 top
1 root      15   0 10348  696  584 S  0.0  0.0   0:00.52 init
2 root      RT  -5     0    0    0 S  0.0  0.0   0:00.09 migration/0
3 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/0
4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.10 migration/1
5 root      34  19     0    0    0 S  0.0  0.0   0:00.01 ksoftirqd/1
6 root      10  -5     0    0    0 S  0.0  0.0   0:14.58 events/0
7 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 events/1
8 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 khelper
33 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kthread
38 root      10  -5     0    0    0 S  0.0  0.0   0:00.03 kblockd/0
39 root      10  -5     0    0    0 S  0.0  0.0   0:00.02 kblockd/1
40 root      14  -5     0    0    0 S  0.0  0.0   0:00.00 kacpid
201 root      13  -5     0    0    0 S  0.0  0.0   0:00.00 cqueue/0
202 root      13  -5     0    0    0 S  0.0  0.0   0:00.00 cqueue/1
205 root      13  -5     0    0    0 S  0.0  0.0   0:00.00 khubd
207 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kseriod
281 root      18   0     0    0    0 S  0.0  0.0   0:00.00 pdflush
282 root      15   0     0    0    0 S  0.0  0.0   0:00.10 pdflush
283 root      10  -5     0    0    0 S  0.0  0.0   0:00.24 kswapd0
284 root      13  -5     0    0    0 S  0.0  0.0   0:00.00 aio/0
285 root      13  -5     0    0    0 S  0.0  0.0   0:00.00 aio/1
491 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 kpsmoused
534 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 mpt_poll_0
535 root      17  -5     0    0    0 S  0.0  0.0   0:00.00 scsi_eh_0
539 root      17  -5     0    0    0 S  0.0  0.0   0:00.00 ata/0
540 root      17  -5     0    0    0 S  0.0  0.0   0:00.00 ata/1
541 root      17  -5     0    0    0 S  0.0  0.0   0:00.00 ata_aux
548 root      18  -5     0    0    0 S  0.0  0.0   0:00.00 kstriped

vmstat : this command provides usage of virtual memory

<oracle:>:/home/oracle=>vmstat 2 10
procs ———–memory———- —swap– —–io—- –system– —–cpu——
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
0  0      0  79908  45896 1488416    0    0   195    73  534    4  3  1 94  2  0
2  0      0  79892  45904 1488440    0    0   112   135 1065 6091  2  0 96  2  0
9  0      0  80116  45904 1488452    0    0    96    53 1041 6187  6  2 91  1  0
3  0      0  79976  45920 1488436    0    0   152   325 1082 6272  3  1 92  5  0
0  0      0  79984  45920 1488460    0    0   104    39 1041 6065  2  1 95  3  0
0  0      0  79984  45920 1488460    0    0   104   121 1056 5996  1  1 98  0  0
1  0      0  79984  45932 1488456    0    0   120   253 1086 5937  2  0 94  3  0
0  0      0  83464  45932 1488476    0    0   136    41 1045 5914  3  1 94  3  0
1  0      0  77456  45940 1488464    0    0    88   203 1055 6168  5  3 89  3  0
1  0      0  74952  45948 1488480    0    0   128   121 1068 6080  5  2 92  1  0

r (runqueue) The runqueue value shows the number of tasks executing and waiting for CPU resources. When this number exceeds the number of CPUs on the server, a CPU bottleneck exists, and some tasks are waiting for execution

 

us (user CPU) This is the amount of CPU that is servicing user tasks.

id (idle) This is the percentage of CPU that is idle.

wa This shows the percentage of CPU that is waiting on external operations such as disk I/O.

 

/home/oracle=>vmstat -s
3090732  total memory
3008296  used memory
2206784  active memory
623580  inactive memory
82436  free memory
46532  buffer memory
1488928  swap cache
2752504  total swap
0  used swap
2752504  free swap
91686 non-nice user cpu ticks
0 nice user cpu ticks
25391 system cpu ticks
2789213 idle cpu ticks
68024 IO-wait cpu ticks
928 IRQ cpu ticks
4389 softirq cpu ticks
0 stolen cpu ticks
5759252 pages paged in
2161677 pages paged out
0 pages swapped in
0 pages swapped out
15903516 interrupts
87301612 CPU context switches
1309033290 boot time
19487 forks

/home/oracle=>vmstat -d
disk- ————reads———— ————writes———– —–IO——
total merged sectors      ms  total merged sectors      ms    cur    sec
ram0       0      0       0       0      0      0       0       0      0      0
ram1       0      0       0       0      0      0       0       0      0      0
ram2       0      0       0       0      0      0       0       0      0      0
ram3       0      0       0       0      0      0       0       0      0      0
ram4       0      0       0       0      0      0       0       0      0      0
ram5       0      0       0       0      0      0       0       0      0      0
ram6       0      0       0       0      0      0       0       0      0      0
ram7       0      0       0       0      0      0       0       0      0      0
ram8       0      0       0       0      0      0       0       0      0      0
ram9       0      0       0       0      0      0       0       0      0      0
ram10      0      0       0       0      0      0       0       0      0      0
ram11      0      0       0       0      0      0       0       0      0      0
ram12      0      0       0       0      0      0       0       0      0      0
ram13      0      0       0       0      0      0       0       0      0      0
ram14      0      0       0       0      0      0       0       0      0      0
ram15      0      0       0       0      0      0       0       0      0      0
sda     9207  13650 3623091   45711  34889  45188  640586   93870      0     42
sdb    26719  10859 2427826  233096 112109 129812 1935760  622245      0    176
dm-0   18213      0  473498   87351  80072      0  640576  156948      0     39
dm-1     113      0     904     744      0      0       0       0      0      0
fd0        0      0       0       0      0      0       0       0      0      0
hdc     1976      9  503944   29083      0      0       0       0      0      3
md0        0      0       0       0      0      0       0       0      0      0
dm-2   36001      0 1545658  271247 241970      0 1935760  978600      0    175
sdc   132554   2144 4954939  580000  69448     23 1750141  371148      0    657
ofsctl      0      0       0       0      0      0       0       0      0      0

 

netstat command :

/home/oracle=>netstat -i
Kernel Interface table
Iface       MTU Met    RX-OK RX-ERR RX-DRP RX-OVR    TX-OK TX-ERR TX-DRP TX-OVR Flg
eth0       1500   0  2131667      0      0      0  1965277      0      0      0 BMRU
eth0:1     1500   0      – no statistics available –                            BMRU
eth0:2     1500   0      – no statistics available –                            BMRU
eth0:3     1500   0      – no statistics available –                            BMRU
eth0:4     1500   0      – no statistics available –                            BMRU
eth1       1500   0     1772      0      0      0       82      0      0      0 BMRU
eth1:1     1500   0      – no statistics available –                            BMRU
lo        16436   0   129428      0      0      0   129428      0      0      0 LRU

/home/oracle=>mpstat 2
Linux 2.6.18-164.el5 (dbsl0001.ukatru.com)      06/25/2011

05:30:44 PM  CPU   %user   %nice    %sys %iowait    %irq   %soft  %steal   %idle    intr/s
05:30:46 PM  all    3.49    0.00    1.75    0.50    0.00    0.00    0.00   94.26   1046.00

<oracle:>:/home/oracle=>netstat –inet -a | more
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address               Foreign Address             State
tcp        0      0 localhost.locald:bootserver *:*                         LISTEN
tcp        0      0 dbsl0001-priv.ukatru.:63842 *:*                         LISTEN
tcp        0      0 dbsl0001-priv.ukatru.:56931 *:*                         LISTEN
tcp        0      0 *:30181                     *:*                         LISTEN
tcp        0      0 *:64077                     *:*                         LISTEN
tcp        0      0 *:sunrpc                    *:*                         LISTEN
tcp        0      0 orasvcdbsl0001.uka:ncube-lm *:*                         LISTEN
tcp        0      0 dbsl0001-vip.ukatr:ncube-lm *:*                         LISTEN
tcp        0      0 dbsl0001.ukatru.co:ncube-lm *:*                         LISTEN
tcp        0      0 orasvcdbsl0001.uka:ncube-lm *:*                         LISTEN
tcp        0      0 orasvcdbsl0001.uka:ncube-lm *:*                         LISTEN
tcp        0      0 localhost.loc:synchronet-db *:*                         LISTEN
tcp        0      0 *:59764                     *:*                         LISTEN
tcp        0      0 *:iclcnet-locate            *:*                         LISTEN
tcp        0      0 dbsl0001-priv.ukatru.:61020 *:*                         LISTEN
tcp        0      0 orasvcdbsl0001.ukatru:60977 orasvcdbsl0001.uka:ncube-lm ESTABLISHED
tcp        0      0 orasvcdbsl0001.ukatru:60983 orasvcdbsl0001.uka:ncube-lm ESTABLISHED
tcp        0      0 dbsl0001.ukatru.co:ncube-lm dbsl0001.ukatru.com:47406   ESTABLISHED
tcp        0      0 orasvcdbsl0001.uka:ncube-lm orasvcdbsl0001.ukatru:60983 ESTABLISHED
tcp        0      0 orasvcdbsl0001.uka:ncube-lm orasvcdbsl0001.ukatru:60977 ESTABLISHED
tcp        0      0 localhost.localdomain:61112 localhost.loc:synchronet-db ESTABLISHED
tcp        0      0 localhost.localdomain:61114 localhost.loc:synchronet-db ESTABLISHED
tcp        0      0 localhost.localdomain:61108 localhost.loc:synchronet-db ESTABLISHED
tcp        0      0 localhost.localdomain:61110 localhost.loc:synchronet-db ESTABLISHED
tcp        0      0 localhost.localdomain:61107 localhost.loc:synchronet-db ESTABLISHED
tcp        0      0 localhost.localdomain:61130 localhost.loc:synchronet-db ESTABLISHED
tcp        0      0 dbsl0001.ukatru.com:47406   dbsl0001.ukatru.co:ncube-lm ESTABLISHED
tcp        0      0 orasvcdbsl0001.uka:ncube-lm orasvcdbsl0001.ukatru:32855 ESTABLISHED
iostat:

/home/oracle=>iostat -p sda 3 10
Linux 2.6.18-164.el5 (dbsl0001.ukatru.com)      06/25/2011

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
3.07    0.00    1.03    2.28    0.00   93.61

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               2.95       240.48        42.92    3623379     646634
sda2            227.02         0.83         2.98      12574      44865
sda1             13.98         0.07         0.07       1063       1026

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
2.34    0.00    1.00    0.83    0.00   95.83

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               1.00         0.00        10.67          0         32
sda2              0.00         0.00         0.00          0          0
sda1              0.00         0.00         0.00          0          0

 

sar :

/home/oracle=>sar -d 2
Linux 2.6.18-164.el5 (dbsl0001.ukatru.com)      06/25/2011

05:33:05 PM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
05:33:07 PM    dev8-0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
05:33:07 PM    dev8-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
05:33:07 PM    dev8-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
05:33:07 PM   dev8-16      2.50      0.00     40.00     16.00      0.00      1.40      1.40      0.35
05:33:07 PM   dev8-17      2.50      0.00     40.00     16.00      0.00      1.40      1.40      0.35
05:33:07 PM  dev253-0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
05:33:07 PM  dev253-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
05:33:07 PM   dev22-0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
05:33:07 PM  dev253-2      5.00      0.00     40.00      8.00      0.00      0.70      0.70      0.35
05:33:07 PM   dev8-32     15.00    304.00    133.00     29.13      0.13      8.77      3.43      5.15
05:33:07 PM   dev8-33     15.00    304.00    133.00     29.13      0.13      8.77      3.43      5.15

Average:          DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
Average:       dev8-0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
Average:       dev8-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
Average:       dev8-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
Average:      dev8-16      2.50      0.00     40.00     16.00      0.00      1.40      1.40      0.35
Average:      dev8-17      2.50      0.00     40.00     16.00      0.00      1.40      1.40      0.35
Average:     dev253-0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
Average:     dev253-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
Average:      dev22-0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
Average:     dev253-2      5.00      0.00     40.00      8.00      0.00      0.70      0.70      0.35
Average:      dev8-32     15.00    304.00    133.00     29.13      0.13      8.77      3.43      5.15
Average:      dev8-33     15.00    304.00    133.00     29.13      0.13      8.77      3.43      5.15

 

 

Categories: Linux

Virtual column in oracle 11g

June 6, 2011 Leave a comment

Virtual columns  allows users to create columns whose data is not supplied by the user, but it is derived by oracle server implicitly from other columns. Most importantly, their disk space consumption is NULL because their data is not stored in the table.

Key points about Virtual columns:

1)Data can not be inserted into virtual column

2) The virtual column and the columns to be used in the derivation of virtual column data must belong to the same table.

3)It can be used as normal columns without any restriction. It can be constrained, indexed, and can be used in DML or DDL statements. Note that it cannot be updated in UPDATE statement

4)We can partition the table based on virtual column

We can use below query to define virtual columns defined in the users schema.

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN
FROM USER_TAB_COLS
WHERE VIRTUAL_COLUMN = ‘YES’;

5)we can not create virtual columns on Temporary tables, object types, clusters, External tables and Index Organized Tables

I tried to create table using following statment and used sysdate in the virtual column expression and got the following error.

Syntax

COLUMN [DATA TYPE] [GENERATED ALWAYS] AS (EXPRESSION) [VIRTUAL]

create table virtual_column (MEMBER_ID number(10),
MEMBER_NAME varchar2(25),
BIRTH_DATE date,
AGE_IN_MONTHS number(5) AS(SYSDATE-BIRTH_DATE))

ORA-54002: only pure functions can be specified in a virtual column expression

create table virtual_column (MEMBER_ID number(10),
MEMBER_NAME varchar2(25),
MONTHLY_SALARY number(10,2),
ANNUAL_SALARY number(10,2) AS(12*MONTHLY_SALARY))

SQL> insert into virtual_column values(1,’POOJITHA’,5000,10000);
insert into ukatru.virtual_column values(1,’POOJITHA’,5000,10000)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

insert into virtual_column(MEMBER_ID,MEMBER_NAME,MONTHLY_SALARY) values(1,’POOJITHA’,5000);

SQL> select * from ukatru.virtual_column;

MEMBER_ID MEMBER_NAME               MONTHLY_SALARY ANNUAL_SALARY
———- ————————- ————– ————-
1 POOJITHA                            5000         60000

Create index on virtual columns:

CREATE INDEX IDX_ANUAL_SALARY ON virtual_column (ANNUAL_SALARY);

If you query user_indexes table the index  is created as function based index.

SELECT INDEX_NAME,  INDEX_TYPE, FUNCIDX_STATUS
FROM   USER_INDEXES
WHERE TABLE_NAME = ‘VIRTUAL_COLUMN’;

INDEX_NAME                     INDEX_TYPE                  FUNCIDX_
—————————— ————————— ——–
IDX_ANUAL_SALARY               FUNCTION-BASED NORMAL       ENABLED

we can use alter table command to add virtual column to the table.

alter table virtual_column
add QUARTERLY_SALARY AS (MONTHLY_SALARY*3)

Adding constraint on the virtual column.

ALTER TABLE virtual_column
ADD CONSTRAINT QUARTERLY_SALARY_CHECK CHECK(QUARTERLY_SALARY != 0);

SQL> insert into virtual_column(MEMBER_ID,MEMBER_NAME,MONTHLY_SALARY) values(2,’POOJITHA’,0);
insert into virtual_column(MEMBER_ID,MEMBER_NAME,MONTHLY_SALARY) values(2,’POOJITHA’,0)
*
ERROR at line 1:
ORA-02290: check constraint (QUARTERLY_SALARY_CHECK) violated

pivot in oracle 11g

June 6, 2011 Leave a comment

Pivot function is very important function in some interface projects and dataware house environments.

consider following pivot_test table for this exercise.

create table pivot_test
(customer_id number(10),
customer_category varchar2(10),
customer_account number(10)
)

insert into pivot_test values(1,’MED’,5000);
insert into pivot_test values(1,’RX’,5001);
insert into pivot_test values(1,’CAP’,5002);
insert into pivot_test values(1,’REV’,5003);
commit;

SQL> select * from pivot_test;
CUSTOMER_ID|CUSTOMER_C|CUSTOMER_ACCOUNT
———–|———-|—————-
1|MED       |            5000
1|RX        |            5001
1|CAP       |            5002
1|REV       |            5003

SQL> select *
from
(select CUSTOMER_ID
, CUSTOMER_CATEGORY
, CUSTOMER_ACCOUNT
from pivot_test ) s
PIVOT (sum(CUSTOMER_ACCOUNT)
for CUSTOMER_CATEGORY in (‘MED’,’RX’,’CAP’,’REV’)
)

CUSTOMER_ID|   ‘MED’|    ‘RX’|   ‘CAP’|   ‘REV’
———–|——–|——–|——–|——–
1|    5000|    5001|    5002|    5003

String Aggregate function –LISTAGG

June 5, 2011 Leave a comment

In oracle 11gR2 LISTAGG is a new function which ise used to perform string aggregation.

Example data:

CUSTOMER_ID CUSTOMER_NAME
1 uma
1 poojitha
1 radhika
2 mahesh
2 katru

String aggregation is simply the grouping and concatenation of multiple rows of data into a single row per group

with string aggregation the output looks like below.

CUSTOMER_ID CUSTOMER_NAME_AGGREGATED
1 uma,poojitha,radhika
2 mahesh,katru

listagg syntax overview

The LISTAGG function has the following syntax structure:

LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

LISTAGG is an aggregate function that can optionally be used as an analytic (i.e. the optional OVER() clause). The following elements are mandatory:

  • the column or expression to be aggregated;
  • the WITHIN GROUP keywords;
  • the ORDER BY clause within the grouping.

SQL>SELECT customer_id, LISTAGG(customer_name, ‘,’) WITHIN GROUP (ORDER BY customer_name) AS customer_name_aggregated
FROM   listagg_test
GROUP BY customer_id;
CUSTOMER_ID CUSTOMER_NAME_AGGREGATED
———– ——————————
1 poojitha,radhika,uma
2 katru,mahesh

SQL> SELECT customer_id, LISTAGG(customer_name, ‘,’) WITHIN GROUP () AS customer_name_aggregated
FROM   listagg_test
GROUP BY customer_id;  2    3
SELECT customer_id, LISTAGG(customer_name, ‘,’) WITHIN GROUP () AS customer_name_aggregated
*
ERROR at line 1:
ORA-30491: missing ORDER BY clause

From the above we can conclude that order clause is manadatory.If the order clause is not required for then you can specify like below.

SQL> SELECT customer_id, LISTAGG(customer_name, ‘,’) WITHIN GROUP (ORDER BY  NULL) AS customer_name_aggregated
FROM   listagg_test
GROUP BY customer_id;

CUSTOMER_ID CUSTOMER_NAME_AGGREGATED
———– ——————————
1 poojitha,radhika,uma
2 katru,mahesh

The new LISTAGG function is the fastest technique for string aggregation and has the additional benefit of being a simple built-in function.

Categories: oracle functions

Steps to Configure oracle apex in 11g

June 5, 2011 Leave a comment

Here are the steps to configure oracle apex in 11gR2.

APEX application can access either the embedded PL/SQL gateway or Oracle HTTP server.By using the embedded PL/SQL gateway, it will run using the Oracle XML DB HTTP server

Step1)

To Configure the embedded PL/SQL gateway go to the $ORACLE_HOME/apex directory

*dbsl0001*<oracle:ora11gR2>:/u01/app/oracle/product/11.2.0.2/db_1/bin=>echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.2/db_1

now connect to the database using sqlplus from the database machine.

*dbsl0001*<oracle:ora11gr21>:/u01/app/oracle/product/11.2.0.2/db_1/bin=>./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Jun 4 21:24:53 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> @apxconf

PORT
———-
8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.

Enter a password for the ADMIN user              []
Enter a port for the XDB HTTP listener [      8080]
…changing HTTP Port

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Session altered.

…changing password for ADMIN

PL/SQL procedure successfully completed.

Commit complete.

SQL> alter user anonymous account unlock;

User altered.

Enable Oracle XML DB HTTP server:

SQL> exec dbms_xdb.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

now we can access oracle apex by using following link.

http://host:port/apex


workspace : internal

User : admin

password : you entered when you configured apex.

Categories: oracle-install
%d bloggers like this: