--1、查询ods里月调度的过程
--ods里job查询
SELECT * FROM Dba_Scheduler_Jobs t
WHERE t.STATE <> 'DISABLED'
AND t.JOB_ACTION LIKE '%SP_TFR_ETL_LATE%'
AND owner ='DPRP';
---过程执行时间
SELECT *
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE JOB_NAME = '客户理赔监控报表1'
ORDER BY LOG_DATE DESC;
-
-gp里查询
select * from job_metadata
where job_tp='ORA'
AND SCHD_PERIOD='M'
and job_nm like 'sp%'
AND INIT_FLAG='Y'
--查询这些过程依赖的表
SELECT * FROM dba_dependencies
WHERE owner = 'DPRP'
AND TYPE = 'PROCEDURE'
AND NAME = 'SP_TFR_TRUCK_TRANSIT';
-
-查询ods到gp的所有的表
select SRC_SQL,*
from PJ_JOB_INFO
where TYP <> 'Target'
and STAGE_TYP <> 'PxDataSet'
and STAGE_TYP <> 'ODBCConnectorPX'
and STAGE_TYP not like '%gp%'
and UPPER(SRC_SQL) not like '%BASIC%'
and UPPER(SRC_SQL) not like '%MART%'
and UPPER(SRC_SQL) not like '%GP_%'
order by STAGE_TYP
-
--根据表名字查询报表明细的报表
SELECT * FROM DPRP.T_BI_REPORT T
WHERE UPPER(T.FFROM) LIKE '%T_STL_INNERCOST_1010_BAK%'
OR UPPER(T.FJOIN) LIKE '%T_STL_INNERCOST_1010_BAK%'
sql中select是什么意思
-----正在运行的sql
select GQ.SQL_FULLTEXT, GS.STATUS, GS.STATE
from GV$SESSION GS
join GV$SQL GQ
on GS.SQL_ID = GQ.SQL_ID
where GQ.SQL_FULLTEXT like '%非统一结算%'
and GQ.SQL_FULLTEXT not like '%gv$session%'
-
---过程中包含的内容
select * from pg_proc where upper(prosrc) like '%AS_COCKPIT_TGT_D_ACT_COMPTARGET1%'
----依赖关系
select  datamart.gld_dm_job_relyon_job_test('gld_dm_wb_fee_detail_billing_analy');
select * _sp_test order by level;
----删除列
ALTER TABLE datamart.p_waybill_detail_contra_sum DROP COLUMN industry_source_category_id1
--修改表类型
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
---字典表
select * from BASICMOD.B_CL_SRC_SYS_CLASSIFY
where src_sys_classify_type_id = 10395
where bi_classify_id = 1377000004
---查视图
select * from datamart.t_upload_datafile
where active = 'Y'
and template_id in('1500');
-- 查表哪个时间段有数
select billing_date_id, count(1) from datamart.dm_wb_custcontru_billing_analy_detail
group by billing_date_id
order by billing_date_id
跑数进表
select datamart.dm_wb_custcontru_bill_lcl_par('2016-02-26', '2016-02-29');
检查表数据
select sum(billing_income_m) from datamart.dm_wb_custcontru_bill_lcl_own
where billing_date_id >= 20160201 and billing_date_id < 20160210
select billing_date_id, sum(billing_income), sum(billing_income_m) from datamart.dm_wb_custcontru_bill_lcl_own
--where billing_date_id >= 20160201 and billing_date_id < 20160210
where billing_date_id = 20160209
group by billing_date_id
order by billing_date_id
-- 查跑数批次
select * from datamart.job_log where job_nm = ''
根据表名查相关过程。
select *
from pg_catalog.pg_proc
where prosrc like '%_vw_manual_data_1271%'

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。