Oracle 系统表及相关操作（三）

最新推荐文章于 2024-10-24 19:36:14 发布

原创最新推荐文章于 2024-10-24 19:36:14 发布 · 409 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

oracle

本文详细介绍Oracle数据库的管理与优化方法，包括数据库版本查询、资源消耗进程分析、SQL性能诊断、用户会话与连接管理、AWR报告生成、表空间操作、数据导入导出流程、常见问题解决策略及性能调优技巧。

–数据库版本

select * from product_component_version;

–耗资源的进程
—权限比较大?

grant select any dictionary to 用户;

—这个权限是最低要求

grant select_catalog_role to 用户;

—收回权限

revoke select_catalog_role from user;

select s.schemaname schema_name,decode(sign(48 - command), 1,to_char(command),
'Action Code #'||to_char(command)) action,status session_status,
s.osuser os_user_name,s.sid,p.spid,s.serial# serial_num,
nvl(s.username,'[Oracle process]')user_name,s.terminal terminal,
s.program program,st.value criteria_value
from v$sesstat st,v$session s,v$process p
where st.sid=s.sid 
and st.statistic#=to_number('38')
and('ALL' = 'ALL'or s.status = 'ALL') 
and p.addr = s.paddr
order by st.value desc,p.spid asc,s.username asc,s.osuser asc

—查看消耗资源最多的SQL：

select 
sql_text,
hash_value,
executions,
buffer_gets,
disk_reads,
parse_calls
from v$sqlarea
where buffer_gets > 10000000 or disk_reads >1000000
order by buffer_gets + 100 * disk_reads desc;

–分析性能差的sql

select 
executions,
disk_reads,
buffer_gets,
round((buffer_gets-disk_reads)/buffer_gets,2) as hit_radio,
round(disk_reads/executions,2) as reads_per_run,
sql_text
from v$sqlarea
where executions>0
and buffer_gets>0
and (buffer_gets-disk_reads)/buffer_gets < 0.8

–查询共享池中已经解析过的sql语句及其相关信息
–executions 所有子游标的执行这条语句次数（越高越好）
–buffer_gets 所有子游标运行这条语句导致的读内存次数（越高越好）
–hit_radio 命中率（越高越好）
–disk_reads 所有子游标运行这条语句导致的读磁盘次数（越低越好）
–reads_per_run 每次执行读写磁盘数（越低越好）
–查看CPU占用情况

select * from 
(
  select v.parsing_schema_name,v.sql_id,v.child_number,v.sql_text,v.elapsed_time,v.cpu_time,v.disk_reads,
  rank()over(order by v.cpu_time desc) elapsed_rank 
  from v$sql v
) a where elapsed_rank <= 10;

–查看IO占用情况

select * from 
(
  select v.parsing_schema_name,v.sql_id,v.child_number,v.sql_text,v.elapsed_time,v.cpu_time,v.disk_reads,
  rank()over(order by v.disk_reads desc) elapsed_rank 
  from v$sql v
) a
where elapsed_rank <= 10;

–数据库连接
–查询用户当前会话数

select username,serial#, sid from v$session;

–数据库允许的最大连接数

select value from v$parameter where name ='processes';

–修改最大连接数

alter system set processes = 300 scope = spfile;

–AWR
–导出

sqlplus /nolog
conn / as sysdba
@?/rdbms/admin/awrrpt.sql

– report_type 输入 html
– num_days 输入天数 1-7
– snap 开始和结束的节点
– name D:/awrrpt_201802261630.html
–执行计划
–PL/SQL：
–工具 —> 首选项 —> 窗口类型 —> 计划窗口 —> 根据需要配置要显示在执行计划中的列
–sql：
–explain plan for 【sql】

select * from table (dbms_xplan.display);

–sqlplus
–关闭提示输出
set feedback off
–事务量
– 查看当前用户事务量

select 
s.username,
sum(se.value) "session transaction number",
sum(sy.value) " database transaction number" 
from v$session s,v$sesstat se,v$sysstat sy
where s.sid=se.sid and se.statistic#=sy.statistic#
and sy.name='user commits'
--and s.username=upper('wlzy_gsm')
group by s.username;

