合视图 vw_sql_rubbish_monitor_lhr 的内容

2022年11月4日

0 215 1

Tags： Oracle 监控 vw_sql_rubbish_monitor_lhr

👉 本文共约72个字，系统预计阅读时间或需1分钟。

不带包
带包
包
视图

不带包

CREATE OR REPLACE VIEW VW_SQL_RUBBISH_MONITOR_LHR AS
SELECT A.SID,
       C.SERIAL#,
       (SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID,
       C.OSUSER,
       C.USERNAME,
       (SELECT B.SQL_TEXT FROM V$SQLAREA B WHERE B.SQL_ID = A.SQL_ID) SQL_TEXT,
       A.PLAN_OPERATION || ' ' || A.PLAN_OPTIONS PLAN_OPERATION,
       A.PLAN_PARTITION_START,
       A.PLAN_PARTITION_STOP,
       A.SQL_ID,
       A.SQL_EXEC_START,
       C.LOGON_TIME,
       (D.ELAPSED_TIME / 1000000) ELAPSED_TIME_S,
       'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || A.SQL_ID ||
       ''',' || 0 || ',''advanced''));' SQL_PLAN,
       '笛卡尔积监控' MONITOR_TYPES
  FROM V$SQL_PLAN_MONITOR A, V$SESSION C, V$SQL_MONITOR D
 WHERE A.PLAN_OPERATION = 'MERGE JOIN'
   AND A.PLAN_OPTIONS = 'CARTESIAN'
   AND A.SID = C.SID
   AND C.STATUS = 'ACTIVE'
   AND A.STATUS = 'EXECUTING'
   AND D.STATUS = 'EXECUTING'
   AND A.SID = D.SID
   AND A.KEY = D.KEY
   AND A.SQL_EXEC_ID = D.SQL_EXEC_ID
   AND C.USERNAME NOT IN ('SYS')

UNION

------------------------------------------ SQL 执行时间超过5小时
SELECT A.SID,
       C.SERIAL#,
       (SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID,
       C.OSUSER,
       C.USERNAME,
       A.SQL_TEXT SQL_TEXT,
       '',
       '',
       '',
       A.SQL_ID,
       A.SQL_EXEC_START,
       C.LOGON_TIME,
       CASE
         WHEN A.STATUS = 'DONE(ERROR)' AND A.ERROR_MESSAGE IS NOT NULL THEN
          ((SELECT NB.ELAPSED_TIME
              FROM V$SQLAREA NB
             WHERE NB.SQL_ID = A.SQL_ID) / 1000000)

         ELSE
          (A.ELAPSED_TIME / 1000000)
       END AS ELAPSED_TIME,
       'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || A.SQL_ID ||
       ''',' || 0 || ',''advanced''));' SQL_PLAN,
       '执行时间超过5小时' MONITOR_TYPES
  FROM V$SQL_MONITOR A, V$SESSION C
 WHERE A.SQL_EXEC_ID = C.SQL_EXEC_ID
   AND A.SID = C.SID
   AND C.STATUS = 'ACTIVE'
   AND A.STATUS IN ('EXECUTING', 'DONE(ERROR)')
   AND A.ELAPSED_TIME > 18000000000

UNION

------------------------------------------ 分区表全分区扫描

SELECT A.SID,
       C.SERIAL#,
       (SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID,
       C.OSUSER,
       C.USERNAME,
       B.SQL_TEXT SQL_TEXT,
       A.PLAN_OPERATION || ' ' || A.PLAN_OPTIONS PLAN_OPERATION,
       A.PLAN_PARTITION_START,
       A.PLAN_PARTITION_STOP,
       A.SQL_ID,
       A.SQL_EXEC_START,
       C.LOGON_TIME,
       (B.ELAPSED_TIME / 1000000) ELAPSED_TIME,
       'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || A.SQL_ID ||
       ''',' || 0 || ',''advanced''));' SQL_PLAN,
       '分区表全分区扫描' MONITOR_TYPES
  FROM V$SQL_PLAN_MONITOR A, V$SQL_MONITOR B, V$SESSION C
 WHERE A.PLAN_OPERATION LIKE 'PARTITION%'
   AND A.PLAN_OPTIONS = 'ALL'
   AND A.SID = B.SID
   AND A.KEY = B.KEY
   AND A.SQL_EXEC_ID = B.SQL_EXEC_ID
   AND A.SQL_ID = B.SQL_ID
   AND A.SID = C.SID(+)
   AND A.STATUS = 'EXECUTING'
   AND B.ELAPSED_TIME >= 1000000 * 60

UNION

------------------------------------------ 执行计划中cost花费或者预估行数超大

SELECT A.SID,
       C.SERIAL#,
       (SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID,
       C.OSUSER,
       C.USERNAME,
       B.SQL_TEXT SQL_TEXT,
       A.PLAN_OPERATION || ' ' || A.PLAN_OPTIONS PLAN_OPERATION,
       A.PLAN_PARTITION_START,
       A.PLAN_PARTITION_STOP,
       A.SQL_ID,
       A.SQL_EXEC_START,
       C.LOGON_TIME,
       (B.ELAPSED_TIME / 1000000) ELAPSED_TIME,
       'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || A.SQL_ID ||
       ''',' || 0 || ',''advanced''));' SQL_PLAN,
       '执行计划中cost花费或者预估行数超大' MONITOR_TYPES
  FROM V$SQL_PLAN_MONITOR A, V$SQL_MONITOR B, V$SESSION C
 WHERE A.SID = B.SID
   AND A.KEY = B.KEY
   AND A.SQL_EXEC_ID = B.SQL_EXEC_ID
   AND A.SQL_ID = B.SQL_ID
   AND A.SID = C.SID(+)
   AND A.STATUS = 'EXECUTING'
   AND (A.PLAN_COST >= 3107523095 OR A.PLAN_CARDINALITY > 30748908521460)

UNION
------------------------------------------ tmp 表空间占用过大
SELECT C.SID,
       C.SERIAL#,
       (SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID,
       C.OSUSER,
       C.USERNAME,
       (SELECT NB.SQL_TEXT FROM V$SQLAREA NB WHERE NB.SQL_ID = C.SQL_ID) SQL_TEXT,
       '' PLAN_OPERATION,
       '',
       '',
       C.SQL_ID,
       C.SQL_EXEC_START,
       C.LOGON_TIME,
       ((SELECT NB.ELAPSED_TIME FROM V$SQLAREA NB WHERE NB.SQL_ID = C.SQL_ID) /
       1000000) ELAPSED_TIME,
       'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || C.SQL_ID ||
       ''',' || 0 || ',''advanced''));' SQL_PLAN,
       ' TMP 表空间占用过大 【' || C.BYTES || 'BYTES】' MONITOR_TYPES
  FROM (SELECT A.SESSION_ADDR,
               B.SID,
               B.SERIAL#,
               B.PADDR,
               B.OSUSER,
               B.USERNAME,
               B.SQL_ID,
               B.SQL_EXEC_START,
               B.LOGON_TIME,
               SUM(A.BLOCKS) * 8 * 1024 BYTES
          FROM V$TEMPSEG_USAGE A, V$SESSION B
         WHERE A.SESSION_ADDR = B.SADDR
           AND B.STATUS = 'ACTIVE'
         GROUP BY A.SESSION_ADDR,
                  B.SID,
                  B.SERIAL#,
                  B.PADDR,
                  B.OSUSER,
                  B.USERNAME,
                  B.SQL_ID,
                  B.SQL_EXEC_START,
                  B.LOGON_TIME) C
 WHERE C.BYTES > 50 * 1024 * 1024 * 1024

UNION
-----------------------------------------  SQL占用undo过大
SELECT S.SID,
       S.SERIAL#,
       (SELECT PR.SPID FROM V$PROCESS PR WHERE S.PADDR = PR.ADDR) SPID,
       S.OSUSER,
       S.USERNAME,
       (SELECT NB.SQL_TEXT
          FROM V$SQLAREA NB
         WHERE NB.SQL_ID = S.SQL_ID
           AND ROWNUM = 1) SQL_TEXT,
       '' PLAN_OPERATION,
       '',
       '',
       S.SQL_ID,
       TO_DATE(''),
       S.LOGON_TIME,
       ((SELECT NB.ELAPSED_TIME FROM V$SQLAREA NB WHERE NB.SQL_ID = S.SQL_ID) /
       1000000) ELAPSED_TIME,
       'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || S.SQL_ID ||
       ''',' || 0 || ',''advanced''));' SQL_PLAN,
       'SQL占用undo过大【' ||
       (T.USED_UBLK *
                                            TO_NUMBER((SELECT A.VALUE
                                                        FROM V$PARAMETER A
                                                       WHERE A.NAME =
                                                             'db_block_size'))) || 'BYTES】' MONITOR_TYPES

  FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R, V$ROLLSTAT G
 WHERE T.ADDR = S.TADDR
   AND T.XIDUSN = R.USN
   AND R.USN = G.USN
   AND (T.USED_UBLK *
       TO_NUMBER((SELECT A.VALUE
                    FROM V$PARAMETER A
                   WHERE A.NAME = 'db_block_size'))) >
       50 * 1024 * 1024 * 1024
 ORDER BY SQL_EXEC_START DESC
;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

CREATE OR REPLACE VIEW VW_SQL_RUBBISH_MONITOR_LHR AS

SELECT A.SID,

C.SERIAL#,

(SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID,

C.OSUSER,

C.USERNAME,

(SELECT B.SQL_TEXT FROM V$SQLAREA B WHERE B.SQL_ID = A.SQL_ID) SQL_TEXT,

A.PLAN_OPERATION || ' ' || A.PLAN_OPTIONS PLAN_OPERATION,

A.PLAN_PARTITION_START,

A.PLAN_PARTITION_STOP,

A.SQL_ID,

A.SQL_EXEC_START,

C.LOGON_TIME,

(D.ELAPSED_TIME / 1000000) ELAPSED_TIME_S,

'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || A.SQL_ID ||

''',' || 0 || ',''advanced''));' SQL_PLAN,

'笛卡尔积监控' MONITOR_TYPES

FROM V$SQL_PLAN_MONITOR A, V$SESSION C, V$SQL_MONITOR D

WHERE A.PLAN_OPERATION = 'MERGE JOIN'

AND A.PLAN_OPTIONS = 'CARTESIAN'

AND A.SID = C.SID

AND C.STATUS = 'ACTIVE'

AND A.STATUS = 'EXECUTING'

AND D.STATUS = 'EXECUTING'

AND A.SID = D.SID

AND A.KEY = D.KEY

AND A.SQL_EXEC_ID = D.SQL_EXEC_ID

AND C.USERNAME NOT IN ('SYS')

UNION

------------------------------------------ SQL 执行时间超过5小时

SELECT A.SID,

C.SERIAL#,

(SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID,

C.OSUSER,

C.USERNAME,

A.SQL_TEXT SQL_TEXT,

'',

A.SQL_ID,

A.SQL_EXEC_START,

C.LOGON_TIME,

CASE

WHEN A.STATUS = 'DONE(ERROR)' AND A.ERROR_MESSAGE IS NOT NULL THEN

((SELECT NB.ELAPSED_TIME

FROM V$SQLAREA NB

WHERE NB.SQL_ID = A.SQL_ID) / 1000000)

ELSE

(A.ELAPSED_TIME / 1000000)

END AS ELAPSED_TIME,

'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || A.SQL_ID ||

''',' || 0 || ',''advanced''));' SQL_PLAN,

'执行时间超过5小时' MONITOR_TYPES

FROM V$SQL_MONITOR A, V$SESSION C

WHERE A.SQL_EXEC_ID = C.SQL_EXEC_ID

AND A.SID = C.SID

AND C.STATUS = 'ACTIVE'

AND A.STATUS IN ('EXECUTING', 'DONE(ERROR)')

AND A.ELAPSED_TIME > 18000000000

UNION

------------------------------------------ 分区表全分区扫描

SELECT A.SID,

C.SERIAL#,

(SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID,

C.OSUSER,

C.USERNAME,

B.SQL_TEXT SQL_TEXT,

A.PLAN_OPERATION || ' ' || A.PLAN_OPTIONS PLAN_OPERATION,

A.PLAN_PARTITION_START,

A.PLAN_PARTITION_STOP,

A.SQL_ID,

A.SQL_EXEC_START,

C.LOGON_TIME,

(B.ELAPSED_TIME / 1000000) ELAPSED_TIME,

'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || A.SQL_ID ||

''',' || 0 || ',''advanced''));' SQL_PLAN,

'分区表全分区扫描' MONITOR_TYPES

FROM V$SQL_PLAN_MONITOR A, V$SQL_MONITOR B, V$SESSION C

WHERE A.PLAN_OPERATION LIKE 'PARTITION%'

AND A.PLAN_OPTIONS = 'ALL'

AND A.SID = B.SID

AND A.KEY = B.KEY

AND A.SQL_EXEC_ID = B.SQL_EXEC_ID

AND A.SQL_ID = B.SQL_ID

AND A.SID = C.SID(+)

AND A.STATUS = 'EXECUTING'

AND B.ELAPSED_TIME >= 1000000 * 60

UNION

------------------------------------------ 执行计划中cost花费或者预估行数超大

SELECT A.SID,

C.SERIAL#,

(SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID,

C.OSUSER,

C.USERNAME,

B.SQL_TEXT SQL_TEXT,

A.PLAN_OPERATION || ' ' || A.PLAN_OPTIONS PLAN_OPERATION,

A.PLAN_PARTITION_START,

A.PLAN_PARTITION_STOP,

A.SQL_ID,

A.SQL_EXEC_START,

C.LOGON_TIME,

(B.ELAPSED_TIME / 1000000) ELAPSED_TIME,

'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || A.SQL_ID ||

''',' || 0 || ',''advanced''));' SQL_PLAN,

'执行计划中cost花费或者预估行数超大' MONITOR_TYPES

FROM V$SQL_PLAN_MONITOR A, V$SQL_MONITOR B, V$SESSION C

WHERE A.SID = B.SID

AND A.KEY = B.KEY

AND A.SQL_EXEC_ID = B.SQL_EXEC_ID

AND A.SQL_ID = B.SQL_ID

AND A.SID = C.SID(+)

AND A.STATUS = 'EXECUTING'

AND (A.PLAN_COST >= 3107523095 OR A.PLAN_CARDINALITY > 30748908521460)

UNION

------------------------------------------ tmp 表空间占用过大

SELECT C.SID,

C.SERIAL#,

(SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID,

C.OSUSER,

C.USERNAME,

(SELECT NB.SQL_TEXT FROM V$SQLAREA NB WHERE NB.SQL_ID = C.SQL_ID) SQL_TEXT,

'' PLAN_OPERATION,

'',

C.SQL_ID,

C.SQL_EXEC_START,

C.LOGON_TIME,

((SELECT NB.ELAPSED_TIME FROM V$SQLAREA NB WHERE NB.SQL_ID = C.SQL_ID) /

1000000) ELAPSED_TIME,

'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || C.SQL_ID ||

''',' || 0 || ',''advanced''));' SQL_PLAN,

' TMP 表空间占用过大【' || C.BYTES || 'BYTES】' MONITOR_TYPES

FROM (SELECT A.SESSION_ADDR,

B.SID,

B.SERIAL#,

B.PADDR,

B.OSUSER,

B.USERNAME,

B.SQL_ID,

B.SQL_EXEC_START,

B.LOGON_TIME,

SUM(A.BLOCKS) * 8 * 1024 BYTES

FROM V$TEMPSEG_USAGE A, V$SESSION B

WHERE A.SESSION_ADDR = B.SADDR

AND B.STATUS = 'ACTIVE'

GROUP BY A.SESSION_ADDR,

B.SID,

B.SERIAL#,

B.PADDR,

B.OSUSER,

B.USERNAME,

B.SQL_ID,

B.SQL_EXEC_START,

B.LOGON_TIME) C

WHERE C.BYTES > 50 * 1024 * 1024 * 1024

UNION

----------------------------------------- SQL占用undo过大

SELECT S.SID,

S.SERIAL#,

(SELECT PR.SPID FROM V$PROCESS PR WHERE S.PADDR = PR.ADDR) SPID,

S.OSUSER,

S.USERNAME,

(SELECT NB.SQL_TEXT

FROM V$SQLAREA NB

WHERE NB.SQL_ID = S.SQL_ID

AND ROWNUM = 1) SQL_TEXT,

'' PLAN_OPERATION,

'',

S.SQL_ID,

TO_DATE(''),

S.LOGON_TIME,

((SELECT NB.ELAPSED_TIME FROM V$SQLAREA NB WHERE NB.SQL_ID = S.SQL_ID) /

1000000) ELAPSED_TIME,

'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || S.SQL_ID ||

''',' || 0 || ',''advanced''));' SQL_PLAN,

'SQL占用undo过大【' ||

(T.USED_UBLK *

TO_NUMBER((SELECT A.VALUE

FROM V$PARAMETER A

WHERE A.NAME =

'db_block_size'))) || 'BYTES】' MONITOR_TYPES

FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R, V$ROLLSTAT G

WHERE T.ADDR = S.TADDR

AND T.XIDUSN = R.USN

AND R.USN = G.USN

AND (T.USED_UBLK *

TO_NUMBER((SELECT A.VALUE

FROM V$PARAMETER A

WHERE A.NAME = 'db_block_size'))) >

50 * 1024 * 1024 * 1024

ORDER BY SQL_EXEC_START DESC

;

带包

包

