oracle问题 小集

1. 动态sql
sql_string:=' select sysdate from dual'
execute immediate  sql_string

 

2.存储过程例子

create or replace procedure p_test2 as

begin
for m_for in 1..20000
loop
 insert
   into t_mkt_vipuser
  (vipid, vipnumber, mms_inceptmaxnum_day, sms_inceptmaxnum_day, wap_inceptmaxnum_day, mms_inceptmaxnum_week, sms_inceptmaxnum_week, wap_inceptmaxnum_week, mms_inceptmaxnum_month, sms_inceptmaxnum_month, wap_inceptmaxnum_month)
values
  (1, -1, 0, 0, 0, 0, 0, 0, 0, 0, 0);
  commit;
  end loop;
 

for m_for in 1..10
  loop
  dbms_output.put_line('循环测试:'||m_for);
  end loop;

end;

3.插入数据时，不写日志 insert /*+ append nologging */  into

 

4.oracle 定时任务job

 

begin
  sys.dbms_job.submit(job => :job,
                      what => 'p_back_delhisreportjob;',
                      next_date => to_date('06-09-2007 04:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'trunc(sysdate +1, ''dd'') + 4/24');
  commit;
end;  

 

5.oracle 10g 导出错误 错误 6550 解决方法

使用plsql/developer工具导出数据出现如下：

EXP-00056: 遇到 ORACLE 错误 6550
ORA-06550: line 1, column 41:
PLS-00302: component 'SET_NO_OUTLINES' must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: 导出终止失败 

 

解决方法：exp.exe 改成使用 expdp.exe

类似 导入 就使用 impdp.exe命令

 

在plsql/dev 中 方法改成 E:/oracle/product/10.2.0/client_2/bin/expdp.exe 即可

 

10G的新特性IMPDP和EXPDP