SELECT A.OWNER,
A.TABLE_NAME,
A.COLUMN_NAME,
A.DATA_TYPE,
A.NULLABLE,
A.NUM_DISTINCT,
A.NUM_NULLS,
B.NUM_ROWS,
-- A.LOW_VALUE,
DECODE (
A.LOW_VALUE,
NULL, NULL,
DECODE (
A.DATA_TYPE,
'CLOB', UTL_RAW.CAST_TO_VARCHAR2 (A.LOW_VALUE),
'BLOB', UTL_RAW.CAST_TO_VARCHAR2 (A.LOW_VALUE),
'CHAR', UTL_RAW.CAST_TO_VARCHAR2 (A.LOW_VALUE),
'NUMBER', TO_CHAR (UTL_RAW.CAST_TO_NUMBER (A.LOW_VALUE)),
'VARCHAR2', TO_CHAR (UTL_RAW.CAST_TO_VARCHAR2 (A.LOW_VALUE)),
'NVARCHAR2', TO_CHAR (UTL_RAW.CAST_TO_NVARCHAR2 (A.LOW_VALUE)),
'BINARY_DOUBLE', TO_CHAR (
UTL_RAW.CAST_TO_BINARY_DOUBLE (A.LOW_VALUE)),
'BINARY_FLOAT', TO_CHAR (
UTL_RAW.CAST_TO_BINARY_FLOAT (A.LOW_VALUE)),
'DATE', TO_CHAR (
1780
+ TO_NUMBER (SUBSTR (A.LOW_VALUE, 1, 2), 'XX')
+ TO_NUMBER (SUBSTR (A.LOW_VALUE, 3, 2), 'XX'))
|| '-'
|| TRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (A.LOW_VALUE, 5, 2), 'XX'),
'00'))
|| '-'
|| TRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (A.LOW_VALUE, 7, 2), 'XX'),
'00'))
|| ' '
|| TRIM (
TO_CHAR (
( TO_NUMBER (SUBSTR (A.LOW_VALUE, 9, 2), 'XX')
- 1),
'00'))
|| ':'
|| TRIM (
TO_CHAR (
( TO_NUMBER (SUBSTR (A.LOW_VALUE, 11, 2),
'XX')
- 1),
'00'))
|| ':'
|| TRIM (
TO_CHAR (
( TO_NUMBER (SUBSTR (A.LOW_VALUE, 13, 2),
'XX')
- 1),
'00')),
A.LOW_VALUE))
AS LOW_VALUE_TEXT,
-- A.HIGH_VALUE,
DECODE (
A.HIGH_VALUE,
NULL, NULL,
DECODE (
A.DATA_TYPE,
'CLOB', UTL_RAW.CAST_TO_VARCHAR2 (A.HIGH_VALUE),
'BLOB', UTL_RAW.CAST_TO_VARCHAR2 (A.HIGH_VALUE),
'CHAR', UTL_RAW.CAST_TO_VARCHAR2 (A.HIGH_VALUE),
'NUMBER', TO_CHAR (UTL_RAW.CAST_TO_NUMBER (A.HIGH_VALUE)),
'VARCHAR2', TO_CHAR (UTL_RAW.CAST_TO_VARCHAR2 (A.HIGH_VALUE)),
'NVARCHAR2', TO_CHAR (UTL_RAW.CAST_TO_NVARCHAR2 (A.HIGH_VALUE)),
'BINARY_DOUBLE', TO_CHAR (
UTL_RAW.CAST_TO_BINARY_DOUBLE (A.HIGH_VALUE)),
'BINARY_FLOAT', TO_CHAR (
UTL_RAW.CAST_TO_BINARY_FLOAT (A.HIGH_VALUE)),
'DATE', TO_CHAR (
1780
+ TO_NUMBER (SUBSTR (A.HIGH_VALUE, 1, 2), 'XX')
+ TO_NUMBER (SUBSTR (A.HIGH_VALUE, 3, 2), 'XX'))
|| '-'
|| TRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (A.HIGH_VALUE, 5, 2), 'XX'),
'00'))
|| '-'
|| TRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (A.HIGH_VALUE, 7, 2), 'XX'),
'00'))
|| ' '
|| TRIM (
TO_CHAR (
( TO_NUMBER (SUBSTR (A.HIGH_VALUE, 9, 2),
'XX')
- 1),
'00'))
|| ':'
|| TRIM (
TO_CHAR (
( TO_NUMBER (SUBSTR (A.HIGH_VALUE, 11, 2),
'XX')
- 1),
'00'))
|| ':'
|| TRIM (
TO_CHAR (
( TO_NUMBER (SUBSTR (A.HIGH_VALUE, 13, 2),
'XX')
- 1),
'00')),
A.HIGH_VALUE))
AS HIGH_VALUE_TEXT,
A.DENSITY,
A.NUM_BUCKETS,
A.SAMPLE_SIZE,
A.AVG_COL_LEN,
A.CHAR_LENGTH,
CASE WHEN A.LAST_ANALYZED = B.LAST_ANALYZED THEN 'Y' ELSE 'N' END AS LAST_FLG
FROM SYS.ALL_TAB_COLS A, SYS.ALL_TABLES B
WHERE A.OWNER = 'SYSTEM' AND A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME;