All about ORACLE

Goldengate reading XML data from BLOB column and inserting record into target table

2011-04-28T06:16:00.000-07:00

Given below is a simple workaround for reading XML from a blob column and parsing XML and inserting it as a record into the target table.Usage of C userexit to accomplish the same is mentioned later in this post. Extract group e_xmlextract e_xmluserid source, password ggsrmthost rukh, mgrport 8809rmttrail /opt/oracle/gg/rtrails/xmtable source.xml_blob, &sqlexec (id lookup, query " &select x.*

Goldengate Reading JMS queue and update table.

2011-04-28T05:33:00.000-07:00

Scenario Description In this scenario we are sending a XML message to the queue and using Goldengate the message is read from the queue and updated in the table. Setup for Java adapter Install Java jdk1.5 or laterSet env variablesWindows:set JAVA_HOME=C:\prog\java\jdk1.6.0_18set PATH=%JAVA_HOME%\bin;%PATH%set PATH=%JAVA_HOME%\jre\bin\server;%PATH% Download Java Adapter for GG and unzip it

Goldengate Bidirectional replication with conflict resolution

2011-04-20T04:46:00.000-07:00

Scenario Description In this Scenario we have tried to perform bidirectional replication. The extract process on Site A , e_sitea will extract the data and send it as a remote trail file to Site B. The replicat process on Site B, r_sitea will read from the trail file and replicate the same in database on Site B. User tries to update a column on Site A and at the same instance another user

Deleting child records and parent records

2010-07-08T11:34:00.003-07:00

How to delete all corresponding child records in order to delete the parent record. example: CREATE TABLE EMP ( ENO NUMBER(3) NOT NULL, NAME VARCHAR2(5), DNO NUMBER(2) NOT NULL, CONSTRAINT EMP_FKEY FOREIGN KEY (DNO) REFERENCES DEPT (DNO) INITIALLY IMMEDIATE DEFERRABLE, CONSTRAINT EMP_PKEY PRIMARY KEY (ENO)); SQL> set constraint emp_fkey deferred; The order of deletes is now not

Deleting duplicate values in table

2010-07-08T11:34:00.002-07:00

I have some duplicate values in my table.i want to delete one of those values SQL> CREATE TABLE A (B NUMBER, C VARCHAR(1)); Table created. SQL> INSERT INTO A VALUES (1, 'A'); 1 row created. SQL> INSERT INTO A VALUES (2, 'B'); 1 row created. SQL> INSERT INTO A VALUES (3, 'C'); 1 row created. SQL> INSERT INTO A VALUES (1, 'A'); 1 row created. SQL> INSERT INTO A VALUES (4, 'D'); 1 row

Extract Numbers from a given string

2010-07-08T11:34:00.001-07:00

select NUM from (select Substr('22,23,24,san,35',instr(','||'22,23,24,san,35',',',1,rownum) ,instr(','||'22,23,24,san,35',',',1,rownum+1)-instr(','||'22,23,24,san,35',',',1,rownum)-1) NUM from all_objects where rownum <= (select length('22,23,24,san,35') - length(TRANSLATE('22,23,24,san,35',' ,',' ')) from dual) union all select substr('22,23,24,san,35',instr('22,23,24,san,35',',',-1)+1) from

Script to generate Foreign keys

2010-07-08T11:34:00.000-07:00

SET termout OFF SET feedback OFF SET lines 120 SET serveroutput ON SIZE 1000000 SPOOL C:\crt_fk.SQL DECLARE v_virg VARCHAR2 (1); v_maxcol NUMBER; v_tname VARCHAR2 (30); v_string1 VARCHAR2 (2000); v_string2 VARCHAR2 (2000); CURSOR c1 IS SELECT table_name FROM user_tables ORDER BY table_name ASC; BEGIN DBMS_OUTPUT.put_line ('--'); DBMS_OUTPUT.put_line ('-- FOREIGN KEYS --');

Killing sniped sessions in UNIX

2010-07-08T11:33:00.000-07:00

http://www.orsweb.com/downloads/source/424.html #!/bin/ksh #* filename==>cleanup_sniped.ksh #* #* Name: cleanup_sniped.ksh #* #* Date: 13 December, 2001 #* #* Purpose: #* A script to clean up sniped sessions at the OS level. #* The database sessions will dissappear after the #* OS process is killed. #* This script is based on Oracle NOTE: 96170.1 #* #* Prepared/ #* CopyRighted By:

ORA-01502 and ORA-00604 when dropping a user

2009-04-27T10:54:00.000-07:00

SQL> drop user test cascade; drop user test cascade*ERROR at line 1:ORA-00604: error occurred at recursive SQL level 2ORA-01502: index 'SYS.I_SEQ1' or partition of such index is in unusable state connect as sys SQL> alter index SYS.I_SEQ1 rebuild online; Index altered. SQL> drop user test cascade; User dropped.

Oracle Application server forms and reports install

2009-04-02T10:04:00.000-07:00

Oracle Application Server 10g Release2 Installation best practices 1.The Infrastructure (Foundation) has to be installed firstName the home as infra and the path as infraiasChoose Identity managemenet and metadata repository Select administrator privileges check boxLeave the defaultsUse automatic .If you want to specify the ports then you can create a file and choose the filename heresample

ORA-04093:REFERENCES TO COLUMNS OF TYPE LONG ARE NOT ALLOWED IN TRIGGERS

2006-10-16T06:54:00.000-07:00

SQL>create table test(data1 varchar2(10),data2 long raw,data3 varchar2(99));SQL>create or replace trigger t1 after Insert or update or delete on test for each row declare begin null; end;SQL>Alter trigger t1 compile debug;SQL>Show error# cause: When compiling a trigger in debug mode which refers to a tablecontaining a LONG RAW column, the compilation will

ORA-04091: table is mutating, trigger/function may not see it

2006-10-16T06:50:00.000-07:00

The Mutating table error is a well-known problem encountered in development; most developers have come across this error.ORA-04091: table is mutating,trigger/function may not see itThe basic reason for this error is the way Oracle manages a read consistent view of data. The error is encountered when a row-level trigger accesses the same table on which it is based, while executing. The table is

Accidental deletion of parameter file

2006-10-16T04:14:00.000-07:00

It is always a good idea to include the parameter file in your backupsbut in the rare event of not having a backup and loosing the file,there are various things you can do. Some of those would be:-- Create a new init file and within that file all you have to do ismake sure that these parameters are set : db_name, control_files,db_block_size and compatible. Once these parameters are set, you

Multiblock read count

2006-10-16T03:43:00.000-07:00

As everyone will be aware, this parameter controls how much data Oracle thinks it can retrieve from the disks in a single trip (during a table scan). The important part of this statement is the "how much Oracle thinks". It is relatively easy to manufacture a circumstance where Oracle thinks it can achieve a much higher read size than can actually be serviced by the operating system. Unfortunately

Monitoring rollback progress

2006-10-16T03:37:00.000-07:00

When a large transaction takes a long time to rollback, it is good to know how much of the rollback is done and estimate how long it is going to take. Given the session's sid, it can be done with the simple statement below, tested on Oracle9i. When a transaction is rolling back, the t.used_ublk and t.used_urec will decrease until they become 0. By sampling the two measures at different points of

ORA-01994: GRANT failed: cannot add users to public password file

2006-10-13T05:57:00.000-07:00

ORA-01994: GRANT failed: cannot add users to public password fileThis error occurs on granting sysoper permission to a userIn the initialization parameter file of your database.initSID.ora add this line and then restart the database.REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

ORA-00980: synonym translation is no longer valid

2006-10-13T05:56:00.000-07:00

SQL>create table maintab as select * from tab; Table created. SQL> create synonym syn_maintab for maintab; Synonym created. SQL> select * from syn_maintab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- AAAAA 10 rows selected. SQL> drop table maintab; Table dropped. SQL> select * from syn_maintab; select * from syn_maintab * ERROR at line 1: ORA-00980: synonym

ORA-00979: not a GROUP BY expression

2006-10-13T05:54:00.000-07:00

select e.deptno, d.deptno,sum(e.sal) from dept d,e emp where d.deptno=e.deptno group by e.deptno d.deptno,sum(e.sal) from d dept,e emp*ERROR at line 2:ORA-00979: not a GROUP BY expressionGROUP BY clause should contain all the columns(without group function) in the query select e.deptno,d.deptno,sum(e.sal) from dept d,emp ewhere d.deptno=e.deptno group by e.deptno,d.deptno;

Tracing network errors in oracle

2006-10-13T05:51:00.000-07:00

I don't remember the URL of the site where I got this article from.It is really good.If anyone knows please let me know.I will remove this article and put the link here instead.1 Introduction~~~~~~~~~~~~For most problems you need to identify the relevant parts of aconnection to trace. To do this consider which scenario you arehaving problems with and where tracing needs to be enabled.Note that

Find difference between two dates in days,hrs,mins and sec

2006-10-13T04:51:00.000-07:00

selecttrunc(to_date('15-jan-2006','dd-mon-yyyy')-sysdate)"Days",trunc(mod((to_date('15-jan-2006','dd-mon-yyyy')-sysdate)*24,24))"Hrs",trunc(mod((to_date('15-jan-2006','dd-mon-yyyy')-sysdate)*24*60,60))"Mins",trunc(mod((to_date('15-jan-2006','dd-mon-yyyy')-sysdate)*24*60*60,60))"Secs"from dual;

Online Table reorganisation

2006-10-13T04:48:00.000-07:00

What can be Redefined ONLINE on a Table?----------------------------------------A non-partitioned table can be converted into a partitioned table, andvice versaThe organization of a table can be changed from a heap based to IOTs (IndexOrganized Tables), and vice versaNon-primary key columns can be droppedNew columns can be added to a tableExisting columns can be renamedParallel support can be

Interview questions vol3

2006-10-12T07:37:00.000-07:00

1 What are the new features in Oracle 9i compared to earlier ones?2.What is undo tablespace and why is called so?What was it called in earlier versions?3.Now if you are appointed as a Oracle DBA in a company, can you tell what all are the databases that are running on a particular server? 4.Now if a database is in mount stage how can you find that database name since you are totally new to the

ORA-00997: illegal use of LONG datatype

2006-10-12T07:35:00.000-07:00

While copying the data from one database to another using database link. For the tables which has long data types, its giving the error : 1)insert into DOC select * from DOC@acc_link ORA-00997: illegal use of LONG datatypeUnfortunately, you cannot do an "insert into select" for tables with long datatype columns. If what you're trying it outside of PLSQL or any applications then you can use

Useful scripts for DBA

2006-10-12T07:21:00.000-07:00

These work good on 9i .free.sql-amount of freespace in a tablespaceselect tablespace_name, round(sum(bytes)/(1024*1024),0) MB from dba_free_space group by tablespace_name/---------------------------------------------------------------------compile.sql-compile uncompiled objectsset pages 10000set lines 1500set feedback offset heading offspool com.lstselect ' alter '|| decode(object_type, 'PACKAGE

Faster import export

2006-10-10T05:15:00.000-07:00

For fast exportSet the buffer parameter to high value.use direct=ySet recordlength parameter to high.Make sure on which on disk u r taking export should have minimal I/O. For fast ImportImport without indexes then use the indexfile, due to which u can skip the creation of indexes at the time of import.Increase the size of database buffer cache and log_buffer.Keep database in no archive mode.Use

Oracle Data encryption package dbms_obfuscation_toolkit

2006-10-10T03:55:00.000-07:00

One of the newer features added since oracle8i is data encryption. Oracle has supplied a new package called dbms_obfuscation_toolkit which allows you to encrypt and decrypt strings. This functionality will be particularly useful for data encryption when sensitive data like passwords and social security numbers are stored within the database. This article gives an example on encryption and

Row movement in oracle

2006-10-10T03:52:00.000-07:00

Row movement is associated with partitioned and non-partitioned tables but is mostly associated with non-partitione tables. For partitioned tables when ROW MOVEMENT is enabled users have the ability to update partitioning key columns in such a way that a row no longer belongs in its current partition, causing such rows to migrate to the appropriate partition. Row movement is enabled at the table

Drop all indexes in a SCHEMA

2006-10-10T03:48:00.000-07:00

How to drop all indexes in a SCHEMASQL>set pages 10000SQL>set wrap offSQL>set heading offSQL>spool c:/indx.sql SQL> select ' drop index ' || index_name ||';' from user_indexes; drop index IND1; drop index IND2; drop index IND3; SQL> spool off; SQL>@indx.sql) in that particular schema in which you want to drop indexes.A similar script can be used for synonyms especially when you import a schema

Creating a Data Guard Configuration on SUN cluster

2006-10-10T02:48:00.000-07:00

1) Ensure the Primary database is in ARCHIVELOG mode:SQL> archive log listDatabase log mode No Archive ModeAutomatic archival DisabledArchive destination /export/home/oracle/temp/oracle/archOldest online log sequence 7Current log sequence 9SQL> alter database close;Database altered.SQL> alter database archivelog;Database altered.SQL> shutdown immediateORA-01109: database not openDatabase

