------ 전체 테이블 목록
select a.owner as tbl_owner, a.owner_type as tbl_owner_type,
a.tbl_name, a.tbl_type, B.DB_LOCATION_URI, b.name as db_name from  HIVE.TBLS a, HIVE.DBS b where a.db_id=B.DB_id;

------전체 코디네이터 목록
select a.id, a.name, dbms_lob.substr(a.description, 10000,1), B.USERNAME from HUE.DESKTOP_DOCUMENT2 a, hue.auth_user b
where a.owner_id=B.ID and type='oozie-coordinator2' and is_history=0 and is_trashed=0;

------전체 WF 목록
select a.name, dbms_lob.substr(a.description, 10000,1), B.USERNAME from HUE.DESKTOP_DOCUMENT2 a, hue.auth_user b
where a.owner_id=B.ID and type='oozie-workflow2' and is_history=0 and is_trashed=0;

---전체 코디네이터및 WF목록
select a.id, a.name, dbms_lob.substr(a.description, 10000,1), B.USERNAME, a.type, A.LAST_MODIFIED from HUE.DESKTOP_DOCUMENT2 a, hue.auth_user b
where a.owner_id=B.ID and type in('oozie-workflow2','oozie-coordinator2') and is_history=0 and is_trashed=0;

-----from_document2_id기준 전체

select b.lvl, a.id, (select k.username from hue.auth_user k where k.id=a.owner_id) as username, a.last_modified,
a.name, dbms_lob.substr(a.description, 10000,1) as remark, a.type
, (select c.name from hue.desktop_document2 c where c.id=b.from_document2_id) as from_work, b.from_document2_id as from_id
, (select d.name from hue.desktop_document2 d where d.id=b.to_document2_id) as to_work
, (select dbms_lob.substr(e.description, 10000,1) from hue.desktop_document2 e where e.id=b.to_document2_id) as to_work_remark
, b.to_document2_id as to_id
,b.isloop
from hue.desktop_document2 a,
(
  select level as lvl, id, from_document2_id, to_document2_id, connect_by_iscycle isloop from HUE.DESKTOP_DOCUMENT2_DEPENDENCIES
   -- start with from_document2_id=67292 -- 코디네이터
  connect by nocycle prior to_document2_id = from_document2_id
) b
where a.id=b.from_document2_id and is_history=0 and is_trashed=0 and type in ('oozie-coordinator2','oozie-workflow2');

-------코디네이터를 기준으로 코디네이터와 WF 구조 목록.
select b.lvl, a.id, (select k.username from hue.auth_user k where k.id=a.owner_id) as username, a.last_modified,
a.name, dbms_lob.substr(a.description, 10000,1) as remark, a.type
, (select c.name from hue.desktop_document2 c where c.id=b.from_document2_id) as from_work, b.from_document2_id as from_id
, (select d.name from hue.desktop_document2 d where d.id=b.to_document2_id) as to_work
, (select dbms_lob.substr(e.description, 10000,1) from hue.desktop_document2 e where e.id=b.to_document2_id) as to_work_remark
, b.to_document2_id as to_id
,b.isloop
from hue.desktop_document2 a,
(
  select level as lvl, id, from_document2_id, to_document2_id, connect_by_iscycle isloop from HUE.DESKTOP_DOCUMENT2_DEPENDENCIES
   -- start with from_document2_id=67292 -- 코디네이터
   start with from_document2_id in (select id from HUE.DESKTOP_DOCUMENT2
       where type='oozie-coordinator2' and is_history=0 and is_trashed=0)
  connect by nocycle prior to_document2_id = from_document2_id
) b
where a.id=b.from_document2_id and is_history=0 and is_trashed=0 and type in ('oozie-coordinator2','oozie-workflow2');

------HUE.DESKTOP_DOCUMENT2_DEPENDENCIES에는 없고 HUE.DESKTOP_DOCUMENT2에만 있는 코디네이터 혹은 워크플로우 목록
select a.*, b.username from HUE.DESKTOP_DOCUMENT2 a, hue.auth_user b
       where type in('oozie-coordinator2','oozie-workflow2') and is_history=0 and is_trashed=0
       and a.id not in ( select from_document2_id from HUE.DESKTOP_DOCUMENT2_DEPENDENCIES
                       union
                       select to_document2_id from HUE.DESKTOP_DOCUMENT2_DEPENDENCIES
                      )
       and a.owner_id=B.ID;