Oracle日常维护笔记-数据操作
1 数据操作
1.1 用A表的数据 某一列 更新B表 数据的某一列
Update tableA a set a.col1 = (select b.col1 from tableB b where b.ind = a.ind)
l 如果再A表中有的列，而在B表中没有，那么A表中的列会被更新成空
解决方法：
update test3 b set b.username = (select c.username from test2 c where c.ind = b.ind)
where b.ind in (select e.ind from test3 e inner join test2 f on e.ind = f.ind);
1.2 用表复制，用A表来创建B表
create table test4 as select * from test2;
此方法会把A表的数据也复制给B表，但是不会创建索引
l 只创建表，不复制数据：
create table test4 as select * from test2 where 1 = 2;
1.3 PL/SQL插入批量数据
DECLARE
CUSTOMER_ID NUMBER(18);
BEGIN
CUSTOMER_ID := 302000000035000001;
WHILE CUSTOMER_ID <= 302000000035000001 LOOP
BEGIN
insert into cps_customer_last_activity values(CUSTOMER_ID, sysdate-200,'');
commit;
CUSTOMER_ID := CUSTOMER_ID +1;
END;
END LOOP;
END;
1.4 分批更新数据
方法一：
drop table t ;
create table t as select * from dba_objects;
create index i_t_id on t(object_id);
/
create or replace procedure p_update as
type type_1 is table of t%rowtype index by binary_integer;
va type_1;
cur sys_refcursor;
begin
open cur for 'select * from dba_objects t where owner=''SYS''';
loop
fetch cur bulk collect
into va limit 10000;
exit when va.count = 0;
forall i in 1 .. va.count
update t
set t.object_name = va(i).object_name
where t.object_id = va(i).object_id;
commit;
end loop;
end;
/
方法二：
loop
update table
where rownum<=10000;
if sql%found then
commit;
else
exit;
end if;
end loop;
1.5 导出导入某个分区数据
导出：
expdp cpstxn22/Aa123456 dumpfile=CPS_TRANS_RECORD.dmp logfile=20150310.log tables=CPS_TRANS_RECORD:SYS_P4703;
-----------导出多个分区用逗号隔开
导入：
impdp cpstxn22/Aa123456 DIRECTORY=DATA_PUMP_DIR DUMPFILE=CPS_TRANS_RECORD.dmp TABLES=CPS_TRANS_RECORD logfile=imp.log table_exists_action=append
---记得赋权限
综合语句：
example: imp scott/tiger ignore=y tables=(emp,dept) full=n
or tables=(t1:p1,t1:p2), if t1 is partitioned table
example: exp scott/tiger grants=y tables=(emp,dept,mgr)
or tables=(t1:p1,t1:p2), if t1 is partitioned table
example: expdp scott/tiger dumpfile=scott.dmp directory=dmpdir schemas=scott or tables=(t1:p1,t1:p2), if t1 is partitioned table
example: impdp scott/tiger directory=dmpdir dumpfile=scott.dmp
1.6 数据清理
1. 首先执行生成批量删除存储过程：
create or replace procedure delBigTab
(
p_TableName in varchar2,
p_Condition in varchar2,
p_Count in varchar2
)
as
pragma autonomous_transaction;
n_delete number:=0;
begin
while 1=1 loop
EXECUTE IMMEDIATE
'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
USING p_Count;
if SQL%NOTFOUND then
exit;
else
n_delete:=n_delete + SQL%ROWCOUNT;
end if;
commit;
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
end;
/
2 . 执行命令
exec delBigTab('TABLE_NAME’,’ CUSTOMER_ID < 205000000008000000','10000');
第一个参数是表明，第二个参数是 删除的范围，第三个参数是每次提交的数量
1.7 导入外部数据
1.1 使用sqlldr导入文件
1 编写控制文件
使用sqlldr命令导入数据，要另外再写一个控制文件（.ctl格式），这里假设控制文件名是test.ctl。
2 向一个空表student中插入数据的test.ctl内容如下：
Load DATA
infile 'input.unl'
Insert
into table student
FIELDS TERMINATED by '|'
(ID,Name,SCORE)
"replace(:name, '\\n',chr(10))"
3 向一个有数据的表student中插入数据的test.ctl内容如下：
Load DATA
infile 'input.unl'
Append
into table student
FIELDS TERMINATED by '|'
(ID,Name,SCORE)
4 Shell命令行执行命令
sqlldr 数据库用户名/数据库用户密码 control=test.ctl
5 SQLLDR导入换行特殊字符
Name "replace(:name, '\\n',chr(10))"
1.2 使用PL/SQL导入外部数据
1.构建UNL文件，按列值|列值的形式构建数据文件。
2.用对应数据库用户登录PL/SQL:选择tools菜单下的 text importer菜单，
选择红色菜单，然后导入unl文件
3然后配置导入数据选项
选择菜单页data from textfile
①中的2表示你有多少列数据要导入到表中， end at line-end 表示在换行时表示所有列输入结束，"表示用什么符号去引用你的分割符
②列格式预览和选择
③列格式起始位置和结束位置标记
④列值的预览
4导入数据选项
选择菜单页 data to oracle
1 表示导入到的数据库用户和数据库表名， 复选项表示是否删除之前已经有的数据
2 表示多少条数据提交一次， 是否/忽略覆盖重复数据，
3 每列对应哪个字段,需要用户自己定义，有点繁琐，
5 选择好以后点击 import导入
1.8 快速把某个字段值更新成同一个值
方法就是通过删除列和添加带默认值的列来操作：
alter table cps_customer drop column dates;
alter table cps_customer add load_data_ts TIMESTAMP default to_date(sysdate,'yyyy-MM-dd HH24:mi:ss') not null;
1.9 MERGE用法
MERGE语句会检查原数据表记录和目标表记录。如果记录在原数据表和目标表中均存在，则目标表中的记录将被原数据表中的记录所更新（执行Update操作）；如果目标表中不存在的某（些）记录，在原数据表中存在，则原数据表的这（些）记录将被插入到目标表中（执行Insert操作）。
在Oracle 10g之前，merge语句支持匹配更新和不匹配插入两种简单的用法，在10g中Oracle对merge语句做了增强，增加了条件选项和DELETE操作。
语法：
MERGE [hint] INTO [schema ] table [t_alias]
USING [schema ]{ table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;
1.MERGE INTO test T1
2.USING (SELECT OWNER , OBJECT_NAME , MAX(ID) ID FROM T GROUP BY OWNER, OBJECT_NAME) T
3.ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
4.WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
5.WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);
第一行 命名目标表并给别名T1
第二行 using子句提供merge操作的数据源，命名T
第三行 on子句指定合并的条件
第四行 when matched then 子句判定条件符合则对表T1做什么改变（或删除）
第五行 when not matched then 子句判断条件不符合则插入的操作
1.10 根据临时表删除重复记录
delete from cps_product_service p
where exists (select 1
from cps_temp t
where t.product_unique_id = p.product_unique_id
and t.service_index = p.service_index);
commit;
1.11 十进制转36进制存储过程
create or replace function f_36_tr_10(v_36_data in varchar)
return number
is
v_data number(18);
begin
select sum(data) into v_data from
(
select (
case substr(upper(v_36_data),rownum,1)
when 'A' then '10'
when 'B' then '11'
when 'C' then '12'
when 'D' then '13'
when 'E' then '14'
when 'F' then '15'
when 'G' then '16'
when 'H' then '17'
when 'I' then '18'
when 'J' then '19'
when 'K' then '20'
when 'L' then '21'
when 'M' then '22'
when 'N' then '23'
when 'O' then '24'
when 'P' then '25'
when 'Q' then '26'
when 'R' then '27'
when 'S' then '28'
when 'T' then '29'
when 'U' then '30'
when 'V' then '31'
when 'W' then '32'
when 'X' then '33'
when 'Y' then '34'
when 'Z' then '35'
else substr(v_36_data,rownum,1) end
)*power(36,length(v_36_data)-rownum) data
from dual
connect by rownum<=length(v_36_data)
);
return v_data;
exception
when others then
return null;
end;
CREATE OR REPLACE FUNCTION N10_TO_36(V_NUM NUMBER)
RETURN VARCHAR IS RESULT VARCHAR(10);
NUM NUMBER;
TMP NUMBER;
TEMP NUMBER;
BEGIN
NUM := V_NUM;
TMP := TRUNC(NUM / 36);
WHILE NUM > 0 LOOP
TEMP := MOD(NUM, 36);
IF TEMP < 10 THEN
RESULT := TEMP || RESULT;
ELSE
RESULT := CHR(TEMP + 55) || RESULT;
END IF;
NUM := TMP;
TMP := TRUNC(NUM / 36);
END LOOP;
return RESULT;
end;
1.12 使用存储过程
create or replace procedure pro_1(id varchar2)
is
name varchar2(20); -- 声明一个变量
begin
-- 以下就是存储过程的主体部分
dbms_output.put_line('id:' || id || ' name:' || name);
end;
alter procedure procedure_name compile; ---编译
set serveroutput on;
execute pro_1(00813025); --执行
使用游标和循环匹配
DECLARE
cursor temp
is select * from lbi_dw.t_s_sys_log t where t.task_status not in (1,2,-1,-2) and t.task_name='prc_m_r_iden_funds_detail_c';
begin
for t in temp
loop
if t.task_id = 540137 then
dbms_output.put_line(t.table_name||' '||t.task_id);
end if;
end loop;
end;
/
---------------------------------------------------
declare
orgid number(18):=0;
shorts varchar(24):='';
flag number(2):=0;
cursor temp is select account_no, entryCount from (
select t.account_no account_no, count(1) as entryCount from cpstxn.cps_account_entry t
where t.transaction_time>=to_date('20160623 00:00:00','yyyymmdd hh24:mi:ss')
and t.transaction_time<=to_date('20160623 23:59:59','yyyymmdd hh24:mi:ss')
and t.identity_type!='1000'
and t.realtime_update='0'
group by t.account_no having count(1)> 10
order by entryCount desc
) n where rownum<50 ;
begin
for te in temp
loop
if te.entryCount >= 3000 then
select count(s.identity_id) into flag from cpstxn.cps_org_account s where s.ACCOUNT_NO = te.account_no ;
if flag >= 1 then
select s.identity_id into orgid from cpstxn.cps_org_account s where s.ACCOUNT_NO = te.account_no ;
select short_code into shorts from cpsmgt.cps_biz_org where BIZ_ORG_ID=orgid;
dbms_output.put_line(orgid||' , '||shorts);
end if;
end if;
end loop;
end;
/
1.13 更改数据库快照时间更新周期
begin
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(11520,60);
end;
/
1.14 关于_ktb_debug_flags
alter system set "_ktb_debug_flags"=8 scope=both sid='*';
1.15 修改数据库密码错误重试延迟
alter system set event ="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" scope=spfile;