Oracle常用语句

一、导出有条件数据:

exp zh/mm@signon tables=(tablename1) file=d:\aa.dmp query=\"where ID='C8ECD78D123C42AEA64910C30325CEA7'\"

 

二、修改字符集：

a.查看字符集 select * from v$nls_parameters

b.客户端:cmd下运行set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

c.服务器

sql> 修改字符集

plsql /nolog

connect sys/密码 as sysdba;

shutdown immediate      --关闭数据库例程

startup mount;       --启动数据库

alter system enable restricted session;

alter system set job_queue_proceses=0;

alter database open;

alter database character set ZHS16GBK;

alter database character set INTERNAL_USE ZHS16GBK;  跳过字符超集检查，直接更改

shutdown immediate;

startup;

 

三、删除归档日志：

在cmd中输入Rman target sys/oracle@fjzwt
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1/24';( 一个小时前的日志)

 

四、删除重复记录

delete from tabletemplate where id in (select id from tabletemplate group by id having count(*)>1)

and rowid not in (select max(rowid) from tabletemplate group by id having count(*)>1);

 

五、修改sys密码

进入数据库服务器那台电脑

开始菜单->运行->输入‘CMD’,打开命令提示符窗口，输入如下命令：

orapwd file=D:/oracle/product/11.1.0/db_1/database/pwdctcsys.ora password=newpass

 

六、存储过程恢复,以sys登录

  select object_id from dba_objects where object_name ='存储过程名称'

  select * From source$ as of timestamp to_timestamp('2012-11-21 11:00:00', 'yyyy-mm-dd hh24:mi:ss') where obj#=233454;

 

七、数据回闪

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time,to_char(dbms_flashback.get_system_change_number) scn from dual;

create table newTable as select * from Table1  as of timestamp to_timestamp('2012-05-22 10:00:00','yyyy-mm-dd hh24:mi:ss')

 

八、空表导出

select table_name from user_tables where NUM_ROWS=0;

Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;

 

九、Oracle 返回有重复的记录 ：

select id from A group by id  having count(id) > 1;

 

十、分页存储过程：

procedure PRO_GetList(p_begin in number,p_end in number,p_out out p_cur)

 is

 begin

  open p_out for

  select * from (SELECT a.* ,rownum r_num FROM Ta a

  where  rownum <=''|| p_end ||'') where r_num>''|| p_begin ||'';

 end;

 

十一、存储过程循环：

  procedure PRO_UpdataOrderID(p_ID in varchar,p_orderID in varchar)

  is

  position integer;

  ID_TmpStr varchar2(64);

  ID_Str varchar2(4000);

 

  orderID_TmpStr varchar2(64);

  orderID_Str varchar2(4000);

 

  begin

  ID_Str := p_ID;

  orderID_Str:=p_orderID;

 

  While Instr(ID_Str, ',', 1, 1) > 0 Loop

        position := Instr(ID_Str, ',', 1, 1);

        ID_TmpStr := Substr(ID_Str, 1, position - 1);

        ID_Str := SubStr(ID_Str, position + 1, length(ID_Str));

 

        position := Instr(orderID_Str, ',', 1, 1);

        orderID_TmpStr := Substr(orderID_Str, 1, position - 1);

        orderID_Str := SubStr(orderID_Str, position + 1, length(orderID_Str));

       

        update Users set OrderID= orderID_TmpStr where ID= ID_TmpStr;

      end loop;

  if not ID_Str is null then

       --将最后一个保存

        update Users set OrderID= orderID_Str where ID= ID_Str;

      end if;

  end;

转载于:https://www.cnblogs.com/gaodaidai/p/3643523.html