How to insert & as a column value in a table

Introduction:



This post is very simple, but usefull, as when I tried to find this thing on google, I couldnt get any page, may be I didnt search properly, but I wanted to share how we can insert & as a column value. This post is very useful if you are inserting thousands of records and all have & in one of the column values. As if you will try to run in SQL*PLUS, it would be asking you the value for a variable preceeding &.



Reolution:



We did differnet testings, and finally was able to insert the & as a column value, following are the testings and results.



1- created an empty table with one field as text varchar2(60).



test-sql>create table testtable (text varchar2(60));
Table created.



2- different tries to insert the &, last one is successfull.



a- test-sql>insert into testtable values(' this is & record');
Enter value for record: &
old 1: insert into testtable values(' this is & record')
new 1: insert into testtable values(' this is &')

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &



b- test-sql>insert into testtable values(' this is '&' record');
insert into testtable values(' this is '&' record')
*
ERROR at line 1:
ORA-00917: missing comma



c- test-sql>insert into testtable values(' this is "&" record');

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &
this is "&" record



d- test-sql>insert into testtable values(' this is '''&''' record');
insert into testtable values(' this is '''&''' record')
*
ERROR at line 1:
ORA-00917: missing comma



e- test-sql> insert into testtable values (' this is ' '&' ' record');

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &
this is "&" record
this is & record

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect

Introduction:

We had issue with Listener connectivity from client. Even the Listener was running, but it was throwing the following error message.

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connectdescriptor

Troubleshooting:

1- We checked with the tnsping utility from client side, and server side, and it was all working, it was displaying the following message.

TNS Ping Utility for 32-bit Windows: Version 9.2.0.8.0 - Production on 20-APR-20
09 10:02:32

Copyright (c) 1997, 2006, Oracle Corporation. All rights reserved.

Used parameter files:
D:\oracle\ora92\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = TESTSERVER)(PORT = 1528))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_N
AME = TESTDB)))
OK (20 msec)

2- When we checked through sql*plus it was showing the following message.

H:\>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Apr 20 10:02:52 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: system@TESTDB
Enter password:
ERROR:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor

3- We checked the local_listener parameter in the database and it was not set to any of the value.

SQL> show parameter local

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string

4- Here are the listener.ora and tnsnames.ora files. The important point to note here is the port number was 1528

LISTNER.ORA

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTSERVER)(PORT = 1528))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDB)
(ORACLE_HOME = /u02/oracle/products/9.2.0)
(SID_NAME = TESTDB)
)
)

TNSNAMES.ORA

TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTSERVER)(PORT = 1528))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)
Resolution:

1- We set the LOCAL_LISTENER parameter to service name "TESTDB" and checked and it was all working.

H:\>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Apr 20 10:02:52 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: system@TESTDB_ora
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL>

2- Following was the value of LOCAL_LISTENER parameter

SQL> show parameter local_list

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string TESTDB

Inactive - No Concurrent Manager after running autoconfig

Introduction:

Due to IP change in one of our APPS testing environment, we ran autoconfig on all the application tier and database tier, but after the successful running of autoconfig, when we checked the concurrent manager by submitting "Active Users" request, it was showing "Inactive - No Concurrent Manager" error.





Troubleshooting:

Following are the steps which we did.



We shutdown all the application tier services web , forms , concurrent manager and again restarted, but it was showing the same error, "Inactive No Manager" for submission of "Active Users" request. see the screen shot.

We checked the log files for concurrent manager, and we found following error messages in ICM log files.

---------------------------------------------------------------------------Application Object Library: Concurrent Processing version 11.5
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
Internal Concurrent Manager started : 04-MAR-2009 09:44:58
---------------------------------------------------------------------------Spawned Process 8573Could not contact Service Manager FNDSM_TESTSERVER_uat. The TNS alias could not be located, the listener process on TESTSERVER could not be contacted, or the listener failed to spawn the Service Manager process.Spawned Process 8574
Process monitor session started : 04-MAR-2009 09:44:59---------------------

Resolution:

We searched on metalink site, and found following note 392246.1helpful.

Requests Stuck with Inactive No Manager - Routine AFPESA Cannot Construct the Name of an Executable File

When we checked the env files in $APPL_TOP/admin, we found that the Custom Top were not defined in that file. We updated the env file with the custom top and restarted the Concurrent manager and it was working fine.

Concurrent Manager Request error

Introduction:

We got Concurrent Manager Request error for one of our List Generation request. When we checked the log file (by clicking on view log button),

we got the following message

Troubleshooting and Resolution:

We logged in with the "sysadmin" user and searched the required List Generation request.

And then clicked Diagnostics button, which showed the following message.

When we checked the executable, it was not on the path mentioned. It was a symbolic link to the directoy which was of produciton, as it was the cloned system. We remove the symbolic link, and recreated with the correct path. And this List Generation request worked fine.

ORA-27211: Failed to load Media Management Library

Introduction:
We had interesting but painfull issue for Oracle backups which are done with Veritas Netbackup. There was activity of patching on Netbackup. And after the patching there was issues with Oracle Backups. All the Oracle backups were failing with following error messages....

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 02/25/2009 13:41:11
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

Troubleshooting:

We tried different steps to resolve the error. Following are the steps which we did.

1- Checked with the "sbttest" command, to see whether Netbackup is doing successfull test backups. and we got the following errors.

$>sbttest test

The sbt function pointers are loaded from oracle.static library.libobk.so could not be loaded. Check that it is installed.

2- We tested with simple rman commands to allocate a tape channel and the release. and following was the error messages.

RMAN> run {
2> allocate channel ch1 type 'SBT_TAPE';
3> release channel ch1;
4> }

using target database controlfile instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 02/25/2009 13:40:43
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

3- The above error messages were showing that MML is not able to load, and that libobk.so was being used as static from Oracle binaries.

We explicitly used the Netbackup /usr/openv/netbackup/bin/libobk.so64.1, inside the RMAN script, but we got the same error as of step 2. Following were the error messages

RMAN> run {
2> allocate channel ch1 type 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1';
3> release channel ch1;
4> }

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 02/25/2009 13:41:11
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

The above all steps showed that there was strange kind of problem with Netbackup. As we were not able to allocate one channel with "sbt_tape" type, and all the times MML was not able to be loaded.

We also check in the udump of the Oracle Database server, and following were the error messages in udump trace file.

*** SESSION ID:(47.58746) 2009-02-25 13:40:43.872
SKGFQ OSD: Error in function sbtinit on line 2384
SKGFQ OSD: Look for SBT Trace messages in file /usr/oracle/home/oracle/admin/ORA/u
dump1/sbtio.log
SBT Initialize failed for oracle.static
*** 2009-02-25 13:41:11.006
Failed to load SBT library /usr/openv/netbackup/bin/libobk.so64.1

The above error messages of udump trace file, was clearly showing that the libobk.so64.1 was not able to load. And also we found valid symbolic link from $ORALCE_HOME/lib/libobk.so to /usr/openv/netbackup/bin/libobk.so64.1

Resolution:

After trying every way, finally our system admin did one thing and got resolved the issue. What he did, he renamed the /usr/openv/netbackup directory and again pushed the netbackup client from Netbackup Server, and we again tested the Oracle Backups and it was all working. Following is the simple allocation of channel of 'sbt_tape'



RMAN> run {
2> allocate channel ch1 type 'SBT_TAPE';
3> release channel ch1;
4> }

using target database controlfile instead of recovery catalog
allocated channel: ch1
channel ch1: sid=35 devtype=SBT_TAPE
channel ch1: VERITAS NetBackup for Oracle - Release 6.0 (2005090703)

released channel: ch1

RMAN>

Partitioned Index rebuild with "Parallel" and without "Parallel" clause

Introduction:
In this article we will see, the impact of "Parallel" clause of rebuilding a partition index. We would also enable tracing on the session which execute the Alter index rebuild statement, and also all those parallel sessions, which are invoked automatically when we use "Parallel" clause.

Steps:
For this test, we can make one partitioned index unusable, we will use exchange partition clause of Oracle 9i, without specifying "Update global indexes", this will cause index unusable. And then we will rebuild both with "Parallel" and without "Parallel" clause.

1- Check the status of partitioned index.

SQL> select substr(index_name,1,20) index_name,substr(partition_name,1,20) part_name , status from dba_ind_partitions
where index_name like '%TEST%'
;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
TEST_PARTITIONED_TABLE_IDX JAN2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_02 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_02 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_02 USABLE



2- Exchange the partition with table.

a- we need to make an empty table of the partitioned table, and then we will exchange the partition with that empty table.

sql>create table TEST_PARTITIONED_TABLE_Test as select * from TEST_PARTITIONED_TABLE ;

Table created.

B- We will now exchange the partition JAN2008_02 with table TEST_PARTITIONED_TAB LE_Test.

SQL> alter table TEST_PARTITIONED_TABLE exchange partition JAN2008_02 with table TEST_PARTITIONED_TABLE_Test;

Table altered.

c- check the number of rows in the partition JAN2008_02, to verify that rows has been moved to the TEST_PARTITIONED_TABLE.

SQL>select count(1) from TEST_PARTITIONED_TABLE partition (jan2008_02);

COUNT(1)
----------
0

d- check the number of rows in teh TEST_PARTITIONED_TABLE

SQL>select count(1) from TEST_PARTITIONED_TABLE_test;

COUNT(1)
----------
14140873


e- get the data back in the partition JAN2008_02.

SQL>alter table TEST_PARTITIONED_TABLE exchange partition JAN2008_02 with table TEST_PARTITIONED_TABLE_Test;

Table altered.

3- Check index status after exchange partition.

INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
TEST_PARTITIONED_TABLE_IDX JAN2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_02 UNUSABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_02 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_02 USABLE

4- Rebuild unusable index partition JAN2008_02 without "Parallel"

SQL>alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online;

Index altered.

5- We can check the trace file for the above statement, and can estimate the time taken to rebuild the above index.

From the trace file for the above session.

alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 230.33 845.32 614267 522610 6455 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 230.33 845.32 614267 522610 6455 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: ALL_ROWS
Parsing user id: 5 (SYSTEM)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 27213 0.13 56.25
direct path write 1113 0.73 169.77
direct path read 11273 0.53 232.59
db file sequential read 30 0.01 0.10
rdbms ipc reply 117 0.04 0.10
log file sync 1 0.01 0.01
SQL*Net message to client 1 0.00 0.00



The elapsed time is 845.32 seconds (approx. 14.08 min.)

Now to check the impact of the parallel clause, we need to repeat steps 1 to 3.

5- after completing the steps 1 - 3, now we can rebuild the index with parallel clause.

SQL>alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online parallel;

Index altered.

6- From the trace file,

alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online parallel

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 1 0 0
Execute 1 1.58 765.66 599831 527410 12060 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.60 765.68 599831 527411 12060 0

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 5 (SYSTEM)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
ksu process alloc latch yield 3 0.09 0.19
process startup 6 0.04 0.24
PX Deq: Join ACK 4 0.00 0.00
PX Deq: Execute Reply 683 1.96 761.48
PX Deq: Parse Reply 3 0.01 0.01
PX qref latch 23 0.01 0.30
PX Deq: Table Q qref 2 0.00 0.00
db file sequential read 9 0.01 0.01
rdbms ipc reply 98 0.04 0.12
log file sync 2 0.01 0.01
PX Deq: Signal ACK 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 2443.89 2443.89


The elapsed time is 765.68 second. (approx. 12.76 min)

Result:

The rebuild with parallel clause is faster then normal rebuild of the index.

How to trace user session on logon

How to trace a user session on logon

Introduction:

Some times it is not easy to trace a user session, especially if user is using forms applicaiton. For forms applicaiton some times single user open 2 session in database. So one have to trace both the sessions, otherwise valueable information can miss. And offcourse it takes time to tell user, that once you logged in then wait, dont do any thing, till I tell you to do so. Once user get logged in, then you have to go to database and need to get SID and SERIAL#, and then tracing can be enabled.

Logon Triggers:

To overcome on this probelm, we can make a trigger on logon event of database, the trigger will be fired as soon user logs in to database. And it will start tracing the session automatically.

Trigger code:

Following is the trigger, which will do tracing automatically for user "TEST". Also we can make 2 tables to log the information, these tables can be checked that whether the tracing was done successfully or not. We have 2 tables here, one for the successfull tracing information and other to handle the exception.

Following are the codes for tables and trigger.

1- To log the successfull tracing information.

create table trace_start_log (status varchar2(35),trace_date date);

2- To log the exception, in case of any error.

create table trace_error_log (status varchar2(35),trace_date date);

3- Logon Trigger

CREATE OR REPLACE TRIGGER SESSION_TRACE
AFTER LOGON ON DATABASE
declare
v_user varchar2(30);
begin
select user into v_user from dual;
if v_user='TEST_USER' then
execute immediate 'alter session set sql_trace=true';
insert into trace_start_log values('trace started for TEST_USER',sysdate);
end if;
exception
when no_data_found then
insert into trace_error_log values(' no data found for TEST_USER',sysdate);
end;
/

Testing of the trigger:

We can check by logging in by TEST_USER, and we can check the table TRACE_START_LOG, also one can find the trace files in udump directory.

sql>select * from trace_start_log;

STATUS TRACE_DAT
----------------------------------- ---------
trace started for TEST_USER 28-JAN-09