crosstab query transposing columns and rows

2006-10-10T02:42:00.000-07:00

SELECT * FROM (SELECT job, sum(decode(deptno,10,sal)) DEPT10, sum(decode(deptno,20,sal)) DEPT20, sum(decode(deptno,30,sal)) DEPT30, sum(decode(deptno,40,sal)) DEPT40 FROM scott.emp GROUP BY job)ORDER BY 1;JOB DEPT10 DEPT20 DEPT30 DEPT40--------- ---------- ---------- ---------- ----------ANALYST

count(*) and count(1)

2006-10-10T02:38:00.000-07:00

What is the difference between count(1) and count(*) in a sql queryeg.select count(1) from emp; andselect count(*) from emp; Nothing, they are the same, incur the same amount of work -- do the same thing, take the same amount of resources.You can see this via:ops$tkyte@ORA817.US.ORACLE.COM> alter session set sql_trace=true;Session altered.ops$tkyte@ORA817.US.ORACLE.COM> select count(*) from

Modify datatype of column -coulmn definitions

2006-10-10T02:31:00.000-07:00

Modifying Column DefinitionsYou can use the MODIFY clause to change any of the following parts of a column definition: * datatype * size * default value * NOT NULL column constraintThe MODIFY clause need only specify the column name and the modified part of the definition, rather than the entire column definition.Datatypes and SizesYou can change a CHAR column to VARCHAR2 (or VARCHAR) and

