SQL>select * from ttt;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
12 rows selected.
SQL>
SQL>select rowid ,empno from ttt;
ROWID EMPNO
------------------ ----------
AAANB8AAEAAAAHNAAA 7369
AAANB8AAEAAAAHNAAB 7499
AAANB8AAEAAAAHNAAC 7521
AAANB8AAEAAAAHNAAD 7566
AAANB8AAEAAAAHNAAO 7369
AAANB8AAEAAAAHNAAP 7499
AAANB8AAEAAAAHNAAQ 7521
AAANB8AAEAAAAHNAAR 7566
AAANB8AAEAAAAHQAAA 7369
AAANB8AAEAAAAHQAAB 7566
AAANB8AAEAAAAHQAAC 7521
AAANB8AAEAAAAHQAAD 7499
12 rows selected.
对重复的行保留其中的一行,其他的行删除。
一般的做法是在表中设一个字段rowid来表示行的行数,那么在删除的时候使用min(rowid)来把其中的一行过滤掉。
delete from ttt where empno in
(select empno from ttt group by empno having count(*) >1)
and rowid not in(select min(rowid) from ttt group by empno having count(*) >1);
但是如果没有该rowid字段时,则不能使用这种方法,因为没有办法过滤其中的一行。此时就需要借助于一张临时表,方法是把不重复的记录先放在临时表中,然后删除表中所有的数据,再从临时表中把数据取回即可。
select distinct * into tmp from emp
delete from emp
insert into emp select * from tmp
SQL>
SQL>delete from ttt where empno in
2 (select empno from ttt group by empno having count(*) >1)
3 and rowid not in(select min(rowid) from ttt group by empno having count(*) >1);
8 rows deleted.
SQL>
SQL>commit;
Commit complete.
SQL>select * from ttt;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
SQL>
更多参考:
the specified nodes are not clusterable
根据rowid删除表中重复的行
Agent process exited abnormally during initialization
一次字符乱码的解决过程
rman实验(一)
rman实验(二)
ORA-00600: internal error code, arguments: [keltnf
ORA-00600: ORA-12012 ORA-08102解决
linux下完全删除oracle
INFO: /usr/bin/ld: crt1.o: No such file: No such file or directory
centos4.8_64上安装oracle10201建库报ORA-12547
EM乱码解决
ORA-31613 Master process DM00 failed during startup
ORA-00600: internal error code, arguments: [4194], [29], [27], [], [], [], [], []
ORA-24324 ORA-01041 ORA-03113
centos5.3升级oracle
pdksh-5.2.14-36.el5.i386.rpm
使用rman进行数据库迁移
oracle10.2.0.1升级到10.2.0.4报错
Upgrade Oracle 10g from 10.2.0.1 to 10.2.0.4 (Single Instance)
改oracle的name和dbid
修改oracle实例名
Solaris8上迁移oracle8i---上
Solaris8上迁移oracle8i---下
未备份归档日志导致数据丢失的实验
使用NBU进行数据库迁移
catalog备份数据库
RMAN FORMAT字符串格式化
Error: can not register my instance state - -1
not all alterations performed
The ASM instance configured on the local node is a single-instance ASM
/u01/crs102/bin/crsctl.bin: error while loading shared libraries: libstdc++.so.5: cannot open shared
Initializing the Oracle ASMLib driver: [FAILED]
ORA-00245: control file backup operation failed
WARNING: failed to read mirror side 1 of virtual extent 229 logical extent
模拟恢复参数文件
Interface eth0 checked failed
import server uses ZHS16GBK character set (possible charset conversion)