常用SQL代码

--查询存储过程报错情况
SELECT * FROM  RPT_ETL_LOG WHERE ERR_CODE <> 0 ORDER BY STOP_TM DESC   
SELECT * FROM bpbw_dz WHERE BP_TAB_NM =''  

SELECT * FROM ALL_SOURCE WHERE TEXT LIKE'%%';

--查看近7天内停掉的JOB任务
SELECT T.* FROM USER_SCHEDULER_JOBS T WHERE T.STATE='DISABLED' AND  
TRUNC(LAST_START_DATE)>SYSDATE-7

-- 查看JOB报错
select l.job_name,j.job_action,d.ERRORS job报错信息,l.log_date from user_scheduler_job_log l 
left join user_scheduler_jobs j on j.job_name=l.JOB_NAME
left join user_scheduler_job_run_details d on d.log_id=l.log_id
where l.STATUS ='FAILED' and l.log_date>sysdate-1;

--查看哪个JOB调用了哪个存储过程
select * from user_scheduler_jobs;

-- 查看JOB运行时长
SELECT round(to_number(stop_tm-start_tm)*24*60) diff,t.*  FROM  RPT_ETL_LOG t
 WHERE round(to_number(stop_tm-start_tm)*24*60) >5
 and err_code =0 ORDER BY STOP_TM DESC

--查看表空间文件路径
select * from dba_data_files where TABLESPACE_NAME ='TS_DRILLING'
--扩充表空间
ALTER DATABASE DATAFILE '+DATA/JHEPDB/DATAFILE/ts_drilling.dbf' RESIZE 30G;
--新增数据文件
ALTER TABLESPACE TS_DRILLING
ADD DATAFILE '+DATA_DG/JHBW/DATAFILE/zj/ts_drilling_3.dbf'
SIZE 32767M;

--查看表空间大小
select a.tablespace_name,
a.bytes / 1024 / 1024 "Sum MB",
(a.bytes - b.bytes) / 1024 / 1024 "used MB",
b.bytes / 1024 / 1024 "free MB",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes) desc;

--1.密码过期检查
select username,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE from dba_users where expiry_date <to_date('2023-1-3','yyyy-mm-dd')
and ACCOUNT_STATUS ='OPEN' order by EXPIRY_DATE desc;
--2.和密码本比较，我们有的账号，登录看账号密码是否正确
--3.修改账户为原始密码
alter user SANRUI_CEJING  identified by "SANRUI_cejing";

ALTER USER SCOTT account UNLOCK

工作内容：
一、日常巡检
1、对各自负责的服务器进行每周3次的进程巡检，将服务器进程情况填写到《1-XX油田数据库服务器巡检记录_模板.xlsx》。
执行脚本参见：《1-油田数据库服务器巡检记录_脚本.sql》。
2、“进程占用%”和“会话占用%”超过80%时，导出《2-XX油田数据库服务器进程查杀记录_模板.xlsx》，
按照“时长(h)”从长到短依次执行“完成当前事务并终止session”列查杀”非活动”的进程。
执行脚本参见：《2-油田数据库服务器进程清理记录_脚本.sql》。
3、如果数据组使用PLSQL无法杀掉进程，应将导出的《2-XX油田数据库服务器进程查杀记录_模板.xlsx》
及时发送给系统组，由系统组在服务器端执行“无法终止时系统查杀”列进行查杀。
4、“非活动数”占用比例过高时，应及时反馈给开发组进行排查处理。
二、阻塞进程查杀
1、日常巡检到存在阻塞进程，或用户反馈系统速度变慢时应及时排查数据库是否存在阻塞进程，
导出《2-XX油田数据库服务器进程查杀记录_模板.xlsx》及时反馈至对应油田数据岗负责人员；
2、数据岗负责人确认后，按照“时长(h)”从长到短依次执行“完成当前事务并终止session”列查杀”阻塞”的进程。
执行脚本参见：《3-油田数据库服务器进进程阻塞记录_脚本.sql》
3、如果数据组使用PLSQL无法杀掉进程，应将导出的《2-XX油田数据库服务器进程查杀记录_模板.xlsx》
及时发送给系统组，由系统组在服务器端执行“无法终止时系统查杀”列进行查杀。
三、其他配合工作
必要时配合系统组确定导致阻塞的SQL、过程、视图，以及所属专业或应用。
----------------------------------------------------------------------------------------------------------------------------------
查看登录用户的IP:
select logon_time,username,client_info,machine from v$session where username='SR_SCXXH' order by logon_time desc

--创建表空间：
SELECT * from Dba_Data_Files  --查询表空间文件
create tablespace ts_app datafile '+DATA_DG/JHBW/DATAFILE/ts_app.dbf' size 2G; -- 创建表空间

--当前时间，只取年月日
select TRUNC(SYSDATE) from dual
select to_char(TRUNC(SYSDATE),'yyyy-mm-dd') from dual --转换成字符串格式

--OracleMD5加密
select  utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => '123456')) from dual 
结果：E10ADC3949BA59ABBE56E057F20F883E

--mysql查所有字段及注释
select * from information_schema.`COLUMNS` c 
where COLUMN_NAME ='SGDWDM'

--DBID的取值插入数据
--用SYS_GUID()插入数据，随机取32位的不重复字符

--传出参数接收并输出到控制台（游标接收）
DECLARE
--TYPE IQUERY_CURSOR IS REF CURSOR;
  OU INT;
BEGIN
  PKG_MUD_IQUERY.P_GET_ZHLJ_SHXX_ROWCOUNT('JH2014102701',OU);
  DBMS_OUTPUT.PUTLINE(OU);
END;

--查数据库表全对象：
select * from all_objects where OBJECT_NAME='YS_DHB07' and owner='SL_NYGK_YT'