Database characterset migration

2006-10-10T02:29:00.000-07:00

In most cases a full export and import is recommended to properly convert all data to a new character set. However if, and only if, the new character set is a strict superset of the current character set, you can use the ALTER DATABASE CHARACTER SET statement to expedite migration to a new database character set. The target character set is a strict superset if each and every code point in the

Select date in GMT format

2006-10-10T02:15:00.000-07:00

NEW_TIME (oracle8i and above... pls check for older version) NEW_TIME(date, zone1, zone2) Convert between GMT and US time zones (but not CET)zone1 and zone2 can be any of the following values: Value Description AST Atlantic Standard Time ADT Atlantic Daylight Time BST Bering Standard Time BDT Bering Daylight Time CST Central Standard Time CDT Central

Capturing machine name for users in oracle

2006-10-10T02:13:00.000-07:00

Create the fieldsCREAT_DATE DATE;CREAT_USER VARCHAR2(25);MODI_DATE DATE;MODI_USER VARCHAR2(25);in tables.Use this follwing code in every table row leveltrigger.DECLARE VDATE DATE; vUSER VARCHAR2(20);BEGIN SELECT SYSDATE , audit_user(USERENV('SESSIONID'))INTO VDATE, VUSER FROM DUAL; IF INSERTING THEN :NEW.CREAT_DATE :=VDATE; :NEW.CREAT_USER :=VUSER; :NEW.MODI_DATE :=

