Tom:

How about something like this for your challenge?  It isn't pretty, but I think it shows what you were looking for...


SQL> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.1.0 - 64bit Production
With the Partitioning option
JServer Release 9.0.1.1.0 - Production

SQL> connect scott@o9it
Enter password:
Connected.

SQL> create cluster tom_test
  2  (deptno number);

Cluster created.

SQL> create table emp2
  2  (empno number primary key,
  3  deptno number not null)
  4  cluster tom_test (deptno);

Table created.

SQL> create table dept2
  2  (deptno number,
  3  dname varchar2(32))
  4  cluster tom_test (deptno);

Table created.

SQL> CREATE INDEX idx_tom_test ON CLUSTER tom_test;

Index created.

SQL> insert into emp2 values (1000, 100);

1 row created.

SQL> insert into dept2 values (100, 'Test Dept');

1 row created.

SQL> update emp2
  2  set emp2.deptno = (select dept2.deptno from dept2
  3                     where dept2.rowid = emp2.rowid
  4                     and dept2.dname = 'Test Dept');

1 row updated.

SQL>

- Mark 

Dear Tom,
Do duplicate rows have different rowids?
Please see below.

SQL> show rel
release 902000100

SQL> select rowid,t.* from t t;

ROWID                       X                                                   
------------------ ----------                                                   
AAAHoZAABAAAMViAAD          1                                                   
AAAHoZAABAAAMViAAE          1                                                   
AAAHoZAABAAAMViAAF          1                                                   

Please do reply.
Bye!

 

Thanks for the suggestions and the excellent pointer to the split script. I had to add an additional condition as  ' and data_object_id is not null' to the last inline select statement in the split script as follows.

(select data_object_id from user_objects where object_name  = upper('&TNAME')  and data_object_id is not null
)

Without the additional predicate clause, I was running into the following error.
SQL> @split
Enter value for chunks: 4
Enter value for tname: TEMPLATE
Enter value for user: FACEXPDBO
Enter value for tname: TEMPLATE
       GRP MIN_RID            MAX_RID
---------- ------------------ ------------------
         0 AAALp6AARAAAAAJAAA AAALp6AAuAAAA+ICcQ
         0 AAALqEAARAAAAAJAAA AAALqEAAuAAAA+ICcQ
         0 AAALvEAARAAAAAJAAA AAALvEAAuAAAA+ICcQ
         0 AAALvJAARAAAAAJAAA AAALvJAAuAAAA+ICcQ
         0 AAALvOAARAAAAAJAAA AAALvOAAuAAAA+ICcQ
         0 AAALw9AARAAAAAJAAA AAALw9AAuAAAA+ICcQ
         0 AAALxCAARAAAAAJAAA AAALxCAAuAAAA+ICcQ
         0 AAALxHAARAAAAAJAAA AAALxHAAuAAAA+ICcQ
         0 AAALxMAARAAAAAJAAA AAALxMAAuAAAA+ICcQ
         0 AAALxRAARAAAAAJAAA AAALxRAAuAAAA+ICcQ
         0 AAAL5rAARAAAAAJAAA AAAL5rAAuAAAA+ICcQ
         0 AAAL5wAARAAAAAJAAA AAAL5wAAuAAAA+ICcQ
         0 AAAL52AARAAAAAJAAA AAAL52AAuAAAA+ICcQ
         0 AAAL57AARAAAAAJAAA AAAL57AAuAAAA+ICcQ
         0 AAAL9jAARAAAAAJAAA AAAL9jAAuAAAA+ICcQ
ERROR:
ORA-01410: invalid ROWID
ORA-06512: at "SYS.DBMS_ROWID", line 38



15 rows selected.

Once I added the predicate clause, it worked fine and was returning 64 rows for the 16 partitions. I also did a count after doing a union query on the select rowids ranges to make sure the script alteration was okay. It matched up fine with the total count from the template table. 

Thanks again !!
Valar