Oracle 删除重复数据只留一条
- 查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断
select * from 表 where Id in (select Id from 表 group byId having count(Id) >1
- 删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录
DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);
- 查找表中多余的重复记录（多个字段）
select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having count(*) > 1)
- 删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)
- 查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)
例子
delete
--select *
from cell where cgi in (select cgi from cell group by cgi having count(cgi) > 1)
and time_stamp not in (select min(time_stamp) from cell group by cgi having count(cgi)>1)
查看UNDO空间的快照
select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s
where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username;
查看正在进行的会话
SELECT b.sid oracleID, b.username 登录Oracle用户名, b.serial#, spid 操作系统ID, paddr, sql_text 正在执行的SQL, b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c WHERE a.addr = b.paddr AND b.sql_hash_value = c.hash_value
锁表和删除会话
select p.spid, a.serial#, c.object_name, b.session_id, b.oracle_username, b.os_user_name from v$process p, v$session a, v$locked_object b, all_objects c where p.addr = a.paddr and a.process = b.process and c.object_id = b.object_id ;
select l.session_id,o.owner,o.object_name from v$locked_object l,dba_objects o where l.object_id=o.object_id;
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t
where t1.session_id=t2.sid order by t2.logon_time;
alter system kill session '89,22763';session_id,serial#
索引失效
select index_name ,status from user_indexes where Status = 'UNUSABLE'
select 'alter index ' || index_name || ' rebuild;' from user_indexes where Status = 'UNUSABLE'
select owner, index_name,table_name from dba_indexes where status = 'UNUSABLE'
select index_owner,index_Name,partition_name from dba_ind_partitions where status = 'UNUSABLE';
新建用户
select * from dba_users
create user fast_nokia identified by fast_nokia
grant select any dictionary to fast_nokia
grant connect,select any table to fast_nokia
Oracle表空间增加数据文件
- 查看每个表空间当前数据文件剩余块
select * from dba_data_files where tablespace_name='TPADBS06'
select * from dba_temp_files
- 给表空间增加数据文件
ALTER TABLESPACE USERS ADD DATAFILe '+DGSTATIC/jxwydb/datafile/users21.dbf' SIZE 50M AUTOEXTEND ON NEXT 100M MAXSIZE 31G;
ALTER TABLESPACE TPADBS05 ADD DATAFILe '+DGWARE/jxwydb/datafile/tpadbs05_24.dbf' SIZE 50M AUTOEXTEND ON NEXT 100M MAXSIZE 31G;
Oracle自动摘除分片维护
- 分片维护表
select * from tab_partition_drop
- 作业调度时间（目前是5点运行）
select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';
select * from user_jobs where what='PROC_DROP_PATITION_SCRIPT;
表空间查询
- 查看单个表空间使用情况
SELECT TABLESPACE_NAME,SEGMENT_NAME,TO_CHAR(SUM(BYTES)/(1024*1024),'999G999D999') CNT_MB
FROM DBA_EXTENTS
WHERE OWNER='NIOSDB'
AND SEGMENT_TYPE LIKE 'TABLE%'
and TABLESPACE_NAME='NIOSDBS'
GROUP BY TABLESPACE_NAME,SEGMENT_NAME;
*查看所有表空间使用情况
SELECT TABLESPACE_NAME,MAX_M,COUNT_BLOCKS FREE_BLK_CNT,SUM_FREE_M,TO_CHAR(100 * SUM_FREE_M/SUM_M, '99.99')||'%' AS PCT_FREE
FROM (SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 AS SUM_M
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME),
(SELECT TABLESPACE_NAME AS FS_TS_NAME,MAX(BYTES)/1024/1024 AS MAX_M,COUNT(BLOCKS) AS COUNT_BLOCKS,SUM(BYTES/1024/1024) AS SUM_FREE_M
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME)
WHERE TABLESPACE_NAME = FS_TS_NAME
ORDER BY PCT_FREE
恢复表数据修改内容
create table backup_table as
select * from source_table as of timestamp sysdate - 1/48 where title like '%测试%';
sysdate - 1/48 半个小时以内操作的 sysdate - 1/24 一个小时内操作的