How to create alpha numeric sequence.

2006-10-10T01:58:00.000-07:00

First, the character "A" can be represented as CHR(65), "B" as CHR(66),and so on.The answer lies in using two Oracle functions: MOD and TRUNC.-- MOD gives the remainder of two numbers. For example, MOD(50,26) gives 24, which is the remainder of 50/26.-- TRUNC gives just the integer value of a number. So TRUNC(50/26) gives 1, which is the integer value of 1.92307692...So, you want a sequence of

Oracle DBA Checklist

2006-10-10T00:21:00.000-07:00

Purpose: This document gives details for performing daily, weekly, and monthly checks of the status of one or more Oracle databases. All SQL and PL/SQL code for the listed checks can be found in the appendix. The latest version of this paper should always be available on the primary author's home page, .Change Notes: 1.1: Typo in 'existext.sql' identified by Steve DeNunzio, fixed 1.2: Typos

Interview questions for Oracle DBA -Vol2

2006-10-10T00:17:00.000-07:00

1)How many memory layers are in the shared pool? 2)How do you find out from the RMAN catalog if a particular archive log has been backed-up? 3)How can you tell how much space is left on a given file system and how much space each of the file system's subdirectories take-up? 4)Define the SGA and how you would configure SGA for a mid-sized OLTP environment? What is involved in tuning the SGA? 5)