--EXCEL序号自动往下填充：
选择需要填充的列，输入填充函数=row(a1)，按下组合键ctrl+enter即可。

--修改字段数据类型的方法，例：需要修改字段a的数据类型从number(5,2)修改成varchar2(10),
1、添加一个字段b varchar2(10)
2、将需要修改数据类型的字段a的数据复制到字段b（不要用update，要直接复制）
3、将字段a的数据删除
4、修改字段a的数据类型
5、将字段b的数据复制到字段a，此时已完成字段a的数据类型的修改
6、删除字段b

--同步删除SQL
--方法一：
DELETE FROM ODS_YQSCSJ_YQJTCJL@EPBW  O WHERE NOT EXISTS (SELECT 1
          FROM YQSCSJ_YQJTCJL L WHERE O.JHDM= L.JHDM AND O.TCSJB= L.TCSJB ); 
--方法二：
DELETE FROM ODS_YQSCSJ_YQJTCJL@EPBW  O WHERE (JHDM,TCSJB) NOT IN  (SELECT JHDM,TCSJB
          FROM YQSCSJ_YQJTCJL L); 
--以后数据库建ETL，bp,bw尽量全表做同步删除这两种写法都试下，那个效率高用那个，一般下面效率高些，不然后期BW多数据都是麻烦

--查看表给某个用户的权限
--PLSQL中按住CTRL点表名，点击右下角View SQL，可以看到DDL语句最下面是授权语句。可以查看表对用户授予的权限。

--查看用户对表的授权情况：
SELECT * FROM ALL_TAB_PRIVS WHERE TABLE_NAME='SYS_USER_LOGIN'

--添加授权与同义词：
grant select,update,delete,insert on SRC_HW_KTKF_DYYCCS to HW_YCGL;   （这里授权给汉威的）
--创建公有同义词
create public synonym SRC_KFYDSJ_MAP_DYDM for epbw_jh.SRC_KFYDSJ_MAP_DYDM; 

--表授权语句（江汉工程报表用户）
grant select on STG_HYFX_YSRJ to JH_GCBB;
--创建私有同义词
create synonym JH_GCBB.STG_HYFX_YSRJ for EPBW_JH.STG_HYFX_YSRJ; 

--查询所有同义词
select * from user_synonyms where TABLE_NAME='STG_HYFX_YSBP_SXYSJHZ' OR TABLE_NAME='STG_HYFX_YSBP_TSYSJHZ';
----查询所有公有同义词
select * from DBA_SYNONYMS
--删除私有同义词
DROP SYNONYM STG_HYFX_YSBP_SXYSJHZ; --删除名为STG_HYFX_YSBP_SXYSJHZ的私有同义词
--删除公有同义词
DROP PUBLIC SYNONYM EMC_FGS_DP_SCYXDT;--删除名为EMC_FGS_DP_SCYXDT的公有同义词

--数据服务相关查询代码：
SELECT * FROM bpbw_dz WHERE BP_TAB_NM =''  

SELECT * FROM ALL_SOURCE WHERE TEXT LIKE'%%';

SELECT * FROM ALL_TAB_COMMENTS WHERE COMMENTS LIKE '%%';

SELECT * FROM ALL_COL_COMMENTS WHERE COMMENTS LIKE '%%';

--查询某字段含有%的数据（两种方法）
select * from  CJCGZL_YWCGCJCGQX where CJQXMCDM like '%\%%' escape'\'; --转义字符
select CJQXMCDM,instr(CJQXMCDM,'%') from  CJCGZL_YWCGCJCGQX where instr(CJQXMCDM,'%')>0;

--数据服务自定义SQL时（注意 /*{DBID:主键}*/一定是大写，不然注释出不来）
SELECT 
DBID, /*{DBID:主键}*/
DB_NM, /*{DB_NM:数据库名称}*/
DB_PORT, /*{DB_PORT:数据库端口}*/
DB_IP /*{DB_IP:数据库ip}*/
FROM DB_INFO   这样的格式，注释会自动带到参数里面

--查看EPBW数据库30天内的临时表
select * from all_objects where  OWNER ='EPBW_JH' AND object_name like 'LS_%' AND OBJECT_TYPE ='TABLE' AND CREATED <SYSDATE -30;

--查询所有约束条件，约束键
select * from user_constraints

--联合更新1
update   ODS_ZJ_CXJL  a set 
  cdzzbx =(select  max(x)   from  LS_ODS_ZJ_CXJL_GXH_20220902 b
   where a.jhdm=b.jm and a.js=b.js and  a.fwj=b.fw and a.jxj=b.jx ),
cdhzby  =(select  max(y)    from  LS_ODS_ZJ_CXJL_GXH_20220902 b 
where a.jhdm=b.jm and a.js=b.js and  a.fwj=b.fw and a.jxj=b.jx) 
where  (jhdm ,js, jxj ,fwj ) in (select jm ,js, jx, fw  from LS_ODS_ZJ_CXJL_GXH_20220902 ) 
and ( a.cdzzbx is  null or a.cdhzby is  null) 
--联合更新2
update YQSCSJ_CYXJSJ kc set kc.yy=(select ls.yy from ls_st_20230215 ls where ls.jhdm=kc.jhdm and to_date(substr(ls.rq,1,10),'yyyy/mm/dd')=kc.jlsj),
kc.ty=(select ls.ty from ls_st_20230215 ls where ls.jhdm=kc.jhdm and to_date(substr(ls.rq,1,10),'yyyy/mm/dd')=kc.jlsj),
kc.hy=(select ls.hy from ls_st_20230215 ls where ls.jhdm=kc.jhdm and to_date(substr(ls.rq,1,10),'yyyy/mm/dd')=kc.jlsj),
kc.xgsj=sysdate
where (kc.jhdm,kc.jlsj) in (select ls.jhdm,to_date(substr(ls.rq,1,10),'yyyy/mm/dd') from ls_st_20230215 ls)

