oracle使用技巧--备忘

luozhangwen 2011-01-05 09:03:14



1. oracle下修改密码

SQL>conn / as sysdba 

SQL>alter user system identified by 新密码; 



2.解锁用户scott

conn / as sysdba --以SYS身份登录

alter user scott account unlock; --解锁用户scott

conn scott/tiger --再次以scott登录



3.创建序列

create sequence T_ACCOUNT_POLICY_ID

minvalue 1

maxvalue 999999999999999999999999999

start with 1

increment by 1

nocache;



4.模糊带有百分好的数据

select SUBSCRIBERIDENTIFY from t_pcc_subscriber where SUBSCRIBERIDENTIFY like '%\%%' escape '\';   



5.查询日期

select to_date(sysdate,'yyyy-MM-dd') from dual;  



6.随机抽取5条记录



select SUBSCRIBERIDENTIFY from (select * from t_pcc_subscriber order by dbms_random.value ) where rownum < 5;  随机抽取5条记录



7.Rownum的用法,查询1-2条记录

select * from (select rownum rowIndex, t.* from t_pcc_subscriber t order by id) where rowIndex between 1 and 2;  



8.最有效的删除重复记录



delete from lzw l1 where l1.ROWID > (select min(l2.ROWID) from lzw l2 where l1.name = l2.name);    



9.子查询需要两列都匹配的

SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604) 



10. 用NOT EXISTS替代NOT IN (这个只能在面试的时候有用,^_^实际上效果差不多....)

在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的

(因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS. 

select deptno from dept d where exists (select * from emp e where d.deptno=e.deptno);



select count(*) from lzw where exists(select id from lzw where id between 1000 and 2000);



11. 在linux下面使用批量语句要用"/"来结束

create or replace procedure lzwInsert

is

maxrecords number:=10000000;

begin

   for i in 1000001..maxrecords loop

           insert into lzw values(i,i);

           commit;

         end loop;

end lzwInsert;

/



12.针对oracle11G密码180过期问题.

su - oracle

oracle@p-rac1:~>sqlplus / as sysdba

SQL> SELECT username,PROFILE FROM dba_users where username='DBO';

如果上述红色字体是“DEFAULT”，则说明没有做过密码永久过期保护



su - oracle

sqlplus -S -L / as sysdba<<!

set feedback off

create profile dbo_profile limit

FAILED_LOGIN_ATTEMPTS unlimited

PASSWORD_LIFE_TIME unlimited

/

alter user dbo profile dbo_profile;

!



13. 直接在root用户下执行命令.

su - oracle -c "sqlplus -S / as sysdba <<!

set head off

select * from dual; >>/home/oracle/aa.txt 

quit

!

"





drop user DBO cascade;         //删除用户

drop user RM9000USER cascade;  //删除用户