–二操作
–表空间
–创建表空间

create tablespace pds datafile'E:\aaa.dbf'size 500m autoextend on next 100m maxsize 2g extent` management local;

–增加表空间

alter tablespace uf add datafile 'G:\aaa.DBF' size 2g;

–删除表空间

-- drop tablespace a; -- 不删除数据文件
drop tablespace tablespace_name including contents and datafiles;

–回收站
–查看

select * from user_recyclebin;

–删除当前用户

purge recyclebin;

–彻底删除表命令

drop table tab_name purge;

–用户
–删除用户

drop user pds cascade;

–杀掉连接用户

select username,sid,serial# from v$session where username='USER_NAME';
alter system kill session '【sid】,【serial#】';

–解决账号被锁定问题

sqlplus / as sysdba 
alter user ra account unlock;

–修改密码

alter user you_username identified by you_password;

–导入导出
–导出用户

exp aaa/aaa@orcl log=d:\a.log file=d:\a.dmp
exp aaa/aaa@orcl log=d:\a.log file=E:\OracleData\backup\aaa.dmp

–导入用户

imp aaa/aaa@orcl log=d:\a.log file=d:\a.dmp fromuser=ra touser=ra tablespaces=ra ignore=yes

–导出用户(expdp)

sqlplus drop directory kh_directory;
sqlplus create directory kh_directory as'd:\\';
expdp aaa/aaa@orcl directory= aaa schemas=aaa dumpfile=aaa.dmp log=a.log version=10.255.0.0.0

–导入用户(impdp)

sqlplus drop directory kh_directory;
sqlplus create directory kh_directory as'd:\\';
impdp aaa/aaa@orcl directory=aaa dumpfile=aaa.dmp schemas=aaa;

–附1 解决使用oracle11g无法导出(exp/imp)空表的问题
–在图形工具中，如sqldeveloper,pl/sqldeveloper用以下这句查找空表

select 'alter table '||table_name||' allocate extent;' 
from user_tables 
where (num_rows=0 or num_rows is null) and partitioned='NO';

–把查询结果导出，就是把语句复制出来单独运行

–附：

exp user_name/password@orcl log=d:\\ log file=e:\\ddd.dmp

–授权

grant connect to npm_aaa

—表
—导入导出

---导出表(exp)

    exp aaa/aaa@orcl file=d:\aaaa.dmp tables=(table1,tabloe2)log=d:\aaaa.log

-- linux

    exp aaa/aaaa@aaaa file=/aaaa.dmp tables=aaaa:p201501,aaaa:p201506

-- window
exp aaa/aaa@orcl file=d:\aaa.dmp tables=aaa query=""" where a>20 """  
---导入表(imp)
imp aaa/aaa@orcl file=d:\aaa.dmp log=d:\aaa.log fromuser=aaa touser=aaaaignore=yes
---导出表(exp)
create directory kh_directoryas'd:\\';
expdp aaa/aaa@orcl directory=dpdata schemas=aaa dumpfile=a.dmp log=a.log
---导入表(imp)
create directory kh_directoryas'd:\\';
impdp aaa/aaa@orcl directory=dpdata dumpfile=aaa.dmp schemas=aaa;

—分区、索引

---分区
partition by range (month_key)
(
  partition p_1m_201710 values less than (201711),
  partition p_1m_201711 values less than (201712)
)
---天分区
create table a_ft_user_keepcell_dy
(
  day_key integer
)
partition by range (day_key)
(
  partition p_1d_20170904 values less than (20170905)
);
---小时区
create table a_ft_user_keepcell_h
(
  hour_key integer
)
partition by range (hour_key)
(
  partition p_1h_2017090401 values less than (2017090402)
);
---小时天分区
partition by range (hour_key)
(
  partition p_1h_20180201 values less than (2018020200)
);

—索引

create bitmap index cccc on aaa (CELL_KEY) nologging local;

—删除分区数据

alter table aaa truncate partition p_1m_201710;

—修改
—表名

alter table aaa rename to kh_tac;

—添加字段

alter table aaa add column bbb varchar(16);
alter table aaa add column bbb varchar(16) after kpi_zhname;

—修改

alter table aaa rename column table_name to a;
alter table aaa modify a varchar2(50);

–删除

alter table aaa drop column a;

–重新统计user_tables等系统表

analyze table aaa compute statistics;

UPDATE
update a set a1=(select b1 from b where a.id=b.id)
update a set (a1,a2,a3)=(select b1,b2,b3 from b where a.id=b.id)

----表分析
----优化查询
—表

analyze table aaa compute statistics;
select'analyze table '||table_name||' compute statistics;'from user_tables;

—分区

analyze table kh partition(p1) compute statistics ;
select 'analyze table '||table_name||' partition('||partition_name||') compute statistics;' 
from user_tab_partitions
where table_name=upper('kh');

–

select table_name,num_rows from user_tables;

—释放空间
–表

alter table dw_dm_co_tac2 move;
select'alter table '||table_name||' move;'from user_tables where partitioned='NO';

–分区

alter table kh move partition p1;
select 'alter table '||table_name||' move partition '||partition_name||';' 
from user_tab_partitions
where table_name=upper('kh');

–外部表

create table test
(
  id varchar2(100)
)
organization external
(
  type oracle_loader
  default directory kh_directory
  access parameters
  (
    records delimited by'\n'
    fields terminated by','
    missing field values are null
    (id)
  )
  location (kh_directory:'1.txt','2.txt')
)
reject limit unlimited;

–查看表结构

select table_name,column_name,data_type||'('||data_length||')'
from user_tab_columns
--where table_name=upper('aaa') 
where column_name like '%IP%'
order by table_name,column_id

–授权

grant select on sys_table_log to npm_aaa;
grant insert on sys_table_log to npm_aaa;

–数据
–导入导出

sql loader

–命令

sqlldr userid=aaa/aaaa@orcl control=d:\a.ctl log=d:\a.log bad=d:\a.bad direct=true readsize=4194304

–控制文件

options(skip=0)     					--标题(从第几行开始)没有填0
load data
characterset utf8     					--字符集
infile  'd:\b.txt'      					--文件
truncate          					--清除原文件不清楚append
into table student      				--表名
fields terminated by ','    				--分隔符
optionally enclosed by '"'  				--字符串加"
trailing nullcols
(
  id "replace(:id,'NULL',null)",      	-- 替换空字符串为空
  a "regexp_replace(:a,'\\D','')"   	-- 转化掉非数值字符
  b "f_division(:a,10)"         		-- 自定义函数
  name char(1024)         		-- 当大于256时需要指定长度
  time sysdate          			-- 当前时间
)

–附1 解决使用oracle11g无法导出(exp/imp)空表的问题
–在图形工具中，如sqldeveloper,pl/sqldeveloper用以下这句查找空表

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null;

–把查询结果导出，就是把语句复制出来单独运行
–spool
–命令
sqlplus -s aaa/aaa@orcl @d:\a.spl

–控制文件

set echo off                     -- 显示start启动的脚本中的每个sql命令，缺省为on 
set colsep ,                     -- 分隔符，但不稳定
set newp none                    -- 
set term off                     -- 不在屏幕上显示  
set feedback off                 -- 不显示sql查询或修改行数 
set heading off 　　             -- 输出域标题，缺省为on 
set termout off                  -- 显示脚本中的命令的执行结果，缺省为on 
set linesize 4096                -- 行宽
set pagesize 50000               -- 输出每页行数，缺省为24,为了避免分页，可设定为0
set trimspool on
spool d:\o_se_ur_gtpv2.csv
select cdr_type||','||time from aaa;
spool off
exit

–附1生成|隔开，两处表名

select 'select '||replace(wm_concat(lower(column_name)),',','||''|''||')||' from aaa;'
from
(
  select column_name
  from user_tab_columns 
  where table_name=upper('aaa')
  order by column_id 
)

–附2 生成,隔开，字符串加’，两处表名

select'select '||replace(wm_concat(lower(column_name)),',','||'',''||')||' from aaa;'
from
(
  select case when data_type not like'%CHAR%'then column_name else'''''''''||'||column_name||'||'''''''''end as column_name
  from user_tab_columns 
  where table_name=upper('aaa')
  order by column_id 
)

—附3 生成,隔开，字符串加”，两处表名

select'select '||replace(wm_concat(lower(column_name)),',','||'',''||')||' from aaa;'
from
(
  select case when data_type not like '%CHAR%'then column_name else''''||'"'||''''||'||'||column_name||'||'||''''||'"'||''''end as column_name
  from user_tab_columns 
  where table_name=upper('aaa')
  order by column_id 
)

–插入多条

insert all
into kh_user values(11,11,22)
into kh_user values(11,11,22)
select 1 from dual;
--Oracle导入Hive
sqoop import    \
--connect "jdbc:oracle:thin:@10.111.111.111:1111/aaa"    \
--username npm_tele    \
--password My_678\#   \
--hive-import    \
--hive-table wangyun.npm_tele_dw_dm_re_eutrancell    \
--target-dir /wangyun/inspur/tmp    \
--delete-target-dir    \
--fields-terminated-by '|'    \
--query 'SELECT 201711,to_char(EUTRANCELL_KEY) EUTRANCELL_KEY,TAC,CI,EUTRANCELL_ID,EUTRANCELL_ENAME,EUTRANCELL_ZHNAME,ENODEB_KEY,ENODEB_ENAME,ENODEB_ZHNAME,COUNTRY_KEY,COUNTRY_NAME,CITY_KEY,CITY_NAME,PROVINCE_KEY,PROVINCE_NAME,VENDOR_KEY,VENDOR_NAME,COVERTYPE_KEY,COVERTYPE_NAME,COVERFLAG_KEY,COVERFLAG_NAME,NETTYPE_KEY,LONGITUDE,LATITUDE,AZIMUTH,STATUS,START_TIME,END_TIME,TIME_STAMP,ECGI FROM DW_DM_RE_EUTRANCELL_1 WHERE $CONDITIONS'  \
-m 1

–数据库启动
–启动监听

lsntctl start;

–数据库启动

startup nomount;
   alter database mount;
   alter database open;

–指定spfile启动数据库。

startup pfile='G:\\aaaa.ORA';

–创建spfile根据pfile

create spfile from pfile='G:\\aaa.ORA';

–关闭数据库

shutdown immediate;
shutdown abort;

–字符集
–查看服务端字符集

select userenv('language') from dual;

– 修改客户端字符集
regedit修改key NLS_LANG=“需要的字符集”
–数据库归档
–1查看数据库归档

archive log list;

–2 数据库归档模式切换

shutdown;
startup mount;
alter database archivelog|noarchivelog;
alter database open;

–3 修改自动归档

alter system archive log start|stop;

–4 修改归档文件大小

alter system set db_recovery_file_dest_size=16g scope=both;

– 修改归档文件路径

 alter system set db_recovery_file_dest='/data/archive' scope=both sid='psmsg';

–6 删除截止到前一天归档

delete archivelog until time 'sysdate-1';

–7 删除过期归档

crosscheck archivelog all;
delete expired archivelog all;

–控制文件
–http://www.xifenfei.com/2347.html
–查看flash_recovery area使用情况

select * from V$FLASH_RECOVERY_AREA_USAGE;

–登录rman模式

rman target sys/pw@sid;

–三其他函数
–3.1字符串函数
to_char
–填充0

select to_char(05,'FM09')from dual

replace translate

–字符串替换 replace
–字符替换 translate

select to_char(12, 'FM9990999.9') from dual;
select to_char(123.456, 'FM9990.099') from dual;
select translate('11.22.11.4','.1234','.') from dual;
trim
select trim('   t e c h   ') from dual; 
select trim(' 'from  '  t e c h ') from dual;
select trim(both '0' from '00100200300') from dual;--两头
select ltrim('00012030','0') from dual;
select trim(leading '0' from '00012030')  from dual; --前置
select rtrim('00100200300','0') from dual;
select trim(trailing '0' from '00100200300') from dual; --后置

lpad、rpad
–截取字符串
– 1025-2015

select substr(a,1,instr(a,'-')-1),substr(a,instr(a,'-')+1,100),a
from (select'1025-2015' a from dual)

–中文

select * from dw_dm_co_tactype where length(tactype_name)=lengthb(tactype_name);

–正则表达式
–1 查找

select * from dw_dm_co_tac where regexp_like(tactype_zhname,'\s')
select case when regexp_like('asdf','AS','i') then 1 else 0 end from dual;

–2 替换

select tactype_zhname,regexp_replace(tactype_zhname,'\W','') from dw_dm_co_tac

–修改科学计数法

select ap_nasid,
regexp_replace(ap_nasid,'(.*)E(\d+)','\1')*power(10,regexp_replace(ap_nasid,'(.*)E(\d+)','\2'))
from dw_dm_wlan_hot 
where ap_nasid like '%E%'

–3 截取

select regexp_substr('1.2.3.4','\d+\.\d+\.\d+') from dual;

–拆分

select regexp_substr('1.2.3.4','\d+',1,2) from dual;

update kh_sp set sp_key=regexp_substr(sp_ip,'[^.]+',1,1)*1000000000+regexp_substr(sp_ip,'[^.]+',1,2)*1000000+regexp_substr(sp_ip,'[^.]+',1,3)*1000+regexp_substr(sp_ip,'[^.]+',1,4)

–3.2 时间函数
–UTC转字符

select to_char(to_date(19700101,'yyyymmdd')+1449718739/86400,'yyyy-mm-dd hh24:mi:ss') from dual;

–时间转UTC

select round(sysdate-to_date('19700101','yyyymmdd'))*24*60*60 from dual;

–毫秒

select to_char(to_timestamp('20160514093027971','yyyymmddhh24missff'),'yyyy-mm-dd hh24:mi:ss.ff')from dual;

–毫秒3位数字

select to_char(to_timestamp('20160514093027971','yyyymmddhh24missff'),'yyyy-mm-dd hh24:mi:ss.ff3')from dual;

–获取当月天数

select last_day(sysdate)-last_day(add_months(sysdate,-1))from dual;

–获取上一月

select to_char(add_months(trunc(to_date(20160930,'yyyymmdd')),-1),'yyyymm')from dual;

–判断是否周末、周几

select to_char(sysdate,'d') from dual where to_char(sysdate,'d') not n(1,7);

–取消补0

select to_char(to_date(20170211,'yyyymmdd'),'FMyyyy-mm-dd')from dual;

–微妙时间差

round((to_date(trunc(procedure_end_time/1000),'yyyymmddhh24miss')-to_date(trunc(procedure_start_time/1000),'yyyymmddhh24miss')+(mod(procedure_end_time,1000)/1000-mod(procedure_start_time,1000)/1000)/86400)*86400,-1)

– 获取上周日

select to_char(trunc(to_date(20190212,'yyyymmdd'), 'd'),'yyyyMMdd') from dual;

– 获取这周一

select to_char(trunc(to_date(20190211,'yyyymmdd'), 'iw'),'yyyyMMdd') from dual;

–3.3 截断函数

round(a,b)
trunc(a,b) trunc(time,'hh24')

–b小数点数
round --四舍五入
trunc --截断

–3.4 随机函数

trunc(dbms_random.value(0,10))  ---0-9之间随机数
update dw_ft_userl3_mn set country_key=
(
  case trunc(dbms_random.value(1,12))
    when 1 then 1
    when 2 then 2
    when 3 then 3
    when 4 then 16
    when 5 then 17
    when 6 then 27
    when 7 then 34
    when 8 then 35
    when 9 then 48
    when 10 then 82
    when 11 then 83
  end
)

–3.5 开窗函数
–开窗函数，与组函数不同的是，返回多个行

select id,class,name,score,
avg(score)over(partition by substr(id,1,4)) "年级平均成绩",
avg(score)over(partition by substr(id,1,6)) "班平均成绩"
from student order by id;

select id,class,name,score,
sum(score)over(partition by substr(id,1,4) order by id) "年级累计成绩"
from student order by id;

– 并列

select id,score,row_number()over(order by score desc) from student

–row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开始排序）
–rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
–dense_rank()也是连续排序，有两个第二名时仍然跟着第三名
–3.6 递归

select * from kh_area
start with area_name='山东省'
connect by priorarea_id=parent_id

–3.7 行变纵、分组合并
–按年级列出姓名

select substr(id,1,4),wm_concat(name)
from student group by substr(id,1,4);

–查询所有姓名

select replace(wm_concat(name),',',' ') from student;

–3.8 去重
– 双in
– 3处表名 4出字段名

select * from dw_dm_co_city where city_key in
(select city_key from dw_dm_co_city group by city_key having count(*)>1)
and rowid not in
(select min(rowid) from dw_dm_co_city group by city_key having count(*)>1)

–in+外联in的内表可以建临时表，下同
– 3处表名 5处字段名

delete from dw_dm_co_city where rowid in
(
  select t1.rowid from dw_dm_co_city t1 left join
  (
    select city_key,min(rowid) B from dw_dm_co_city
    group by city_key having count(*)>1
  )t2 on t1.city_key=t2.city_key
  where t2.city_key is not null
  and t1.rowid>t2.B
)

– in+over
– 2处表名 1处去重字段 1处排序字段

delete from dw_dm_co_tac where rowid in
(
  select rowid as row_number from
  (
    select rowid as row_number,row_number()over(partition by tac_8 order by tac_8)as row_count 
    from dw_dm_co_tac
  )
  where row_count>1
)

– in+over
– 双字段去重 2处表名 1处去重字段 1处排序字段

delete from aaa where rowid in
(
  select rowid as row_number from
  (
    select rowid as row_number,
    row_number()over(partition by city_key,month_key order by aaa_n desc) as row_count 
    from dw_dc_aaa_user_n_m
  )
  where row_count>1
)

–in+外联(多字段)
– 3处表名 5处字段名

delete from dw_dm_co_tac where rowid in
(
  select t1.rowid from dw_dm_co_tac t1 left join
  (
    select tac_8,tactype_key,min(rowid) b from dw_dm_co_tac 
    group by tac_8,tactype_key having count(*)>1
  ) t2 on t1.tac_8=t2.tac_8 and t1.tactype_key=t2.tactype_key
  where t2.tac_8 is not null
  and t2.tactype_key is not null
  and t1.rowid>t2.b
)

—3.9分母为0

decode(coalesce(分母,0),0,0,分子/分母)

—3.10空
–1 coalesce
–返回第一个非空的

select coalesce(null,null,3)from dual;

–2 判断条件
– 与空计算都是空结果是一个空值
select 0+null from dual;
– 空值等号则该条件为假

select case when 1=null then 1 else 0 end from dw_dm_co_city;-- 0
select * from dw_dm_co_city where 1=null;-- 是没有结果，不是空值
select * from dw_dm_co_city where f(a)!=1;-- 若为空则作废
select * from dw_dm_co_city where 1=0;-- 是没有结果，不是空值
select * from dw_dm_co_city where 1=1 or 1=null;-- 有
select * from dw_dm_co_city where 1=1 and 1=null;-- 没有结果

–内容为空

select * from dw_dm_co_city where city_key=1;-- 空值不参与
select * from dw_dm_co_city where city_key!=1;-- 空值不参与
select * from dw_dm_co_city where coalesce(city_key,0)!=1-- 考虑空值的情况

–3.11 最大最小
greatest
least
–自定义函数
– 随机函数

create or replace function random
return float is
begin
return dbms_random.value(0,1);
end;

– 除法函数

create or replace function f_division
(numerator in float,
denominator in float,
len integer default 4)
return float
is
  var1 float;
  var2 float;
  res float;
  var3 integer;
begin
  var1 :=numerator;
  var2 :=denominator;
  var3 :=len;
  if var1 is null or var2 is null then
    return 0;
  end if;
  if var2 =0 or var1 =0then
    res :=0;
  else
    res:=round(numerator/denominator,var3);
end if;
return res;
end;

– ipv6

create or replace function f_ipv6
(
  iip in varchar
)
return varchar
is
ip varchar2(128);
begin
  ip:=regexp_replace(iip,'^:','0:');
  ip:=regexp_replace(ip,':$',':0');
  
  ip:=
lpad(regexp_substr(replace(ip,'::',lpad('0:',2*(7-length(regexp_replace(ip,'\w','')))+3,':0')),'\w+',1,1),4,'0')||':'||
lpad(regexp_substr(replace(ip,'::',lpad('0:',2*(7-length(regexp_replace(ip,'\w','')))+3,':0')),'\w+',1,2),4,'0')||':'||
lpad(regexp_substr(replace(ip,'::',lpad('0:',2*(7-length(regexp_replace(ip,'\w','')))+3,':0')),'\w+',1,3),4,'0')||':'||
lpad(regexp_substr(replace(ip,'::',lpad('0:',2*(7-length(regexp_replace(ip,'\w','')))+3,':0')),'\w+',1,4),4,'0')||':'||
lpad(regexp_substr(replace(ip,'::',lpad('0:',2*(7-length(regexp_replace(ip,'\w','')))+3,':0')),'\w+',1,5),4,'0')||':'||
lpad(regexp_substr(replace(ip,'::',lpad('0:',2*(7-length(regexp_replace(ip,'\w','')))+3,':0')),'\w+',1,6),4,'0')||':'||
lpad(regexp_substr(replace(ip,'::',lpad('0:',2*(7-length(regexp_replace(ip,'\w','')))+3,':0')),'\w+',1,7),4,'0')||':'||
lpad(regexp_substr(replace(ip,'::',lpad('0:',2*(7-length(regexp_replace(ip,'\w','')))+3,':0')),'\w+',1,8),4,'0');

return ip;
end;

–四造数
–4.1 维度关联造数
–1.1全量关联，即笛卡尔积
select * from kh1 a,kh2 b
–1.2 左全量，右随机
–1.2.1 一对一，右随机且不为空
–1 标准模式

select
a.a||'-'||b.a
from (select a,round(dbms_random.value(0.5,(select count(*)+0.5from kh2)))as n from kh1) a
inner join(select a,rownum as n from kh2) b 
on a.n=b.n
order by a.a

–拆分模式
–1

create table aaa1 as
select a,round( dbms_random.value(0.5,(select count(*)+0.5 from kh2)))as n from kh1;

–2

create table aaa2 as
select a,rownum as n from kh2;

–3

select a.a||'-'||b.a
from aaa1 a,aaa2 b
where a.n=b.n
order by a.a

–更新模式

select * from dw_ft_cu_tr_usercell_mn;
create table aaa as
select row_number()over(orderby tac_8 )as row_id,tac_8 from dw_dm_co_tac;
update dw_ft_cu_tr_usercell_mn set imei=round(dbms_random.value(1,443965))
update dw_ft_cu_tr_usercell_mn a set imei=(select tac_8 from aaa b where a.imei=b.row_id);

– 一对一，右随机可为空
– 约50%关联不上

select
a.a||'-'||b.a
from(
  select a,
  round(dbms_random.value(0.5,(select round(count(*)*2)+0.5 from kh2))) as n 
  from kh1
) a
  left join(select a,rownum as n from kh2) b on a.n=b.n
order by a.a

– 一对多，右随机且可为空

select
a.a||'-'||b.a
from(select a,round( dbms_random.value(0.5,(select round(count(*)*2)+0.5 from kh2)))as n from kh1) a
left join(select a,rownum as n from kh2) b 
on mod(a.n*b.n+a.n+b.n,round(dbms_random.value(19,31)))=1
order by a.a

– 一对多，右随机且不可为空
– 存储过程

begin
  for i in 1..22 loop
    insert into aaa

    select
    'diameter'                             as cdr_type,
    round(dbms_random.value(0,458))        as duration,
    round(dbms_random.value(460000000000000,460009999999999))
                                           as imsi,
    round(dbms_random.value(11000000000,19999999999))
                                           as msisdn,
    round(dbms_random.value(0,999999999999999))
                                           as imei,
    460                                    as home_mcc,
    lpad(round(dbms_random.value(0,3)),2,0)as home_mnc,
    b.tac                                  as tac,

    from
    (
      select
      round(dbms_random.value(7300,9900))  as rand_a,
      round(dbms_random.value(19,23))      as rand_b
      from dual
    ) a
      left join dw_dm_co_tac b on mod(b.tac,a.rand_a)=1
      left join aaa c on mod(c.mno_map_key,a.rand_b)=1;

  end loop;
end;

– 加权模拟

select 
a.imsi,
a.imei,
a.msisdn,
b.city_key,
b.country_key,
from kh_user a,kh_city b
where round((b.num_n+2000)*rand())>1000
and a.row_id=b.row_id

–五存储过程
–5.1创建

create or replace procedure myproc(in_p in varchar2,out_p out varchar2)as
temp varchar2(100);
begin
insert into aaa values(sysdate);
commit;
end;

–5.2 执行
–1 pl/sql

begin
  area;
end;

declare
  a varchar2(2222);
  b varchar2(2222);
  c varchar2(2222);
begin
  for i in 2019032801..2019032803 loop
    a:=to_char(i);
    delete from dw_gsm_cell_pm_h where serv_tim=to_date(a,'yyyy-mm-dd hh24:mi:ss') and vendor='HW';
    commit;
    wlzy_gsm.p_hw_bsc_cell(a,b,c);
    commit;
  end loop;
end;

–2 sqlplus

exec area;

–5.3 授权
–存储过程默认不支持访问其他用户的表

grant select any table to swxt with admin option;

–http://blog.csdn.net/liangyike/article/details/7534299/
–六JOB
–6.1 创建
–1pl/sql
–what: myproc;
–下一个日期： 2016/10/15 11:30:00
–间隔: trunc(sysdate,‘mi’)+5/(24*60)-- 不要用sysdate+，有可能延后
–2sqlplus

variable job number;
begin
   dbms_job.submit(:job,'myproc;',sysdate,'sysdate+1/24/60');
commit;
end;

/
–附：
–1:每分钟执行
Interval => TRUNC(sysdate,‘mi’) + 1/ (24*60)
–2:每天定时执行
–例如：每天的凌晨1点执行
Interval => TRUNC(sysdate) + 1 +1/ (24)
–3:每周定时执行
–例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,‘星期一’))+1/24
–4:每月定时执行
–例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
–5:每季度定时执行
–例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),‘Q’) + 1/24
–6:每半年定时执行
–例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,‘yyyy’),6)+1/24
–7:每年定时执行
–例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,‘yyyy’),12)+1/24
–6.2 删除

select * from user_jobs;
begin
  dbms_job.remove(24);
commit;
end;

–七序列
–7.1 创建

create sequence squ_basic_sesid
minvalue 100000000000000000
maxvalue 999999999999999999
start with 100000000000000000
increment by 1;

–7.2 使用

select seqtest.currval from dual;-- 不增加
select seqtest.nextval from dual;-- 增加

–八过程
–8.1 时间维度表生成

declare
 d_date date;
begin
  d_date:=to_date(20160101,'yyyymmdd');
while d_date<=to_date(20181231,'yyyymmdd')loop
insert into re_day
select
    d_date                          as day_d,
    to_char(d_date,'yyyy/mm/dd')as date_s,
case when to_char(d_date,'d')=7or to_char(d_date,'d')=1 then 1 else 0 end
as is_weekend
from dual;
    d_date:=d_date+1;
end loop;
end;

declare
 i number;
begin
for i in1..10000 loop
if mod(i,3)=2 and mod(i,5)=4 and mod(i,7)=6 and mod(i,9)=8 and mod(i,11)=0 then
    dbms_output.put_line(i);
end if;
end loop;
end;

标签

#oracle #系统表