Interview questions for oracle DBA -Vol1

2006-10-10T00:15:00.000-07:00

1.Explain the components of physical database structure of Oracle database?Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files. 2. Explain the components of logical database structure of Oracle database?There are tablespaces and database's schema objects.3. Explain a tablespace?A database is divided into Logical

Table locking in Oracle RAC

2006-07-10T00:53:00.000-07:00

The other day there was some problem in a particular module .People were not able to save recors and also not able to query record related to a particular table.When I analysed with OEM I found that the table had more than one "exclusive lock" held on it.I was puzzled and I tried removing them by killing sessions related to those locks ,but again after a few minutes it was back.I am not an

Oracle 10g database on linux all versions

2006-02-15T02:46:00.000-08:00

Installing Linux RHEL4 Oracle Database 10g Release 2 is certified to run the base release of RHEL4 (Advanced Server and Enterprise Server) without updates. The easiest and fastest way to apply the updates for a fresh install of Linux is to perform the install by using the update

Error while connecting to RAC database (TNS-12545)

2006-02-09T20:54:00.000-08:00

C:\Documents and Settings\Administrator>sqlplus system@sunSQL*Plus: Release 9.2.0.1.0 - Production on Tue Sep 20 11:09:37 2005Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Enter password:ERROR:ORA-12545: Connect failed because target host or object does not existEnter user-name:SQLNET.ORA entryFatal NI connect error 12545, connecting to:(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(

Troubleshooting installation of Oracle 9.2 on Sun solaris

2006-02-02T04:11:00.000-08:00

Error While Creating database 1. The installation goes fine without any errors. But when the Oracle DatabseConfiguration Assistant was started, it stopped with an error while trying to createand start the oracle instance. When clicking on 'Ignore', it continues with moreerrors. The error messages:ORA-2730: failure occured at: skgpwreset1ORA-27303: additional information: invalid

Standby archive apply throws error ORA-01152

2006-01-31T20:00:00.000-08:00

This is what happened today .I tried to restore a cold backup of the primary on the standby and started it with a backup normal controlfile.alter database mount standby database ;controlfile is not standby controlfileso I went to the primary and took a controlfile backupalter database create standby controlfile as '/bkp/standby.ctl';and ftp 'ed it to the standbyThen I started the database ,note

Step by step Data guard for oracle 9i on windows

2006-01-16T05:31:00.000-08:00

Maximum Performance data guard The steps for creating a maximum performance physical standby database are: 1. Ensure your primary database is in archive log mode.To make the primary database in archive modeC:\Documents and Settings\Administrator>sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 4 13:17:54 2005Copyright (c)

Standby server on RAC environment

2006-01-16T05:00:00.000-08:00

init.ora for standbybackground_dump_dest='/arch/dump/bdump'#cluster_database_instances=2#cluster_database=TRUEcompatible='9.2.0.5'control_files='/oracle/admin/ctl/standbysun.ctl'core_dump_dest='/arch/dump/cdump'cursor_sharing='EXACT'db_block_size=8192db_cache_size=200000000db_domain='world'db_file_multiblock_read_count=8db_name='sun'fast_start_mttr_target=600hash_join_enabled=TRUE#sun11.

How to Change SID of oracle database

2006-01-15T23:30:00.000-08:00

This entry describes how to find and change the "db_name" for a database, or the ORACLE_SID for an instance, without recreating the database. SCOPE & APPLICATION For DBAs requiring to either find or change the db_name or ORACLE_SID. To find the current DB_NAME and ORACLE_SID: Query the views v$database and v$thread. V$DATABASE gives DB_NAME V$THREAD gives

How to Change the IAS_ADMIN password for Enterprise Manager

2006-01-15T23:25:00.000-08:00

Changing the EM Administrator account (ias_admin) for the EM Web Site in EM 9.0.2 If you already know the current EM Administrative password for ias_admin, you can change the password from within the EM Web Site by clicking on the Preferences link on the top right-hand side of the screen. 1. You must enter the old password, the new password and confirm the new password. Or You can also go to

Oracle RAC (Real Application Clusters)

2006-01-13T04:00:00.000-08:00

.profile# @(#)local.profile 1.8 99/03/26 SMIstty istripPATH=/usr/bin:$ORACLE_HOME/bin:.export PATH#echo $PATH## If possible, start the windows system#if [ "`tty`" = "/dev/console" ] ; then if [ "$TERM" = "sun" -o "$TERM" = "sun-color" -o "$TERM" = "AT386" ] then if [ ${OPENWINHOME:-""} = "" ] ; then OPENWINHOME=/usr/openwin

Step by Step Installation of Solaris 8 OS

2006-01-13T01:18:00.000-08:00

SF280R OS Installation Steps Configuration: V480/2 * 900Mhz CPU/4GB RAM/FC Controller/2 * 36GB Internal HDD/ USB Keyboard & Mouse ON SF280 STAGE I ---- OS Installation ======= 1. Mount OS DVD media on drive and access OK prompt by typing stop a (Note STOP key is to left-top of key pad) 2. At OK prompt type OK> boot cdrom This will access the DVD and OS installation beings

Step by step installation of Oracle 9.2 on Sun solaris

2006-01-13T01:04:00.000-08:00

Check Hardware Requirements Minimal Memory: 256 MBMinimal Swap Space: Twice the amount of the RAM To determine the amount of RAM memory installed on your system, enter the following command.$ /usr/sbin/prtconfTo determine the amount of SWAP installed on your system, enter the

Could not mount Database after restarting the cluster

2006-01-13T00:56:00.000-08:00

Could not mount Database after restarting the cluster. ALERT LOG ORA-00202: controlfile: '/dev/vx/rdsk/racdg/orcl_raw_controlfile1' ORA-27041: unable to open file SVR4 Error: 6: No such device or address Additional information: 2 Thu Jan 5 20:12:07 2006 ORA-205 signalled during: ALTER DATABASE MOUNT... DISKDUMP $ dd if=/dev/vx/rdsk/racdg/orcl_raw_controlfile1 of=/dev/

COLD BACKUP OF RAW FILESYSTEM

2006-01-13T00:28:00.000-08:00

COLD BACKUP OF RAW FILESYSTEM SQL> select status from v$backup; STATUS--------- NOT ACTIVE NOT ACTIVE ...21 rows selected. select file#,status,error,fuzzy,recover from v$datafile_header; SQL> col error format a20 SQL> / FILE# STATUS ERROR FUZ REC---------- ------- -------------------- --- --- 1 ONLINE YES NO

Oracle patch 9.2.0.6 on RAC

2006-01-12T03:19:00.000-08:00

Before Installing the patch kindly read the readme.html located in the disk 1 directory thoroughly.Note:The 32-bit version of the patch set must be installed only on the 32-bit version of the database software, regardless of whether the operating system is 32-bit or 64-bit. The 64-bit version of the patch set must be installed only on the 64-bit version of the database software that runs on the

Oracle 9.2 on SUSE 9.3

2006-01-12T03:18:00.000-08:00

Hardware Requirements The system must meet the following minimum hardware requirements: Requirement Minimum Value RAM 512 MB Swap space Approx. twice the size of RAM Disk space in /tmp 400 MB Disk space for software files 2.5 GB (2621440 KB) Disk space for database files 1.2

How to export selective rows

2006-01-12T03:13:00.000-08:00

TIP : Now you can export a selective rows of a table(Feature of Oracle 8i) You can export a selective rows of table with a new introduced QUERY option of export.You can use QUERY option on command line as well as in parameter file. Preferable way is to use parameter file. Method 1 :- Command line ON WINDOWS PLATFORM Task:- Export only rows of table emp where comm. > 20