ORA-30926: unable to get a stable set of rows in the source tables

Quite often, you may see this warning message when you use MERGE statement.

If you get this, then your Merge statement is trying to update multiple rows in "WHEN MATCHED" Clause.

Multiple updates to the same row in destination table is NOT allowed.

Multiple insert is allowed though.

To know for sure, If you comment out your "WHEN MATCHED" section, you will no longer get this warning.

Oracle: How to size UNDO Tablespace for Automatic Management

Here are some queries to figure out how to manage your UNDO Tablespace from 10g onwards. Oracle strongly recommends using UNDO_MANAGEMENT=AUTO.

If your UNDO tablespace is fixed size, some of the queries may help you. All of them are from Oracle Metalink.Replace UNDOTBS1 with name of your UNDO tablespace.

--UNDO Tablespace size
Select SUM(BYTES)/1024/1024 as "MB"
from dba_data_files
where tablespace_name = 'UNDOTBS1';

--Free space available
Select SUM(BYTES)/1024/1024 as "MB"
from dba_FREE_SPACE
where tablespace_name = 'UNDOTBS1';

--Active/Expired segments
SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 as "MB", COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

--To calculate space required for your UNDO to grow
SELECT ((UR * (UPS * DBS)) + (DBS * 24))/1024/1024 AS "MB"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));

UR=UNDO_RETENTION (in seconds)
UPS=Number of UNDO data blocks generated per second
DBS=DB_BLOCK_SIZE (in bytes)

--SQL to find the active transaction that is consuming the UNDO
-- Replace UNDOTBS02 with your UNDO tablespace name

SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.machine,
--s.osuser,s.terminal,s.module,s.schemaname,
s.state,s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 Undo_Size_KB,
t.status Transaction_Status,t.start_time,
t1.tablespace_name,sq.sql_text
FROM SYS.v_$rollname r, SYS.v_$session s, SYS.v_$transaction t, SYS.v_$parameter x, dba_rollback_segs t1, sys.v$sql sq
WHERE s.taddr = t.addr
and s.sql_id = sq.sql_id(+)
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
AND t1.segment_id = r.usn
AND t1.tablespace_name = 'UNDOTBS02'

Windows Explorer: Display security tab in Windows XP Professional

Click Start and launch Control Panel
Locate Folder Options
Click on the tab View, Scroll all the way down
Uncheck the option "Use simple file sharing (Recommended)"
Click OK. Now you should see Security tab on each folder.

Oracle: Data Block

Data Blocks: Smallest logical component of oracle database.It consists of number of bytes of disk space in OS. Usually in units of 2kb,4kb,8kb(Default),16kb or 32kb.

DB_BLOCK_SIZE gives your current block size.

Remember, Oracle will read the disk in terms of blocks. If you a have name of 4kb stored in a 32kb block, oracle will read the entire 32kb to retrieve your name. Also, OS also has disk block size and reads data in terms of disk block, Typically your oracle block size should be a multiple of disk block size.

System Tablespace is always created with standard block size. In addition to standard you can create 4 non standard block size for your tablespace.Data block contains row data portion (data stored in table or index) , free space prortion (space left for new data),overhad and header portion (for maintenance).

Extents: Two or more contiguous (touching each other) data blocks. It is a unit of space allocation
Segements: Set of extents to form a logical structure like table or index
Tablspace: Set of one or more data files containing related segments

In order to see data contained in a block, you need to dump it in OS

Select OWNER,SEGMENT_NAME,segment_type,tablespace_name,
header_file,header_block from dba_segments
where segment_name = 'CASES'

Alter System dump datafile header_file BLOCK header_block

Alter system dump datafile 15 block 1339

Go to UDUMP directory, the latest *.trc file will contain the binary dump of the block

Oracle: SQL to retrieve table and column comments

For the whole database
Select ut.owner,ut.table_name,ut.comments Table_Comments,uc.column_name,uc.comments Column_Comments
from dba_tab_comments ut, dba_col_comments uc,dba_tab_columns utc
where ut.owner = uc.owner
and uc.owner = utc.owner
and ut.table_name = uc.table_name
and ut.owner = 'Your_Owner_Name'
and uc.table_name = utc.table_name
and uc.column_name = utc.column_name
order by owner,ut.table_name

For the Schema
Select ut.table_name,ut.comments Table_Comments,uc.column_name,uc.comments Column_Comments,data_type
from USER_tab_comments ut, user_col_comments uc,user_tab_columns utc
where ut.table_name = uc.table_name
and uc.table_name = utc.table_name
and uc.column_name = utc.column_name
order by ut.table_name