--查看表创建时间（CREATED为创建时间，LAST_DDL_TIME为最后修改时间）
select * from user_objects where OBJECT_NAME='STG_HYFX_YSBPJD_TSY' --表名

--v$access视图显示数据库中当前锁定的对象及访问它们的会话
select * from v$access where object='PKG_ETL_SRC'

--锁表解锁
--查询
select a.object_name,b.session_id,c.serial#,c.program,c.username,c.command,c.machine,c.lockwait
from all_objects a,v$locked_object b,v$session c where a.object_id=b.object_id and c.sid=b.session_id;
--解锁
alter system kill session '5195,63317';

--终止正在运行的SQL（可以找出哪些SQL在运行，查询太慢且无法点中止的时候用，得另外开一个plsql程序kill进程）：
1、查SID,SERIAL#
select b.sid,b.username,b.serial#,a.spid,b.paddr,c.sql_text,b.machine from v$process a,v$session b,v$sqlarea c
where a.addr=b.PADDR and b.sql_hash_value=c.hash_value --and SQL_TEXT like '%%'
2、KILL进程
alter system kill session '3778,49340'

--ORA-04021:等待锁定对象时发生超时
--1.查SID,SERIAL#
select b.serial#,a.*
from v$session_wait a,v$session b
where a.sid=b.SID;
--2.KILL进程
alter SYSTEM KILL SESSION '110,23002';  --'SID,SERIAL#'

--强制停止正在运行的JOB（如果ORACLE包里的存储过程有JOB正在执行，这个包就不能编译，编译时会卡死，就用停job的语句之后再编译包）
--（只是停这一次，不影响JOB下次计划自动运行，通过sid杀进程只是杀会话，不能停job，停job只能用以下语句）
BEGIN
DBMS_SCHEDULER.STOP_JOB('JOB_LOAD_SRC_YCGL_DJYGSJ',FORCE=>TRUE);
END;

--无法在源表中获得一组稳定的行:
源表关联的字段数值不唯一导致的

--查询所有的JOB（可以看到job当前的state状态,runing是该job正在运行）
SELECT * FROM ALL_SCHEDULER_JOBS

--查看哪个JOB调用了哪个存储过程
select * from user_scheduler_jobs where job_action like '%LOAD_RPT_GCBB_CYJZYTZ%';

--统计JOB运行时间频率
SELECT JOB_NAME,REPEAT_INTERVAL FROM 
USER_SCHEDULER_JOBS WHERE JOB_NAME LIKE '%%';

-- 查看JOB运行情况
      SELECT OWNER,
             JOB_NAME,
             STATUS,
             ACTUAL_START_DATE  --to_char(ACTUAL_START_DATE,'yyyy-MM-dd HH24:MI:SS')
        FROM DBA_SCHEDULER_JOB_RUN_DETAILS                  --DBA_SCHEDULER_JOBS查看所有JOB
       WHERE JOB_NAME = 'JOB_NAME'                                   --设定的Job Name
    ORDER BY LOG_DATE DESC;

-- 查看JOB运行ETL日志
SELECT * FROM  RPT_ETL_LOG WHERE PRG_NM ='LOAD_ODS_CYQ_SCSJ_HSFX' ORDER BY STOP_TM DESC;
select sysdate from dual;   -- 配合上条代码查看系统时间用，测试job是否运行

