梦想有多远

判断使用的是spfile还是pfile

select decode(count(*), 1, ’spfile’, ‘pfile’ )
from v$spparameter
where rownum=1
and isspecified=’TRUE’

alter system switch logfile 是强制日志切换，不一定就归档当前的重做日志文件（若自动归档打开，就归档前的重做日志，若自动归档没有打开，就不归档当前重做日志。）
alter system archive log current 是归档当前的重做日志文件，不管自动归档有没有打都归档。
主要的区别在于:
ALTER SYSTEM SWITCH LOGFILE对单实例数据库或RAC中的当前实例执行日志切换;
而ALTER SYSTEM ARCHIVE LOG CURRENT会对数据库中的所有实例执行日志切换。

为什么执行热备后要执行alter system archive log current 这个语句，是不是必须的？

一般的RMAN脚本都是这样写的，因为RMAN是可以备份归档日志的。alter system archive log current 这样后就可以将所有的归档都备份出来了。这样做是为了保证数据的完整和一致。

ALTER SYSTEM SWITCH LOGFILE ;
SWITCH LOGFILE Clause
The SWITCH LOGFILE clause lets you explicitly force Oracle to begin writing to a new redo log file group, regardless of whether the files in the current redo log file group are full. When you force a log switch, Oracle begins to perform a checkpoint but returns control to you immediately rather than when the checkpoint is complete. To use this clause, your instance must have the database open.

ALTER SYSTEM ARCHIVE LOG CURRENT ;
CURRENT Clause
Specify CURRENT to manually archive the current redo log file group of the specified thread（instance）, forcing a log switch. If you omit the THREAD parameter, then Oracle archives all redo log file groups from all enabled threads（instances）, including logs previous to current logs. You can specify CURRENT only when the database is open.

ALTER SYSTEM ARCHIVE LOG CURRENT NOSWITCH;
NOSWITCH
Specify NOSWITCH if you want to manually archive the current redo log file group without forcing a log switch. This setting is used primarily with standby databases to prevent data divergence when the primary database shuts down. Divergence implies the possibility of data loss in case of primary database failure.

You can use the NOSWITCH clause only when your instance has the database mounted but not open. If the database is open, then this operation closes the database automatically. You must then manually shut down the database before you can reopen

一
用wmsys.wm_concat() 函数（NINGOO)
假如有如下表，其中各个i值对应的行数是不定的

SQL> select * from t;

I A D
———- ———- ——————-
1 b 2008-03-27 10:55:42
1 a 2008-03-27 10:55:46
1 d 2008-03-27 10:55:30
2 z 2008-03-27 10:55:55
2 t 2008-03-27 10:55:59
要获得如下结果，注意字符串需要按照D列的时间排序：

1 d,b,a
2 z,t

SQL> select i,wmsys.wm_concat(a)
2 from
3 (select * from t order by i,d)
4 group by i;

I WMSYS.WM_CONCAT(A)
———- ——————–
1 d,b,a
2 z,t
执行计划上看，只需要做一次表扫描就可以了，但是这个函数是加密过的，执行计划并不能显示函数内部的操作。

二、row_number()

SQL> Select a.*,row_number() over(Partition By Name Order By val) rn From a;

ID NAME VAL RN
———- ———- ———- ———-
1 aaa 30 1
2 aaa 40 2
3 aaa 60 3
4 bbb 40 1
5 bbb 60 2
6 bbb 80 3

6 rows selected.

SQL> select Name,sum(decode(tmp.rn,1,val)) val1,sum(decode(tmp.rn,2,val)) val2,sum(decode(tmp.rn,3,val)) val3 from (Select a.*,row_number() over(Partition By Name Order By val) rn From a)tmp Group By Name;

NAME VAL1 VAL2 VAL3
———- ———- ———- ———-
aaa 30 40 60
bbb 40 60 80

看到的几种删除重复数据方法：总结下
test是需要处理的表有 m n两列

1 创建临时表
create table test_temp (select distinct * from test);
truncate table test
inserto into test (select * from test_temp);
适合小数据量

2 利用ROWID
因为数据库里的ROWID是唯一的

delete from test where rowid in (select a.rowid from test a,test b where a.rowid>b.rowid and a.m=b.m and a.n = b.n)
适合每条记录只有一条重复数据的情况

3 MAX或MIN函数（ROWID)

delete from test a where rowid not in (select max(b.rowid) from test b where a.m=b.m and a.n = b.n);

4 row_number函数

delete from test where rowid not in (select t.rowid from (select rowid,row_number() over (partition by m,n order by m,n) as rank from test) t where rank=1);

5 exceptions into exceptions

SQL> create table t ( a int, b int, c int );

表已创建。

SQL> insert into t select rownum,rownum+1,rownum+2 from all_objects where rownum <
<5;

已创建4行。

SQL> insert into t select *from t where rownum<<3;

已创建2行。

SQL> commit;

提交完成。

SQL> select *from t;

A B C
———- ———- ———-
1 2 3
2 3 4
3 4 5
4 5 6
1 2 3
2 3 4

已选择6行。
SQL> create table exceptions(row_id rowid,
2 owner varchar2(30),
3 table_name varchar2(30),
4 constraint varchar2(30));

表已创建。

SQL>
SQL> alter table t add constraint t_unique
2 unique(a,b,c) exceptions into exceptions;
alter table t add constraint t_unique
*
ERROR 位于第 1 行:
ORA-02299: 无法验证 (EPUSER.T_UNIQUE) - 未找到重复关键字

SQL> create table dups
2 as select *from t where rowid in (select row_id from exceptions);

表已创建。

SQL> select *from dups;

A B C
———- ———- ———-
1 2 3
2 3 4
1 2 3
2 3 4

SQL> select row_id from exceptions;

ROW_ID
——————
AAAIEJAAKAAAyMSAAA
AAAIEJAAKAAAyMSAAE
AAAIEJAAKAAAyMSAAB
AAAIEJAAKAAAyMSAAF

SQL> delete from t where rowid in ( select row_id
2 from exceptions );

已删除4行。

SQL> insert into t select distinct * from dups;

已创建2行。

SQL>
SQL> commit;

提交完成。

SQL> select *from t;

A B C
———- ———- ———-
3 4 5
4 5 6
1 2 3
2 3 4

查找重复数据：

SQL> select * from t a
2 where rowid >any (select rowid from t b where a.a=b.a);

A B C
———- ———- ———-
1 2 3
2 3 4