CREATE OR REPLACE PACKAGE pkg_utility_lhr AUTHID CURRENT_USER IS

  v_pkg_name VARCHAR2(255) := upper('pkg_utility_lhr');
  ---变量

  ---------------    第一部分： 函数         ------------------------
  ------① 得到表的索引列

  FUNCTION fun_get_index_column_lhr(p_owner      VARCHAR2,
                                    p_index_name VARCHAR2,
                                    p_table_name VARCHAR2) RETURN VARCHAR2;
  ---- ② long类型 转化为VARCHAR2
  FUNCTION fun_long_to_char_lhr(in_rowid      ROWID,
                                in_owner      VARCHAR,
                                in_table_name VARCHAR,
                                in_column     VARCHAR2) RETURN VARCHAR;

  ----④ 得到一个字符串中特殊字符的个数
  FUNCTION fun_getspecharcounts_lhr(p_string  IN VARCHAR2,
                                    p_spechar IN VARCHAR2) RETURN NUMBER;

  -----⑦ 该函数返回 比率

  FUNCTION fun_get_percent_lhr(p_fenzi     NUMBER,
                               p_fenmu     NUMBER,
                               p_precision NUMBER) RETURN VARCHAR2;
  -----⑧ 该函数返回 一个对象的大小
  FUNCTION fun_get_objectsize_lhr(p_objectname VARCHAR2) RETURN VARCHAR2;

  ------⑨ 该函数 返回一个秒转换为标准时间
  FUNCTION fun_get_total_time_lhr(p_total_seconds NUMBER,
                                  p_flag          VARCHAR2 DEFAULT 'S') --秒
   RETURN VARCHAR2 DETERMINISTIC;

  ------（19）该函数    得到 送给ods数据的提取格式
  FUNCTION fun_get_ods_string_lhr(p_table VARCHAR2) RETURN VARCHAR2;

  ------（19--1）该函数    得到 送给ods数据的提取格式2
  FUNCTION fun_get_ods_string2_lhr(p_table     VARCHAR2,
                                   p_separator VARCHAR2 DEFAULT '|')
    RETURN VARCHAR2;

  ------（20）该函数    得到 spool 数据的提取格式
  FUNCTION fun_get_spool_string_lhr(p_table VARCHAR2) RETURN VARCHAR2;
  FUNCTION fun_get_spool_string2_lhr(p_table VARCHAR2) RETURN VARCHAR2;

  ------（21）该函数    将 interval day to second 转换为 易读格式
  FUNCTION fun_get_interval_date_lhr(p_date INTERVAL DAY TO SECOND)
    RETURN VARCHAR2;

  ------（22）该函数    将字符串分割成多行     返回一个table 集合
  FUNCTION fun_get_tab_lhr(p_string    IN VARCHAR2,
                           p_separator IN VARCHAR2 DEFAULT ',')
    RETURN type_tab_lhr;

  ------（23）该函数    将得到 视图 的创建语句
  FUNCTION fun_get_create_view_lhr(p_name VARCHAR2, p_length NUMBER)
    RETURN VARCHAR2;
  ------（24）该函数    将得到 表 的创建语句
  FUNCTION fun_get_create_table_lhr(p_owner VARCHAR2, p_name VARCHAR2)
    RETURN VARCHAR2;
  ------（25）该函数  得到正确的数字
  FUNCTION fun_get_right_number_lhr(p_number    NUMBER,
                                    p_precision NUMBER,
                                    p_str       VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2 DETERMINISTIC;

  ------（27）该函数  判断2个表的列是否相同
  FUNCTION f_get_table_is_same(p_user1  VARCHAR2,
                               p_table1 VARCHAR2,
                               p_user2  VARCHAR2,
                               p_table2 VARCHAR2,
                               is_order NUMBER -- 1表示按照列号来比对，0或2表示按照列名称来比对
                               ) RETURN VARCHAR2;

  ------------------------------------------------------------------------------------------------------  得到行转列的语句  2013-06-07 17:58:52
  FUNCTION f_get_row2_col_sql_lhr(p_table VARCHAR2,
                                  p_cols  VARCHAR2,
                                  p_c0    VARCHAR2,
                                  p_c1    VARCHAR2 DEFAULT NULL,
                                  p_c2    VARCHAR2 DEFAULT NULL,
                                  p_c3    VARCHAR2 DEFAULT NULL,
                                  p_c4    VARCHAR2 DEFAULT NULL,
                                  p_c5    VARCHAR2 DEFAULT NULL,
                                  p_c6    VARCHAR2 DEFAULT NULL,
                                  p_c7    VARCHAR2 DEFAULT NULL,
                                  p_c8    VARCHAR2 DEFAULT NULL,
                                  p_c9    VARCHAR2 DEFAULT NULL,
                                  p_c10   VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;

  FUNCTION f_get_row2_col_sql_lhr_01(p_table VARCHAR2,
                                     p_cols  VARCHAR2,
                                     p_c0    VARCHAR2,
                                     p_c1    VARCHAR2 DEFAULT NULL,
                                     p_c2    VARCHAR2 DEFAULT NULL,
                                     p_c3    VARCHAR2 DEFAULT NULL,
                                     p_c4    VARCHAR2 DEFAULT NULL,
                                     p_c5    VARCHAR2 DEFAULT NULL,
                                     p_c6    VARCHAR2 DEFAULT NULL,
                                     p_c7    VARCHAR2 DEFAULT NULL,
                                     p_c8    VARCHAR2 DEFAULT NULL,
                                     p_c9    VARCHAR2 DEFAULT NULL,
                                     p_c10   VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;
  FUNCTION f_get_row2_col_sql_lhr_02(p_c1  VARCHAR2 DEFAULT NULL,
                                     p_c2  VARCHAR2 DEFAULT NULL,
                                     p_c3  VARCHAR2 DEFAULT NULL,
                                     p_c4  VARCHAR2 DEFAULT NULL,
                                     p_c5  VARCHAR2 DEFAULT NULL,
                                     p_c6  VARCHAR2 DEFAULT NULL,
                                     p_c7  VARCHAR2 DEFAULT NULL,
                                     p_c8  VARCHAR2 DEFAULT NULL,
                                     p_c9  VARCHAR2 DEFAULT NULL,
                                     p_c10 VARCHAR2 DEFAULT NULL,
                                     p_t   VARCHAR2) RETURN VARCHAR2;

  FUNCTION f_ToNumber_lhr(strNumber VARCHAR2) RETURN NUMBER;

  ------（28）该函数  得到平均值
  FUNCTION f_get_avg_lhr(p_fenzi     NUMBER,
                         p_fenmu     NUMBER,
                         p_precision NUMBER) RETURN VARCHAR2;

  --- 得到sql_trace的文件路径
  FUNCTION f_get_sql_trace_name_lhr RETURN VARCHAR2;

  FUNCTION F_GET_DEPENDENCIES_LHR(p_referenced_name VARCHAR2) RETURN VARCHAR2;

  ---- 得到url
  FUNCTION f_get_url_encode(url_string IN VARCHAR2,
                            charset    IN VARCHAR2 DEFAULT 'GB2312')
    RETURN VARCHAR2;

  ---返回正确的大小     入参为字节
  FUNCTION f_get_right_size_lhr(p_size      IN VARCHAR2,
                                p_precision number default 3) RETURN VARCHAR2;

  ------动态sql  可以通过函数来返回
  FUNCTION f_get_exec_sql_lhr(p_sql IN VARCHAR2) RETURN VARCHAR2;

  FUNCTION f_get_objectsize_lhr(p_owner          VARCHAR2,
                                p_segment_name   VARCHAR2,
                                p_partition_name VARCHAR2,
                                p_flag           NUMBER,
                                p_return_flag    NUMBER DEFAULT NULL)
    RETURN VARCHAR2;

  FUNCTION f_get_sql_id_lhr(p_sid NUMBER) RETURN VARCHAR2;

  FUNCTION f_get_sqltune_lhr(p_sqltext   CLOB,
                             p_user_name VARCHAR2 DEFAULT USER) RETURN CLOB;

  ---得到max+decode语句
  FUNCTION f_get_max_decode_lhr(p_tablename   VARCHAR2,
                                p_groupby_col VARCHAR2,
                                p_value_col   VARCHAR2) RETURN VARCHAR2;

  ------------------- 历史执行计划
  FUNCTION f_get_sqlplan_his_lhr(p_sqlid IN VARCHAR2,
                                 p_sid   NUMBER DEFAULT '',
                                 P_KEY   NUMBER DEFAULT '',
                                 P_FLAG  NUMBER DEFAULT 3)
    RETURN SYS_REFCURSOR;

  ---------------    第二部分： 存过         ------------------------

  --- ② 创建表 和注释
  PROCEDURE pro_create_table_lhr(p_user        VARCHAR2, -- 表的用户
                                 p_tablename   VARCHAR2, --表名
                                 p_tablenamecn VARCHAR2 DEFAULT NULL --表的解释
                                 );

  --- ④ 实现字符串转多列
  PROCEDURE pro_str_change_column_lhr(p_tablename VARCHAR2, --要转换的表名
                                      p_column    VARCHAR2, --要转换的列名
                                      p_spechar   VARCHAR2 --要转换的列名中的特殊字符，即分隔符
                                      );
  --- ④ 实现字符串转多行
  PROCEDURE p_str_change_row_lhr(p_tablename VARCHAR2, --要转换的表名
                                 p_column    VARCHAR2, --要转换的列名
                                 p_spechar   VARCHAR2 --要转换的列名中的特殊字符，即分隔符
                                 );
  -----⑥ 插入clob字段
  PROCEDURE pro_insert_clob_lhr(ASQL VARCHAR2, ALongVar1 CLOB);

  -----⑦   job 的执行频率检查 2013-06-09 16:50:00
  PROCEDURE P_JOB_repeat_interval_LHR(v_sql VARCHAR2);

  -----⑧ 删除历史数据
  PROCEDURE P_del_LHR(p_user   VARCHAR2,
                      p_table  VARCHAR2,
                      p_col    VARCHAR2,
                      p_tjdate VARCHAR2);
  --------⑨   展示表空间的情况
  PROCEDURE P_show_space_LHR(p_segname_1   IN VARCHAR2, -- 表名称
                             p_type_1      IN VARCHAR2 DEFAULT 'TABLE',
                             p_space       IN VARCHAR2 DEFAULT 'AUTO', --MANUAL
                             p_analyzed    IN VARCHAR2 DEFAULT 'Y', --N
                             p_partition_1 IN VARCHAR2 DEFAULT NULL,
                             p_owner_1     IN VARCHAR2 DEFAULT USER);

  --------- 收缩表空间
  PROCEDURE p_move_table_lhr(p_table    VARCHAR2,
                             p_owner    VARCHAR2 DEFAULT USER,
                             p_parallel NUMBER DEFAULT 1);
  --------- 移动表空间
  PROCEDURE p_move_tablespace_lhr(p_table      VARCHAR2,
                                  p_owner      VARCHAR2 DEFAULT USER,
                                  p_tablespace VARCHAR2 DEFAULT 'SDH_DATA');
  ----创建同义词
  PROCEDURE P_create_synonym_LHR(P_OWNER VARCHAR2, P_OBJECT_TYPE VARCHAR2);
  ------得到执行计划
  PROCEDURE p_sql_explain_lhr(stmt    VARCHAR2,
                              format  VARCHAR2 DEFAULT 'ADVANCED',
                              exponly BOOLEAN DEFAULT TRUE);
  ------修改分区名称
  PROCEDURE p_rename_partition_lhr(p_owner      VARCHAR2,
                                   p_table_name VARCHAR2,
                                   v_type       NUMBER DEFAULT 1 -- 1代表分区表，2代表分区索引
                                   );
  -------分析表
  PROCEDURE p_gather_table_stats_lhr(p_owner      VARCHAR2,
                                     p_table_name VARCHAR2,
                                     p_cascade    CHAR DEFAULT 'TRUE' --是否分析索引
                                     );
  --------执行动态sql
  PROCEDURE p_exec_sql_lhr(p_number NUMBER);

  ------------分析索引碎片

  PROCEDURE P_ANALYZE_INDEX_SATAS_LHR;
  PROCEDURE P_ANALYZE_INDEX_ERROR_LHR;

  -----------------将数据行转为列显示
  PROCEDURE p_print_table_lhr(p_query IN VARCHAR2);

  ----------------创建临时外部表查看告警事件
  PROCEDURE p_externaltable_lhr(p_tracename VARCHAR2,
                                P_directory VARCHAR2 DEFAULT 'DIR_ALERT_LHR');

  ---------------估算即将创建的索引大小?
  PROCEDURE p_create_index_size_lhr(p_sql VARCHAR2);

END pkg_utility_lhr;
/
CREATE OR REPLACE PACKAGE BODY pkg_utility_lhr IS

  ----------------------------    第一部分： 函数         ------------------------

  FUNCTION fun_get_index_column_lhr(p_owner      VARCHAR2,
                                    p_index_name VARCHAR2,
                                    p_table_name VARCHAR2) RETURN VARCHAR2 IS
    -----------------------------------------------------------------------------------
    -- Created on 2012/12/2 1:29:13 by lhr
    --Changed on2012/12/2 1:29:13 by lhr
    -- function： 该函数返回索引列
    -----------------------------------------------------------------------------------

    v_results VARCHAR2(4000);
    v_index   VARCHAR2(4000);
    v_temp    VARCHAR2(4000);
    v_counts  NUMBER;
    v_num     NUMBER := 0;
    v_counts1 NUMBER;
  BEGIN

    ----LOB类型的索引
    SELECT COUNT(1)
      INTO v_counts1
      FROM dba_indexes d
     WHERE d.index_name = p_index_name
       AND d.owner = p_owner
       AND d.index_type = 'LOB';

    IF v_counts1 > 0 THEN
      SELECT DIC.COLUMN_NAME
        INTO v_index
        FROM dba_lobs dic
       WHERE dic.INDEX_NAME = p_index_name
         AND dic.Table_Name = p_table_name
         AND dic.OWNER = p_owner;
      RETURN v_index;
    END IF;

    ----其它类型的索引
    SELECT COUNT(1)
      INTO v_counts
      FROM dba_ind_expressions dic
     WHERE dic.index_name = p_index_name
       AND dic.table_name = p_table_name
       AND dic.INDEX_OWNER = p_owner;

    IF v_counts > 0 THEN
      FOR cur IN (SELECT dic.*
                    FROM dba_ind_expressions dic
                   WHERE dic.index_name = p_index_name
                     AND dic.table_name = p_table_name
                     AND dic.INDEX_OWNER = p_owner

                  ) LOOP

        v_num := v_num + 1;
        IF v_num = v_counts THEN
          v_temp := cur.column_expression;
        ELSE

          v_temp := cur.column_expression || ',' || v_temp;

        END IF;

      END LOOP;

      BEGIN
        SELECT to_char(wmsys.wm_concat(dic.column_name))
          INTO v_index
          FROM dba_ind_columns dic
         WHERE dic.index_name = p_index_name
           AND dic.table_name = p_table_name
           AND dic.COLUMN_NAME NOT LIKE 'SYS%'
           AND dic.INDEX_OWNER = p_owner
         GROUP BY dic.index_name, dic.table_name;
      EXCEPTION
        WHEN OTHERS THEN
          v_results := v_temp;
          RETURN v_results;
      END;

      v_results := v_index || ',' || v_temp;

      RETURN v_results;

    ELSE

      SELECT to_char(wmsys.wm_concat(dic.column_name))
        INTO v_index
        FROM dba_ind_columns dic
       WHERE dic.index_name = p_index_name
         AND dic.table_name = p_table_name
         AND dic.INDEX_OWNER = p_owner
       GROUP BY dic.index_name, dic.table_name;
      RETURN v_index;
    END IF;

    RETURN v_results;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN v_index;
  END fun_get_index_column_lhr;

  ---------------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------------
  FUNCTION fun_long_to_char_lhr(in_rowid      ROWID,
                                in_owner      VARCHAR,
                                in_table_name VARCHAR,
                                in_column     VARCHAR2) RETURN VARCHAR AS
    text_c1 VARCHAR2(32767);
    --text_c1 clob;
    sql_cur VARCHAR2(2000);
    --
  BEGIN

    IF in_owner IS NULL THEN

      sql_cur := 'select ' || in_column || ' from ' || in_table_name ||
                 ' where rowid = ' || chr(39) || in_rowid || chr(39);
      -- dbms_output.put_line(sql_cur);

    ELSE

      sql_cur := 'select ' || in_column || ' from ' || in_owner || '.' ||
                 in_table_name || ' where rowid = ' || chr(39) || in_rowid ||
                 chr(39);
      -- dbms_output.put_line(sql_cur);

    END IF;
    EXECUTE IMMEDIATE sql_cur
      INTO text_c1;

    text_c1 := substr(text_c1, 1, 4000);
    RETURN text_c1;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN '';
  END fun_long_to_char_lhr;

  ---------------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------------
  ----④ 得到一个字符串中特殊字符的个数
  --- 11g可以使用：select REGEXP_COUNT(t.importance2,';',1) from    sjcl_10maxsize_table_lhr t;
  FUNCTION fun_getspecharcounts_lhr(p_string  IN VARCHAR2,
                                    p_spechar IN VARCHAR2) RETURN NUMBER AS
    v_count    NUMBER := 0;
    v_position NUMBER := 0; --特殊字符的位置
    -- v_Spechar  VARCHAR2(5);

  BEGIN
    ---没有这句如果入参为空就会陷入死循环
    IF p_string IS NULL THEN
      RETURN '';
    END IF;
    LOOP
      --找到特殊字符的位置
      SELECT instr(p_string, p_spechar, v_position + 1)
        INTO v_position
        FROM dual;

      --判断是否退出循环
      IF v_position >= length(p_string) THEN
        v_count := v_count + 1;
        EXIT;

      ELSIF v_position = 0 THEN
        EXIT;
      END IF;

      v_count := v_count + 1;
    END LOOP;

    RETURN v_count;
  END fun_getspecharcounts_lhr;

  ---------------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------------

  -----⑦ 该函数返回 比率
  FUNCTION fun_get_percent_lhr(p_fenzi     NUMBER,
                               p_fenmu     NUMBER,
                               p_precision NUMBER) RETURN VARCHAR2 IS

    RESULT    VARCHAR2(255);
    v_percent VARCHAR2(255);
  BEGIN

    IF p_fenzi IS NULL OR p_fenmu IS NULL THEN

      RETURN '';

    ELSIF p_fenzi = 0 OR p_fenmu = 0 THEN

      RETURN 0 || '%';

    ELSE
      v_percent := (p_fenzi / p_fenmu) * 100;
      RESULT := CASE
                  WHEN v_percent < 1 THEN
                   0 || round(v_percent, p_precision) || '%'
                  ELSE
                   round(v_percent, p_precision) || '%'
                END;

      SELECT decode(RESULT, '00%', '0%', RESULT) INTO RESULT FROM dual;

      RETURN RESULT;

    END IF;

  END fun_get_percent_lhr;

  ---------------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------------

  -----⑧ 该函数返回  一个对象的大小
  FUNCTION fun_get_objectsize_lhr(p_objectname VARCHAR2) RETURN VARCHAR2 IS
    -----------------------------------------------------------------------------------
    -- Created on 2012/9/3 13:00:21 by lhr
    --Changed on 2012/9/3 13:00:21 by lhr
    -- function：
    -----------------------------------------------------------------------------------
    RESULTS VARCHAR2(255);
  BEGIN
    SELECT SUM(bytes) / (1024 * 1024 * 1024)
      INTO RESULTS
      FROM user_segments
     WHERE segment_name = upper(p_objectname);

    IF RESULTS IS NULL THEN
      RETURN '表' || upper(p_objectname) || '不存在';

    ELSIF RESULTS < 1 THEN
      SELECT round(RESULTS * 1024, 3) INTO RESULTS FROM dual;
      IF RESULTS < 1 THEN
        RETURN 0 || RESULTS || ' M';
      ELSE

        RETURN RESULTS || ' M';
      END IF;
    ELSE
      RETURN round(RESULTS, 3) || ' G';
    END IF;

    RETURN RESULTS;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END fun_get_objectsize_lhr;

  ---------------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------------

  ------⑨ 该函数 返回一个秒转换为标准时间的字符串
  FUNCTION fun_get_total_time_lhr(p_total_seconds NUMBER,
                                  p_flag          VARCHAR2 DEFAULT 'S')
    RETURN VARCHAR2 DETERMINISTIC IS
    -----------------------------------------------------------------------------------
    -- Created on 2012/10/18 16:49:28 by lhr
    --Changed on 2015/9/3 16:49:28 by lhr
    -- function： 该函数 返回一个秒转换为标准时间
    -----------------------------------------------------------------------------------
    v_tmp           FLOAT(18);
    v_returns       VARCHAR2(200);
    V_total_seconds NUMBER := p_total_seconds;
  BEGIN

    IF p_total_seconds IS NULL OR p_total_seconds = 0 THEN

      RETURN 0 || '秒';

    END IF;

    ---传入的为天转换为秒
    IF UPPER(p_flag) = 'D' THEN
      V_total_seconds := p_total_seconds * 24 * 60 * 60;
    END IF;

    -- 首先处理日期

    --年
    v_tmp := trunc(V_total_seconds / (12 * 30 * 24 * 3600));

    IF v_tmp > 0 THEN
      v_returns := v_tmp || '年';

    END IF;

    ---月
    v_tmp := trunc(MOD(V_total_seconds, (12 * 30 * 24 * 3600)) /
                   (3600 * 24 * 30));

    IF v_tmp > 0 THEN
      v_returns := v_returns || v_tmp || '月';

    END IF;

    --天
    v_tmp := trunc(MOD(V_total_seconds, (3600 * 24 * 30)) / (3600 * 24));

    IF v_tmp > 0 THEN
      v_returns := v_returns || v_tmp || '天';

    END IF;

    --处理小时

    v_tmp := trunc(MOD(V_total_seconds, 3600 * 24) / 3600);

    IF v_tmp > 0 THEN
      v_returns := v_returns || v_tmp || '时';
    END IF;

    --处理分钟

    v_tmp := trunc(MOD(V_total_seconds, 3600) / 60);

    IF v_tmp > 0 THEN
      v_returns := v_returns || v_tmp || '分';
    END IF;

    --处理秒钟
    v_tmp := trunc(MOD(V_total_seconds, 60), 3);

    IF v_tmp > 0 AND v_tmp < 1 THEN
      v_returns := v_returns || '0' || v_tmp || '秒';

    ELSIF v_tmp >= 1 THEN
      v_returns := v_returns || v_tmp || '秒';
    ELSIF v_tmp = 0 AND v_returns IS NULL THEN
      v_returns := '0' || trunc(V_total_seconds, 6) || '秒';
    END IF;

    RETURN v_returns;

  END fun_get_total_time_lhr;

  ---------------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------------

  ------（19）该函数    得到 送给ods数据的提取格式
  FUNCTION fun_get_ods_string_lhr(p_table VARCHAR2) RETURN VARCHAR2 AS

    -----------------------------------------------------------------------------------
    -- Created on 2012/11/07 11:33:07 by lhr
    --Changed on 2012/11/07 11:33:07 by lhr
    -- function：
    /*        drop table  XB_COLS_LHR;
    Create Table XB_COLS_LHR(
    id number,
    lie varchar2(4000)
    );*/
    -----------------------------------------------------------------------------------
    v_return    VARCHAR2(4000);
    v_return1   VARCHAR2(4000);
    v_return2   VARCHAR2(4000);
    v_max       VARCHAR2(255);
    v_table     VARCHAR2(255) := upper(p_table);
    v_date_type VARCHAR2(255) := 'YYYY-MM-DD HH24:MI:SS';
    PRAGMA AUTONOMOUS_TRANSACTION;

  BEGIN

    SELECT COUNT(1) INTO v_max FROM cols c WHERE c.table_name = v_table;

    IF v_max >= 150 THEN

      SELECT 'SELECT  REPLACE(REPLACE(' ||
             REPLACE(REPLACE(to_char(wmsys.wm_concat(decode(c.column_id,
                                                            v_max,
                                                            c.column_name ||
                                                            '||chr(5)',
                                                            c.column_name ||
                                                            '||chr(5)||'))),
                             ',',
                             ''),
                     '，',
                     ',') ---modify

        INTO v_return1
        FROM (SELECT t.table_name,
                     decode(t.data_type,
                            upper('DATE'),
                            'to_char(t.' || t.column_name || '，''' ||
                            v_date_type || ''')',
                            't.' || t.column_name) column_name,
                     t.column_id
                FROM cols t
               WHERE t.table_name = v_table ---modify
                 AND t.column_id <= 120
               ORDER BY t.column_id) c;

      EXECUTE IMMEDIATE 'truncate table XB_COLS_LHR';
      INSERT INTO xb_cols_lhr VALUES (1, v_return1);
      COMMIT;

      SELECT REPLACE(REPLACE(to_char(wmsys.wm_concat(decode(c.column_id,
                                                            v_max,
                                                            c.column_name ||
                                                            '||chr(5)',
                                                            c.column_name ||
                                                            '||chr(5)||'))),
                             ',',
                             ''),
                     '，',
                     ',') || ',chr(10),''''),chr(13),'''') FROM ' ||
             v_table || ' t;' ---modify

        INTO v_return2
        FROM (SELECT t.table_name,
                     decode(t.data_type,
                            upper('DATE'),
                            'to_char(t.' || t.column_name || '，''' ||
                            v_date_type || ''')',
                            't.' || t.column_name) column_name,
                     t.column_id
                FROM cols t
               WHERE t.table_name = v_table ---modify
                 AND t.column_id > 120
               ORDER BY t.column_id) c;

      INSERT INTO xb_cols_lhr VALUES (2, v_return2);
      COMMIT;

      v_return := '见表 select * from XB_COLS_LHR; ';

    ELSE

      SELECT 'SELECT  REPLACE(REPLACE(' ||
             REPLACE(REPLACE(to_char(wmsys.wm_concat(decode(c.column_id,
                                                            v_max,
                                                            c.column_name ||
                                                            '||chr(5)',
                                                            c.column_name ||
                                                            '||chr(5)||'))),
                             ',',
                             ''),
                     '，',
                     ',') || ',chr(10),''''),chr(13),'''') FROM ' ||
             v_table || ' t;' ---modify

        INTO v_return
        FROM (SELECT t.table_name,
                     decode(t.data_type,
                            upper('DATE'),
                            'to_char(t.' || t.column_name || '，''' ||
                            v_date_type || ''')',
                            't.' || t.column_name) column_name,
                     t.column_id
                FROM cols t
               WHERE t.table_name = v_table ---modify
               ORDER BY t.column_id) c;
    END IF;

    RETURN v_return;
  END fun_get_ods_string_lhr;

  ---------------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------------

  ------（19--1）该函数    得到 送给ods数据的提取格式  ,可以定义分隔符
  FUNCTION fun_get_ods_string2_lhr(p_table     VARCHAR2,
                                   p_separator VARCHAR2 DEFAULT '|')
    RETURN VARCHAR2 AS

    -----------------------------------------------------------------------------------
    -- Created on 2012/11/15 11:33:07 by lhr
    --Changed on 2012/11/15 11:33:07 by lhr
    -- function：
    /*        drop table  XB_COLS_LHR;
    Create Table XB_COLS_LHR(
    id number,
    lie varchar2(4000)
    );*/
    -----------------------------------------------------------------------------------
    v_return    VARCHAR2(4000);
    v_return1   VARCHAR2(4000);
    v_return2   VARCHAR2(4000);
    v_max       VARCHAR2(255);
    v_table     VARCHAR2(255) := upper(p_table);
    v_date_type VARCHAR2(255) := 'YYYY-MM-DD HH24:MI:SS';
    PRAGMA AUTONOMOUS_TRANSACTION;

  BEGIN

    SELECT COUNT(1) INTO v_max FROM cols c WHERE c.table_name = v_table;

    IF v_max >= 150 THEN

      SELECT 'SELECT  REPLACE(REPLACE(' ||
             REPLACE(REPLACE(to_char(wmsys.wm_concat(decode(c.column_id,
                                                            v_max,
                                                            c.column_name ||
                                                            '||''' ||
                                                            p_separator,
                                                            c.column_name ||
                                                            '||''' ||
                                                            p_separator ||
                                                            '''||'))),
                             ',',
                             ''),
                     '，',
                     ',') ---modify

        INTO v_return1
        FROM (SELECT t.table_name,
                     decode(t.data_type,
                            upper('DATE'),
                            'to_char(t.' || t.column_name || '，''' ||
                            v_date_type || ''')',
                            't.' || t.column_name) column_name,
                     t.column_id
                FROM cols t
               WHERE t.table_name = v_table ---modify
                 AND t.column_id <= 120
               ORDER BY t.column_id) c;

      EXECUTE IMMEDIATE 'truncate table XB_COLS_LHR';
      INSERT INTO xb_cols_lhr VALUES (1, v_return1);
      COMMIT;

      SELECT REPLACE(REPLACE(to_char(wmsys.wm_concat(decode(c.column_id,
                                                            v_max,
                                                            c.column_name ||
                                                            '||''' ||
                                                            p_separator || '''',
                                                            c.column_name ||
                                                            '||''' ||
                                                            p_separator ||
                                                            '''||'))),
                             ',',
                             ''),
                     '，',
                     ',') || ',chr(10),''''),chr(13),'''') FROM ' ||
             v_table || ' t;' ---modify

        INTO v_return2
        FROM (SELECT t.table_name,
                     decode(t.data_type,
                            upper('DATE'),
                            'to_char(t.' || t.column_name || '，''' ||
                            v_date_type || ''')',
                            't.' || t.column_name) column_name,
                     t.column_id
                FROM cols t
               WHERE t.table_name = v_table ---modify
                 AND t.column_id > 120
               ORDER BY t.column_id) c;

      INSERT INTO xb_cols_lhr VALUES (2, v_return2);
      COMMIT;

      v_return := '见表 select * from XB_COLS_LHR; ';

    ELSE

      SELECT 'SELECT  REPLACE(REPLACE(' ||
             REPLACE(REPLACE(to_char(wmsys.wm_concat(decode(c.column_id,
                                                            v_max,

                                                            c.column_name ||
                                                            '||''' ||
                                                            p_separator || '''',

                                                            c.column_name ||
                                                            '||''' ||
                                                            p_separator ||
                                                            '''||'))),
                             ',',
                             ''),
                     '，',
                     ',') || ',chr(10),''''),chr(13),'''') FROM ' ||
             v_table || ' t;' ---modify

        INTO v_return
        FROM (SELECT t.table_name,
                     decode(t.data_type,
                            upper('DATE'),
                            'to_char(t.' || t.column_name || '，''' ||
                            v_date_type || ''')',
                            't.' || t.column_name) column_name,
                     t.column_id
                FROM cols t
               WHERE t.table_name = v_table ---modify
               ORDER BY t.column_id) c;
    END IF;

    RETURN v_return;
  END fun_get_ods_string2_lhr;

  ---------------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------------

  ------（20）该函数    得到 spool 数据的提取格式
  FUNCTION fun_get_spool_string_lhr(p_table VARCHAR2) RETURN VARCHAR2 AS

    -----------------------------------------------------------------------------------
    -- Created on 2012/11/07 11:33:07 by lhr
    --Changed on 2012/11/07 11:33:07 by lhr
    -- function：
    -----------------------------------------------------------------------------------
    v_return  VARCHAR2(32767);
    v_return1 VARCHAR2(32767);
    v_return2 VARCHAR2(32767);
    v_max     VARCHAR2(255);
    v_table   VARCHAR2(255) := upper(p_table);
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN

    SELECT COUNT(1) INTO v_max FROM cols c WHERE c.table_name = v_table;

    ---列
    SELECT 'SELECT ' || 0 || '||'',' ||
           to_char(wmsys.wm_concat(decode(t.column_id,
                                          v_max,
                                          '''||''' || t.column_name || '''',
                                          '''||''' || t.column_name ||
                                          '''||''')

                                   )) || ' lie FROM dual a '
      INTO v_return1
      FROM (SELECT c.column_name, c.column_id
              FROM cols c
             WHERE c.table_name = v_table
             ORDER BY c.column_id) t;

    --表
    SELECT 'UNION SELECT replace(replace( ' || 1 || '||'',' ||
           to_char(wmsys.wm_concat(decode(t.column_id,
                                          v_max,
                                          ''' || REPLACE(' || t.column_name ||
                                          ','','',''、'')',
                                          ''' || REPLACE(' || t.column_name ||
                                          ','','',''、'')||'''))) ||
           ',chr(10),''''),chr(13),'''')  from ' || v_table
      INTO v_return2
      FROM (SELECT c.column_name, c.column_id
              FROM cols c
             WHERE c.table_name = v_table
             ORDER BY c.column_id) t;

    IF length(v_return2) > 2000 THEN

      EXECUTE IMMEDIATE 'truncate table XB_COLS_LHR';
      INSERT INTO xb_cols_lhr
      VALUES
        (1, 'SELECT substr(t.lie, 3) FROM   (' || v_return1);
      INSERT INTO xb_cols_lhr VALUES (2, v_return2 || ') t;');
      COMMIT;

      v_return := '见表 select * from XB_COLS_LHR; ';

    ELSE

      v_return := 'SELECT substr(t.lie, 3) FROM   (' || v_return1 ||
                  v_return2 || ') t;';
    END IF;
    RETURN v_return;
  END fun_get_spool_string_lhr;

  FUNCTION fun_get_spool_string2_lhr(p_table VARCHAR2) RETURN VARCHAR2 AS

    -----------------------------------------------------------------------------------
    -- Created on 2012/11/07 11:33:07 by lhr
    --Changed on 2015/07/29 11:33:07 by lhr
    -- function：
    -----------------------------------------------------------------------------------
    v_return  VARCHAR2(32767);
    v_return1 VARCHAR2(32767);
    v_return2 VARCHAR2(32767);
    v_return3 VARCHAR2(32767);
    v_max     VARCHAR2(255);
    v_table   VARCHAR2(255) := upper(p_table);
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN

    SELECT COUNT(1) INTO v_max FROM cols c WHERE c.table_name = v_table;

    ---列
    SELECT 'SELECT ' || 0 || ',' ||
           to_char(wmsys.wm_concat('''' || t.column_name || '''' || ' ' ||
                                   t.column_name)) || '  FROM dual a '
      INTO v_return1
      FROM (SELECT c.column_name, c.column_id
              FROM cols c
             WHERE c.table_name = v_table
             ORDER BY c.column_id) t;

    --表
    SELECT 'UNION all SELECT ' || 1 || ',' ||
           to_char(wmsys.wm_concat(decode(t.data_type,
                                          'CLOB',
                                          'dbms_lob.substr(' ||
                                          t.column_name || ',1,4000) ' ||
                                          t.column_name,
                                          t.column_name || '||''''' || ' ' ||
                                          t.column_name))) || ' from  ' ||
           v_table

      INTO v_return2
      FROM (SELECT c.column_name, c.column_id, c.DATA_TYPE
              FROM cols c
             WHERE c.table_name = v_table
             ORDER BY c.column_id) t;

    --合并
    SELECT 'SELECT ' || to_char(wmsys.wm_concat(t.column_name)) ||
           ' from (' || v_return1 || v_return2 || ') t;'

      INTO v_return3
      FROM (SELECT c.column_name, c.column_id, c.DATA_TYPE
              FROM cols c
             WHERE c.table_name = v_table
             ORDER BY c.column_id) t;

    IF length(v_return2) > 2000 THEN

      EXECUTE IMMEDIATE 'truncate table XB_COLS_LHR';
      INSERT INTO xb_cols_lhr VALUES (1, v_return3);
      COMMIT;

      v_return := '见表 select * from XB_COLS_LHR; ';

    ELSE

      v_return := v_return3;
    END IF;
    RETURN v_return;
  END fun_get_spool_string2_lhr;

  ---------------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------------
  ------（21）该函数    将 interval day to second 转换为 易读格式
  FUNCTION fun_get_interval_date_lhr(p_date INTERVAL DAY TO SECOND)
    RETURN VARCHAR2 AS

    -----------------------------------------------------------------------------------
    -- Created on 2012/11/13 11:36:09 by lhr
    --Changed on 2012/11/13 11:36:09 by lhr
    -- function：
    -----------------------------------------------------------------------------------

    v_return  VARCHAR2(255);
    v_day     VARCHAR2(255) := extract(DAY FROM p_date);
    v_hour    VARCHAR2(255) := extract(hour FROM p_date);
    v_minute  VARCHAR2(255) := extract(minute FROM p_date);
    v_second  VARCHAR2(255) := extract(SECOND FROM p_date);
    v_second1 VARCHAR2(255);
  BEGIN

    v_second1 := CASE
                   WHEN v_second < 1 AND v_second > 0 THEN
                    '0' || round(v_second, 3) || '秒'
                   ELSE
                    round(v_second, 3) || '秒'
                 END;

    IF v_day != 0 THEN
      RETURN v_day || '天' || v_hour || '时' || v_minute || '分' || v_second1;
    ELSIF v_hour != 0 THEN
      RETURN v_hour || '时' || v_minute || '分' || v_second1;
    ELSIF v_minute != 0 THEN
      RETURN v_minute || '分' || v_second1;
    ELSIF v_second != 0 THEN
      RETURN v_second1;
    END IF;
    RETURN v_return;
  END fun_get_interval_date_lhr;

  ---------------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------------
  ------（22）该函数    将字符串分割成多行   返回一个table 集合
  FUNCTION fun_get_tab_lhr(p_string    IN VARCHAR2,
                           p_separator IN VARCHAR2 DEFAULT ',')
    RETURN type_tab_lhr IS
    -----------------------------------------------------------------------------------
    -- Created on 2012/11/14 20:37:51 by lhr
    --Changed on 2012/11/14 20:37:51 by lhr
    -- function：单个函数返回多行单列
    --create or replace type type_tab_lhr is table of varchar2(2000);
    -- 测试用例:SELECT * FROM TABLE(pkg_lhr_all_package.fun_get_tab_lhr('3,6,5,23,756'));
    --select * from the(select pkg_lhr_all_package.fun_get_tab_lhr('3,6,5,23,756') from dual);
    -----------------------------------------------------------------------------------
    v_tab           type_tab_lhr := type_tab_lhr(); --返回内存表
    v_num           NUMBER := 1; --行数 和 分隔符出现的次数
    v_value         VARCHAR2(255);
    v_position      NUMBER;
    v_last_position NUMBER := 0;

  BEGIN

    --有一个参数为空就退出函数
    IF p_string IS NULL THEN
      RETURN NULL;
    END IF;

    --利用循环来插入到表类型中
    LOOP

      --找到分隔符的位置

      SELECT decode(instr(p_string, p_separator, 1, v_num),
                    0,
                    length(p_string) + 1,
                    instr(p_string, p_separator, 1, v_num))
        INTO v_position
        FROM dual;

      ---提取需要的值
      v_value         := substr(p_string,
                                v_last_position + 1,
                                v_position - v_last_position - 1);
      v_last_position := v_position;

      ---对内存表进行赋值
      v_tab.extend;
      v_tab(v_num) := v_value;

      v_num := v_num + 1;

      IF v_position >= length(p_string) THEN
        EXIT;
      END IF;

    END LOOP;

    RETURN v_tab;
  EXCEPTION
    WHEN OTHERS THEN
      v_tab.delete;
  END fun_get_tab_lhr;

  ------（23）该函数    将得到 视图 的创建语句
  FUNCTION fun_get_create_view_lhr(p_name VARCHAR2, p_length NUMBER)
    RETURN VARCHAR2 AS

    sql_cur VARCHAR2(255);
    text_c1 VARCHAR2(4000);

    /* SELECT DBMS_METADATA.GET_DDL('VIEW', 'VW_ADDRESS_LHR', 'TELANT')
    FROM   DUAL;*/
  BEGIN

    IF p_length > 3900 THEN

      RETURN '';

    ELSE
      sql_cur := 'select text from user_views where VIEW_NAME = ' ||
                 chr(39) || p_name || chr(39);
      -- dbms_output.put_line(sql_cur);

      EXECUTE IMMEDIATE sql_cur
        INTO text_c1;

      text_c1 := 'CREATE OR REPLACE VIEW ' || p_name || ' as ' ||
                 substr(text_c1, 1, 4000) || ';';

      RETURN text_c1;
    END IF;
    RETURN text_c1;

  END fun_get_create_view_lhr;

  ------------------------------------------------------------------------------------------------------    ------（24）该函数    将得到 表 的创建语句
  FUNCTION fun_get_create_table_lhr(p_owner VARCHAR2, p_name VARCHAR2)
    RETURN VARCHAR2 AS

    v_return VARCHAR2(4000);
  BEGIN
    SELECT REPLACE(REPLACE(REPLACE(DBMS_METADATA.GET_DDL('TABLE',
                                                         p_name,
                                                         SCHEMA => p_owner),
                                   '"' || p_owner || '"',
                                   ' '),
                           '.',
                           ' '),
                   '"',
                   ' ') || ';'
      INTO v_return
      FROM dual;

    RETURN v_return;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN '';
  END;

  ---------------------------------------------------------------------------------------

  -----⑦ 该函数返回 正确的精度 防止  .45 00 等
  FUNCTION fun_get_right_number_lhr(p_number    NUMBER,
                                    p_precision NUMBER,
                                    p_str       VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2 DETERMINISTIC IS

    RESULT VARCHAR2(255);
  BEGIN

    RESULT := CASE
                WHEN p_number = '00' THEN
                 '0' || p_str
                WHEN p_number < 1 AND p_number > 0 THEN
                 '0' || round(p_number, p_precision) || p_str
                WHEN p_number < 0 THEN
                 '0' || p_str
                ELSE
                 round(p_number, p_precision) || p_str

              END;

    RETURN RESULT;

  EXCEPTION
    WHEN OTHERS THEN
      RETURN round(p_number, p_precision) || p_str;

  END fun_get_right_number_lhr;

  ------------------------------------------------------------------------------------------------------   比较 2个表的列     2013-06-06 18:27:33

  /*     drop table xb_table_compare_lhr;
  Create Table xb_table_compare_lhr(
  column_id number,
  table1_column varchar2(255),
  table1_column_type  varchar2(255),
  table2_column varchar2(255),
  table2_column_type  varchar2(255),
  column_name_is_same number,
  column_type_is_same    number
  );

  select pkg_utility_lhr.f_get_table_is_same('ff','XT_FTTH_PI_RES_LHR',2) from dual;
  select * from xb_table_compare_lhr ;*/

  FUNCTION f_get_table_is_same(p_user1  VARCHAR2,
                               p_table1 VARCHAR2,
                               p_user2  VARCHAR2,
                               p_table2 VARCHAR2,
                               is_order NUMBER --是否按照列的顺序来比较
                               ) RETURN VARCHAR2

   AS
    v_max_cols  NUMBER;
    r_compare   xb_table_compare_lhr%ROWTYPE;
    v_num       NUMBER := 1;
    p_max_table VARCHAR2(255);
    p_max_user  VARCHAR2(255);
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    EXECUTE IMMEDIATE 'truncate table xb_table_compare_lhr';
    --最大列数
    SELECT CASE
             WHEN (SELECT COUNT(1)
                     FROM dba_tab_cols d
                    WHERE d.TABLE_NAME = upper(p_table1)
                      AND d.owner = upper(p_user1)) >
                  (SELECT COUNT(1)
                     FROM dba_tab_cols d
                    WHERE d.TABLE_NAME = upper(p_table2)
                      AND d.owner = upper(p_user2)) THEN
              (SELECT COUNT(1)
                 FROM dba_tab_cols d
                WHERE d.TABLE_NAME = upper(p_table1)
                  AND d.owner = upper(p_user1))
             ELSE
              (SELECT COUNT(1)
                 FROM dba_tab_cols d
                WHERE d.TABLE_NAME = upper(p_table2)
                  AND d.owner = upper(p_user2))
           END,
           CASE
             WHEN (SELECT COUNT(1)
                     FROM dba_tab_cols d
                    WHERE d.TABLE_NAME = upper(p_table1)
                      AND d.owner = upper(p_user1)) >
                  (SELECT COUNT(1)
                     FROM dba_tab_cols d
                    WHERE d.TABLE_NAME = upper(p_table2)
                      AND d.owner = upper(p_user2)) THEN
              p_table1
             ELSE
              p_table2
           END,
           CASE
             WHEN (SELECT COUNT(1)
                     FROM dba_tab_cols d
                    WHERE d.TABLE_NAME = upper(p_table1)
                      AND d.owner = upper(p_user1)) >
                  (SELECT COUNT(1)
                     FROM dba_tab_cols d
                    WHERE d.TABLE_NAME = upper(p_table2)
                      AND d.owner = upper(p_user2)) THEN
              upper(p_user1)
             ELSE
              upper(p_user2)
           END
      INTO v_max_cols, p_max_table, p_max_user
      FROM dual;

    IF is_order = 1 THEN

      FOR cur IN 1 .. v_max_cols LOOP

        BEGIN
          SELECT d.COLUMN_ID,
                 d.COLUMN_NAME,
                 d.DATA_TYPE || '(' || d.DATA_LENGTH || ')'
            INTO r_compare.column_id,
                 r_compare.table1_column,
                 r_compare.table1_column_type
            FROM dba_tab_cols d
           WHERE d.TABLE_NAME = upper(p_table1)
             AND d.COLUMN_ID = v_num
             AND d.owner = upper(p_user1);
        EXCEPTION
          WHEN OTHERS THEN
            r_compare.table1_column      := '';
            r_compare.table1_column_type := '';
        END;

        BEGIN
          SELECT d.COLUMN_ID,
                 d.COLUMN_NAME,
                 d.DATA_TYPE || '(' || d.DATA_LENGTH || ')'
            INTO r_compare.column_id,
                 r_compare.table2_column,
                 r_compare.table2_column_type
            FROM dba_tab_cols d
           WHERE d.TABLE_NAME = upper(p_table2)
             AND d.COLUMN_ID = v_num
             AND d.owner = upper(p_user2);
        EXCEPTION
          WHEN OTHERS THEN
            r_compare.table2_column      := '';
            r_compare.table2_column_type := '';
        END;

        IF r_compare.table1_column_type = r_compare.table2_column_type THEN
          r_compare.column_type_is_same := 1;
        ELSE
          r_compare.column_type_is_same := 0;
        END IF;

        IF r_compare.table1_column = r_compare.table2_column THEN
          r_compare.column_name_is_same := 1;
        ELSE
          r_compare.column_name_is_same := 0;
        END IF;

        INSERT INTO xb_table_compare_lhr
          (column_id,
           table1_column,
           table1_column_type,
           table2_column,
           table2_column_type,
           column_name_is_same,
           column_type_is_same)
        VALUES
          (r_compare.column_id,
           r_compare.table1_column,
           r_compare.table1_column_type,
           r_compare.table2_column,
           r_compare.table2_column_type,
           r_compare.column_name_is_same,
           r_compare.column_type_is_same);
        COMMIT;

        v_num := v_num + 1;
      END LOOP;
    ELSIF is_order IN (0, 2) THEN

      FOR cur IN (SELECT *
                    FROM dba_tab_cols d
                   WHERE d.TABLE_NAME = upper(p_max_table)
                     AND d.owner = upper(p_max_user)) LOOP

        BEGIN
          SELECT d.COLUMN_ID,
                 d.COLUMN_NAME,
                 d.DATA_TYPE || '(' || d.DATA_LENGTH || ')'
            INTO r_compare.column_id,
                 r_compare.table1_column,
                 r_compare.table1_column_type
            FROM dba_tab_cols d
           WHERE d.TABLE_NAME = upper(p_table1)
             AND d.COLUMN_NAME = cur.column_name

             AND d.owner = upper(p_user1);
        EXCEPTION
          WHEN OTHERS THEN
            r_compare.column_id          := '';
            r_compare.table1_column      := '';
            r_compare.table1_column_type := '';
        END;

        BEGIN
          SELECT d.COLUMN_ID,
                 d.COLUMN_NAME,
                 d.DATA_TYPE || '(' || d.DATA_LENGTH || ')'
            INTO r_compare.column_id,
                 r_compare.table2_column,
                 r_compare.table2_column_type
            FROM dba_tab_cols d
           WHERE d.TABLE_NAME = upper(p_table2)
             AND d.COLUMN_NAME = cur.column_name
             AND d.owner = upper(p_user2);
        EXCEPTION
          WHEN OTHERS THEN
            r_compare.column_id          := '';
            r_compare.table2_column      := '';
            r_compare.table2_column_type := '';
        END;

        IF r_compare.table1_column_type = r_compare.table2_column_type THEN
          r_compare.column_type_is_same := 1;
        ELSE
          r_compare.column_type_is_same := 0;
        END IF;

        IF r_compare.table1_column = r_compare.table2_column THEN
          r_compare.column_name_is_same := 1;
        ELSE
          r_compare.column_name_is_same := 0;
        END IF;

        INSERT INTO xb_table_compare_lhr
          (column_id,
           table1_column,
           table1_column_type,
           table2_column,
           table2_column_type,
           column_name_is_same,
           column_type_is_same)
        VALUES
          ('',
           r_compare.table1_column,
           r_compare.table1_column_type,
           r_compare.table2_column,
           r_compare.table2_column_type,
           r_compare.column_name_is_same,
           r_compare.column_type_is_same);
        COMMIT;

        v_num := v_num + 1;
      END LOOP;
    END IF;

    RETURN 'select * from xb_table_compare_lhr;';

  EXCEPTION
    WHEN OTHERS THEN

      RETURN 'select * from xb_table_compare_lhr;';

  END f_get_table_is_same;

  ------------------------------------------------------------------------------------------------------  得到行转列的语句  2013-06-07 17:58:52

  -----------------------------------------------------------------------------------
  -- Created on 2013-06-07 17:58:52 by lhr
  --Changed on 2013-06-07 17:58:52 by lhr
  -- function：
  /*  Create Table xb_row2col_lhr( a varchar2(255),b number);*/

  /* SELECT pkg_utility_lhr.f_get_row2_col_sql_lhr('XT_FTTH_PI_RES_LHR_03',
  'serviceinstancecode',
  'accesscode',
  'resinsid',
  'resspec',
  'resservinsresinsassoc_id')
  FROM   dual;*/
  -----------------------------------------------------------------------------------
  FUNCTION f_get_row2_col_sql_lhr(p_table VARCHAR2,
                                  p_cols  VARCHAR2,
                                  p_c0    VARCHAR2,
                                  p_c1    VARCHAR2 DEFAULT NULL,
                                  p_c2    VARCHAR2 DEFAULT NULL,
                                  p_c3    VARCHAR2 DEFAULT NULL,
                                  p_c4    VARCHAR2 DEFAULT NULL,
                                  p_c5    VARCHAR2 DEFAULT NULL,
                                  p_c6    VARCHAR2 DEFAULT NULL,
                                  p_c7    VARCHAR2 DEFAULT NULL,
                                  p_c8    VARCHAR2 DEFAULT NULL,
                                  p_c9    VARCHAR2 DEFAULT NULL,
                                  p_c10   VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2 AS

    v_num    NUMBER := 1;
    v_num1   NUMBER := 0;
    v_sql1   VARCHAR2(4000);
    v_sql2   VARCHAR2(4000);
    v_sql3   VARCHAR2(4000);
    v_sql4   VARCHAR2(4000);
    v_sql5   VARCHAR2(4000) := 'select t.' || p_c0;
    v_return VARCHAR2(4000);
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN

    EXECUTE IMMEDIATE 'truncate table xb_row2col_lhr';
    EXECUTE IMMEDIATE 'insert into xb_row2col_lhr SELECT  t.' || p_cols ||
                      ', count(1)
FROM  ' || p_table || ' t' || ' group by t.' || p_cols;
    COMMIT;

    v_sql1 := '( ' ||
              pkg_utility_lhr.f_get_row2_col_sql_lhr_01(p_table,
                                                        p_cols,
                                                        p_c0,
                                                        p_c1,
                                                        p_c2,
                                                        p_c3,
                                                        p_c4,
                                                        p_c5,
                                                        p_c6,
                                                        p_c7,
                                                        p_c8,
                                                        p_c9,
                                                        p_c10);

    SELECT COUNT(1) INTO v_num1 FROM xb_row2col_lhr;
    FOR cur IN (SELECT * FROM xb_row2col_lhr) LOOP

      IF v_num1 = v_num THEN

        v_sql2 := v_sql2 || v_sql1 || '''' || cur.a || ''') t' || v_num || '  ';
        v_sql3 := v_sql3 || ' t.' || p_c0 || '=' || 't' || v_num || '.' || p_c0 ||
                  '(+);';

      ELSE

        v_sql2 := v_sql2 || v_sql1 || '''' || cur.a || ''') t' || v_num ||
                  '  ,';
        v_sql3 := v_sql3 || ' t.' || p_c0 || '=' || 't' || v_num || '.' || p_c0 ||
                  '(+) and ';

      END IF;

      v_sql5 := v_sql5 ||
                pkg_utility_lhr.f_get_row2_col_sql_lhr_02(p_c1,
                                                          p_c2,
                                                          p_c3,
                                                          p_c4,
                                                          p_c5,
                                                          p_c6,
                                                          p_c7,
                                                          p_c8,
                                                          p_c9,
                                                          p_c10,
                                                          't' || v_num);

      v_num := v_num + 1;
    END LOOP;

    v_sql4 := '(select distinct t.' || p_c0 || ' from ' || p_table ||
              ' t) t,';

    v_return := v_sql5 || ' from ' || v_sql4 || v_sql2 || 'where ' ||
                v_sql3;
    RETURN v_return;

  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE || ',' || SQLERRM);
  END f_get_row2_col_sql_lhr;

  FUNCTION f_get_row2_col_sql_lhr_01(p_table VARCHAR2,
                                     p_cols  VARCHAR2,
                                     p_c0    VARCHAR2,
                                     p_c1    VARCHAR2 DEFAULT NULL,
                                     p_c2    VARCHAR2 DEFAULT NULL,
                                     p_c3    VARCHAR2 DEFAULT NULL,
                                     p_c4    VARCHAR2 DEFAULT NULL,
                                     p_c5    VARCHAR2 DEFAULT NULL,
                                     p_c6    VARCHAR2 DEFAULT NULL,
                                     p_c7    VARCHAR2 DEFAULT NULL,
                                     p_c8    VARCHAR2 DEFAULT NULL,
                                     p_c9    VARCHAR2 DEFAULT NULL,
                                     p_c10   VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2 AS
    v_sql1 VARCHAR2(4000);
  BEGIN

    IF p_c10 IS NOT NULL THEN
      v_sql1 := 'select t.' || p_c0 || ', t.' || p_c10 || ',   t.' || p_c9 ||
                ',   t.' || p_c8 || ',   t.' || p_c7 || ',   t.' || p_c6 ||
                ',   t.' || p_c5 || ',   t.' || p_c4 || ',   t.' || p_c3 ||
                ',   t.' || p_c2 || ',   t.' || p_c1 || ' from ' || p_table ||
                ' t where t.' || p_cols || '=';

    ELSIF p_c9 IS NOT NULL THEN
      v_sql1 := 'select t.' || p_c0 || ', t.' || p_c9 || ',   t.' || p_c8 ||
                ',   t.' || p_c7 || ',   t.' || p_c6 || ',   t.' || p_c5 ||
                ',   t.' || p_c4 || ',   t.' || p_c3 || ',   t.' || p_c2 ||
                ',   t.' || p_c1 || ' from ' || p_table || ' t where t.' ||
                p_cols || '=';
    ELSIF p_c8 IS NOT NULL THEN
      v_sql1 := 'select t.' || p_c0 || ', t.' || p_c8 || ',   t.' || p_c7 ||
                ',   t.' || p_c6 || ',   t.' || p_c5 || ',   t.' || p_c4 ||
                ',   t.' || p_c3 || ',   t.' || p_c2 || ',   t.' || p_c1 ||
                ' from ' || p_table || '   t where t.' || p_cols || '=';
    ELSIF p_c7 IS NOT NULL THEN
      v_sql1 := 'select t.' || p_c0 || ', t.' || p_c7 || ',   t.' || p_c6 ||
                ',   t.' || p_c5 || ',   t.' || p_c4 || ',   t.' || p_c3 ||
                ',   t.' || p_c2 || ',   t.' || p_c1 || ' from ' || p_table ||
                '   t where t.' || p_cols || '=';
    ELSIF p_c6 IS NOT NULL THEN
      v_sql1 := 'select t.' || p_c0 || ', t.' || p_c6 || ',   t.' || p_c5 ||
                ',   t.' || p_c4 || ',   t.' || p_c3 || ',   t.' || p_c2 ||
                ',   t.' || p_c1 || ' from ' || p_table || '   t where t.' ||
                p_cols || '=';
    ELSIF p_c5 IS NOT NULL THEN
      v_sql1 := 'select t.' || p_c0 || ', t.' || p_c5 || ',   t.' || p_c4 ||
                ',   t.' || p_c3 || ',   t.' || p_c2 || ',   t.' || p_c1 ||
                ' from ' || p_table || '   t where t.' || p_cols || '=';
    ELSIF p_c4 IS NOT NULL THEN
      v_sql1 := 'select t.' || p_c0 || ', t.' || 't.' || p_c4 || ',   t.' || p_c3 ||
                ',   t.' || p_c2 || ',   t.' || p_c1 || ' from ' || p_table ||
                '   t where t.' || p_cols || '=';
    ELSIF p_c3 IS NOT NULL THEN
      v_sql1 := 'select t.' || p_c0 || ', t.' || p_c3 || ',   t.' || p_c2 ||
                ',   t.' || p_c1 || ' from ' || p_table || '   t where t.' ||
                p_cols || '=';
    ELSIF p_c2 IS NOT NULL THEN
      v_sql1 := 'select t.' || p_c0 || ', t.' || p_c2 || ',   t.' || p_c1 ||
                ' from ' || p_table || '   t where t.' || p_cols || '=';
    ELSIF p_c1 IS NOT NULL THEN
      v_sql1 := 'select t.' || p_c0 || ', t.' || p_c1 || ' from ' ||
                p_table || '   t where t.' || p_cols || '=';

    END IF;

    RETURN v_sql1;

  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE || ',' || SQLERRM);
  END f_get_row2_col_sql_lhr_01;

  FUNCTION f_get_row2_col_sql_lhr_02(p_c1  VARCHAR2 DEFAULT NULL,
                                     p_c2  VARCHAR2 DEFAULT NULL,
                                     p_c3  VARCHAR2 DEFAULT NULL,
                                     p_c4  VARCHAR2 DEFAULT NULL,
                                     p_c5  VARCHAR2 DEFAULT NULL,
                                     p_c6  VARCHAR2 DEFAULT NULL,
                                     p_c7  VARCHAR2 DEFAULT NULL,
                                     p_c8  VARCHAR2 DEFAULT NULL,
                                     p_c9  VARCHAR2 DEFAULT NULL,
                                     p_c10 VARCHAR2 DEFAULT NULL,
                                     p_t   VARCHAR2) RETURN VARCHAR2 AS

    v_sql1 VARCHAR2(4000);
  BEGIN

    IF p_c10 IS NOT NULL THEN
      v_sql1 := ',' || p_t || '.' || p_c10 || ', ' || p_t || '.' || p_c9 || ', ' || p_t || '.' || p_c8 ||
                ', t.' || p_c7 || ', ' || p_t || '.' || p_c6 || ', ' || p_t || '.' || p_c5 ||
                ', t.
' || p_c4 || ', ' || p_t || '.' || p_c3 || ', ' || p_t || '.' || p_c2 ||
                ', t.
' || p_c1;

    ELSIF p_c9 IS NOT NULL THEN
      v_sql1 := ',' || p_t || '.' || p_c9 || ', ' || p_t || '.' || p_c8 || ', ' || p_t || '.' || p_c7 || ', ' || p_t || '.' || p_c6 || ', ' || p_t || '.' || p_c5 || ', ' || p_t || '.' || p_c4 || ', ' || p_t || '.' || p_c3 || ', ' || p_t || '.' || p_c2 || ', ' || p_t || '.' || p_c1;
    ELSIF p_c8 IS NOT NULL THEN
      v_sql1 := ',' || p_t || '.' || p_c8 || ', ' || p_t || '.' || p_c7 || ', ' || p_t || '.' || p_c6 || ', ' || p_t || '.' || p_c5 || ', ' || p_t || '.' || p_c4 || ', ' || p_t || '.' || p_c3 || ', ' || p_t || '.' || p_c2 || ', ' || p_t || '.' || p_c1;
    ELSIF p_c7 IS NOT NULL THEN
      v_sql1 := ',' || p_t || '.' || p_c7 || ', ' || p_t || '.' || p_c6 || ', ' || p_t || '.' || p_c5 || ', ' || p_t || '.' || p_c4 || ', ' || p_t || '.' || p_c3 || ', ' || p_t || '.' || p_c2 || ', ' || p_t || '.' || p_c1;
    ELSIF p_c6 IS NOT NULL THEN
      v_sql1 := ',' || p_t || '.' || p_c6 || ', ' || p_t || '.' || p_c5 || ', ' || p_t || '.' || p_c4 || ', ' || p_t || '.' || p_c3 || ', ' || p_t || '.' || p_c2 || ', ' || p_t || '.' || p_c1;
    ELSIF p_c5 IS NOT NULL THEN
      v_sql1 := ',' || p_t || '.' || p_c5 || ', ' || p_t || '.' || p_c4 || ', ' || p_t || '.' || p_c3 || ', ' || p_t || '.' || p_c2 || ', ' || p_t || '.' || p_c1;
    ELSIF p_c4 IS NOT NULL THEN
      v_sql1 := ',' || p_t || '.' || ' ' || p_t || '.' || p_c4 || ', ' || p_t || '.' || p_c3 || ', ' || p_t || '.' || p_c2 || ', ' || p_t || '.' || p_c1;
    ELSIF p_c3 IS NOT NULL THEN
      v_sql1 := ',' || p_t || '.' || p_c3 || ', ' || p_t || '.' || p_c2 || ', ' || p_t || '.' || p_c1;
    ELSIF p_c2 IS NOT NULL THEN
      v_sql1 := ',' || p_t || '.' || p_c2 || ', ' || p_t || '.' || p_c1;
    ELSIF p_c1 IS NOT NULL THEN
      v_sql1 := ',' || p_t || '.' || p_c1;

    END IF;

    RETURN v_sql1;

  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE || ',
' || SQLERRM);
  END f_get_row2_col_sql_lhr_02;

  FUNCTION f_ToNumber_lhr(strNumber VARCHAR2) RETURN NUMBER IS
  BEGIN
    RETURN(to_number(strNumber));
  EXCEPTION
    WHEN OTHERS THEN
      RETURN('转换错误，非数字');
  END;

  ---------------------------------------------------------------------------------------

  -----⑦ 该函数返回 得到平均值
  FUNCTION f_get_avg_lhr(p_fenzi     NUMBER,
                         p_fenmu     NUMBER,
                         p_precision NUMBER) RETURN VARCHAR2 IS

    RESULT    VARCHAR2(255);
    v_percent VARCHAR2(255);
  BEGIN

    IF p_fenzi IS NULL OR p_fenmu IS NULL OR p_fenzi = 0 OR p_fenmu = 0 THEN

      RETURN '0';

    ELSE
      v_percent := (p_fenzi / p_fenmu);
      RESULT := CASE
                  WHEN v_percent < 1 THEN
                   '0' || round(v_percent, p_precision)
                  ELSE
                   round(v_percent, p_precision)
                END;

      RETURN RESULT;

    END IF;

  END f_get_avg_lhr;

  FUNCTION f_get_sql_trace_name_lhr RETURN VARCHAR2 AS

    v_return VARCHAR2(255);
  BEGIN

    SELECT d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
           p.spid || '.trc' trace_file_name
      INTO v_return
      FROM (SELECT p.spid
              FROM v$mystat m, v$session s, v$process p
             WHERE m.statistic# = '1'
               AND s.sid = m.sid
               AND p.addr = s.paddr) p,
           (SELECT t.instance
              FROM v$thread t, v$parameter v
             WHERE v.name = 'thread'
               AND (v.value = '0' OR to_char(t.thread#) = v.VALUE)) i,
           (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;

    RETURN v_return;

  END f_get_sql_trace_name_lhr;

  FUNCTION F_GET_DEPENDENCIES_LHR(p_referenced_name VARCHAR2) RETURN VARCHAR2 AS

    v_return VARCHAR2(255);

    v_count NUMBER;

    v_tmp VARCHAR2(255) := upper(p_referenced_name);
  BEGIN

    LOOP
      SELECT COUNT(1)
        INTO v_count
        FROM user_dependencies u
       WHERE u.referenced_name = v_tmp;

      v_return := v_tmp;
      EXIT WHEN v_count = 0;

      SELECT u.name
        INTO v_tmp
        FROM user_dependencies u
       WHERE u.referenced_name = v_tmp
         AND rownum = 1;

    END LOOP;

    RETURN v_return;

  END F_GET_DEPENDENCIES_LHR;

  ------------------------------------------------------------------------------------------------------
  FUNCTION f_get_url_encode(url_string IN VARCHAR2,
                            charset    IN VARCHAR2 DEFAULT 'GB2312')
    RETURN VARCHAR2 AS
  BEGIN
    RETURN utl_url.escape(url_string, TRUE, charset); -- note use of TURE
  END f_get_url_encode;

  ----------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------
  FUNCTION f_get_right_size_lhr(p_size      IN VARCHAR2,
                                p_precision number default 3) RETURN VARCHAR2 AS

    v_results VARCHAR2(255);

  BEGIN

    SELECT CASE
             WHEN p_size < 1024 THEN
              p_size || 'BYTES'
             WHEN P_SIZE >= 1024 AND P_SIZE < 1048576 THEN
              ROUND(P_SIZE / 1024, p_precision) || 'KB'
             WHEN P_SIZE >= 1048576 AND P_SIZE < 1073741824 THEN
              ROUND(P_SIZE / 1048576, p_precision) || 'M'
             WHEN P_SIZE >= 1073741824 AND P_SIZE < 1099511627776 THEN
              ROUND(P_SIZE / 1073741824, p_precision) || 'G'
             WHEN P_SIZE >= 1099511627776 AND P_SIZE < 1125899906842624 THEN
              ROUND(P_SIZE / 1099511627776, p_precision) || 'T'
           END AS SIZES
      INTO v_results
      FROM dual;

    RETURN v_results;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END f_get_right_size_lhr;

  -------------------------------------------------------------------------------------------------------------------------------------------------------------------
  ------动态sql  可以通过函数来返回
  FUNCTION f_get_exec_sql_lhr(p_sql IN VARCHAR2) RETURN VARCHAR2 AS

    v_results VARCHAR2(255);

  BEGIN

    EXECUTE IMMEDIATE p_sql
      INTO v_results;

    RETURN v_results;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN '';
  END;

  --------------------    返回对象的大小

  FUNCTION f_get_objectsize_lhr(p_owner          VARCHAR2,
                                p_segment_name   VARCHAR2,
                                p_partition_name VARCHAR2,
                                p_flag           NUMBER,
                                p_return_flag    NUMBER DEFAULT NULL)
    RETURN VARCHAR2 IS
    -----------------------------------------------------------------------------------
    -- Created on 2012/9/3 13:00:21 by lhr
    --Changed on 2012/9/3 13:00:21 by lhr
    -- function：

    ---  1  INDEX 、TABLE
    ---  2  TABLE PARTITION 、INDEX PARTITION
    ---   3  TABLE SUBPARTITION      、INDEX PARTITION
    --  -----------------------------------------------------------------------------------
    v_RESULTS VARCHAR2(255);
  BEGIN

    IF p_flag = 1 THEN
      SELECT SUM(d.BYTES)
        INTO v_RESULTS
        FROM dba_segments d
       WHERE d.segment_name = p_segment_name
         AND d.owner = p_owner
       GROUP BY d.segment_name;

    ELSIF p_flag = 2 THEN

      SELECT SUM(d.BYTES)
        INTO v_RESULTS
        FROM dba_segments d,
             (SELECT b.index_owner       table_owner,
                     b.index_name        table_name,
                     b.partition_name,
                     b.subpartition_name
                FROM dba_ind_subpartitions b
              UNION ALL
              SELECT a.table_owner,
                     table_name,
                     partition_name,
                     subpartition_name
                FROM dba_tab_subpartitions a) dp
       WHERE d.segment_name = p_segment_name
         AND d.partition_name = dp.subpartition_name
         AND dp.table_owner = p_owner
         AND dp.table_name = p_segment_name
         AND dp.partition_name = p_partition_name
         AND d.owner = p_owner
       GROUP BY d.segment_name;

    ELSIF p_flag = 3 THEN
      SELECT SUM(d.BYTES)
        INTO v_RESULTS
        FROM dba_segments d
       WHERE d.segment_name = p_segment_name
         AND d.partition_name = p_partition_name
         AND d.owner = p_owner
       GROUP BY d.segment_name;
    END IF;

    IF p_return_flag IS NULL THEN
      RETURN v_RESULTS;

    ELSE
      RETURN pkg_utility_lhr.f_get_right_size_lhr(v_RESULTS);
    END IF;

    RETURN v_RESULTS;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN '';
  END f_get_objectsize_lhr;

  --------------------   根据会话的sid得到执行的sql_id

  FUNCTION f_get_sql_id_lhr(p_sid NUMBER) RETURN VARCHAR2 IS
    -----------------------------------------------------------------------------------
    -- Created on 2014-03-05 23:26:59 by lhr
    --Changed on 2014-03-05 23:26:59 by lhr
    -- function：

    --  -----------------------------------------------------------------------------------
    v_RESULTS VARCHAR2(255);
  BEGIN

    SELECT a.SQL_ID
      INTO v_RESULTS
      FROM (SELECT t.SID, t.SQL_ID
              FROM v$session_longops t
             WHERE t.SQL_ID IS NOT NULL
            UNION ALL
            SELECT b.SESSION_ID, b.SQL_ID
              FROM v$active_session_history b
             WHERE b.SQL_ID IS NOT NULL) a
     WHERE a.SID = p_sid
       AND EXISTS (SELECT 1 FROM v$sql b WHERE b.SQL_ID = a.SQL_ID)
       AND rownum = 1;

    RETURN v_RESULTS;
  EXCEPTION
    WHEN OTHERS THEN

      RETURN '';
  END f_get_sql_id_lhr;

  --------------------   根据sql语句得到该sql语句的优化建议

  FUNCTION f_get_sqltune_lhr(p_sqltext   CLOB,
                             p_user_name VARCHAR2 DEFAULT USER) RETURN CLOB IS
    -----------------------------------------------------------------------------------
    -- Created on 2014-03-13 14:51:38 by lhr
    --Changed on 2014-03-13 14:51:38 by lhr
    -- function：

    --  -----------------------------------------------------------------------------------
    v_RESULTS      CLOB;
    v_my_task_name VARCHAR2(255);
    v_user_name    VARCHAR2(255) := upper(p_user_name);
    PRAGMA AUTONOMOUS_TRANSACTION; --自治事务
  BEGIN

    v_my_task_name := dbms_sqltune.create_tuning_task(sql_text  => p_sqltext,
                                                      user_name => v_user_name);
    dbms_sqltune.execute_tuning_task(v_my_task_name);

    v_RESULTS := 'SELECT dbms_sqltune.report_tuning_task(''' ||
                 v_my_task_name || ''') FROM DUAL;';

    SELECT dbms_sqltune.report_tuning_task(v_my_task_name)
      INTO v_RESULTS
      FROM DUAL;

    RETURN v_RESULTS;

  EXCEPTION
    WHEN OTHERS THEN

      RETURN '';
  END f_get_sqltune_lhr;

  ---得到max+decode语句
  FUNCTION f_get_max_decode_lhr(p_tablename   VARCHAR2,
                                p_groupby_col VARCHAR2,
                                p_value_col   VARCHAR2) RETURN VARCHAR2 IS
    -----------------------------------------------------------------------------------
    -- Created on 2014-03-20 11:32:32 by lhr
    --Changed on 2014-03-20 11:32:32 by lhr
    -- function：  得到max+decode语句
    --select pkg_utility_lhr.f_get_max_decode_lhr(p_tablename => 'user_objects', p_groupby_col =>'object_type' , p_value_col =>'object_id' ) from dual;
    --  -----------------------------------------------------------------------------------
    v_RESULTS VARCHAR2(255);

    PRAGMA AUTONOMOUS_TRANSACTION; --自治事务
  BEGIN

    v_RESULTS := 'SELECT ''max(decode(' || p_groupby_col || ',''''''||' ||
                 p_groupby_col || '||'''''',' || p_value_col || ')) ''||''' ||
                 '  "''||' || p_groupby_col || '||''",''
 FROM   (SELECT DISTINCT ' || p_groupby_col || ' FROM ' ||
                 p_tablename || ');';
    RETURN v_RESULTS;

  EXCEPTION
    WHEN OTHERS THEN
      RETURN '';
  END f_get_max_decode_lhr;

  ------------------- 历史执行计划

  FUNCTION f_get_sqlplan_his_lhr(p_sqlid IN VARCHAR2,
                                 p_sid   NUMBER DEFAULT '',
                                 P_KEY   NUMBER DEFAULT '',
                                 P_FLAG  NUMBER DEFAULT 3)
    RETURN SYS_REFCURSOR IS

    cur_sys SYS_REFCURSOR;

  BEGIN

    IF P_FLAG = 1 THEN
      OPEN cur_sys FOR
        SELECT v.sql_plan_hash_value,
               v.plan_line_id id,
               lpad(' ', 2 * (LEVEL - 1)) || v.plan_operation || ' ' ||
               v.plan_options Operation,
               v.plan_object_owner,
               v.plan_object_name,
               v.plan_object_type,
               v.plan_cardinality "Rows",
               v.output_rows,
               v.plan_cost,
               v.plan_partition_start,
               v.plan_partition_stop,
               pkg_utility_lhr.fun_get_total_time_lhr(v.plan_time) plan_time,
               v.plan_cpu_cost,
               v.plan_io_cost,
               v.plan_temp_space,
               v.starts,
               v.physical_read_bytes,
               v.physical_write_bytes,
               v.sql_text,
               V.SQL_FULLTEXT,
               v.username,
               v.os_user,
               v.elapsed_time2,
               v.sql_exec_start,
               v.key,
               v.in_date,
               V.CHILD_NUMBER,
               V.OBJECT#,
               V.OBJECT_ALIAS,
               V.OPTIMIZER,
               V.SEARCH_COLUMNS,
               V.FILTER_PREDICATES,
               V.ACCESS_PREDICATES,
               V.PROJECTION,
               V.OTHER_XML,
               V.BINDS_XML
          FROM (SELECT a.*,
                       b.elapsed_time2,
                       b.sql_text,
                       B.SQL_FULLTEXT,
                       b.username,
                       b.os_user,
                       B.BINDS_XML
                  FROM xb_sql_plan_monitor_lhr a, xb_sql_monitor_lhr b
                 WHERE a.sql_monitor_id = b.id
                   AND a.sql_id = p_sqlid) v
         START WITH v.plan_parent_id IS NULL
        CONNECT BY PRIOR v.plan_line_id = v.plan_parent_id
               AND v.key = PRIOR v.key
         ORDER BY v.in_date, v.key, v.plan_line_id;

    ELSIF P_FLAG = 2 THEN
      OPEN cur_sys FOR
        SELECT v.sql_plan_hash_value,
               v.plan_line_id id,
               lpad(' ', 2 * (LEVEL - 1)) || v.plan_operation || ' ' ||
               v.plan_options Operation,
               v.plan_object_owner,
               v.plan_object_name,
               v.plan_object_type,
               v.plan_cardinality "Rows",
               v.output_rows,
               v.plan_cost,
               v.plan_partition_start,
               v.plan_partition_stop,
               pkg_utility_lhr.fun_get_total_time_lhr(v.plan_time) plan_time,
               v.plan_cpu_cost,
               v.plan_io_cost,
               v.plan_temp_space,
               v.starts,
               v.physical_read_bytes,
               v.physical_write_bytes,
               v.sql_text,
               V.SQL_FULLTEXT,
               v.username,
               v.os_user,
               v.elapsed_time2,
               v.sql_exec_start,
               v.key,
               v.in_date,
               V.CHILD_NUMBER,
               V.OBJECT#,
               V.OBJECT_ALIAS,
               V.OPTIMIZER,
               V.SEARCH_COLUMNS,
               V.FILTER_PREDICATES,
               V.ACCESS_PREDICATES,
               V.PROJECTION,
               V.OTHER_XML,
               V.BINDS_XML
          FROM (SELECT a.*,
                       b.elapsed_time2,
                       b.sql_text,
                       B.SQL_FULLTEXT,
                       b.username,
                       b.os_user,
                       B.BINDS_XML
                  FROM xb_sql_plan_monitor_lhr a, xb_sql_monitor_lhr b
                 WHERE a.sql_monitor_id = b.id
                   AND a.sql_id = p_sqlid
                   AND a.sid = p_sid) v
         START WITH v.plan_parent_id IS NULL
        CONNECT BY PRIOR v.plan_line_id = v.plan_parent_id
               AND v.key = PRIOR v.key
         ORDER BY v.in_date, v.key, v.plan_line_id;
    ELSIF P_FLAG = 3 THEN
      OPEN cur_sys FOR
        SELECT v.sql_plan_hash_value,
               v.plan_line_id id,
               lpad(' ', 2 * (LEVEL - 1)) || v.plan_operation || ' ' ||
               v.plan_options Operation,
               v.plan_object_owner,
               v.plan_object_name,
               v.plan_object_type,
               v.plan_cardinality "Rows",
               v.output_rows,
               v.plan_cost,
               v.plan_partition_start,
               v.plan_partition_stop,
               pkg_utility_lhr.fun_get_total_time_lhr(v.plan_time) plan_time,
               v.plan_cpu_cost,
               v.plan_io_cost,
               v.plan_temp_space,
               v.starts,
               v.physical_read_bytes,
               v.physical_write_bytes,
               v.sql_text,
               V.SQL_FULLTEXT,
               v.username,
               v.os_user,
               v.elapsed_time2,
               v.sql_exec_start,
               v.key,
               v.in_date,
               V.CHILD_NUMBER,
               V.OBJECT#,
               V.OBJECT_ALIAS,
               V.OPTIMIZER,
               V.SEARCH_COLUMNS,
               V.FILTER_PREDICATES,
               V.ACCESS_PREDICATES,
               V.PROJECTION,
               V.OTHER_XML,
               V.BINDS_XML
          FROM (SELECT a.*,
                       b.elapsed_time2,
                       b.sql_text,
                       B.SQL_FULLTEXT,
                       b.username,
                       b.os_user,
                       B.BINDS_XML
                  FROM xb_sql_plan_monitor_lhr a, xb_sql_monitor_lhr b
                 WHERE a.sql_monitor_id = b.id
                   AND a.sql_id = p_sqlid
                   AND a.sid = p_sid
                   AND A.KEY = P_KEY) v
         START WITH v.plan_parent_id IS NULL
        CONNECT BY PRIOR v.plan_line_id = v.plan_parent_id
               AND v.key = PRIOR v.key
         ORDER BY v.in_date, v.key, v.plan_line_id;
    END IF;

    RETURN cur_sys;

  END f_get_sqlplan_his_lhr;

  ------------------------------    第二部分： 存过         ------------------------

  ---------------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------------
  ---存过2   创建表

  PROCEDURE pro_create_table_lhr(p_user        VARCHAR2,
                                 p_tablename   VARCHAR2,
                                 p_tablenamecn VARCHAR2 DEFAULT NULL) AS
    -----------------------------------------------------------------------------------
    -- Created on 2012/7/30 10:04:17 by lhr
    --Changed on 2012/7/30 10:04:17 by lhr
    -- function： 该存过
    /*drop table xb_lhr_tab_cols  ;
    Create Table xb_lhr_tab_cols (
    id       NUMBER(18) not null,
    table_NAME VARCHAR2(255)  not null,
    column_name VARCHAR2(255)  not null,
    column_type VARCHAR2(255)   not null,
    column_name_cn VARCHAR2(255),
    nullable VARCHAR2(255) check (nullable in('Y','N','是','否')),
    comments VARCHAR2(255)
    );
    */
    -----------------------------------------------------------------------------------
    v_ctsql0 VARCHAR2(4000) := 'Create table  ' || p_user || '.' ||
                               p_tablename || ' (  ' || chr(13);
    v_ctsql  VARCHAR2(4000);
    v_maxrow NUMBER(18);
    v_count  NUMBER(18);
    --EXCEPTION raise_application_exception;
  BEGIN

    ---判断表是否存在

    SELECT COUNT(1)
      INTO v_count
      FROM dba_tables t
     WHERE t.table_name = upper(p_tablename)
       AND t.owner = upper(p_user);
    IF v_count >= 1 THEN
      EXECUTE IMMEDIATE 'drop table ' || p_user || '.' || p_tablename;
    END IF;

    --表中行数
    SELECT MAX(id)
      INTO v_maxrow
      FROM xb_lhr_tab_cols t
     WHERE upper(t.table_name) = upper(p_tablename);

    --创建表
    FOR cur IN (SELECT decode(t.id,
                              v_maxrow,
                              (t.column_name || '  ' || t.column_type),
                              (t.column_name || '  ' || t.column_type || ',' ||
                              chr(13))) row_sql
                  FROM xb_lhr_tab_cols t
                 WHERE upper(t.table_name) = upper(p_tablename)
                 ORDER BY t.id) LOOP
      v_ctsql := v_ctsql || cur.row_sql;

    END LOOP;

    v_ctsql := v_ctsql0 || v_ctsql || '  ) ';
    EXECUTE IMMEDIATE upper(v_ctsql);

    --列注释
    FOR cur IN (SELECT decode(t.comments,
                              NULL,
                              decode(t.nullable,
                                     NULL,
                                     ('comment on column ' || p_user || '.' ||
                                     t.table_name || '.' || t.column_name ||
                                     ' is ''' || t.column_name_cn || ''''),
                                     ('comment on column ' || p_user || '.' ||
                                     t.table_name || '.' || t.column_name ||
                                     ' is ''' || t.column_name_cn ||
                                     '；is_null: ' || t.nullable || '''')),
                              decode(t.nullable,
                                     NULL,
                                     ('comment on column ' || p_user || '.' ||
                                     t.table_name || '.' || t.column_name ||
                                     ' is ''' || t.column_name_cn || ':' ||
                                     t.comments || ''''),
                                     ('comment on column ' || p_user || '.' ||
                                     t.table_name || '.' || t.column_name ||
                                     ' is ''' || t.column_name_cn || ':' ||
                                     t.comments || '；is_null: ' || t.nullable || ''''))) coc_sql
                  FROM xb_lhr_tab_cols t
                 WHERE upper(t.table_name) = upper(p_tablename)) LOOP

      EXECUTE IMMEDIATE cur.coc_sql;

    END LOOP;

    --表注释
    v_ctsql := 'comment on table ' || p_user || '.' || p_tablename ||
               ' is ' || '''' || p_tablenamecn || '''';
    EXECUTE IMMEDIATE v_ctsql;

    ---添加非空约束
    FOR cur IN (SELECT 'ALTER TABLE ' || p_user || '.' || t.table_name ||
                       ' MODIFY ' || t.column_name || ' NOT NULL' null_sql
                  FROM xb_lhr_tab_cols t
                 WHERE upper(t.table_name) = upper(p_tablename)
                   AND t.nullable IN ('N', '否')) LOOP

      EXECUTE IMMEDIATE cur.null_sql;

    END LOOP;

    dbms_output.put_line('表已正确创建!!');

  EXCEPTION
    WHEN no_data_found THEN
      dbms_output.put_line('没找到任何数据!!');
    WHEN too_many_rows THEN
      dbms_output.put_line('找到多行数据，建议使用游标!!');
    WHEN OTHERS THEN
      dbms_output.put_line(' other errors！！' || SQLCODE || ',' || SQLERRM ||
                           '  v_ctsql:' || chr(13) || v_ctsql);
  END pro_create_table_lhr;

  --------------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------------
  ---存过4

  PROCEDURE pro_str_change_column_lhr(p_tablename VARCHAR2, --要转换的表名
                                      p_column    VARCHAR2, --要转换的列名
                                      p_spechar   VARCHAR2 --要转换的列名中的特殊字符，即分隔符
                                      ) AS

    -----------------------------------------------------------------------------------
    -- Created on 2012/8/17 19:05:49 by lhr
    --Changed on 2012/8/17 19:05:49 by lhr
    -- function：实现字符串转多列
    ---注意要分割的字符串的格式：6；24；36；72；9999；     最后必须加上分隔符
    -- 最终结果在： select * from xb_str_to_col_lhr;
    -----------------------------------------------------------------------------------
    v_count    NUMBER(18);
    v_sql      VARCHAR2(4000);
    v_col_num  NUMBER(18);
    v_i        NUMBER(18) := 1;
    v_col_name VARCHAR2(255);
    v_str      VARCHAR2(255) := '(' || p_column || '  varchar2(255),';
  BEGIN

    --判断  xb_str_to_col_lhr 是否存在
    SELECT COUNT(1)
      INTO v_count
      FROM user_tables u
     WHERE u.table_name = upper('xb_str_to_col_lhr');

    IF v_count = 1 THEN

      EXECUTE IMMEDIATE 'drop table xb_str_to_col_lhr';

    END IF;

    --找出分隔符的个数
    v_sql := ' SELECT MAX(pkg_utility_lhr.fun_getspecharcounts_lhr(' ||
             p_column || ',''' || p_spechar || '''))  FROM  ' ||
             p_tablename;
    EXECUTE IMMEDIATE v_sql
      INTO v_col_num;

    --循环拼装要创建表的字符串
    LOOP

      v_col_name := 'c' || v_i;

      IF v_i = v_col_num THEN

        v_str := v_str || v_col_name || ' varchar2(255),rowid_o rowid)';
      ELSE

        v_str := v_str || v_col_name || ' varchar2(255),';
      END IF;

      v_i := v_i + 1;

      EXIT WHEN v_i > v_col_num;

    END LOOP;

    v_sql := 'CREATE TABLE xb_str_to_col_lhr' || v_str;

    EXECUTE IMMEDIATE v_sql;

    --建立索引
    /*     v_sql := ' CREATE INDEX IDX_str_' || p_column ||
             '_lhr on xb_str_to_col_lhr(' || p_column || ')  ';
    EXECUTE IMMEDIATE v_sql;*/
    v_sql := 'insert into xb_str_to_col_lhr(' || p_column ||
             ',rowid_o ) select ' || p_column || ',rowid from ' ||
             p_tablename;
    EXECUTE IMMEDIATE v_sql;
    COMMIT;

    ---更新要转换的列
    v_sql := ' UPDATE xb_str_to_col_lhr  t  SET    t.c1 = (SELECT substr(' ||
             p_column || ', 1, instr(' || p_column || ', ''' || p_spechar ||
             ''', 1, 1) - 1)  FROM  xb_str_to_col_lhr a  where t.rowid=a.rowid)';

    EXECUTE IMMEDIATE v_sql;
    COMMIT;

    ---循环更新列
    v_i := 2;
    LOOP

      v_col_name := 'c' || v_i;

      v_sql := 'update xb_str_to_col_lhr t set t.
' || v_col_name || '=(
SELECT substr(' || p_column || ',
instr(' || p_column || ',''' || p_spechar || ''', 1, ' ||
               (v_i - 1) || ') + 1,
instr(' || p_column || ',''' || p_spechar || ''', 1, ' || v_i || ') -
instr(' || p_column || ',''' || p_spechar || ''', 1, ' ||
               (v_i - 1) ||
               ') - 1)
FROM   xb_str_to_col_lhr a where t.rowid=a.rowid)';
      EXECUTE IMMEDIATE v_sql;
      COMMIT;
      v_i := v_i + 1;

      EXIT WHEN v_i > v_col_num;

    END LOOP;

  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE || ',' || SQLERRM);
      dbms_output.put_line(v_sql);
  END pro_str_change_column_lhr;

  ------------------------------------------------------------------------------------------------------
  PROCEDURE p_str_change_row_lhr(p_tablename VARCHAR2, --要转换的表名
                                 p_column    VARCHAR2, --要转换的列名
                                 p_spechar   VARCHAR2 --要转换的列名中的特殊字符，即分隔符
                                 ) AS

    -----------------------------------------------------------------------------------
    -- Created on 2014-04-08 15:00:27 by lhr
    --Changed on2014-04-08 15:00:27 by lhr
    -- function：实现字符串转多行
    ---注意要分割的字符串的格式：6；24；36；72；9999；     最后必须加上分隔符
    -- 最终结果在： select * from xb_str_to_row_lhr;
    -----------------------------------------------------------------------------------
    v_position  NUMBER := 0;
    v_position2 NUMBER := 0;
    v_length    NUMBER := 0;
    TYPE rc IS REF CURSOR; --动态游标
    cur_rc rc;

    v_c VARCHAR2(4000);
  BEGIN

    EXECUTE IMMEDIATE 'truncate table xb_str_to_row_lhr';

    OPEN cur_rc FOR 'SELECT ' || p_column || '  FROM ' || p_tablename;
    LOOP

      FETCH cur_rc
        INTO v_c;

      v_position := 0;
      LOOP
        EXECUTE IMMEDIATE ' SELECT CASE
                           WHEN instr(''' || v_c || ''',
                                      ''' ||
                          p_spechar || ''',' || (v_position + 1) ||
                          ' ) = 0 THEN
                            length(''' || v_c || ''')
                           ELSE
                            instr(''' || v_c ||
                          ''',''' || p_spechar || ''',' || (v_position + 1) || ')
                       END
                FROM   dual'
          INTO v_position2;

        EXECUTE IMMEDIATE ' INSERT INTO xb_str_to_row_lhr
                    VALUES
                        (substr(''' || v_c || ''',
                               ' ||
                          (v_position + 1) || ',
                                ' ||
                          (v_position2 - 1 - v_position) || '),
                       ''' || v_c || ''')';

        EXECUTE IMMEDIATE 'select  length(''' || v_c || ''') from dual'
          INTO v_length;

        v_position := v_position2;
        EXIT WHEN v_position >= v_length;

        COMMIT;
      END LOOP;

      COMMIT;

      EXIT WHEN cur_rc%NOTFOUND;

    END LOOP;
    CLOSE cur_rc; --关闭游标

  END p_str_change_row_lhr;

  ------------------------------------------------------------------------------------------------------ 插入clob字段

  PROCEDURE pro_insert_clob_lhr(ASQL VARCHAR2, ALongVar1 CLOB) AS

    -- ASQL：带参数的INSERT　INTO　语句
    -- ALongVar1 : 参数值，大于4000字节
    --
    --exec    pro_insert_clob_lhr('insert into aa values(:1)',  '大于4000字节');

  BEGIN
    EXECUTE IMMEDIATE ASQL
      USING ALongVar1;
  END pro_insert_clob_lhr;

  ------------------------------------------------------------------------------------------------------    job 的执行频率检查   2013-06-09 16:50:00

  PROCEDURE P_JOB_repeat_interval_LHR(v_sql VARCHAR2) AS
    L_start_date  TIMESTAMP;
    l_next_date   TIMESTAMP;
    l_return_date TIMESTAMP;
    --v_sql varchar2(255):='FREQ=WEEKLY;INTERVAL=1;BYDAY=FRI;BYHOUR=03;BYMINUTE=30;BYSECOND=30';

  BEGIN
    l_start_date  := trunc(SYSTIMESTAMP);
    l_return_date := l_start_date;
    FOR ctr IN 1 .. 10 LOOP
      dbms_scheduler.evaluate_calendar_string(v_sql,
                                              l_start_date,
                                              l_return_date,
                                              l_next_date);

      dbms_output.put_line('Next Run on: ' ||
                           to_char(l_next_date, 'mm/dd/yyyy hh24:mi:ss'));

      l_return_date := l_next_date;

    END LOOP;
  END P_JOB_repeat_interval_LHR;

  -----------------------------------------------------------------------------------
  -- Created on 2013-06-21 17:16:57 by lhr
  --Changed on 2013-06-21 17:16:57 by lhr
  -- function：   删除本月统计过的数据
  -----------------------------------------------------------------------------------
  PROCEDURE P_del_LHR(p_user   VARCHAR2,
                      p_table  VARCHAR2,
                      p_col    VARCHAR2,
                      p_tjdate VARCHAR2) AS

    v_sql VARCHAR2(4000);
    v_num NUMBER;
  BEGIN
    v_sql := ' SELECT COUNT(1) FROM  ' || p_user || '.' || p_table ||
             ' t  WHERE  t.' || p_col || ' = ''' || p_tjdate || '''
                 ';
    EXECUTE IMMEDIATE v_sql
      INTO v_num;

    IF v_num >= 0 THEN
      v_sql := 'DELETE FROM  ' || p_user || '.' || p_table || ' t WHERE t.' ||
               p_col || ' = ''' || p_tjdate || '''';
      EXECUTE IMMEDIATE v_sql;
      COMMIT;
    END IF;

  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('error:' || SQLCODE || ',' || SQLERRM);
  END P_del_LHR;

  ------------------------------------------------------------------------------------------------------ 展示表空间的情况
  PROCEDURE P_show_space_LHR(p_segname_1   IN VARCHAR2, --表名称
                             p_type_1      IN VARCHAR2 DEFAULT 'TABLE',
                             p_space       IN VARCHAR2 DEFAULT 'AUTO', --MANUAL
                             p_analyzed    IN VARCHAR2 DEFAULT 'Y', --N
                             p_partition_1 IN VARCHAR2 DEFAULT NULL,
                             p_owner_1     IN VARCHAR2 DEFAULT USER) AS
    --set serveroutput on;
    --Exec  pkg_utility_lhr.P_show_space_LHR('test3','TABLE','AUTO','Y');
    p_segname   VARCHAR2(100);
    p_type      VARCHAR2(30);
    p_owner     VARCHAR2(30);
    p_partition VARCHAR2(50);

    l_unformatted_blocks NUMBER;
    l_unformatted_bytes  NUMBER;
    l_fs1_blocks         NUMBER;
    l_fs1_bytes          NUMBER;
    l_fs2_blocks         NUMBER;
    l_fs2_bytes          NUMBER;
    l_fs3_blocks         NUMBER;
    l_fs3_bytes          NUMBER;
    l_fs4_blocks         NUMBER;
    l_fs4_bytes          NUMBER;
    l_full_blocks        NUMBER;
    l_full_bytes         NUMBER;

    l_free_blks          NUMBER;
    l_total_blocks       NUMBER;
    l_total_bytes        NUMBER;
    l_unused_blocks      NUMBER;
    l_unused_bytes       NUMBER;
    l_LastUsedExtFileId  NUMBER;
    l_LastUsedExtBlockId NUMBER;
    l_LAST_USED_BLOCK    NUMBER;
    l_segment_space_mgmt VARCHAR2(255);
    -- inline procedure to print out numbers nicely formatted
    -- with a simple label
    PROCEDURE p(p_label IN VARCHAR2, p_num IN NUMBER) IS
    BEGIN
      DBMS_OUTPUT.put_line(RPAD(p_label, 40, '.') || p_num);
    END;
  BEGIN
    p_segname   := UPPER(p_segname_1);
    p_owner     := UPPER(p_owner_1);
    p_type      := p_type_1;
    p_partition := UPPER(p_partition_1);

    IF (p_type_1 = 'i' OR p_type_1 = 'I') THEN
      p_type := 'INDEX';
    END IF;

    IF (p_type_1 = 't' OR p_type_1 = 'T') THEN
      p_type := 'TABLE';
    END IF;

    IF (p_type_1 = 'tp' OR p_type_1 = 'TP') THEN
      p_type := 'TABLE PARTITION';
    END IF;

    IF (p_type_1 = 'ip' OR p_type_1 = 'IP') THEN
      p_type := 'INDEX PARTITION';
    END IF;

    IF (p_type_1 = 'c' OR p_type_1 = 'C') THEN
      p_type := 'CLUSTER';
    END IF;

    -- this query is executed dynamically in order to allow this procedure
    -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
    -- via a role as is customary.
    -- NOTE: at runtime, the invoker MUST have access to these two
    -- views!
    -- this query determines if the object is an ASSM object or not
    BEGIN
      EXECUTE IMMEDIATE 'select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segname
and (:p_partition is null or
seg.partition_name = :p_partition)
and seg.owner = :p_owner
and seg.tablespace_name = ts.tablespace_name'
        INTO l_segment_space_mgmt
        USING p_segname, p_partition, p_partition, p_owner;
    EXCEPTION
      WHEN too_many_rows THEN
        dbms_output.put_line('This must be a partitioned table, use p_partition => ');
        RETURN;
    END;
    DBMS_SPACE.UNUSED_SPACE(segment_owner             => p_owner,
                            segment_name              => p_segname,
                            segment_type              => p_type,
                            partition_name            => p_partition,
                            total_blocks              => l_total_blocks,
                            total_bytes               => l_total_bytes,
                            unused_blocks             => l_unused_blocks,
                            unused_bytes              => l_unused_bytes,
                            LAST_USED_EXTENT_FILE_ID  => l_LastUsedExtFileId,
                            LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
                            LAST_USED_BLOCK           => l_LAST_USED_BLOCK);

    -- if the object is in an ASSM tablespace, we must use this API
    -- call to get space information, otherwise we use the FREE_BLOCKS
    -- API for the user-managed segments
    IF p_space = 'MANUAL' OR (p_space <> 'auto' AND p_space <> 'AUTO') THEN
      DBMS_SPACE.FREE_BLOCKS(segment_owner     => p_owner,
                             segment_name      => p_segname,
                             segment_type      => p_type,
                             partition_name    => p_partition,
                             freelist_group_id => 0,
                             free_blks         => l_free_blks);

      p('Free Blocks', l_free_blks);
    END IF;

    p('Total Blocks', l_total_blocks);
    p('Total Bytes', l_total_bytes);
    p('Unused Blocks', l_unused_blocks);
    p('Unused Bytes', l_unused_bytes);
    p('Last Used Ext FileId', l_LastUsedExtFileId);
    p('Last Used Ext BlockId', l_LastUsedExtBlockId);
    p('Last Used Block', l_LAST_USED_BLOCK);

    /*IF the segment is analyzed */
    IF p_analyzed = 'Y' THEN
      DBMS_SPACE.SPACE_USAGE(segment_owner      => p_owner,
                             segment_name       => p_segname,
                             segment_type       => p_type,
                             partition_name     => p_partition,
                             unformatted_blocks => l_unformatted_blocks,
                             unformatted_bytes  => l_unformatted_bytes,
                             fs1_blocks         => l_fs1_blocks,
                             fs1_bytes          => l_fs1_bytes,
                             fs2_blocks         => l_fs2_blocks,
                             fs2_bytes          => l_fs2_bytes,
                             fs3_blocks         => l_fs3_blocks,
                             fs3_bytes          => l_fs3_bytes,
                             fs4_blocks         => l_fs4_blocks,
                             fs4_bytes          => l_fs4_bytes,
                             full_blocks        => l_full_blocks,
                             full_bytes         => l_full_bytes);
      DBMS_OUTPUT.put_line(RPAD('', 50, '*'));
      DBMS_OUTPUT.put_line('The segment is analyzed');
      p('0% -- 25% free space blocks', l_fs1_blocks);
      p('0% -- 25% free space bytes', l_fs1_bytes);
      p('25% -- 50% free space blocks', l_fs2_blocks);
      p('25% -- 50% free space bytes', l_fs2_bytes);
      p('50% -- 75% free space blocks', l_fs3_blocks);
      p('50% -- 75% free space bytes', l_fs3_bytes);
      p('75% -- 100% free space blocks', l_fs4_blocks);
      p('75% -- 100% free space bytes', l_fs4_bytes);
      p('Unused Blocks', l_unformatted_blocks);
      p('Unused Bytes', l_unformatted_bytes);
      p('Total Blocks', l_full_blocks);
      p('Total bytes', l_full_bytes);
    END IF;
  END P_show_space_LHR;

  ------------------------------------------------------------------------------------------------------
  PROCEDURE p_move_table_lhr(p_table    VARCHAR2,
                             p_owner    VARCHAR2 DEFAULT USER,
                             p_parallel NUMBER DEFAULT 1) AS

    v_table VARCHAR2(255) := upper(p_table);
    v_owner VARCHAR2(255) := upper(p_owner);

  BEGIN
    --收缩空间
    EXECUTE IMMEDIATE 'alter table ' || v_owner || '.' || v_table ||
                      ' move nologging parallel ' || p_parallel;
    EXECUTE IMMEDIATE 'alter table ' || v_owner || '.' || v_table ||
                      ' parallel  1';
    --重建索引

    FOR cur IN (SELECT 'alter index ' || d.owner || '.' || d.index_name ||
                       ' rebuild nologging parallel' || p_parallel exec_sql,
                       'alter index ' || d.owner || '.' || d.index_name ||
                       '   parallel 1' exec_sql1
                  FROM dba_indexes d
                 WHERE d.table_name = v_table
                   AND d.owner = v_owner

                ) LOOP
      EXECUTE IMMEDIATE cur.exec_sql;
      EXECUTE IMMEDIATE cur.exec_sql1;
    END LOOP;

    -- 收集表的统计信息

    dbms_stats.gather_table_stats(v_owner,
                                  v_table,
                                  cascade => TRUE,
                                  degree  => p_parallel);

  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('error:' || SQLCODE || ',' || SQLERRM);
  END p_move_table_lhr;
  ------------------------------------------------------------------------------------------------------
  PROCEDURE p_move_tablespace_lhr(p_table      VARCHAR2,
                                  p_owner      VARCHAR2 DEFAULT USER,
                                  p_tablespace VARCHAR2 DEFAULT 'SDH_DATA') AS

    v_table      VARCHAR2(255) := upper(p_table);
    v_owner      VARCHAR2(255) := upper(p_owner);
    v_tablespace VARCHAR2(255) := upper(p_tablespace);

  BEGIN
    --移动表空间
    EXECUTE IMMEDIATE 'alter table ' || v_owner || '.' || v_table ||
                      ' move tablespace ' || v_tablespace || ' nologging';
    --重建索引

    FOR cur IN (SELECT 'alter index ' || d.index_name ||
                       ' rebuild nologging' exec_sql
                  FROM dba_indexes d
                 WHERE d.table_name = v_table
                   AND d.owner = v_owner

                ) LOOP
      EXECUTE IMMEDIATE cur.exec_sql;
    END LOOP;

    -- 收集表的统计信息

    dbms_stats.gather_table_stats(v_owner,
                                  v_table,
                                  cascade => TRUE,
                                  degree  => 2);

  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('error:' || SQLCODE || ',' || SQLERRM);
  END p_move_tablespace_lhr;

  ------------------------------------------------------------------------------------------------------
  PROCEDURE P_create_synonym_LHR(P_OWNER VARCHAR2, P_OBJECT_TYPE VARCHAR2) AS

  BEGIN

    IF P_OBJECT_TYPE = UPPER('ALL') THEN

      FOR cur IN (SELECT 'CREATE OR REPLACE SYNONYM  ' || D.OBJECT_NAME ||
                         ' FOR  ' || D.OWNER || '.' || D.OBJECT_NAME create_synonym
                    FROM dba_objects d
                   WHERE D.OWNER = UPPER(P_OWNER)) LOOP
        BEGIN
          EXECUTE IMMEDIATE cur.create_synonym;
        EXCEPTION
          WHEN OTHERS THEN
            NULL;
        END;

      END LOOP;

    ELSIF P_OBJECT_TYPE = UPPER('COMMON') THEN
      FOR cur IN (SELECT 'CREATE OR REPLACE SYNONYM  ' || D.OBJECT_NAME ||
                         ' FOR  ' || D.OWNER || '.' || D.OBJECT_NAME create_synonym
                    FROM dba_objects d
                   WHERE d.OBJECT_TYPE IN ('TABLE',
                                           'VIEW',
                                           'SEQUENCE',
                                           'TYPE',
                                           'PROCEDURE',
                                           'PACKAGE',
                                           'PACKAGE BODY',
                                           'FUNCTION',
                                           'TRIGGER',
                                           'TYPE BODY',
                                           'DATABASE LINK')
                     AND D.OWNER = UPPER(P_OWNER)) LOOP
        BEGIN
          EXECUTE IMMEDIATE cur.create_synonym;
        EXCEPTION
          WHEN OTHERS THEN
            NULL;
        END;

      END LOOP;
    ELSE
      FOR cur IN (SELECT 'CREATE OR REPLACE SYNONYM  ' || D.OBJECT_NAME ||
                         ' FOR  ' || D.OWNER || '.' || D.OBJECT_NAME create_synonym
                    FROM dba_objects d
                   WHERE d.OBJECT_TYPE = upper(P_OBJECT_TYPE)
                     AND D.OWNER = UPPER(P_OWNER)) LOOP
        BEGIN
          EXECUTE IMMEDIATE cur.create_synonym;
        EXCEPTION
          WHEN OTHERS THEN
            NULL;
        END;

      END LOOP;
    END IF;

  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('error:' || SQLCODE || ',' || SQLERRM);
  END P_create_synonym_LHR;

  ------得到执行计划
  PROCEDURE p_sql_explain_lhr(stmt    VARCHAR2,
                              format  VARCHAR2 DEFAULT 'ADVANCED',
                              exponly BOOLEAN DEFAULT TRUE) AS
    ------------------------------------------------------------
    -- 描述：解析和显示语句执行计划                          ---
    -- 参数描述                                              ---
    --     stmt：解析或执行的语句                            ---
    --     format：执行计划输出格式，参加DBMS_XPLAN中描述    ---
    --     exponly：是否仅解析                               ---
    --         TRUE：仅调用EXPLAIN PLAN命令解析语句          ---
    --        FALSE：执行语句后从缓存获得执行计划            ---
    ------------------------------------------------------------
    c        NUMBER;
    r        NUMBER;
    sqlid    VARCHAR2(100);
    childnum NUMBER;
  BEGIN
    dbms_output.enable(1000000); --100W是最大的了
    IF exponly THEN
      EXECUTE IMMEDIATE 'explain plan for ' || stmt;
      FOR xpl_rec IN (SELECT *
                        FROM TABLE(dbms_xplan.display(NULL, NULL, format))) LOOP
        dbms_output.put_line(xpl_rec.plan_table_output);
      END LOOP;
    ELSE
      c := dbms_sql.open_cursor;
      dbms_sql.parse(c, stmt, dbms_sql.native);
      r := dbms_sql.execute_and_fetch(c);
      LOOP
        EXIT WHEN r <= 0;
        r := dbms_sql.fetch_rows(c);
      END LOOP;
      SELECT DISTINCT p.sql_id, p.child_number
        INTO sqlid, childnum
        FROM v$sql_cursor sc, v$sql_plan p, v$open_cursor c, v$sqlarea q
       WHERE p.address = sc.PARENT_HANDLE
         AND p.sql_id = q.sql_id
         AND c.sql_id = q.sql_id
         AND c.sid = SYS_CONTEXT('USERENV', 'SID')
         AND q.sql_text LIKE substr(stmt, 0, 30) || chr(37)
         AND rownum <= 1;
      --select distinct s.sql_id, s.child_number into sqlid, childnum from v$sql_plan s, v$sql_cursor c where s.address=c.PARENT_HANDLE and c.curno=c and rownum<=1;
      dbms_sql.close_cursor(c);
      FOR xpl_rec IN (SELECT *
                        FROM TABLE(dbms_xplan.display_cursor(sqlid,
                                                             childnum,
                                                             format))) LOOP
        dbms_output.put_line(xpl_rec.plan_table_output);
      END LOOP;
    END IF;
    ROLLBACK;
  END p_sql_explain_lhr;

  ------修改分区名称
  PROCEDURE p_rename_partition_lhr(p_owner      VARCHAR2,
                                   p_table_name VARCHAR2,
                                   v_type       NUMBER DEFAULT 1 -- 1代表分区表，2代表分区索引
                                   ) AS
    -----------------------------------------------------------------------------------
    -- Created on 2014-04-03 10:34:10 by lhr
    --Changed on 2014-04-03 10:34:10 by lhr
    -- function：   修改分区名称
    --   high_value :  TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
    --修改后：P201406
    -----------------------------------------------------------------------------------
    v_sql                VARCHAR2(400);
    v_table_name         user_tab_partitions.table_name%TYPE;
    v_index_name         dba_ind_partitions.index_name%TYPE;
    v_partition_name     user_tab_partitions.partition_name%TYPE;
    V_INDEX_OWNER        dba_ind_partitions.Index_Owner%TYPE;
    v_high_value         VARCHAR2(200);
    v_tmp_partition_name user_tab_partitions.partition_name%TYPE;
    CURSOR cur IS
      SELECT d.table_name, partition_name, high_value --
        FROM dba_tab_partitions d
       WHERE d.table_owner = upper(p_owner)
         AND d.table_name = upper(p_table_name)
         AND partition_name LIKE 'SYS%';
    CURSOR cur1 IS
      SELECT d.index_owner, d.index_name, D.partition_name, d.high_value
        FROM dba_ind_partitions d, DBA_INDEXES DI
       WHERE d.index_name = DI.index_name
         AND DI.index_type NOT IN ('LOB')
         AND DI.table_owner = upper(p_owner)
         AND DI.table_name = upper(p_table_name)
         AND d.partition_name LIKE 'SYS%';

  BEGIN

    IF v_type = 1 THEN
      OPEN cur;
      LOOP
        FETCH cur
          INTO v_table_name, v_partition_name, v_high_value;
        EXIT WHEN cur%NOTFOUND;
        v_tmp_partition_name := substr(v_high_value, 11, 10);
        v_tmp_partition_name := to_char(to_date(v_tmp_partition_name,
                                                'yyyy-mm-dd') - 1,
                                        'yyyymm');
        v_sql                := 'alter table ' || p_owner || '.' ||
                                v_table_name || ' rename partition ' ||
                                v_partition_name || ' to P' ||
                                v_tmp_partition_name;
        -- dbms_output.put_line(v_sql);
        EXECUTE IMMEDIATE v_sql;
      END LOOP;
      CLOSE cur;
      ------修改索引分区
    ELSE
      OPEN cur1;
      LOOP
        FETCH cur1
          INTO V_INDEX_OWNER, v_index_name, v_partition_name, v_high_value;
        EXIT WHEN cur1%NOTFOUND;
        v_tmp_partition_name := substr(v_high_value, 11, 10);
        v_tmp_partition_name := to_char(to_date(v_tmp_partition_name,
                                                'yyyy-mm-dd') - 1,
                                        'yyyymm');
        v_sql                := 'alter index ' || V_INDEX_OWNER || '.' ||
                                v_index_name || ' rename partition ' ||
                                v_partition_name || ' to P' ||
                                v_tmp_partition_name;
        --   dbms_output.put_line(v_sql);
        EXECUTE IMMEDIATE v_sql;
      END LOOP;
      CLOSE cur1;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;

  END p_rename_partition_lhr;

  PROCEDURE p_gather_table_stats_lhr(p_owner      VARCHAR2,
                                     p_table_name VARCHAR2,
                                     p_cascade    CHAR DEFAULT 'TRUE' --是否分析索引
                                     ) AS
    -----------------------------------------------------------------------------------
    -- Created on 2014-04-03 10:34:10 by lhr
    --Changed on 2014-04-03 10:34:10 by lhr
    -- function：   创建job来分析表
    -----------------------------------------------------------------------------------
    v_PLSQL_BLOCK VARCHAR2(4000);
  BEGIN

    v_PLSQL_BLOCK := 'dbms_stats.gather_table_stats(''' || p_owner ||
                     ''',''' || p_table_name || ''',' || 'cascade      => ' ||
                     p_cascade || ',degree       => 8);';

    DBMS_SCHEDULER.CREATE_JOB(JOB_NAME   => 'JOB_ANALYZE_' ||
                                            substr(p_table_name, 4, 10) || '_' ||
                                            trunc(dbms_random.value * 100),
                              JOB_TYPE   => 'PLSQL_BLOCK',
                              JOB_ACTION => v_PLSQL_BLOCK,
                              ENABLED    => TRUE,
                              START_DATE => SYSDATE,
                              comments   => '分析表' || p_table_name);

  END p_gather_table_stats_lhr;

  PROCEDURE p_exec_sql_lhr(p_number NUMBER) AS
    -----------------------------------------------------------------------------------
    -- Created on 2014-04-03 10:34:10 by lhr
    --Changed on 2014-04-03 10:34:10 by lhr
    -- function：   调用存过动态执行sql
    -----------------------------------------------------------------------------------
    v_job_name VARCHAR2(4000);
  BEGIN

    v_job_name := 'JOB_pkg_nm_lhr_execsql_' ||
                  trunc(dbms_random.value * 100);

    DBMS_SCHEDULER.CREATE_JOB(JOB_NAME            => v_job_name,
                              JOB_TYPE            => 'STORED_PROCEDURE',
                              JOB_ACTION          => 'pkg_nm_lhr.p_exec_sql_lhr',
                              NUMBER_OF_ARGUMENTS => 1,
                              ENABLED             => FALSE,
                              START_DATE          => SYSDATE,
                              comments            => '创建job执行sql');
    --设置存过的参数
    dbms_scheduler.set_job_argument_value(job_name          => v_job_name,
                                          argument_position => 1,
                                          argument_value    => p_number);
    dbms_scheduler.enable(NAME => v_job_name);
  END p_exec_sql_lhr;

  --------------- 分析索引碎片
  PROCEDURE P_ANALYZE_INDEX_SATAS_LHR IS

  BEGIN

    FOR cur IN (SELECT A.owner,
                       a.INDEX_NAME,
                       a.index_type,
                       '' partition_name,
                       a.table_owner,
                       a.table_name,
                       a.table_type,
                       a.uniqueness,
                       ' analyze index ' || a.owner || '.' || a.index_name ||
                       ' validate structure' cur_sql
                  FROM dba_indexes a
                 WHERE index_type <> 'LOB'
                   AND A.partitioned = 'NO'
                   AND a.table_name != 'XB_AUDIT_DDL_LHR'
                   AND a.owner NOT IN
                       ('MGMT_VIEW',
                        'SYS',
                        'SYSTEM',
                        'DBSNMP',
                        'SYSMAN',
                        'OUTLN',
                        'FLOWS_FILES',
                        'MDSYS',
                        'ORDSYS',
                        'EXFSYS',
                        'WMSYS',
                        'APPQOSSYS',
                        'APEX_030200',
                        'OWBSYS_AUDIT',
                        'ORDDATA',
                        'CTXSYS',
                        'ANONYMOUS',
                        'XDB',
                        'ORDPLUGINS',
                        'OWBSYS',
                        'SI_INFORMTN_SCHEMA',
                        'OLAPSYS',
                        'SCOTT',
                        'ORACLE_OCM',
                        'XS$NULL',
                        'BI',
                        'PM',
                        'MDDATA',
                        'IX',
                        'SH',
                        'DIP',
                        'OE',
                        'APEX_PUBLIC_USER',
                        'HR',
                        'SPATIAL_CSW_ADMIN_USR',
                        'SPATIAL_WFS_ADMIN_USR')
                UNION ALL
                SELECT A.owner,
                       a.INDEX_NAME,
                       a.index_type,
                       d.partition_name,
                       a.table_owner,
                       a.table_name,
                       a.table_type,
                       a.uniqueness,
                       ' analyze index ' || a.owner || '.' || a.index_name ||
                       ' partition(' || d.partition_name ||
                       ')  validate structure' cur_sql
                  FROM dba_ind_partitions d
                  JOIN DBA_PART_INDEXES DP
                    ON (D.INDEX_NAME = DP.index_name)
                  LEFT OUTER JOIN DBA_INDEXES A
                    ON (A.index_name = D.INDEX_NAME AND
                       A.table_name = DP.table_name AND
                       A.owner = D.INDEX_OWNER)
                 WHERE index_type <> 'LOB'
                   AND A.partitioned = 'YES'
                   AND a.table_name != 'XB_AUDIT_DDL_LHR'
                   AND a.owner NOT IN
                       ('MGMT_VIEW',
                        'SYS',
                        'SYSTEM',
                        'DBSNMP',
                        'SYSMAN',
                        'OUTLN',
                        'FLOWS_FILES',
                        'MDSYS',
                        'ORDSYS',
                        'EXFSYS',
                        'WMSYS',
                        'APPQOSSYS',
                        'APEX_030200',
                        'OWBSYS_AUDIT',
                        'ORDDATA',
                        'CTXSYS',
                        'ANONYMOUS',
                        'XDB',
                        'ORDPLUGINS',
                        'OWBSYS',
                        'SI_INFORMTN_SCHEMA',
                        'OLAPSYS',
                        'SCOTT',
                        'ORACLE_OCM',
                        'XS$NULL',
                        'BI',
                        'PM',
                        'MDDATA',
                        'IX',
                        'SH',
                        'DIP',
                        'OE',
                        'APEX_PUBLIC_USER',
                        'HR',
                        'SPATIAL_CSW_ADMIN_USR',
                        'SPATIAL_WFS_ADMIN_USR')) LOOP

      BEGIN
        EXECUTE IMMEDIATE cur.cur_sql;

        INSERT INTO xt_analyze_index_stats_lhr
          (id,
           index_owner,
           index_name,
           partition_name,
           index_type,
           table_owner,
           table_name,
           table_type,
           uniqueness,
           height,
           blocks,
           lf_rows,
           lf_blks,
           lf_rows_len,
           lf_blk_len,
           br_rows,
           br_blks,
           br_rows_len,
           br_blk_len,
           del_lf_rows,
           del_lf_rows_len,
           distinct_keys,
           most_repeated_key,
           btree_space,
           used_space,
           pct_used,
           rows_per_key,
           blks_gets_per_access,
           pre_rows,
           pre_rows_len,
           opt_cmpr_count,
           opt_cmpr_pctsave,
           del_lf_rows_per,
           in_date)
          SELECT s_xt_analyze_index_stats_lhr.nextval,
                 cur.owner,
                 a.name,
                 a.partition_name,
                 cur.index_type,
                 cur.table_owner,
                 cur.table_name,
                 cur.table_type,
                 cur.uniqueness,
                 height,
                 blocks,
                 lf_rows,
                 lf_blks,
                 lf_rows_len,
                 lf_blk_len,
                 br_rows,
                 br_blks,
                 br_rows_len,
                 br_blk_len,
                 del_lf_rows,
                 del_lf_rows_len,
                 distinct_keys,
                 most_repeated_key,
                 btree_space,
                 used_space,
                 pct_used,
                 rows_per_key,
                 blks_gets_per_access,
                 pre_rows,
                 pre_rows_len,
                 opt_cmpr_count,
                 opt_cmpr_pctsave,
                 round(a.del_lf_rows / a.lf_rows, 2) del_lf_rows_per,
                 SYSDATE
            FROM Index_Stats a;

      EXCEPTION
        WHEN OTHERS THEN
          INSERT INTO xt_analyze_index_error_lhr
            (id,
             index_owner,
             index_name,
             table_owner,
             tablename,
             errors,
             partition_name,
             in_date)
          VALUES
            (s_xt_analyze_index_error_lhr.nextval,
             cur.owner,
             cur.index_name,
             cur.table_owner,
             cur.table_name,
             cur.partition_name,
             dbms_utility.format_error_backtrace,
             SYSDATE);

          COMMIT;
      END;

    END LOOP;

    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;

  END P_ANALYZE_INDEX_SATAS_LHR;

  PROCEDURE P_ANALYZE_INDEX_ERROR_LHR IS

  BEGIN

    FOR cur IN (SELECT a.id,
                       A.Index_Owner,
                       a.INDEX_NAME,
                       a.index_type,
                       a.partition_name,
                       a.table_owner,
                       a.tablename,
                       table_type,
                       uniqueness,
                       ' analyze index ' || a.index_owner || '.' ||
                       a.index_name || ' validate structure' cur_sql
                  FROM xt_analyze_index_error_lhr a) LOOP

      BEGIN
        EXECUTE IMMEDIATE cur.cur_sql;

        INSERT INTO xt_analyze_index_stats_lhr
          (id,
           index_owner,
           index_name,
           partition_name,
           index_type,
           table_owner,
           table_name,
           table_type,
           uniqueness,
           height,
           blocks,
           lf_rows,
           lf_blks,
           lf_rows_len,
           lf_blk_len,
           br_rows,
           br_blks,
           br_rows_len,
           br_blk_len,
           del_lf_rows,
           del_lf_rows_len,
           distinct_keys,
           most_repeated_key,
           btree_space,
           used_space,
           pct_used,
           rows_per_key,
           blks_gets_per_access,
           pre_rows,
           pre_rows_len,
           opt_cmpr_count,
           opt_cmpr_pctsave,
           del_lf_rows_per,
           in_date)
          SELECT s_xt_analyze_index_stats_lhr.nextval,
                 cur.index_owner,
                 a.name,
                 a.partition_name,
                 cur.index_type,
                 cur.table_owner,
                 cur.tablename,
                 cur.table_type,
                 cur.uniqueness,
                 height,
                 blocks,
                 lf_rows,
                 lf_blks,
                 lf_rows_len,
                 lf_blk_len,
                 br_rows,
                 br_blks,
                 br_rows_len,
                 br_blk_len,
                 del_lf_rows,
                 del_lf_rows_len,
                 distinct_keys,
                 most_repeated_key,
                 btree_space,
                 used_space,
                 pct_used,
                 rows_per_key,
                 blks_gets_per_access,
                 pre_rows,
                 pre_rows_len,
                 opt_cmpr_count,
                 opt_cmpr_pctsave,
                 round(a.del_lf_rows / a.lf_rows, 2) del_lf_rows_per,
                 SYSDATE
            FROM Index_Stats a;

        DELETE FROM xt_analyze_index_error_lhr a WHERE a.id = cur.id;
        COMMIT;

      EXCEPTION
        WHEN OTHERS THEN
          NULL;

          COMMIT;
      END;

    END LOOP;

    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;

  END P_ANALYZE_INDEX_ERROR_LHR;

  -----------------将数据行转为列显示      set serveroutput on size 1000000
  PROCEDURE p_print_table_lhr(p_query IN VARCHAR2) IS

    -- this utility is designed to be installed ONCE in a database and used
    -- by all.  Also, it is nice to have roles enabled so that queries by
    -- DBA's that use a role to gain access to the DBA_* views still work
    -- that is the purpose of AUTHID CURRENT_USER

    l_theCursor   INTEGER DEFAULT dbms_sql.open_cursor;
    l_columnValue VARCHAR2(4000);
    l_status      INTEGER;
    l_descTbl     dbms_sql.desc_tab;
    l_colCnt      NUMBER;
    l_rowCnt      NUMBER := 1;
  BEGIN
    EXECUTE IMMEDIATE 'alter session set NLS_DATE_FORMAT=''YYYY-MM-DD  HH24:MI:SS''';
    -- parse and describe the query sent to us.  we need
    -- to know the number of columns and their names.
    dbms_sql.parse(l_theCursor, p_query, dbms_sql.native);
    dbms_sql.describe_columns(l_theCursor, l_colCnt, l_descTbl);

    -- define all columns to be cast to varchar2's, we
    -- are just printing them out
    FOR i IN 1 .. l_colCnt LOOP
      dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
    END LOOP;
    -- execute the query, so we can fetch
    l_status := dbms_sql.execute(l_theCursor);
    IF l_status < 0 THEN
      NULL;
    END IF;
    -- loop and print out each column on a separate line
    -- bear in mind that dbms_output only prints 255 characters/line
    -- so we'll only see the first 200 characters by my design...
    WHILE (dbms_sql.fetch_rows(l_theCursor) > 0) LOOP
      FOR i IN 1 .. l_colCnt LOOP
        dbms_sql.column_value(l_theCursor, i, l_columnValue);
        dbms_output.put_line(rpad(l_descTbl(i).col_name, 30) || ': ' ||
                             l_columnValue);
      END LOOP;
      dbms_output.put_line('[Row:' || l_rowCnt ||
                           ']----------------------------------------------------------------------------------------------------------------------');
      l_rowCnt := l_rowCnt + 1;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_sql.close_cursor(l_theCursor);
      RAISE;
  END p_print_table_lhr;

  ----------------创建临时外部表查看告警事件
  PROCEDURE p_externaltable_lhr(p_tracename VARCHAR2,
                                P_directory VARCHAR2 DEFAULT 'DIR_ALERT_LHR') AS
  BEGIN
    BEGIN
      EXECUTE IMMEDIATE 'drop TABLE xt_alert_dump_lhr';
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;

    EXECUTE IMMEDIATE 'create table xt_alert_dump_lhr ( text varchar2(2000) )
organization external (
type oracle_loader
default directory ' || P_directory || '
    access parameters (
            records delimited by newline nologfile nobadfile
    )
location(' || p_tracename || ')
)
reject limit unlimited';

    dbms_output.put_line(' 表已创建:  xt_alert_dump_lhr');

  END p_externaltable_lhr;

  ---------------估算即将创建的索引大小?
  PROCEDURE p_create_index_size_lhr(p_sql VARCHAR2) IS
    v_used_bytes      NUMBER;
    v_allocated_bytes NUMBER;
  BEGIN
    dbms_space.create_index_cost(p_sql, v_used_bytes, v_allocated_bytes);
    dbms_output.put_line('【used= ' ||
                         pkg_utility_lhr.f_get_right_size_lhr(v_used_bytes) ||
                         '  allocated= ' ||
                         pkg_utility_lhr.f_get_right_size_lhr(v_allocated_bytes) || '】');
  END p_create_index_size_lhr;

END pkg_utility_lhr;
/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

864

865

866

867

868

869

870

871

872

873

874

875

876

877

878

879

880

881

882

883

884

885

886

887

888

889

890

891

892

893

894

895

896

897

898

899

900

901

902

903

904

905

906

907

908

909

910

911

912

913

914

915

916

917

918

919

920

921

922

923

924

925

926

927

928

929

930

931

932

933

934

935

936

937

938

939

940

941

942

943

944

945

946

947

948

949

950

951

952

953

954

955

956

957

958

959

960

961

962

963

964

965

966

967

968

969

970

971

972

973

974

975

976

977

978

979

980

981

982

983

984

985

986

987

988

989

990

991

992

993

994

995

996

997

998

999

1000

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1012

1013

1014

1015

1016

1017

1018

1019

1020

1021

1022

1023

1024

1025

1026

1027

1028

1029

1030

1031

1032

1033

1034

1035

1036

1037

1038

1039

1040

1041

1042

1043

1044

1045

1046

1047

1048

1049

1050

1051

1052

1053

1054

1055

1056

1057

1058

1059

1060

1061

1062

1063

1064

1065

1066

1067

1068

1069

1070

1071

1072

1073

1074

1075

1076

1077

1078

1079

1080

1081

1082

1083

1084

1085

1086

1087

1088

1089

1090

1091

1092

1093

1094

1095

1096

1097

1098

1099

1100

1101

1102

1103

1104

1105

1106

1107

1108

1109

1110

1111

1112

1113

1114

1115

1116

1117

1118

1119

1120

1121

1122

1123

1124

1125

1126

1127

1128

1129

1130

1131

1132

1133

1134

1135

1136

1137

1138

1139

1140

1141

1142

1143

1144

1145

1146

1147

1148

1149

1150

1151

1152

1153

1154

1155

1156

1157

1158

1159

1160

1161

1162

1163

1164

1165

1166

1167

1168

1169

1170

1171

1172

1173

1174

1175

1176

1177

1178

1179

1180

1181

1182

1183

1184

1185

1186

1187

1188

1189

1190

1191

1192

1193

1194

1195

1196

1197

1198

1199

1200

1201

1202

1203

1204

1205

1206

1207

1208

1209

1210

1211

1212

1213

1214

1215

1216

1217

1218

1219

1220

1221

1222

1223

1224

1225

1226

1227

1228

1229

1230

1231

1232

1233

1234

1235

1236

1237

1238

1239

1240

1241

1242

1243

1244

1245

1246

1247

1248

1249

1250

1251

1252

1253

1254

1255

1256

1257

1258

1259

1260

1261

1262

1263

1264

1265

1266

1267

1268

1269

1270

1271

1272

1273

1274

1275

1276

1277

1278

1279

1280

1281

1282

1283

1284

1285

1286

1287

1288

1289

1290

1291

1292

1293

1294

1295

1296

1297

1298

1299

1300

1301

1302

1303

1304

1305

1306

1307

1308

1309

1310

1311

1312

1313

1314

1315

1316

1317

1318

1319

1320

1321

1322

1323

1324

1325

1326

1327

1328

1329

1330

1331

1332

1333

1334

1335

1336

1337

1338

1339

1340

1341

1342

1343

1344

1345

1346

1347

1348

1349

1350

1351

1352

1353

1354

1355

1356

1357

1358

1359

1360

1361

1362

1363

1364

1365

1366

1367

1368

1369

1370

1371

1372

1373

1374

1375

1376

1377

1378

1379

1380

1381

1382

1383

1384

1385

1386

1387

1388

1389

1390

1391

1392

1393

1394

1395

1396

1397

1398

1399

1400

1401

1402

1403

1404

1405

1406

1407

1408

1409

1410

1411

1412

1413

1414

1415

1416

1417

1418

1419

1420

1421

1422

1423

1424

1425

1426

1427

1428

1429

1430

1431

1432

1433

1434

1435

1436

1437

1438

1439

1440

1441

1442

1443

1444

1445

1446

1447

1448

1449

1450

1451

1452

1453

1454

1455

1456

1457

1458

1459

1460

1461

1462

1463

1464

1465

1466

1467

1468

1469

1470

1471

1472

1473

1474

1475

1476

1477

1478

1479

1480

1481

1482

1483

1484

1485

1486

1487

1488

1489

1490

1491

1492

1493

1494

1495

1496

1497

1498

1499

1500

1501

1502

1503

1504

1505

1506

1507

1508

1509

1510

1511

1512

1513

1514

1515

1516

1517

1518

1519

1520

1521

1522

1523

1524

1525

1526

1527

1528

1529

1530

1531

1532

1533

1534

1535

1536

1537

1538

1539

1540

1541

1542

1543

1544

1545

1546

1547

1548

1549

1550

1551

1552

1553

1554

1555

1556

1557

1558

1559

1560

1561

1562

1563

1564

1565

1566

1567

1568

1569

1570

1571

1572

1573

1574

1575

1576

1577

1578

1579

1580

1581

1582

1583

1584

1585

1586

1587

1588

1589

1590

1591

1592

1593

1594

1595

1596

1597

1598

1599

1600

1601

1602

1603

1604

1605

1606

1607

1608

1609

1610

1611

1612

1613

1614

1615

1616

1617

1618

1619

1620

1621

1622

1623

1624

1625

1626

1627

1628

1629

1630

1631

1632

1633

1634

1635

1636

1637

1638

1639

1640

1641

1642

1643

1644

1645

1646

1647

1648

1649

1650

1651

1652

1653

1654

1655

1656

1657

1658

1659

1660

1661

1662

1663

1664

1665

1666

1667

1668

1669

1670

1671

1672

1673

1674

1675

1676

1677

1678

1679

1680

1681

1682

1683

1684

1685

1686

1687

1688

1689

1690

1691

1692

1693

1694

1695

1696

1697

1698

1699

1700

1701

1702

1703

1704

1705

1706

1707

1708

1709

1710

1711

1712

1713

1714

1715

1716

1717

1718

1719

1720

1721

1722

1723

1724

1725

1726

1727

1728

1729

1730

1731

1732

1733

1734

1735

1736

1737

1738

1739

1740

1741

1742

1743

1744

1745

1746

1747

1748

1749

1750

1751

1752

1753

1754

1755

1756

1757

1758

1759

1760

1761

1762

1763

1764

1765

1766

1767

1768

1769

1770

1771

1772

1773

1774

1775

1776

1777

1778

1779

1780

1781

1782

1783

1784

1785

1786

1787

1788

1789

1790

1791

1792

1793

1794

1795

1796

1797

1798

1799

1800

1801

1802

1803

1804

1805

1806

1807

1808

1809

1810

1811

1812

1813

1814

1815

1816

1817

1818

1819

1820

1821

1822

1823

1824

1825

1826

1827

1828

1829

1830

1831

1832

1833

1834

1835

1836

1837

1838

1839

1840

1841

1842

1843

1844

1845

1846

1847

1848

1849

1850

1851

1852

1853

1854

1855

1856

1857

1858

1859

1860

1861

1862

1863

1864

1865

1866

1867

1868

1869

1870

1871

1872

1873

1874

1875

1876

1877

1878

1879

1880

1881

1882

1883

1884

1885

1886

1887

1888

1889

1890

1891

1892

1893

1894

1895

1896

1897

1898

1899

1900

1901

1902

1903

1904

1905

1906

1907

1908

1909

1910

1911

1912

1913

1914

1915

1916

1917

1918

1919

1920

1921

1922

1923

1924

1925

1926

1927

1928

1929

1930

1931

1932

1933

1934

1935

1936

1937

1938

1939

1940

1941

1942

1943

1944

1945

1946

1947

1948

1949

1950

1951

1952

1953

1954

1955

1956

1957

1958

1959

1960

1961

1962

1963

1964

1965

1966

1967

1968

1969

1970

1971

1972

1973

1974

1975

1976

1977

1978

1979

1980

1981

1982

1983

1984

1985

1986

1987

1988

1989

1990

1991

1992

1993

1994

1995

1996

1997

1998

1999

2000

2001

2002

2003

2004

2005

2006

2007

2008

2009

2010

2011

2012

2013

2014

2015

2016

2017

2018

2019

2020

2021

2022

2023

2024

2025

2026

2027

2028

2029

2030

2031

2032

2033

2034

2035

2036

2037

2038

2039

2040

2041

2042

2043

2044

2045

2046

2047

2048

2049

2050

2051

2052

2053

2054

2055

2056

2057

2058

2059

2060

2061

2062

2063

2064

2065

2066

2067

2068

2069

2070

2071

2072

2073

2074

2075

2076

2077

2078

2079

2080

2081

2082

2083

2084

2085

2086

2087

2088

2089

2090

2091

2092

2093

2094

2095

2096

2097

2098

2099

2100

2101

2102

2103

2104

2105

2106

2107

2108

2109

2110

2111

2112

2113

2114

2115

2116

2117

2118

2119

2120

2121

2122

2123

2124

2125

2126

2127

2128

2129

2130

2131

2132

2133

2134

2135

2136

2137

2138

2139

2140

2141

2142

2143

2144

2145

2146

2147

2148

2149

2150

2151

2152

2153

2154

2155

2156

2157

2158

2159

2160

2161

2162

2163

2164

2165

2166

2167

2168

2169

2170

2171

2172

2173

2174

2175

2176

2177

2178

2179

2180

2181

2182

2183

2184

2185

2186

2187

2188

2189

2190

2191

2192

2193

2194

2195

2196

2197

2198

2199

2200

2201

2202

2203

2204

2205

2206

2207

2208

2209

2210

2211

2212

2213

2214

2215

2216

2217

2218

2219

2220

2221

2222

2223

2224

2225

2226

2227

2228

2229

2230

2231

2232

2233

2234

2235

2236

2237

2238

2239

2240

2241

2242

2243

2244

2245

2246

2247

2248

2249

2250

2251

2252

2253

2254

2255

2256

2257

2258

2259

2260

2261

2262

2263

2264

2265

2266

2267

2268

2269

2270

2271

2272

2273

2274

2275

2276

2277

2278

2279

2280

2281

2282

2283

2284

2285

2286

2287

2288

2289

2290

2291

2292

2293

2294

2295

2296

2297

2298

2299

2300

2301

2302

2303

2304

2305

2306

2307

2308

2309

2310

2311

2312

2313

2314

2315

2316

2317

2318

2319

2320

2321

2322

2323

2324

2325

2326

2327

2328

2329

2330

2331

2332

2333

2334

2335

2336

2337

2338

2339

2340

2341

2342

2343

2344

2345

2346

2347

2348

2349

2350

2351

2352

2353

2354

2355

2356

2357

2358

2359

2360

2361

2362

2363

2364

2365

2366

2367

2368

2369

2370

2371

2372

2373

2374

2375

2376

2377

2378

2379

2380

2381

2382

2383

2384

2385

2386

2387

2388

2389

2390

2391

2392

2393

2394

2395

2396

2397

2398

2399

2400

2401

2402

2403

2404

2405

2406

2407

2408

2409

2410

2411

2412

2413

2414

2415

2416

2417

2418

2419

2420

2421

2422

2423

2424

2425

2426

2427

2428

2429

2430

2431

2432

2433

2434

2435

2436

2437

2438

2439

2440

2441

2442

2443

2444

2445

2446

2447

2448

2449

2450

2451

2452

2453

2454

2455

2456

2457

2458

2459

2460

2461

2462

2463

2464

2465

2466

2467

2468

2469

2470

2471

2472

2473

2474

2475

2476

2477

2478

2479

2480

2481

2482

2483

2484

2485

2486

2487

2488

2489

2490

2491

2492

2493

2494

2495

2496

2497

2498

2499

2500

2501

2502

2503

2504

2505

2506

2507

2508

2509

2510

2511

2512

2513

2514

2515

2516

2517

2518

2519

2520

2521

2522

2523

2524

2525

2526

2527

2528

2529

2530

2531

2532

2533

2534

2535

2536

2537

2538

2539

2540

2541

2542

2543

2544

2545

2546

2547

2548

2549

2550

2551

2552

2553

2554

2555

2556

2557

2558

2559

2560

2561

2562

2563

2564

2565

2566

2567

2568

2569

2570

2571

2572

2573

2574

2575

2576

2577

2578

2579

2580

2581

2582

2583

2584

2585

2586

2587

2588

2589

2590

2591

2592

2593

2594

2595

2596

2597

2598

2599

2600

2601

2602

2603

2604

2605

2606

2607

2608

2609

2610

2611

2612

2613

2614

2615

2616

2617

2618

2619

2620

2621

2622

2623

2624

2625

2626

2627

2628

2629

2630

2631

2632

2633

2634

2635

2636

2637

2638

2639

2640

2641

2642

2643

2644

2645

2646

2647

2648

2649

2650

2651

2652

2653

2654

2655

2656

2657

2658

2659

2660

2661

2662

2663

2664

2665

2666

2667

2668

2669

2670

2671

2672

2673

2674

2675

2676

2677

2678

2679

2680

2681

2682

2683

2684

2685

2686

2687

2688

2689

2690

2691

2692

2693

2694

2695

2696

2697

2698

2699

2700

2701

2702

2703

2704

2705

2706

2707

2708

2709

2710

2711

2712

2713

2714

2715

2716

2717

2718

2719

2720

2721

2722

2723

2724

2725

2726

2727

2728

2729

2730

2731

2732

2733

2734

2735

2736

2737

2738

2739

2740

2741

2742

2743

2744

2745

2746

2747

2748

2749

2750

2751

2752

2753

2754

2755

2756

2757

2758

2759

2760

2761

2762

2763

2764

2765

2766

2767

2768

2769

2770

2771

2772

2773

2774

2775

2776

2777

2778

2779

2780

2781

2782

2783

2784

2785

2786

2787

2788

2789

2790

2791

2792

2793

2794

2795

2796

2797

2798

2799

2800

2801

2802

2803

2804

2805

2806

2807

2808

2809

2810

2811

2812

2813

2814

2815

2816

2817

2818

2819

2820

2821

2822

2823

2824

2825

2826

2827

2828

2829

2830

2831

2832

2833

2834

2835

2836

2837

2838

2839

2840

2841

2842

2843

2844

2845

2846

2847

2848

2849

2850

2851

2852

2853

2854

2855

2856

2857

2858

2859

2860

2861

2862

2863

2864

2865

2866

2867

2868

2869

2870

2871

2872

2873

2874

2875

2876

2877

2878

2879

2880

2881

2882

2883

2884

2885

2886

2887

2888

2889

2890

2891

2892

2893

2894

2895

2896

2897

2898

2899

2900

2901

2902

2903

2904

2905

2906

2907

2908

2909

2910

2911

2912

2913

2914

2915

2916

2917

2918

2919

2920

2921

2922

2923

2924

2925

2926

2927

2928

2929

2930

2931

2932

2933

2934

2935

2936

2937

2938

2939

2940

2941

2942

2943

2944

2945

2946

2947

2948

2949

2950

2951

2952

2953

2954

2955

2956

2957

2958

2959

2960

2961

2962

2963

2964

2965

2966

2967

2968

2969

2970

2971

2972

2973

2974

2975

2976

2977

2978

2979

2980

2981

2982

2983

2984

2985

2986

2987

2988

2989

2990

2991

2992

2993

2994

2995

2996

2997

2998

2999

3000

3001

3002

3003

3004

3005

3006

3007

3008

3009

3010

3011

3012

3013

3014

3015

3016

3017

3018

3019

3020

3021

3022

3023

3024

3025

3026

3027

3028

3029

3030

3031

3032

3033

3034

3035

3036

3037

3038

3039

3040

3041

3042

3043

3044

3045

3046

3047

3048

3049

3050

3051

3052

3053

3054

3055

3056

3057

3058

3059

3060

3061

3062

3063

3064

3065

3066

3067

3068

3069

3070

3071

3072

3073

3074

3075

3076

3077

3078

3079

3080

3081

3082

3083

3084

3085

3086

3087

3088

3089

3090

3091

3092

3093

3094

3095

3096

3097

3098

3099

3100

3101

3102

3103

3104

3105

3106

3107

3108

3109

3110

3111

3112

3113

3114

3115

3116

3117

3118

3119

3120

3121

3122

3123

3124

3125

3126

3127

3128

3129

3130

3131

3132

3133

3134

3135

3136

3137

3138

3139

3140

3141

3142

3143

3144

3145

3146

3147

3148

3149

3150

3151

3152

3153

3154

3155

3156

3157

3158

3159

3160

3161

3162

3163

3164

3165

3166

3167

3168

3169

3170

3171

3172

3173

3174

3175

3176

3177

3178

3179

3180

3181

3182

3183

3184

3185

3186

3187

3188

3189

3190

3191

3192

3193

3194

3195

3196

3197

3198

3199

3200

3201

3202

3203

3204

3205

3206

3207

3208

3209

3210

3211

3212

3213

3214

3215

3216

3217

3218

3219

3220

3221

3222

3223

3224

3225

3226

3227

3228

3229

3230

3231

3232

3233

3234

3235

3236

3237

3238

3239

3240

3241

3242

3243

3244

3245

3246

3247

3248

3249

3250

3251

3252

3253

3254

3255

3256

3257

3258

3259

3260

3261

3262

3263

3264

3265

3266

3267

3268

3269

3270

3271

3272

3273

3274

3275

3276

3277

3278

3279

3280

3281

3282

3283

3284

3285

3286

3287

3288

3289

3290

3291

3292

3293

3294

3295

3296

3297

3298

3299

3300

3301

3302

3303

3304

3305

3306

3307

3308

3309

3310

3311

3312

3313

3314

3315

3316

3317

3318

3319

3320

3321

3322

3323

3324

3325

3326

3327

3328

3329

3330

3331

3332

3333

3334

3335

3336

3337

3338

3339

3340

3341

3342

3343

3344

3345

3346

3347

3348

3349

3350

3351

3352

3353

3354

3355

3356

3357

3358

3359

3360

3361

3362

3363

3364

3365

3366

3367

3368

3369

3370

3371

3372

3373

3374

3375

3376

3377

3378

3379

3380

3381

3382

3383

3384

3385

3386

3387

3388

3389

3390

3391

3392

3393

3394

3395

3396

3397

3398

3399

3400

3401

3402

3403

3404

3405

3406

3407

3408

3409

3410

3411

3412

3413

3414

3415

3416

3417

3418

3419

3420

3421

3422

3423

CREATE OR REPLACE PACKAGE pkg_utility_lhr AUTHID CURRENT_USER IS

v_pkg_name VARCHAR2(255) := upper('pkg_utility_lhr');

---变量

--------------- 第一部分：函数 ------------------------

------① 得到表的索引列

FUNCTION fun_get_index_column_lhr(p_owner VARCHAR2,

p_index_name VARCHAR2,

p_table_name VARCHAR2) RETURN VARCHAR2;

---- ② long类型转化为VARCHAR2

FUNCTION fun_long_to_char_lhr(in_rowid ROWID,

in_owner VARCHAR,

in_table_name VARCHAR,

in_column VARCHAR2) RETURN VARCHAR;

----④ 得到一个字符串中特殊字符的个数

FUNCTION fun_getspecharcounts_lhr(p_string IN VARCHAR2,

p_spechar IN VARCHAR2) RETURN NUMBER;

-----⑦ 该函数返回比率

FUNCTION fun_get_percent_lhr(p_fenzi NUMBER,

p_fenmu NUMBER,

p_precision NUMBER) RETURN VARCHAR2;

-----⑧ 该函数返回一个对象的大小

FUNCTION fun_get_objectsize_lhr(p_objectname VARCHAR2) RETURN VARCHAR2;

------⑨ 该函数返回一个秒转换为标准时间

FUNCTION fun_get_total_time_lhr(p_total_seconds NUMBER,

p_flag VARCHAR2 DEFAULT 'S') --秒

RETURN VARCHAR2 DETERMINISTIC;

------（19）该函数得到送给ods数据的提取格式

FUNCTION fun_get_ods_string_lhr(p_table VARCHAR2) RETURN VARCHAR2;

------（19--1）该函数得到送给ods数据的提取格式2

FUNCTION fun_get_ods_string2_lhr(p_table VARCHAR2,

p_separator VARCHAR2 DEFAULT '|')

RETURN VARCHAR2;

------（20）该函数得到 spool 数据的提取格式

FUNCTION fun_get_spool_string_lhr(p_table VARCHAR2) RETURN VARCHAR2;

FUNCTION fun_get_spool_string2_lhr(p_table VARCHAR2) RETURN VARCHAR2;

------（21）该函数将 interval day to second 转换为易读格式

FUNCTION fun_get_interval_date_lhr(p_date INTERVAL DAY TO SECOND)

RETURN VARCHAR2;

------（22）该函数将字符串分割成多行返回一个table 集合

FUNCTION fun_get_tab_lhr(p_string IN VARCHAR2,

p_separator IN VARCHAR2 DEFAULT ',')

RETURN type_tab_lhr;

------（23）该函数将得到视图的创建语句

FUNCTION fun_get_create_view_lhr(p_name VARCHAR2, p_length NUMBER)

RETURN VARCHAR2;

------（24）该函数将得到表的创建语句

FUNCTION fun_get_create_table_lhr(p_owner VARCHAR2, p_name VARCHAR2)

RETURN VARCHAR2;

------（25）该函数得到正确的数字

FUNCTION fun_get_right_number_lhr(p_number NUMBER,

p_precision NUMBER,

p_str VARCHAR2 DEFAULT NULL)

RETURN VARCHAR2 DETERMINISTIC;

------（27）该函数判断2个表的列是否相同

FUNCTION f_get_table_is_same(p_user1 VARCHAR2,

p_table1 VARCHAR2,

p_user2 VARCHAR2,

p_table2 VARCHAR2,

is_order NUMBER -- 1表示按照列号来比对，0或2表示按照列名称来比对

) RETURN VARCHAR2;

------------------------------------------------------------------------------------------------------ 得到行转列的语句 2013-06-07 17:58:52

FUNCTION f_get_row2_col_sql_lhr(p_table VARCHAR2,

p_cols VARCHAR2,

p_c0 VARCHAR2,

p_c1 VARCHAR2 DEFAULT NULL,

p_c2 VARCHAR2 DEFAULT NULL,

p_c3 VARCHAR2 DEFAULT NULL,

p_c4 VARCHAR2 DEFAULT NULL,

p_c5 VARCHAR2 DEFAULT NULL,

p_c6 VARCHAR2 DEFAULT NULL,

p_c7 VARCHAR2 DEFAULT NULL,

p_c8 VARCHAR2 DEFAULT NULL,

p_c9 VARCHAR2 DEFAULT NULL,

p_c10 VARCHAR2 DEFAULT NULL)

RETURN VARCHAR2;

FUNCTION f_get_row2_col_sql_lhr_01(p_table VARCHAR2,

p_cols VARCHAR2,

p_c0 VARCHAR2,

p_c1 VARCHAR2 DEFAULT NULL,

p_c2 VARCHAR2 DEFAULT NULL,

p_c3 VARCHAR2 DEFAULT NULL,

p_c4 VARCHAR2 DEFAULT NULL,

p_c5 VARCHAR2 DEFAULT NULL,

p_c6 VARCHAR2 DEFAULT NULL,

p_c7 VARCHAR2 DEFAULT NULL,

p_c8 VARCHAR2 DEFAULT NULL,

p_c9 VARCHAR2 DEFAULT NULL,

p_c10 VARCHAR2 DEFAULT NULL)

RETURN VARCHAR2;

FUNCTION f_get_row2_col_sql_lhr_02(p_c1 VARCHAR2 DEFAULT NULL,

p_c2 VARCHAR2 DEFAULT NULL,

p_c3 VARCHAR2 DEFAULT NULL,

p_c4 VARCHAR2 DEFAULT NULL,

p_c5 VARCHAR2 DEFAULT NULL,

p_c6 VARCHAR2 DEFAULT NULL,

p_c7 VARCHAR2 DEFAULT NULL,

p_c8 VARCHAR2 DEFAULT NULL,

p_c9 VARCHAR2 DEFAULT NULL,

p_c10 VARCHAR2 DEFAULT NULL,

p_t VARCHAR2) RETURN VARCHAR2;

FUNCTION f_ToNumber_lhr(strNumber VARCHAR2) RETURN NUMBER;

------（28）该函数得到平均值

FUNCTION f_get_avg_lhr(p_fenzi NUMBER,

p_fenmu NUMBER,

p_precision NUMBER) RETURN VARCHAR2;

--- 得到sql_trace的文件路径

FUNCTION f_get_sql_trace_name_lhr RETURN VARCHAR2;

FUNCTION F_GET_DEPENDENCIES_LHR(p_referenced_name VARCHAR2) RETURN VARCHAR2;

---- 得到url

FUNCTION f_get_url_encode(url_string IN VARCHAR2,

charset IN VARCHAR2 DEFAULT 'GB2312')

RETURN VARCHAR2;

---返回正确的大小入参为字节

FUNCTION f_get_right_size_lhr(p_size IN VARCHAR2,

p_precision number default 3) RETURN VARCHAR2;

------动态sql 可以通过函数来返回

FUNCTION f_get_exec_sql_lhr(p_sql IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_get_objectsize_lhr(p_owner VARCHAR2,

p_segment_name VARCHAR2,

p_partition_name VARCHAR2,

p_flag NUMBER,

p_return_flag NUMBER DEFAULT NULL)

RETURN VARCHAR2;

FUNCTION f_get_sql_id_lhr(p_sid NUMBER) RETURN VARCHAR2;

FUNCTION f_get_sqltune_lhr(p_sqltext CLOB,

p_user_name VARCHAR2 DEFAULT USER) RETURN CLOB;

---得到max+decode语句

FUNCTION f_get_max_decode_lhr(p_tablename VARCHAR2,

p_groupby_col VARCHAR2,

p_value_col VARCHAR2) RETURN VARCHAR2;

------------------- 历史执行计划

FUNCTION f_get_sqlplan_his_lhr(p_sqlid IN VARCHAR2,

p_sid NUMBER DEFAULT '',

P_KEY NUMBER DEFAULT '',

P_FLAG NUMBER DEFAULT 3)

RETURN SYS_REFCURSOR;

--------------- 第二部分：存过 ------------------------

--- ② 创建表和注释

PROCEDURE pro_create_table_lhr(p_user VARCHAR2, -- 表的用户

p_tablename VARCHAR2, --表名

p_tablenamecn VARCHAR2 DEFAULT NULL --表的解释

);

--- ④ 实现字符串转多列

PROCEDURE pro_str_change_column_lhr(p_tablename VARCHAR2, --要转换的表名

p_column VARCHAR2, --要转换的列名

p_spechar VARCHAR2 --要转换的列名中的特殊字符，即分隔符

);

--- ④ 实现字符串转多行

PROCEDURE p_str_change_row_lhr(p_tablename VARCHAR2, --要转换的表名

p_column VARCHAR2, --要转换的列名

p_spechar VARCHAR2 --要转换的列名中的特殊字符，即分隔符

);

-----⑥ 插入clob字段

PROCEDURE pro_insert_clob_lhr(ASQL VARCHAR2, ALongVar1 CLOB);

-----⑦ job 的执行频率检查 2013-06-09 16:50:00

PROCEDURE P_JOB_repeat_interval_LHR(v_sql VARCHAR2);

-----⑧ 删除历史数据

PROCEDURE P_del_LHR(p_user VARCHAR2,

p_table VARCHAR2,

p_col VARCHAR2,

p_tjdate VARCHAR2);

--------⑨ 展示表空间的情况

PROCEDURE P_show_space_LHR(p_segname_1 IN VARCHAR2, -- 表名称

p_type_1 IN VARCHAR2 DEFAULT 'TABLE',

p_space IN VARCHAR2 DEFAULT 'AUTO', --MANUAL

p_analyzed IN VARCHAR2 DEFAULT 'Y', --N

p_partition_1 IN VARCHAR2 DEFAULT NULL,

p_owner_1 IN VARCHAR2 DEFAULT USER);

--------- 收缩表空间

PROCEDURE p_move_table_lhr(p_table VARCHAR2,

p_owner VARCHAR2 DEFAULT USER,

p_parallel NUMBER DEFAULT 1);

--------- 移动表空间

PROCEDURE p_move_tablespace_lhr(p_table VARCHAR2,

p_owner VARCHAR2 DEFAULT USER,

p_tablespace VARCHAR2 DEFAULT 'SDH_DATA');

----创建同义词

PROCEDURE P_create_synonym_LHR(P_OWNER VARCHAR2, P_OBJECT_TYPE VARCHAR2);

------得到执行计划

PROCEDURE p_sql_explain_lhr(stmt VARCHAR2,

format VARCHAR2 DEFAULT 'ADVANCED',

exponly BOOLEAN DEFAULT TRUE);

------修改分区名称

PROCEDURE p_rename_partition_lhr(p_owner VARCHAR2,

p_table_name VARCHAR2,

v_type NUMBER DEFAULT 1 -- 1代表分区表，2代表分区索引

);

-------分析表

PROCEDURE p_gather_table_stats_lhr(p_owner VARCHAR2,

p_table_name VARCHAR2,

p_cascade CHAR DEFAULT 'TRUE' --是否分析索引

);

--------执行动态sql

PROCEDURE p_exec_sql_lhr(p_number NUMBER);

------------分析索引碎片

PROCEDURE P_ANALYZE_INDEX_SATAS_LHR;

PROCEDURE P_ANALYZE_INDEX_ERROR_LHR;

-----------------将数据行转为列显示

PROCEDURE p_print_table_lhr(p_query IN VARCHAR2);

----------------创建临时外部表查看告警事件

PROCEDURE p_externaltable_lhr(p_tracename VARCHAR2,

P_directory VARCHAR2 DEFAULT 'DIR_ALERT_LHR');

---------------估算即将创建的索引大小?

PROCEDURE p_create_index_size_lhr(p_sql VARCHAR2);

END pkg_utility_lhr;

CREATE OR REPLACE PACKAGE BODY pkg_utility_lhr IS

---------------------------- 第一部分：函数 ------------------------

FUNCTION fun_get_index_column_lhr(p_owner VARCHAR2,

p_index_name VARCHAR2,

p_table_name VARCHAR2) RETURN VARCHAR2 IS

-----------------------------------------------------------------------------------

-- Created on 2012/12/2 1:29:13 by lhr

--Changed on2012/12/2 1:29:13 by lhr

-- function：该函数返回索引列

-----------------------------------------------------------------------------------

v_results VARCHAR2(4000);

v_index VARCHAR2(4000);

v_temp VARCHAR2(4000);

v_counts NUMBER;

v_num NUMBER := 0;

v_counts1 NUMBER;

BEGIN

----LOB类型的索引

SELECT COUNT(1)

INTO v_counts1

FROM dba_indexes d

WHERE d.index_name = p_index_name

AND d.owner = p_owner

AND d.index_type = 'LOB';

IF v_counts1 > 0 THEN

SELECT DIC.COLUMN_NAME

INTO v_index

FROM dba_lobs dic

WHERE dic.INDEX_NAME = p_index_name

AND dic.Table_Name = p_table_name

AND dic.OWNER = p_owner;

RETURN v_index;

END IF;

----其它类型的索引

SELECT COUNT(1)

INTO v_counts

FROM dba_ind_expressions dic

WHERE dic.index_name = p_index_name

AND dic.table_name = p_table_name

AND dic.INDEX_OWNER = p_owner;

IF v_counts > 0 THEN

FOR cur IN (SELECT dic.*

FROM dba_ind_expressions dic

WHERE dic.index_name = p_index_name

AND dic.table_name = p_table_name

AND dic.INDEX_OWNER = p_owner

) LOOP

v_num := v_num + 1;

IF v_num = v_counts THEN

v_temp := cur.column_expression;

ELSE

v_temp := cur.column_expression || ',' || v_temp;

END IF;

END LOOP;

BEGIN

SELECT to_char(wmsys.wm_concat(dic.column_name))

INTO v_index

FROM dba_ind_columns dic

WHERE dic.index_name = p_index_name

AND dic.table_name = p_table_name

AND dic.COLUMN_NAME NOT LIKE 'SYS%'

AND dic.INDEX_OWNER = p_owner

GROUP BY dic.index_name, dic.table_name;

EXCEPTION

WHEN OTHERS THEN

v_results := v_temp;

RETURN v_results;

END;

v_results := v_index || ',' || v_temp;

RETURN v_results;

ELSE

SELECT to_char(wmsys.wm_concat(dic.column_name))

INTO v_index

FROM dba_ind_columns dic

WHERE dic.index_name = p_index_name

AND dic.table_name = p_table_name

AND dic.INDEX_OWNER = p_owner

GROUP BY dic.index_name, dic.table_name;

RETURN v_index;

END IF;

RETURN v_results;

EXCEPTION

WHEN OTHERS THEN

RETURN v_index;

END fun_get_index_column_lhr;

---------------------------------------------------------------------------------------

FUNCTION fun_long_to_char_lhr(in_rowid ROWID,

in_owner VARCHAR,

in_table_name VARCHAR,

in_column VARCHAR2) RETURN VARCHAR AS

text_c1 VARCHAR2(32767);

--text_c1 clob;

sql_cur VARCHAR2(2000);

BEGIN

IF in_owner IS NULL THEN

sql_cur := 'select ' || in_column || ' from ' || in_table_name ||

' where rowid = ' || chr(39) || in_rowid || chr(39);

-- dbms_output.put_line(sql_cur);

ELSE

sql_cur := 'select ' || in_column || ' from ' || in_owner || '.' ||

in_table_name || ' where rowid = ' || chr(39) || in_rowid ||

chr(39);

-- dbms_output.put_line(sql_cur);

END IF;

EXECUTE IMMEDIATE sql_cur

INTO text_c1;

text_c1 := substr(text_c1, 1, 4000);

RETURN text_c1;

EXCEPTION

WHEN OTHERS THEN

RETURN '';

END fun_long_to_char_lhr;

---------------------------------------------------------------------------------------

----④ 得到一个字符串中特殊字符的个数

--- 11g可以使用：select REGEXP_COUNT(t.importance2,';',1) from sjcl_10maxsize_table_lhr t;

FUNCTION fun_getspecharcounts_lhr(p_string IN VARCHAR2,

p_spechar IN VARCHAR2) RETURN NUMBER AS

v_count NUMBER := 0;

v_position NUMBER := 0; --特殊字符的位置

-- v_Spechar VARCHAR2(5);

BEGIN

---没有这句如果入参为空就会陷入死循环

IF p_string IS NULL THEN

RETURN '';

END IF;

LOOP

--找到特殊字符的位置

SELECT instr(p_string, p_spechar, v_position + 1)

INTO v_position

FROM dual;

--判断是否退出循环

IF v_position >= length(p_string) THEN

v_count := v_count + 1;

EXIT;

ELSIF v_position = 0 THEN

EXIT;

END IF;

v_count := v_count + 1;

END LOOP;

RETURN v_count;

END fun_getspecharcounts_lhr;

---------------------------------------------------------------------------------------

-----⑦ 该函数返回比率

FUNCTION fun_get_percent_lhr(p_fenzi NUMBER,

p_fenmu NUMBER,

p_precision NUMBER) RETURN VARCHAR2 IS

RESULT VARCHAR2(255);

v_percent VARCHAR2(255);

BEGIN

IF p_fenzi IS NULL OR p_fenmu IS NULL THEN

RETURN '';

ELSIF p_fenzi = 0 OR p_fenmu = 0 THEN

RETURN 0 || '%';

ELSE

v_percent := (p_fenzi / p_fenmu) * 100;

RESULT := CASE

WHEN v_percent < 1 THEN

0 || round(v_percent, p_precision) || '%'

ELSE

round(v_percent, p_precision) || '%'

END;

SELECT decode(RESULT, '00%', '0%', RESULT) INTO RESULT FROM dual;

RETURN RESULT;

END IF;

END fun_get_percent_lhr;

---------------------------------------------------------------------------------------

-----⑧ 该函数返回一个对象的大小

FUNCTION fun_get_objectsize_lhr(p_objectname VARCHAR2) RETURN VARCHAR2 IS

-----------------------------------------------------------------------------------

-- Created on 2012/9/3 13:00:21 by lhr

--Changed on 2012/9/3 13:00:21 by lhr

-- function：

-----------------------------------------------------------------------------------

RESULTS VARCHAR2(255);

BEGIN

SELECT SUM(bytes) / (1024 * 1024 * 1024)

INTO RESULTS

FROM user_segments

WHERE segment_name = upper(p_objectname);

IF RESULTS IS NULL THEN

RETURN '表' || upper(p_objectname) || '不存在';

ELSIF RESULTS < 1 THEN

SELECT round(RESULTS * 1024, 3) INTO RESULTS FROM dual;

IF RESULTS < 1 THEN

RETURN 0 || RESULTS || ' M';

ELSE

RETURN RESULTS || ' M';

END IF;

ELSE

RETURN round(RESULTS, 3) || ' G';

END IF;

RETURN RESULTS;

EXCEPTION

WHEN OTHERS THEN

NULL;

END fun_get_objectsize_lhr;

---------------------------------------------------------------------------------------

------⑨ 该函数返回一个秒转换为标准时间的字符串

FUNCTION fun_get_total_time_lhr(p_total_seconds NUMBER,

p_flag VARCHAR2 DEFAULT 'S')

RETURN VARCHAR2 DETERMINISTIC IS

-----------------------------------------------------------------------------------

-- Created on 2012/10/18 16:49:28 by lhr

--Changed on 2015/9/3 16:49:28 by lhr

-- function：该函数返回一个秒转换为标准时间

-----------------------------------------------------------------------------------

v_tmp FLOAT(18);

v_returns VARCHAR2(200);

V_total_seconds NUMBER := p_total_seconds;

BEGIN

IF p_total_seconds IS NULL OR p_total_seconds = 0 THEN

RETURN 0 || '秒';

END IF;

---传入的为天转换为秒

IF UPPER(p_flag) = 'D' THEN

V_total_seconds := p_total_seconds * 24 * 60 * 60;

END IF;

-- 首先处理日期

--年

v_tmp := trunc(V_total_seconds / (12 * 30 * 24 * 3600));

IF v_tmp > 0 THEN

v_returns := v_tmp || '年';

END IF;

---月

v_tmp := trunc(MOD(V_total_seconds, (12 * 30 * 24 * 3600)) /

(3600 * 24 * 30));

IF v_tmp > 0 THEN

v_returns := v_returns || v_tmp || '月';

END IF;

--天

v_tmp := trunc(MOD(V_total_seconds, (3600 * 24 * 30)) / (3600 * 24));

IF v_tmp > 0 THEN

v_returns := v_returns || v_tmp || '天';

END IF;

--处理小时

v_tmp := trunc(MOD(V_total_seconds, 3600 * 24) / 3600);

IF v_tmp > 0 THEN

v_returns := v_returns || v_tmp || '时';

END IF;

--处理分钟

v_tmp := trunc(MOD(V_total_seconds, 3600) / 60);

IF v_tmp > 0 THEN

v_returns := v_returns || v_tmp || '分';

END IF;

--处理秒钟

v_tmp := trunc(MOD(V_total_seconds, 60), 3);

IF v_tmp > 0 AND v_tmp < 1 THEN

v_returns := v_returns || '0' || v_tmp || '秒';

ELSIF v_tmp >= 1 THEN

v_returns := v_returns || v_tmp || '秒';

ELSIF v_tmp = 0 AND v_returns IS NULL THEN

v_returns := '0' || trunc(V_total_seconds, 6) || '秒';

END IF;

RETURN v_returns;

END fun_get_total_time_lhr;

---------------------------------------------------------------------------------------

------（19）该函数得到送给ods数据的提取格式

FUNCTION fun_get_ods_string_lhr(p_table VARCHAR2) RETURN VARCHAR2 AS

-----------------------------------------------------------------------------------

-- Created on 2012/11/07 11:33:07 by lhr

--Changed on 2012/11/07 11:33:07 by lhr

-- function：

/* drop table XB_COLS_LHR;

Create Table XB_COLS_LHR(

id number,

lie varchar2(4000)

);*/

-----------------------------------------------------------------------------------

v_return VARCHAR2(4000);

v_return1 VARCHAR2(4000);

v_return2 VARCHAR2(4000);

v_max VARCHAR2(255);

v_table VARCHAR2(255) := upper(p_table);

v_date_type VARCHAR2(255) := 'YYYY-MM-DD HH24:MI:SS';

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

SELECT COUNT(1) INTO v_max FROM cols c WHERE c.table_name = v_table;

IF v_max >= 150 THEN

SELECT 'SELECT REPLACE(REPLACE(' ||

REPLACE(REPLACE(to_char(wmsys.wm_concat(decode(c.column_id,

v_max,

c.column_name ||

'||chr(5)',

c.column_name ||

'||chr(5)||'))),

',',

''),

'，',

',') ---modify

INTO v_return1

FROM (SELECT t.table_name,

decode(t.data_type,

upper('DATE'),

'to_char(t.' || t.column_name || '，''' ||

v_date_type || ''')',

't.' || t.column_name) column_name,

t.column_id

FROM cols t

WHERE t.table_name = v_table ---modify

AND t.column_id <= 120

ORDER BY t.column_id) c;

EXECUTE IMMEDIATE 'truncate table XB_COLS_LHR';

INSERT INTO xb_cols_lhr VALUES (1, v_return1);

COMMIT;

SELECT REPLACE(REPLACE(to_char(wmsys.wm_concat(decode(c.column_id,

v_max,

c.column_name ||

'||chr(5)',

c.column_name ||

'||chr(5)||'))),

',',

''),

'，',

',') || ',chr(10),''''),chr(13),'''') FROM ' ||

v_table || ' t;' ---modify

INTO v_return2

FROM (SELECT t.table_name,

decode(t.data_type,

upper('DATE'),

'to_char(t.' || t.column_name || '，''' ||

v_date_type || ''')',

't.' || t.column_name) column_name,

t.column_id

FROM cols t

WHERE t.table_name = v_table ---modify

AND t.column_id > 120

ORDER BY t.column_id) c;

INSERT INTO xb_cols_lhr VALUES (2, v_return2);

COMMIT;

v_return := '见表 select * from XB_COLS_LHR; ';

ELSE

SELECT 'SELECT REPLACE(REPLACE(' ||

REPLACE(REPLACE(to_char(wmsys.wm_concat(decode(c.column_id,

v_max,

c.column_name ||

'||chr(5)',

c.column_name ||

'||chr(5)||'))),

',',

''),

'，',

',') || ',chr(10),''''),chr(13),'''') FROM ' ||

v_table || ' t;' ---modify

INTO v_return

FROM (SELECT t.table_name,

decode(t.data_type,

upper('DATE'),

'to_char(t.' || t.column_name || '，''' ||

v_date_type || ''')',

't.' || t.column_name) column_name,

t.column_id

FROM cols t

WHERE t.table_name = v_table ---modify

ORDER BY t.column_id) c;

END IF;

RETURN v_return;

END fun_get_ods_string_lhr;

---------------------------------------------------------------------------------------

------（19--1）该函数得到送给ods数据的提取格式 ,可以定义分隔符

FUNCTION fun_get_ods_string2_lhr(p_table VARCHAR2,

p_separator VARCHAR2 DEFAULT '|')

RETURN VARCHAR2 AS

-----------------------------------------------------------------------------------

-- Created on 2012/11/15 11:33:07 by lhr

--Changed on 2012/11/15 11:33:07 by lhr

-- function：

/* drop table XB_COLS_LHR;

Create Table XB_COLS_LHR(

id number,

lie varchar2(4000)

);*/

-----------------------------------------------------------------------------------

v_return VARCHAR2(4000);

v_return1 VARCHAR2(4000);

v_return2 VARCHAR2(4000);

v_max VARCHAR2(255);

v_table VARCHAR2(255) := upper(p_table);

v_date_type VARCHAR2(255) := 'YYYY-MM-DD HH24:MI:SS';

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

SELECT COUNT(1) INTO v_max FROM cols c WHERE c.table_name = v_table;

IF v_max >= 150 THEN

SELECT 'SELECT REPLACE(REPLACE(' ||

REPLACE(REPLACE(to_char(wmsys.wm_concat(decode(c.column_id,

v_max,

c.column_name ||

'||''' ||

p_separator,

c.column_name ||

'||''' ||

p_separator ||

'''||'))),

',',

''),

'，',

',') ---modify

INTO v_return1

FROM (SELECT t.table_name,

decode(t.data_type,

upper('DATE'),

'to_char(t.' || t.column_name || '，''' ||

v_date_type || ''')',

't.' || t.column_name) column_name,

t.column_id

FROM cols t

WHERE t.table_name = v_table ---modify

AND t.column_id <= 120

ORDER BY t.column_id) c;

EXECUTE IMMEDIATE 'truncate table XB_COLS_LHR';

INSERT INTO xb_cols_lhr VALUES (1, v_return1);

COMMIT;

SELECT REPLACE(REPLACE(to_char(wmsys.wm_concat(decode(c.column_id,

v_max,

c.column_name ||

'||''' ||

p_separator || '''',

c.column_name ||

'||''' ||

p_separator ||

'''||'))),

',',

''),

'，',

',') || ',chr(10),''''),chr(13),'''') FROM ' ||

v_table || ' t;' ---modify

INTO v_return2

FROM (SELECT t.table_name,

decode(t.data_type,

upper('DATE'),

'to_char(t.' || t.column_name || '，''' ||

v_date_type || ''')',

't.' || t.column_name) column_name,

t.column_id

FROM cols t

WHERE t.table_name = v_table ---modify

AND t.column_id > 120

ORDER BY t.column_id) c;

INSERT INTO xb_cols_lhr VALUES (2, v_return2);

COMMIT;

v_return := '见表 select * from XB_COLS_LHR; ';

ELSE

SELECT 'SELECT REPLACE(REPLACE(' ||

REPLACE(REPLACE(to_char(wmsys.wm_concat(decode(c.column_id,

v_max,

c.column_name ||

'||''' ||

p_separator || '''',

c.column_name ||

'||''' ||

p_separator ||

'''||'))),

',',

''),

'，',

',') || ',chr(10),''''),chr(13),'''') FROM ' ||

v_table || ' t;' ---modify

INTO v_return

FROM (SELECT t.table_name,

decode(t.data_type,

upper('DATE'),

'to_char(t.' || t.column_name || '，''' ||

v_date_type || ''')',

't.' || t.column_name) column_name,

t.column_id

FROM cols t

WHERE t.table_name = v_table ---modify

ORDER BY t.column_id) c;

END IF;

RETURN v_return;

END fun_get_ods_string2_lhr;

---------------------------------------------------------------------------------------

------（20）该函数得到 spool 数据的提取格式

FUNCTION fun_get_spool_string_lhr(p_table VARCHAR2) RETURN VARCHAR2 AS

-----------------------------------------------------------------------------------

-- Created on 2012/11/07 11:33:07 by lhr

--Changed on 2012/11/07 11:33:07 by lhr

-- function：

-----------------------------------------------------------------------------------

v_return VARCHAR2(32767);

v_return1 VARCHAR2(32767);

v_return2 VARCHAR2(32767);

v_max VARCHAR2(255);

v_table VARCHAR2(255) := upper(p_table);

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

SELECT COUNT(1) INTO v_max FROM cols c WHERE c.table_name = v_table;

---列

SELECT 'SELECT ' || 0 || '||'',' ||

to_char(wmsys.wm_concat(decode(t.column_id,

v_max,

'''||''' || t.column_name || '''',

'''||''' || t.column_name ||

'''||''')

)) || ' lie FROM dual a '

INTO v_return1

FROM (SELECT c.column_name, c.column_id

FROM cols c

WHERE c.table_name = v_table

ORDER BY c.column_id) t;

--表

SELECT 'UNION SELECT replace(replace( ' || 1 || '||'',' ||

to_char(wmsys.wm_concat(decode(t.column_id,

v_max,

''' || REPLACE(' || t.column_name ||

','','',''、'')',

''' || REPLACE(' || t.column_name ||

','','',''、'')||'''))) ||

',chr(10),''''),chr(13),'''') from ' || v_table

INTO v_return2

FROM (SELECT c.column_name, c.column_id

FROM cols c

WHERE c.table_name = v_table

ORDER BY c.column_id) t;

IF length(v_return2) > 2000 THEN

EXECUTE IMMEDIATE 'truncate table XB_COLS_LHR';

INSERT INTO xb_cols_lhr

VALUES

(1, 'SELECT substr(t.lie, 3) FROM (' || v_return1);

INSERT INTO xb_cols_lhr VALUES (2, v_return2 || ') t;');

COMMIT;

v_return := '见表 select * from XB_COLS_LHR; ';

ELSE

v_return := 'SELECT substr(t.lie, 3) FROM (' || v_return1 ||

v_return2 || ') t;';

END IF;

RETURN v_return;

END fun_get_spool_string_lhr;

FUNCTION fun_get_spool_string2_lhr(p_table VARCHAR2) RETURN VARCHAR2 AS

-----------------------------------------------------------------------------------

-- Created on 2012/11/07 11:33:07 by lhr

--Changed on 2015/07/29 11:33:07 by lhr

-- function：

-----------------------------------------------------------------------------------

v_return VARCHAR2(32767);

v_return1 VARCHAR2(32767);

v_return2 VARCHAR2(32767);

v_return3 VARCHAR2(32767);

v_max VARCHAR2(255);

v_table VARCHAR2(255) := upper(p_table);

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

SELECT COUNT(1) INTO v_max FROM cols c WHERE c.table_name = v_table;

---列

SELECT 'SELECT ' || 0 || ',' ||

to_char(wmsys.wm_concat('''' || t.column_name || '''' || ' ' ||

t.column_name)) || ' FROM dual a '

INTO v_return1

FROM (SELECT c.column_name, c.column_id

FROM cols c

WHERE c.table_name = v_table

ORDER BY c.column_id) t;

--表

SELECT 'UNION all SELECT ' || 1 || ',' ||

to_char(wmsys.wm_concat(decode(t.data_type,

'CLOB',

'dbms_lob.substr(' ||

t.column_name || ',1,4000) ' ||

t.column_name,

t.column_name || '||''''' || ' ' ||

t.column_name))) || ' from ' ||

v_table

INTO v_return2

FROM (SELECT c.column_name, c.column_id, c.DATA_TYPE

FROM cols c

WHERE c.table_name = v_table

ORDER BY c.column_id) t;

--合并

SELECT 'SELECT ' || to_char(wmsys.wm_concat(t.column_name)) ||

' from (' || v_return1 || v_return2 || ') t;'

INTO v_return3

FROM (SELECT c.column_name, c.column_id, c.DATA_TYPE

FROM cols c

WHERE c.table_name = v_table

ORDER BY c.column_id) t;

IF length(v_return2) > 2000 THEN

EXECUTE IMMEDIATE 'truncate table XB_COLS_LHR';

INSERT INTO xb_cols_lhr VALUES (1, v_return3);

COMMIT;

v_return := '见表 select * from XB_COLS_LHR; ';

ELSE

v_return := v_return3;

END IF;

RETURN v_return;

END fun_get_spool_string2_lhr;

---------------------------------------------------------------------------------------

------（21）该函数将 interval day to second 转换为易读格式

FUNCTION fun_get_interval_date_lhr(p_date INTERVAL DAY TO SECOND)

RETURN VARCHAR2 AS

-----------------------------------------------------------------------------------

-- Created on 2012/11/13 11:36:09 by lhr

--Changed on 2012/11/13 11:36:09 by lhr

-- function：

-----------------------------------------------------------------------------------

v_return VARCHAR2(255);

v_day VARCHAR2(255) := extract(DAY FROM p_date);

v_hour VARCHAR2(255) := extract(hour FROM p_date);

v_minute VARCHAR2(255) := extract(minute FROM p_date);

v_second VARCHAR2(255) := extract(SECOND FROM p_date);

v_second1 VARCHAR2(255);

BEGIN

v_second1 := CASE

WHEN v_second < 1 AND v_second > 0 THEN

'0' || round(v_second, 3) || '秒'

ELSE

round(v_second, 3) || '秒'

END;

IF v_day != 0 THEN

RETURN v_day || '天' || v_hour || '时' || v_minute || '分' || v_second1;

ELSIF v_hour != 0 THEN

RETURN v_hour || '时' || v_minute || '分' || v_second1;

ELSIF v_minute != 0 THEN

RETURN v_minute || '分' || v_second1;

ELSIF v_second != 0 THEN

RETURN v_second1;

END IF;

RETURN v_return;

END fun_get_interval_date_lhr;

---------------------------------------------------------------------------------------

------（22）该函数将字符串分割成多行返回一个table 集合

FUNCTION fun_get_tab_lhr(p_string IN VARCHAR2,

p_separator IN VARCHAR2 DEFAULT ',')

RETURN type_tab_lhr IS

-----------------------------------------------------------------------------------

-- Created on 2012/11/14 20:37:51 by lhr

--Changed on 2012/11/14 20:37:51 by lhr

-- function：单个函数返回多行单列

--create or replace type type_tab_lhr is table of varchar2(2000);

-- 测试用例:SELECT * FROM TABLE(pkg_lhr_all_package.fun_get_tab_lhr('3,6,5,23,756'));

--select * from the(select pkg_lhr_all_package.fun_get_tab_lhr('3,6,5,23,756') from dual);

-----------------------------------------------------------------------------------

v_tab type_tab_lhr := type_tab_lhr(); --返回内存表

v_num NUMBER := 1; --行数和分隔符出现的次数

v_value VARCHAR2(255);

v_position NUMBER;

v_last_position NUMBER := 0;

BEGIN

--有一个参数为空就退出函数

IF p_string IS NULL THEN

RETURN NULL;

END IF;

--利用循环来插入到表类型中

LOOP

--找到分隔符的位置

SELECT decode(instr(p_string, p_separator, 1, v_num),

length(p_string) + 1,

instr(p_string, p_separator, 1, v_num))

INTO v_position

FROM dual;

---提取需要的值

v_value := substr(p_string,

v_last_position + 1,

v_position - v_last_position - 1);

v_last_position := v_position;

---对内存表进行赋值

v_tab.extend;

v_tab(v_num) := v_value;

v_num := v_num + 1;

IF v_position >= length(p_string) THEN

EXIT;

END IF;

END LOOP;

RETURN v_tab;

EXCEPTION

WHEN OTHERS THEN

v_tab.delete;

END fun_get_tab_lhr;

------（23）该函数将得到视图的创建语句

FUNCTION fun_get_create_view_lhr(p_name VARCHAR2, p_length NUMBER)

RETURN VARCHAR2 AS

sql_cur VARCHAR2(255);

text_c1 VARCHAR2(4000);

/* SELECT DBMS_METADATA.GET_DDL('VIEW', 'VW_ADDRESS_LHR', 'TELANT')

FROM DUAL;*/

BEGIN

IF p_length > 3900 THEN

RETURN '';

ELSE

sql_cur := 'select text from user_views where VIEW_NAME = ' ||

chr(39) || p_name || chr(39);

-- dbms_output.put_line(sql_cur);

EXECUTE IMMEDIATE sql_cur

INTO text_c1;

text_c1 := 'CREATE OR REPLACE VIEW ' || p_name || ' as ' ||

substr(text_c1, 1, 4000) || ';';

RETURN text_c1;

END IF;

RETURN text_c1;

END fun_get_create_view_lhr;

------------------------------------------------------------------------------------------------------ ------（24）该函数将得到表的创建语句

FUNCTION fun_get_create_table_lhr(p_owner VARCHAR2, p_name VARCHAR2)

RETURN VARCHAR2 AS

v_return VARCHAR2(4000);

BEGIN

SELECT REPLACE(REPLACE(REPLACE(DBMS_METADATA.GET_DDL('TABLE',

p_name,

SCHEMA => p_owner),

'"' || p_owner || '"',

' '),

'.',

' '),

'"',

' ') || ';'

INTO v_return

FROM dual;

RETURN v_return;

EXCEPTION

WHEN OTHERS THEN

RETURN '';

END;

---------------------------------------------------------------------------------------

-----⑦ 该函数返回正确的精度防止 .45 00 等

FUNCTION fun_get_right_number_lhr(p_number NUMBER,

p_precision NUMBER,

p_str VARCHAR2 DEFAULT NULL)

RETURN VARCHAR2 DETERMINISTIC IS

RESULT VARCHAR2(255);

BEGIN

RESULT := CASE

WHEN p_number = '00' THEN

'0' || p_str

WHEN p_number < 1 AND p_number > 0 THEN

'0' || round(p_number, p_precision) || p_str

WHEN p_number < 0 THEN

'0' || p_str

ELSE

round(p_number, p_precision) || p_str

END;

RETURN RESULT;

EXCEPTION

WHEN OTHERS THEN

RETURN round(p_number, p_precision) || p_str;

END fun_get_right_number_lhr;

------------------------------------------------------------------------------------------------------ 比较 2个表的列 2013-06-06 18:27:33

/* drop table xb_table_compare_lhr;

Create Table xb_table_compare_lhr(

column_id number,

table1_column varchar2(255),

table1_column_type varchar2(255),

table2_column varchar2(255),

table2_column_type varchar2(255),

column_name_is_same number,

column_type_is_same number

);

select pkg_utility_lhr.f_get_table_is_same('ff','XT_FTTH_PI_RES_LHR',2) from dual;

select * from xb_table_compare_lhr ;*/

FUNCTION f_get_table_is_same(p_user1 VARCHAR2,

p_table1 VARCHAR2,

p_user2 VARCHAR2,

p_table2 VARCHAR2,

is_order NUMBER --是否按照列的顺序来比较

) RETURN VARCHAR2

v_max_cols NUMBER;

r_compare xb_table_compare_lhr%ROWTYPE;

v_num NUMBER := 1;

p_max_table VARCHAR2(255);

p_max_user VARCHAR2(255);

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

EXECUTE IMMEDIATE 'truncate table xb_table_compare_lhr';

--最大列数

SELECT CASE

WHEN (SELECT COUNT(1)

FROM dba_tab_cols d

WHERE d.TABLE_NAME = upper(p_table1)

AND d.owner = upper(p_user1)) >

(SELECT COUNT(1)

FROM dba_tab_cols d

WHERE d.TABLE_NAME = upper(p_table2)

AND d.owner = upper(p_user2)) THEN

(SELECT COUNT(1)

FROM dba_tab_cols d

WHERE d.TABLE_NAME = upper(p_table1)

AND d.owner = upper(p_user1))

ELSE

(SELECT COUNT(1)

FROM dba_tab_cols d

WHERE d.TABLE_NAME = upper(p_table2)

AND d.owner = upper(p_user2))

END,

CASE

WHEN (SELECT COUNT(1)

FROM dba_tab_cols d

WHERE d.TABLE_NAME = upper(p_table1)

AND d.owner = upper(p_user1)) >

(SELECT COUNT(1)

FROM dba_tab_cols d

WHERE d.TABLE_NAME = upper(p_table2)

AND d.owner = upper(p_user2)) THEN

p_table1

ELSE

p_table2

END,

CASE

WHEN (SELECT COUNT(1)

FROM dba_tab_cols d

WHERE d.TABLE_NAME = upper(p_table1)

AND d.owner = upper(p_user1)) >

(SELECT COUNT(1)

FROM dba_tab_cols d

WHERE d.TABLE_NAME = upper(p_table2)

AND d.owner = upper(p_user2)) THEN

upper(p_user1)

ELSE

upper(p_user2)

END

INTO v_max_cols, p_max_table, p_max_user

FROM dual;

IF is_order = 1 THEN

FOR cur IN 1 .. v_max_cols LOOP

BEGIN

SELECT d.COLUMN_ID,

d.COLUMN_NAME,

d.DATA_TYPE || '(' || d.DATA_LENGTH || ')'

INTO r_compare.column_id,

r_compare.table1_column,

r_compare.table1_column_type

FROM dba_tab_cols d

WHERE d.TABLE_NAME = upper(p_table1)

AND d.COLUMN_ID = v_num

AND d.owner = upper(p_user1);

EXCEPTION

WHEN OTHERS THEN

r_compare.table1_column := '';

r_compare.table1_column_type := '';

END;

BEGIN

SELECT d.COLUMN_ID,

d.COLUMN_NAME,

d.DATA_TYPE || '(' || d.DATA_LENGTH || ')'

INTO r_compare.column_id,

r_compare.table2_column,

r_compare.table2_column_type

FROM dba_tab_cols d

WHERE d.TABLE_NAME = upper(p_table2)

AND d.COLUMN_ID = v_num

AND d.owner = upper(p_user2);

EXCEPTION

WHEN OTHERS THEN

r_compare.table2_column := '';

r_compare.table2_column_type := '';

END;

IF r_compare.table1_column_type = r_compare.table2_column_type THEN

r_compare.column_type_is_same := 1;

ELSE

r_compare.column_type_is_same := 0;

END IF;

IF r_compare.table1_column = r_compare.table2_column THEN

r_compare.column_name_is_same := 1;

ELSE

r_compare.column_name_is_same := 0;

END IF;

INSERT INTO xb_table_compare_lhr

(column_id,

table1_column,

table1_column_type,

table2_column,

table2_column_type,

column_name_is_same,

column_type_is_same)

VALUES

(r_compare.column_id,

r_compare.table1_column,

r_compare.table1_column_type,

r_compare.table2_column,

r_compare.table2_column_type,

r_compare.column_name_is_same,

r_compare.column_type_is_same);

COMMIT;

v_num := v_num + 1;

END LOOP;

ELSIF is_order IN (0, 2) THEN

FOR cur IN (SELECT *

FROM dba_tab_cols d

WHERE d.TABLE_NAME = upper(p_max_table)

AND d.owner = upper(p_max_user)) LOOP

BEGIN

SELECT d.COLUMN_ID,

d.COLUMN_NAME,

d.DATA_TYPE || '(' || d.DATA_LENGTH || ')'

INTO r_compare.column_id,

r_compare.table1_column,

r_compare.table1_column_type

FROM dba_tab_cols d

WHERE d.TABLE_NAME = upper(p_table1)

AND d.COLUMN_NAME = cur.column_name

AND d.owner = upper(p_user1);

EXCEPTION

WHEN OTHERS THEN

r_compare.column_id := '';

r_compare.table1_column := '';

r_compare.table1_column_type := '';

END;

BEGIN

SELECT d.COLUMN_ID,

d.COLUMN_NAME,

d.DATA_TYPE || '(' || d.DATA_LENGTH || ')'

INTO r_compare.column_id,

r_compare.table2_column,

r_compare.table2_column_type

FROM dba_tab_cols d

WHERE d.TABLE_NAME = upper(p_table2)

AND d.COLUMN_NAME = cur.column_name

AND d.owner = upper(p_user2);

EXCEPTION

WHEN OTHERS THEN

r_compare.column_id := '';

r_compare.table2_column := '';

r_compare.table2_column_type := '';

END;

IF r_compare.table1_column_type = r_compare.table2_column_type THEN

r_compare.column_type_is_same := 1;

ELSE

r_compare.column_type_is_same := 0;

END IF;

IF r_compare.table1_column = r_compare.table2_column THEN

r_compare.column_name_is_same := 1;

ELSE

r_compare.column_name_is_same := 0;

END IF;

INSERT INTO xb_table_compare_lhr

(column_id,

table1_column,

table1_column_type,

table2_column,

table2_column_type,

column_name_is_same,

column_type_is_same)

VALUES

('',

r_compare.table1_column,

r_compare.table1_column_type,

r_compare.table2_column,

r_compare.table2_column_type,

r_compare.column_name_is_same,

r_compare.column_type_is_same);

COMMIT;

v_num := v_num + 1;

END LOOP;

END IF;

RETURN 'select * from xb_table_compare_lhr;';

EXCEPTION

WHEN OTHERS THEN

RETURN 'select * from xb_table_compare_lhr;';

END f_get_table_is_same;

------------------------------------------------------------------------------------------------------ 得到行转列的语句 2013-06-07 17:58:52

-----------------------------------------------------------------------------------

-- Created on 2013-06-07 17:58:52 by lhr

--Changed on 2013-06-07 17:58:52 by lhr

-- function：

/* Create Table xb_row2col_lhr( a varchar2(255),b number);*/

/* SELECT pkg_utility_lhr.f_get_row2_col_sql_lhr('XT_FTTH_PI_RES_LHR_03',

'serviceinstancecode',

合 视图 vw_sql_rubbish_monitor_lhr 的内容

不带包

带包

包

合视图 vw_sql_rubbish_monitor_lhr 的内容