1查看分区大小
SELECT SEGMENT_NAME, PARTITION_NAME, BYTES / 1024 / 1024 USED_MB
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN ('tableone',
'tabletwo')
2 比较两表数据差异
(select * from table1)
minus
(select * from table2)
3 '123,456,789'需要作为in('123','456','789')来查询，但是正常的replace后得到的还是一个字符串，所以才去下面的多行的办法，
不过限于sql长度，反倒不如代码处理后直接得到'123','456','789'合算
select replace(regexp_substr('123,456,789','[^,]+',1,level),',',' ') c1
from dual
connect by level<=length('123,456,789')-length(replace('123,456,789',',',''))+1
4 oracle 10g trim不起作用
REGEXP_REPLACE(param, '\s*', '')
5 按照坐标计算两点间距离
earth_padius:=6378137.0;
radLat1:=3.141592625*lat1/180.0;
radLat2:=3.141592625*lat2/180.0;
a:=radLat1 - radLat2;
b:=3.141592625*lng1/180.0 - 3.141592625*lng2/180.0;
s:=2*Asin(Sqrt(power(sin(a / 2), 2) +cos(radLat1) * cos(radLat2) * power(sin(b / 2), 2)));
s := s * earth_padius;
s := Round(s * 10000) / 10000.0;
return(s);
6 根据表名获取列值函数
create or replace function FUNC_GET_MAX_ETL_TIME(tb_name varchar2,last_etl_time varchar2) return timestamp is
Result timestamp;
V_SQL VARCHAR2(500);
V_RES_DATE timestamp;
begin
if last_etl_time is null then
V_SQL:='select max(etl_time) as max_etl_time from '||tb_name;
else
V_SQL:='select max(etl_time) as max_etl_time from '||tb_name||' where etl_time>=to_timestamp('''||last_etl_time||''',''yyyy-mm-dd hh24:mi:ss.ff'')';
end if;
EXECUTE IMMEDIATE v_sql into V_RES_DATE;
Result:= V_RES_DATE;
return(Result);
end FUNC_GET_MAX_ETL_TIME;
7 自增列：1建立sequence 2建立触发器
create sequence SEQ_ETL_K_T_SCHEDULE
minvalue 1
maxvalue 99999999
start with 67
increment by 1
cache 64
cycle
order;
create or replace trigger TIG_ETL_K_T_SCHEDULE_SEQ
before insert on etl_kettle_trans_schedule
for each row
declare
-- local variables here
begin
select SEQ_ETL_K_T_SCHEDULE.NEXTVAL into:new.id from dual;
end TIG_ETL_K_T_SCHEDULE_SEQ;
8 merge 使用
BEGIN
merge into table_a t1
using (select * from table_b) t2
on (t1.product_id = t2.product_id)
when matched then
update
set t1.VIDEO_ID = t2.VIDEO_ID,
t1.DESCRIPTION = t2.DESCRIPTION
when not matched then
insert
values
(t2.PRODUCT_ID,
t2.VIDEO_ID,);
END;
9 新建基于日志的物化视图
create materialized view log on tbname with rowid,sequence(col1,col2) including new values
