SELECT HT.C15 AS CLUSTER_ID,
HT.C0 AS DATABASE_NAME,
HT.C1 AS TABLE_NAME,
HT.C5 AS LOCATION,
HT.C6 AS NO_OF_COLS,
DBMS_HADOOP_INTERNAL.UNIX_TS_TO_DATE(HT.C8) AS CREATION_TIME,
DBMS_HADOOP_INTERNAL.UNIX_TS_TO_DATE(HT.C9) AS LAST_ACCESSED_TIME,
HT.C2 AS OWNER,
HT.C3 AS TABLE_TYPE,
HT.C10 AS PARTITIONED,
HT.C7 AS NO_OF_PART_KEYS,
HT.C11 AS INPUT_FORMAT,
HT.C12 AS OUTPUT_FORMAT,
HT.C13 AS SERIALIZATION,
HT.C14 AS COMPRESSED,
HT.C16 AS HIVE_URI
FROM
(SELECT DIRECTORY_PATH AS configDir
FROM DBA_DIRECTORIES
WHERE UPPER(DIRECTORY_NAME) = 'ORACLE_BIGDATA_CONFIG') HU,
(SELECT DIRECTORY_PATH AS debugDir
FROM DBA_DIRECTORIES
WHERE UPPER(DIRECTORY_NAME) = 'ORACLE_BIGDATA_DEBUG'
AND (DBMS_HADOOP_INTERNAL.DEBUG_USER_PRIVILEGED('SYS') = 1)
UNION ALL
SELECT 'InvalidDir' FROM DUAL WHERE
NOT EXISTS (SELECT DIRECTORY_PATH AS debugDir
FROM DBA_DIRECTORIES
WHERE UPPER(DIRECTORY_NAME) = 'ORACLE_BIGDATA_DEBUG'
AND (DBMS_HADOOP_INTERNAL.DEBUG_USER_PRIVILEGED('SYS') = 1))
) HV,
LATERAL(SELECT * FROM TABLE(DBMS_HADOOP_INTERNAL.GetHiveTable(LPAD(TO_CHAR(LENGTH(HU.configDir)),8,0)
|| HU.configDir || ';' || HV.debugDir, '*', '*', '*', 'TRUE', 1))) HT
SELECT HT.C15 AS CLUSTER_ID
,
HT.C0 AS DATABASE_NAME
,
HT.C1 AS TABLE_NAME
,
HT.C5 AS LOCATION
,
HT.C6 AS NO_OF_COLS
,
DBMS_HADOOP_INTERNAL.UNIX_TS_TO_DATE(HT.C8) AS CREATION_TIME
,
DBMS_HADOOP_INTERNAL.UNIX_TS_TO_DATE(HT.C9) AS LAST_ACCESSED_TIME
,
HT.C2 AS OWNER
,
HT.C3 AS TABLE_TYPE
,
HT.C10 AS PARTITIONED
,
HT.C7 AS NO_OF_PART_KEYS
,
HT.C11 AS INPUT_FORMAT
,
HT.C12 AS OUTPUT_FORMAT
,
HT.C13 AS SERIALIZATION
,
HT.C14 AS COMPRESSED
,
HT.C16 AS HIVE_URI
FROM
(SELECT DIRECTORY_PATH AS CONFIGDIR
FROM DBA_DIRECTORIES
WHERE UPPER(DIRECTORY_NAME) = 'ORACLE_BIGDATA_CONFIG') HU
,
(SELECT DIRECTORY_PATH AS DEBUGDIR
FROM DBA_DIRECTORIES
WHERE UPPER(DIRECTORY_NAME) = 'ORACLE_BIGDATA_DEBUG'
AND (DBMS_HADOOP_INTERNAL.DEBUG_USER_PRIVILEGED('SYS') = 1)
UNION ALL
SELECT 'INVALIDDIR'
FROM DUAL WHERE
NOT EXISTS (SELECT DIRECTORY_PATH AS DEBUGDIR
FROM DBA_DIRECTORIES
WHERE UPPER(DIRECTORY_NAME) = 'ORACLE_BIGDATA_DEBUG'
AND (DBMS_HADOOP_INTERNAL.DEBUG_USER_PRIVILEGED('SYS') = 1))
) HV
,
LATERAL(SELECT *
FROM TABLE(DBMS_HADOOP_INTERNAL.GETHIVETABLE(LPAD(TO_CHAR(LENGTH(HU.CONFIGDIR))
, 8
, 0)
|| HU.CONFIGDIR || ';' || HV.DEBUGDIR
, '*'
, '*'
, '*'
, 'TRUE'
, 1))) HT
|
|
|