--配置JOB
begin
  sys.dbms_scheduler.create_job(job_name            => 'EPBP_JH.JOB_LOAD_ODS_CYQ_SBXX_LGBJCXX',
                                job_type            => 'PLSQL_BLOCK',
                                job_action          => 'BEGIN
            PKG_ETL_3.LOAD_ODS_CYQ_SBXX_LGBJCXX;
          END;',
                                start_date          => to_date('13-11-2021 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval     => 'Freq=Daily;ByHour=11,23;ByMinute=40',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '采油气设备_螺杆泵基础信息');
end;
/

-- 通过rowid去重
delete from STG_HYFX_FYKXDHSTLCD a where a.rowid in 
(select rowid  from STG_HYFX_FYKXDHSTLCD where rowid not in
(select max(rowid)  from STG_HYFX_FYKXDHSTLCD group by YPH, JHDM, JDB, YPDS, HYFXBGRQ, JCD));

--获取ORACLE所有表的建表语句
SELECT OBJECT_NAME,COMMENTS,TABLE_DDL FROM (
SELECT 
ROW_NUMBER() OVER(PARTITION BY OBJECT_NAME ORDER BY COMMENTS) XH,
U.OBJECT_NAME OBJECT_NAME,
C.COMMENTS COMMENTS,
DBMS_METADATA.GET_DDL(U.OBJECT_TYPE,U.OBJECT_NAME,'EPBW_JH') TABLE_DDL
FROM USER_OBJECTS U LEFT JOIN ALL_TAB_COMMENTS C ON U.OBJECT_NAME=C.TABLE_NAME
WHERE
U.OBJECT_TYPE IN('TABLE')
AND U.OBJECT_NAME LIKE 'ODS_%')
WHERE XH=1

--如何处理 ORA-01489: 字符串连接的结果过长:
通过将 listagg 函数替换为 xmlagg + xmlparse 来解决该问题：
listagg 函数用法：
listagg(列名, '分隔符') within group (order by 列名)
xmlagg + xmlparse 用法：
xmlagg(xmlparse(content 列名||'分隔符') order by 列名).getclobval()
注意:xmlagg + xmlparse 函数返回的 clob 类型数据即文本数据，可以直接拷贝到文本编辑器中

--PLSQL里面date'2022-9-17'格式直接表示日期格式，只能用'-'，不能用'/'或者其他的，
如果是'/'或其他符号就要用to_date()，例：to_date('2022/9/17','yyyy-mm-dd')

--使外键失效：
alter table HYFX_TYHYFXPZ_SJX disable constraint FK_TYHYFXPZ_SJX_TYHYFXPZ_SJB;
--使外键恢复：
alter table HYFX_TYHYFXPZ_SJX enable constraint FK_TYHYFXPZ_SJX_TYHYFXPZ_SJB;

-- 找出外键子表（违反完整约束条件）外键约束：子表外键从父表主键
select a.constraint_name, a.table_name, b.constraint_name,b.TABLE_NAME
from user_constraints a, user_constraints b
where a.constraint_type = 'R'
and b.constraint_type = 'P'
and a.r_constraint_name = b.constraint_name
and a.constraint_name = 'FK_HYFXSJ_TYHYFXSJLRZDK(换成自己的数据库提示的串)'

--查看表占用空间
select * from (
  select t.tablespace_name,t.owner, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mb
  from dba_segments t
  where t.segment_type='TABLE'
  group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type
) t
-- where SEGMENT_NAME='YQSQFX_YYSYXX' -- 查看某张表，不写则是查看全部表的占用空间
order by t.mb desc
;

--化验分析数据入库相关
井号代码关联ods_jcxx_djjcxx
正则表达式 匹配第一个空白:^\s?或^
正则表达式 匹配括号里的内容:(.*?)或(.*)
regexp_replace(ymjcxd,'[^0-9\.]+','') ymjcxd 正则替换，将数字及点以外的替换成空

找到所有自己创建的临时表(以后一次性删除用)：
select * from all_tables where table_name like 'TEMP_ST%' and owner = 'EPBW_JH';

井号代码如果为空则：zlj_jh  （资料井_井号）

ypds有'/'和空的处理：
decode(ypds,'/',0,null,0,ypds),
decode(ypdsa,'/',0,null,0,ypdsa)

用Navicat往Mysql数据库里的表导入CSV文件，1、选表 2、点导入向导选择CSV 3、后面基本都是下一步，然后开始。
注意：如果导入后发现乱码，则CSV文件的编码有问题，鼠标右键以管理员身份运行notepad++，打开CSV文件，点编码，点转为UTF-8编码，保存。再用上面导入的步骤就不会有乱码了。

--同步删除、增量更新
--存储过程中同步删除
  DELETE FROM ODS_BLBM_JXZYCSLBDM @EPBW O WHERE NOT EXISTS (SELECT 1
  FROM BLBM_JXZYCSLBDM L WHERE O.JXCSLBDM= L.JXCSLBDM);
--存储过程中增量更新用MERGE INTO语句

时间：
date'2022-3-15'
to_date('2022-3-15','yyyy-mm-dd') 
to_char(sysdate,'yyyy-mm-dd') 
date''里面日期格式只能填'yyyy-mm-dd'，这个格式是oracle里的日期的输入格式只能'-'分隔;
to_date()里面的格式是解析格式,用来解析第一个参数字符串的格式,分隔符可以任意符号（分隔符无限制，只要能分隔年月日都能解析）,相当于把字符串类型解析成日期类型（改变了数据类型）;
to_char()里面的格式是展示格式，就是按照展示的格式输出数据;
例：
TO_DATE('2022/12/8 11:30:03','YYYY-MM-DD HH24:MI:SS') 
--取当天00:00:00到当天11:00:00的时间
select * from jssbyx_byxjl jb where (jb.cjsja between trunc(sysdate) and trunc(sysdate)+1-46800/86400) 
--当天11:00:00
select trunc(sysdate)+1-46800/86400 from dual

更新10天内的数据：
WHERE (DJ.CJSJ > SYSDATE - 10 or DJ.XGSJ > SYSDATE - 10)

--列转行
select name,listagg(class,';') within group(order by class) class from ls_temp_st_20221108 group by name
--或
select name,to_char(wm_concat(class)) from ls_temp_st_20221108 group by name --wm_concat()默认','分割，listagg(class,';') within group可以自定义分隔符
--排名函数
select a.*,rank() over(order by num)   from ls_temp_st_20221108 a;

--EXCEL中引号内引用单元格，VLOOKUP()和建表字段注释会用到
="'"&(I54)&"'"

--查5天以内的数据，例：
select * from ODS_DZYJ_DCFC 
where cjsj>sysdate-5
or xgsj>sysdate-5

--查表名，表名注释，表的创建时间
SELECT * FROM ALL_TABLES WHERE OWNER='EPBW_JH'；
select * from all_tab_comments where owner='EPBW_JH'；
SELECT a.OBJECT_NAME 表名 ,b.COMMENTS 表名注释,a.CREATED 创建时间 FROM ALL_OBJECTS a left join all_tab_comments b on a.OBJECT_NAME=b.TABLE_NAME
WHERE a.OBJECT_TYPE='TABLE' AND a.OWNER='EPBW_JH' and CREATED>=date'2022-1-1' and object_name not like 'LS%' and object_name not like 'TEMP%' ORDER BY a.CREATED DESC;

--1、导出、生成当前用户的所有表和视图的注释
select 'comment on table '||a.table_name|| ' is '||''''||a.comments||''''||';' from user_tab_comments a where a.table_type in('TABLE','VIEW');
--2、导出、生成当前用户的所有表字段的注释
select 'comment on column '||t.table_name||'.'||t.column_name||' is '||''''||t.comments||''''||';' from user_col_comments t

--用户密码过期巡检
1.密码过期检查
select username,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE from dba_users where expiry_date <to_date('2022-11-14','yyyy-mm-dd')
and ACCOUNT_STATUS ='OPEN'
2.和密码本比较，我们有的账号，登录看账号密码是否正确
3.修改账户为原始密码
alter user JH_QUE  identified by "JH_que2020";

--查看表所占内存空间
SELECT segment_name AS TABLENAME,
       BYTES B,
       BYTES / 1024 KB,
       BYTES / 1024 / 1024 MB
  FROM user_segments
WHERE segment_name = '表名'  --表名要大写

--用rowid可以点解锁键，可以直接复制粘贴数据，跟for update用法相似
select DJ.JHDM,DJ.jh,DJ.JHBM,DJ.SSYQCDM,DJ.ROWID  from DJJCXX DJ where jhbm='鱼翔1'

--delete后回收磁盘空间
alter table YGG enable row movement;
alter table YGG shrink space;

  --数据管控对应的数据库  JH_SJZYGK  JH_sjzygk2022
10.84.21.254:1521/JHSJGKPT  

--查看人员信息和人员所在单位
select * from sys_user s where s.user_nm like '%侯友康%';
select * from sys_org s where s.org_id='12164408';

--查询采气一厂(3140380000)和涪陵页岩气公司(3140390000)
select dwdm,sooo.org_nm||soo.org_nm||so.org_nm from  ZKJCXX z 
left join sys_org so on z.dwdm=so.org_code   
left join sys_org soo on soo.org_id=so.diruorg_id
left join sys_org sooo on sooo.org_id=soo.diruorg_id
where dwdm like '314039%' or dwdm like '314038%';

--查询采气一厂(3140380000)和涪陵页岩气公司(3140390000),且对应最新的站库变更日期
select tt.dwdm,
case when tt.nm like '%采气一厂%' then '采气一厂'
     when tt.nm like '%涪陵%' then '涪陵页岩气公司'
     end dwmc
from (
select t.dwdm,sooo.org_nm||soo.org_nm nm from (
SELECT * FROM ZKJCXX 
WHERE (ZKBH, ZKBGRQ) IN(SELECT ZKBH, MAX(ZKBGRQ) FROM ZKJCXX GROUP BY ZKBH) ) t 
left join sys_org so on so.org_code=t.dwdm
--也能用(LEFT JOIN (SELECT A.*,ROW_NUMBER() OVER(PARTITION BY ZKBH ORDER BY ZKBGRQ DESC) XH FROM ZKJCXX A) T1 ON T1.ZKBH = T.ZKBH  AND T1.XH = 1)
left join sys_org soo on soo.org_id=so.diruorg_id 
left join sys_org sooo on sooo.org_id=soo.diruorg_id
where substr(t.dwdm,1,6) in ('314039','314038')) tt;

--DWDM处理
1、DZSJ_JWHGZJBXX (地质设计_井位合格证基本信息)中的GLDWDM(管理单位代码) 
      SUBSTR(JW.GLDWDM,1,6)  in （'314039','314038'）可以取采气一厂和涪陵公司，用JHDM作为连接条件
2、ZKJCXX(站库基础信息)中的DWDM,用ZKBH(站库编号)作为连接条件
3、JSGX(管线基础信息，也称集输管线)中的DWDM,用JSGXBH(集输管线编号)作为连接条件

--EPBP平台井位发布没有同步到单井基础信息的处理
select * from DZSJ_JWHGZJBXX  where jhbm like '鱼翔1'
select dj.jhdm,dj.jhbm,dj.ssyqcdm,jw.ssyqcdm,dj.rowid from djjcxx dj left join DZSJ_JWHGZJBXX jw on dj.jhdm=jw.jhdm where dj.jhbm like '鱼翔1';--所属油气藏没同步的处理
select dj.jhdm,dj.jhbm,dj.jbdm,jw.jbdm,dj.rowid from djjcxx dj left join  DZSJ_JWHGZJBXX jw on dj.jhdm=jw.jhdm where dj.jh='钟斜951C';--井别没同步的处理

--修改单井开发基础数据，需要修改两张表，单井开发，与最新记录
select t.TCRQ,t.rowid,XGSJ from   DJJBSJ_DJKFJCSJZXJL T  where jhdm ='JH2005121302'
DJJBSJ_DJKFJCSJ
BLBM_DJCLDM
SELECT * FROM DJJCXX WHERE JH ='ES11-4'   JH2005121302
SELECT T.*,T.ROWID FROM  DJJBSJ_DJKFJCSJ T where CLDM ='21' and  jhdm ='JH2005121302'
SELECT * FROM BLBM_DJCLDM  WHERE DJCLMC like '%投产%'

巡检唯一性报错：
巡检如果出现唯一性约束报错，并且EPBP源表和EPBW目的表的约束是一致的情况下，则考虑是否为
存储过程中同步删除的SQL有问题，有可能是EPBP没有（或已删除）的主键在EPBW中并没有删除导致
的插入数据违反唯一性约束。解决方法，在存储过程的头部加上同步删除，例如：
   DELETE FROM ODS_JXZY_XCSG_YLSGZJ_BZCX @EPBW O
     WHERE DBID NOT IN 
     (SELECT DBID FROM JXZYXCZL_YLSG_BZCX L );
该案例来源于EPBP生产库PKG_ETL_UNDERPIT.LOAD_ODS_JXZY_XCSG_YLSGZJ_BZCX，可以直接参考该存储过程，看最开始的两个删除SQL是怎样写的。

-- 哪些模块配置了某某模块的权限(费超)
select * from sys_role where role_id in(select sa.role_id from sys_auth sa where res_id = '9264070');   res_id 是模块的参数    这个SQL是查询  哪些模块配置了某某模块的权限  你们登记下来  以后用得到  

--查找所有或指定的表结构（也称为数据字典）
SELECT U.TABLESPACE_NAME,
       A.TABLE_NAME      AS 表名,
       UT.COMMENTS,
       A.COLUMN_ID       AS 序号,
       -- CASE WHEN (SELECT COUNT(*) FROM USER_VIEWS V WHERE V.VIEW_NAME =A.TABLE_NAME )>0 THEN 'V' ELSE 'U'END AS "TableType",
       A.COLUMN_NAME AS 字段,
       A.DATA_TYPE AS 字段类型,
       CASE
         WHEN A.DATA_TYPE = 'NUMBER' THEN
          CASE
            WHEN A.DATA_PRECISION IS NULL THEN
             A.DATA_LENGTH
            ELSE
             A.DATA_PRECISION
          END
         ELSE
          A.DATA_LENGTH
       END AS 字段长度,
       A.DATA_SCALE 小数位,
       CASE
         WHEN A.NULLABLE = 'N' THEN
          '否'
         ELSE
          ''
       END AS 是否可空,
       B.COMMENTS AS 字段注释,
       CASE WHEN
         INSTR(PU.LX,'P')>0 THEN  '是'
         ELSE
          ''
       END AS 是否主键,
       CASE WHEN
         INSTR(PU.LX,'U')>0 THEN
          '是'
         ELSE
          ''
       END AS 唯一性约束
  FROM USER_TAB_COLS A
 INNER JOIN USER_COL_COMMENTS B
    ON A.TABLE_NAME = B.TABLE_NAME
   AND B.COLUMN_NAME = A.COLUMN_NAME
 INNER JOIN USER_TAB_COMMENTS UT
    ON UT.TABLE_NAME = A.TABLE_NAME
 INNER JOIN USER_TABLES U
    ON U.TABLE_NAME = A.TABLE_NAME
 LEFT JOIN (SELECT UCC.TABLE_NAME, UCC.COLUMN_NAME,LISTAGG(D.CONSTRAINT_TYPE,',') WITHIN GROUP(ORDER BY 1) LX
     FROM USER_CONS_COLUMNS UCC
    INNER JOIN USER_CONSTRAINTS D
       ON UCC.CONSTRAINT_NAME = D.CONSTRAINT_NAME
    WHERE D.CONSTRAINT_TYPE = 'U'
       OR D.CONSTRAINT_TYPE = 'P'
    GROUP BY UCC.TABLE_NAME, UCC.COLUMN_NAME)PU
    ON A.TABLE_NAME = PU.TABLE_NAME
   AND B.COLUMN_NAME = PU.COLUMN_NAME
 WHERE A.TABLE_NAME NOT LIKE 'BLBM%'
 AND A.TABLE_NAME NOT LIKE 'CLBM%'
 AND A.TABLE_NAME IN ('JXZYGL_GZLYSSQ_SGGX',
'JXZYGL_GZLYSSQ_GLXX',
'JXZYGL_GZLYSSQ_PHLWXX',
'JXZYGL_GZLYSSQ_TZCLXX',
'JXZYGL_GZLYSSQ_QTLWXX',
'JXZYGL_GZLYSSQ_XZSGXX') --填写需要找的表名
ORDER BY U.TABLESPACE_NAME,A.TABLE_NAME, A.COLUMN_ID;
--结束

--扫描全表，找出指定JHDM的表
DECLARE 
   V_SQL1 VARCHAR2(500);
   NUM NUMBER;
BEGIN
    FOR I IN (SELECT TABLE_NAME FROM ALL_COL_COMMENTS  WHERE OWNER ='EPBP_JH' AND COLUMN_NAME='JHDM' 
      AND TABLE_NAME NOT LIKE 'BIN$%' AND TABLE_NAME NOT LIKE 'DEL%' AND TABLE_NAME NOT LIKE 'V_%' ) LOOP
       V_SQL1:='SELECT COUNT(*) FROM '||I.TABLE_NAME ||' WHERE JHDM IN (''JH2016010317'',
''JH2011042101'',
''JH2016010287'',
''JH1975012201'',
''JH2016010264'',
''JH1974092601'',
''JH1975072501'',
''JH2016010267'',
''JH2016010250'',
''JH2017031414'',
''JH2016010251'',
''JH2016010252'',
''JH2016010253'')';
       --DBMS_OUTPUT.PUT_LINE(V_SQL1);
       EXECUTE IMMEDIATE V_SQL1 INTO NUM;
       IF(NUM>0) THEN
       DBMS_OUTPUT.PUT_LINE(I.TABLE_NAME||' '||NUM);
       END IF;
    END LOOP;
END;
--结束

--根据指定井号循环查找所有表的数据
DECLARE 
   V_SQL1 VARCHAR2(500);
   V_SQL2 VARCHAR2(500);
   NUM NUMBER;
   SUM1 number;
   V_TABLE_NAME VARCHAR2(500);
   V_TABLE_NAME_COMMENTS VARCHAR2(500);
BEGIN
    SUM1 := 0;
    NUM :=0;
    FOR J IN (SELECT JH,JHDM FROM LS_ZY1    ) LOOP
        
        FOR I IN (SELECT TABLE_NAME FROM ALL_COL_COMMENTS  WHERE OWNER ='EPBP_JH' AND COLUMN_NAME='JHDM' 
            AND TABLE_NAME NOT LIKE 'BIN$%' AND TABLE_NAME NOT LIKE 'DEL%' AND TABLE_NAME NOT LIKE 'V_%' AND TABLE_NAME NOT LIKE '%_PCS') LOOP
            V_SQL1:='SELECT COUNT(*) FROM '||I.TABLE_NAME ||'   WHERE JHDM ='''||J.JHDM||'''';
            --DBMS_OUTPUT.PUT_LINE(V_SQL1);
            EXECUTE IMMEDIATE V_SQL1 INTO NUM;
      --    SUM1 := SUM1 + NUM;
          V_TABLE_NAME := I.TABLE_NAME;
            
          IF(num >0)  THEN
           V_SQL2:='SELECT COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME ='''||I.TABLE_NAME||'''';
           EXECUTE IMMEDIATE V_SQL2 INTO V_TABLE_NAME_COMMENTS;
            --DBMS_OUTPUT.PUT_LINE(J.JHDM||'-'||V_TABLE_NAME);
           INSERT INTO JH_QUE.LS_BD_PY(井号,JHDM,TABLE_NAME,COMMENTS,NUM) VALUES(J.JH,J.JHDM,I.TABLE_NAME,V_TABLE_NAME_COMMENTS,num);
           commit;
          
          END IF;
       END LOOP;
       
    END LOOP;
END;
--结束

--用主键对比，不存在相同主键字段才插入数据
--例：
insert into ...... 
WHERE not exists(select 1 from ODS_CYQ_SCSJ_HSFX@EPBW o where kh.jhdm=o.jhdm and kh.fxsj=o.fxsj)
 -- 两个表存在主键相同的就不插入数据，不相同的才插入数据
-- 手动导入数据从EPBP导入到EPBW数据库案例：
    INSERT INTO ODS_CYQ_SCSJ_HSFX@EPBW o
      (JHDM,
       JH,
       FXSJ,
       LLZHL,
       RHHS,
       ZHHS,
       ZHHSTJ,
       CJR,
       CJSJ,
       XGR,
       XGSJ,
       SHR,
       SHRQ,
       SHZT,
       JGDM,
       PHZ,
       HSLB,
       HYLB)
      SELECT KH.JHDM, --井号代码
             D.JHBM      JH, --井号
             KH.FXSJ, --分析时间
             KH.LLZHL, --氯离子含量
             KH.RHHS, --乳化含水
             KH.ZHHS, --综合含水
             KH.ZHHSTJ, --综合含水体积
             KH.CJR, --创建人
             KH.CJSJ, --创建时间
             KH.XGR, --修改人
             KH.XGSJ, --修改时间
             KH.SHRA     SHR, --审核人
             KH.SHRQ, --审核日期
             KH.SHZT, --审核状态
             KH.ORG_CODE JGDM, --机构代码
             KH.PHZ, --PH值
             KH.HSLB, --含砂率
             KH.HYLB --含盐量
        FROM KFDTSJ_HSFXRSJ KH
        LEFT JOIN DJJCXX D
          ON KH.JHDM = D.JHDM
       WHERE not exists(select 1 from ODS_CYQ_SCSJ_HSFX@EPBW o where kh.jhdm=o.jhdm and kh.fxsj=o.fxsj)
     and kh.fxsj>date'2022-9-15';
--结束

--血缘关系
SELECT
    ROWNUM SEQ,
    DBID,
    TARGET_ACC_NAME,
    TARGET_DB_INSTANCE_NAME,
    TARGET_DB_NAME,
    TARGET_TABLE_ID,
    TARGET_TABLE_NAME,
    TABLE_DESC,
    SRC_TABLE_ID,
    SRC_TABLE_NAME,
    SRC_ACC_NAME,
    SRC_DB_INSTANCE_NAME,
    SRC_DB_NAME,
    SORT_NM ETL_SORT_NM,
    SORT_ID ETL_SORT_ID,
    ETL_NM ETL_NAME,
    DT_SYNC_METH,
    SYNC_PROGRAM
    FROM(
    SELECT
    DISTINCT
    T1.DBID,
    ACC1.DB_SCHEMA_NAME TARGET_ACC_NAME,
    DB1.DB_INSTANCE_NM TARGET_DB_INSTANCE_NAME,
    DB1.DB_NM TARGET_DB_NAME,
    TT1.DBID TARGET_TABLE_ID,
    TT1.TBL_NM TARGET_TABLE_NAME,
    T1.RLTN_DESC TABLE_DESC,
    LISTAGG(TT2.DBID, ',') WITHIN GROUP (ORDER BY TT2.DBID) OVER(PARTITION BY T1.DBID) SRC_TABLE_ID,
    LISTAGG(TT2.TBL_NM, ',') WITHIN GROUP (ORDER BY TT2.DBID) OVER(PARTITION BY T1.DBID) SRC_TABLE_NAME,
    LISTAGG(ACC2.DB_SCHEMA_NAME, ',') WITHIN GROUP (ORDER BY ACC2.DBID) OVER(PARTITION BY T1.DBID)
    SRC_ACC_NAME,
    LISTAGG(ACC2.DBID, ',') WITHIN GROUP (ORDER BY ACC2.DBID) OVER(PARTITION BY T1.DBID) SRC_ACC_DBID,
    LISTAGG(DB2.DB_INSTANCE_NM, ',') WITHIN GROUP (ORDER BY ACC2.DBID) OVER(PARTITION BY T1.DBID)
    SRC_DB_INSTANCE_NAME,
    LISTAGG(DB2.DBID, ',') WITHIN GROUP (ORDER BY ACC2.DBID) OVER(PARTITION BY T1.DBID) SRC_DB_ID,
    LISTAGG(DB2.DB_NM, ',') WITHIN GROUP (ORDER BY ACC2.DBID) OVER(PARTITION BY T1.DBID) SRC_DB_NAME,
    A.ETL_DETAILED_LOGIC_ID,
    S.SORT_NM,
    S.DBID SORT_ID,
    C.ETL_NM,
    C.ETL_EN_NM,
    E.CODE_MEANING DT_SYNC_METH,
    B.MAPPING_NM SYNC_PROGRAM
    FROM BLOOD_RLTN_TAB_TRGT T1
    LEFT JOIN DB_SCHEMA ACC1
    ON T1.TRGT_DB_ID = ACC1.DBID
    LEFT JOIN DB_INFO DB1
    ON ACC1.DB_ID = DB1.DBID
    LEFT JOIN (SELECT DBID,TBL_NM FROM DB_DW_TABLE UNION SELECT DBID,TBL_NM FROM PM_DB_TABLE) TT1
    ON TT1.DBID = T1.TRGT_TBL_ID
    LEFT JOIN BLOOD_RLTN_TAB_SRC T2
    ON T2.TRGT_BLOOD_ID = T1.DBID
    LEFT JOIN DB_SCHEMA ACC2
    ON ACC2.DBID = T2.SRC_DB_ID
    LEFT JOIN DB_INFO DB2
    ON ACC2.DB_ID = DB2.DBID
    LEFT JOIN (SELECT DBID,TBL_NM FROM DB_DW_TABLE UNION SELECT DBID,TBL_NM FROM PM_DB_TABLE) TT2
    ON T2.SRC_TBL_ID = TT2.DBID
    LEFT JOIN ETL_PWC_MAPPING_BLOOD A
    ON T1.DBID = A.ETL_DETAILED_LOGIC_ID
    LEFT JOIN ETL_PWC_MAPPING B
    ON A.ETL_PWC_MAPPING_ID = B.DBID
    LEFT JOIN ETL_INFO C
    ON B.ETL_ID = C.DBID
    LEFT JOIN ETL_FREQ F
    ON F.ETL_ID = C.DBID
    LEFT JOIN ETL_SORT S
    ON C.ETL_SORT_ID = S.DBID
    LEFT JOIN ICC_C_DT_SYNC_METH E
    ON E.CODE_VALUE = C.DT_SYNC_METH_CODE
    WHERE 1=1
)  
--结束

--查找哪些表有JHDM='JH2023050201'
--方法一：
--不用循环的话，用ALL_COL_COMMENTS找出表，然后select所有表jhdm='JH2023050201'的结果集，用union all相连
SELECT * FROM ALL_COL_COMMENTS WHERE OWNER ='EPBP_JH' AND COLUMN_NAME='JHDM' 
AND TABLE_NAME NOT LIKE 'BIN$%' AND TABLE_NAME NOT LIKE 'DEL%' AND TABLE_NAME NOT LIKE 'V_%' AND TABLE_NAME NOT LIKE '%_PCS'
--方法二：
--循环查找JHDMM='JH2023050201'的表
     DECLARE
        V_SQL VARCHAR2(500)  ;
        NUM1    NUMBER(8);
         
       BEGIN
         FOR I IN (SELECT TABLE_NAME FROM USER_COL_COMMENTS  WHERE COLUMN_NAME ='JHDM'
             AND TABLE_NAME NOT LIKE 'BIN%' AND TABLE_NAME NOT LIKE 'DEL%' AND TABLE_NAME !='VH_DZLJ_LJZLJY_XMJY' AND TABLE_NAME NOT LIKE 'V_%'
           ) LOOP
            V_SQL:='SELECT COUNT(*) FROM '||I.TABLE_NAME || ' where jhdm =''JH2023050201'' ';
            EXECUTE IMMEDIATE V_SQL INTO NUM1;
            IF NUM1>0 THEN
               DBMS_OUTPUT.PUT_LINE(I.TABLE_NAME);
            END IF;
         END LOOP;
       END;

 --根据某个值查这个值所在的表
declare
  -- 定义一个关联数组，用于存储查询结果
  type t_result is table of varchar2(1000) index by pls_integer;
  v_sql varchar2(1000);
  data_count number;
  v_results t_result;
  v_index pls_integer := 1;
  -- 声明一个游标，用于查询所有符合条件的表名
  cursor c_tables is
    select distinct table_name
    from all_tables
    where owner='EPBP_JH' and tablespace_name='TS_UNDERPIT';
  -- 声明一个带参数的游标，用于查询每个表中所有符合条件的列
  cursor c_columns (p_table_name varchar2) is
    select distinct column_name, data_type
    from all_tab_columns
    where owner='EPBP_JH' and table_name=p_table_name and data_type='VARCHAR2';
begin
  -- 遍历所有符合条件的表名
  for cur_table in c_tables loop
    -- 遍历每个表中所有符合条件的列
    for cur_column in c_columns(cur_table.table_name) loop
      -- 构造动态SQL语句，查询符合条件的数据行数
      v_sql := 'select count(1) from ' || cur_table.table_name ||
               ' where ' || cur_column.column_name || ' like :val';
      -- 执行动态SQL语句，绑定参数值
      execute immediate v_sql into data_count using '%钟市系统污%';
      -- 如果查询结果不为0，则将SQL语句存储到结果数组中
      if data_count > 0 then
        v_results(v_index) := v_sql;
        v_index := v_index + 1;
      end if;
    end loop;
  end loop;
  -- 输出所有符合条件的SQL语句
  for i in 1..v_results.count loop
    dbms_output.put_line(v_results(i));
  end loop;
exception
  -- 捕获异常并输出错误信息
  when others then
    dbms_output.put_line('Error: ' || sqlerrm);
end;

--找重复数据（EPBW化验分析）
select * from STG_HYFX_YYNWQX where rowid in (
select regexp_substr(rd,'[^,]+',1,r) rd from(
select rd,regexp_count(rd,',')+1 n from(
select listagg(rowid,',') 
within group(order by yph) rd from STG_HYFX_YYNWQX 
group by YPH, JHDM, JDB, YPDS, HYFXBGRQ having count(*)>1)) a
full join 
(select rownum r from dual connect by rownum<=(select max(regexp_count(rd,',')+1) n from(
select listagg(rowid,',') 
within group(order by yph) rd from STG_HYFX_YYNWQX 
group by YPH, JHDM, JDB, YPDS, HYFXBGRQ having count(*)>1))) b on 1=1
where n>=r) order by YPH, JHDM, JDB, YPDS, HYFXBGRQ