SQL> set linesize 150
column analyzed format a30
SELECT TO_TIMESTAMP(last_analyzed) analyzed, sample_size, monitoring, table_name
FROM dba_tables
WHERE table_name = 'L_TRAN_ACCT_INNER_TX';
ANALYZED SAMPLE_SIZE MON TABLE_NAME
------------------------------ ----------- --- --------------------
10-JAN-18 12.00.00 AM 1882562 YES L_TRAN_ACCT_INNER_TX
ANALYZED SAMPLE_SIZE MON TABLE_NAME
------------------------------ ----------- --- --------------------
16-JAN-18 12.00.00 AM 69832300 YES L_TRAN_ACCT_INNER_TX
exec dbms_stats.gather_table_stats('SMTMODS_OWNER','L_TRAN_ACCT_INNER_TX');
set pagesize 1000
set linesize 150
column index_name format a25
column join_index format a10
column analyzed format a22
SELECT index_name, num_rows, TO_TIMESTAMP(last_analyzed) analyzed,
distinct_keys, leaf_blocks, avg_leaf_blocks_per_key, join_index
FROM dba_indexes
WHERE table_name = 'L_TRAN_ACCT_INNER_TX'
ORDER BY index_name;
INDEX_NAME NUM_ROWS ANALYZED DISTINCT_KEYS LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY JOIN_INDEX
------------------------- ---------- ---------------------- ------------- ----------- ----------------------- ----------
INX_L_TRAN_ACCT_INNER_TX 23015029 16-JAN-18 12.00.00 AM 69 67493 978 NO
INX_L_TRAN_ACCT_INNER_TX2 23015029 16-JAN-18 12.00.00 AM 12964 126817 9 NO
PK_L_TRAN_ACCT_INNER_TX 1934461 10-JAN-18 12.00.00 AM 1934461 31575 1 NO
INDEX_NAME NUM_ROWS ANALYZED DISTINCT_KEYS LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY JOIN_INDEX
------------------------- ---------- ---------------------- ------------- ----------- ----------------------- ----------
INX_L_TRAN_ACCT_INNER_TX 71875274 16-JAN-18 12.00.00 AM 196 304342 1552 NO
INX_L_TRAN_ACCT_INNER_TX2 70246378 16-JAN-18 12.00.00 AM 627 543172 866 NO
PK_L_TRAN_ACCT_INNER_TX 69940941 16-JAN-18 12.00.00 AM 69940941 1099467 1 NO
set linesize 150
set pagesize 1000
column table_name format a20
column column_name format a20
select table_name, column_name, num_distinct, num_buckets, sample_size, histogram
from user_tab_col_statistics
where table_name = 'L_TRAN_ACCT_INNER_TX';
TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE HISTOGRAM
-------------------- -------------------- ------------ ----------- ----------- ---------------
L_TRAN_ACCT_INNER_TX OPEN_CANCEL_FLG 0 0 NONE
L_TRAN_ACCT_INNER_TX OPPO_ORG_NAM 0 0 NONE
L_TRAN_ACCT_INNER_TX REV_FLAG 0 0 NONE
L_TRAN_ACCT_INNER_TX ORIG_TX_DATE 0 0 NONE
L_TRAN_ACCT_INNER_TX TRANS_TYPE 0 0 NONE
L_TRAN_ACCT_INNER_TX BUSINESS_ACCT_NUM 0 0 NONE
L_TRAN_ACCT_INNER_TX COUNTPTY_AREA_CD 0 0 NONE
L_TRAN_ACCT_INNER_TX COUNTPTY_NATION_CD 0 0 NONE
L_TRAN_ACCT_INNER_TX DATE_SOURCESD 2 1 1882562 NONE
L_TRAN_ACCT_INNER_TX DEPARTMENTD 1 1 1882562 NONE
L_TRAN_ACCT_INNER_TX MAIN_CERTI_CODE 0 0 NONE
L_TRAN_ACCT_INNER_TX MAIN_CERTI_TYPE 0 0 NONE
L_TRAN_ACCT_INNER_TX ACCT_TIME 0 0 NONE
L_TRAN_ACCT_INNER_TX TRANS_BAL 40200 1 41858 NONE
L_TRAN_ACCT_INNER_TX PAYMENT_PROPERTY 2 1 1882562 NONE
L_TRAN_ACCT_INNER_TX TRAN_STS 1 1 1882562 NONE
L_TRAN_ACCT_INNER_TX REMARK 2 1 1882562 NONE
L_TRAN_ACCT_INNER_TX CLOSE_DATE 19 1 1882562 NONE
L_TRAN_ACCT_INNER_TX INPUT_DATE 18 1 1882562 NONE
L_TRAN_ACCT_INNER_TX OPPO_ITEM_ID 0 0 NONE
L_TRAN_ACCT_INNER_TX AUTH_TELLER_CD 0 0 NONE
L_TRAN_ACCT_INNER_TX OP_TELLER_NUM 0 0 NONE
L_TRAN_ACCT_INNER_TX TRANS_FLG 1 1 1882562 NONE
L_TRAN_ACCT_INNER_TX CHANNEL 1 1 1882562 NONE
L_TRAN_ACCT_INNER_TX OPPO_ORG_NUM 6 1 5099 NONE
L_TRAN_ACCT_INNER_TX OPPO_ACCT_NAM 974 1 36676 NONE
L_TRAN_ACCT_INNER_TX OPPO_ACCT_NUM 1337 1 36688 NONE
L_TRAN_ACCT_INNER_TX TX_AMT 154336 254 5510 HYBRID
L_TRAN_ACCT_INNER_TX TX_TYPE 2 1 1882562 NONE
L_TRAN_ACCT_INNER_TX CURR_CD 12 12 1882562 FREQUENCY
L_TRAN_ACCT_INNER_TX TX_TIME 1 1 1882562 NONE
L_TRAN_ACCT_INNER_TX TX_DATE 18 18 1882562 FREQUENCY
L_TRAN_ACCT_INNER_TX ITEM_ID 0 0 NONE
L_TRAN_ACCT_INNER_TX ORG_NUM 7 7 1882562 FREQUENCY
L_TRAN_ACCT_INNER_TX ACCT_NUM 3328 241 5508 HYBRID
L_TRAN_ACCT_INNER_TX SUB_NUM 550 254 1882562 TOP-FREQUENCY
L_TRAN_ACCT_INNER_TX REFERENCE_SUB_NUM 2 2 1882562 FREQUENCY
L_TRAN_ACCT_INNER_TX TX_NUM 371456 1 1882562 NONE
L_TRAN_ACCT_INNER_TX DATA_DATE 6 6 1882562 FREQUENCY
39 rows selected.
TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE HISTOGRAM
-------------------- -------------------- ------------ ----------- ----------- ---------------
L_TRAN_ACCT_INNER_TX DATA_DATE 196 196 69832300 FREQUENCY
L_TRAN_ACCT_INNER_TX TX_NUM 13636608 1 69832300 NONE
L_TRAN_ACCT_INNER_TX REFERENCE_SUB_NUM 2 2 69832300 FREQUENCY
L_TRAN_ACCT_INNER_TX SUB_NUM 574 254 69832300 TOP-FREQUENCY
L_TRAN_ACCT_INNER_TX ACCT_NUM 6275 241 5556 HYBRID
L_TRAN_ACCT_INNER_TX ORG_NUM 7 7 69832300 FREQUENCY
L_TRAN_ACCT_INNER_TX ITEM_ID 78 78 69832300 FREQUENCY
L_TRAN_ACCT_INNER_TX TX_DATE 296 254 69832300 TOP-FREQUENCY
L_TRAN_ACCT_INNER_TX TX_TIME 1 1 69832300 NONE
L_TRAN_ACCT_INNER_TX CURR_CD 14 14 69832300 FREQUENCY
L_TRAN_ACCT_INNER_TX TX_TYPE 2 1 69832300 NONE
L_TRAN_ACCT_INNER_TX TX_AMT 2783488 1 69832300 NONE
L_TRAN_ACCT_INNER_TX OPPO_ACCT_NUM 5348 1 1596758 NONE
L_TRAN_ACCT_INNER_TX OPPO_ACCT_NAM 2504 1 1595668 NONE
L_TRAN_ACCT_INNER_TX OPPO_ORG_NUM 6 1 402514 NONE
L_TRAN_ACCT_INNER_TX CHANNEL 1 1 69832300 NONE
L_TRAN_ACCT_INNER_TX TRANS_FLG 1 1 69832300 NONE
L_TRAN_ACCT_INNER_TX OP_TELLER_NUM 0 0 NONE
L_TRAN_ACCT_INNER_TX AUTH_TELLER_CD 0 0 NONE
L_TRAN_ACCT_INNER_TX OPPO_ITEM_ID 59 1 1066779 NONE
L_TRAN_ACCT_INNER_TX INPUT_DATE 288 1 69832300 NONE
L_TRAN_ACCT_INNER_TX CLOSE_DATE 297 1 69832300 NONE
L_TRAN_ACCT_INNER_TX REMARK 2 1 69832300 NONE
L_TRAN_ACCT_INNER_TX TRAN_STS 1 1 69832300 NONE
L_TRAN_ACCT_INNER_TX PAYMENT_PROPERTY 2 1 69832300 NONE
L_TRAN_ACCT_INNER_TX TRANS_BAL 0 0 NONE
L_TRAN_ACCT_INNER_TX ACCT_TIME 0 0 NONE
L_TRAN_ACCT_INNER_TX MAIN_CERTI_TYPE 0 0 NONE
L_TRAN_ACCT_INNER_TX MAIN_CERTI_CODE 0 0 NONE
L_TRAN_ACCT_INNER_TX DEPARTMENTD 1 1 69832300 NONE
L_TRAN_ACCT_INNER_TX DATE_SOURCESD 2 1 69832300 NONE
L_TRAN_ACCT_INNER_TX COUNTPTY_NATION_CD 0 0 NONE
L_TRAN_ACCT_INNER_TX COUNTPTY_AREA_CD 0 0 NONE
L_TRAN_ACCT_INNER_TX BUSINESS_ACCT_NUM 0 0 NONE
L_TRAN_ACCT_INNER_TX TRANS_TYPE 0 0 NONE
L_TRAN_ACCT_INNER_TX ORIG_TX_DATE 0 0 NONE
L_TRAN_ACCT_INNER_TX REV_FLAG 0 0 NONE
L_TRAN_ACCT_INNER_TX OPPO_ORG_NAM 0 0 NONE
L_TRAN_ACCT_INNER_TX OPEN_CANCEL_FLG 0 0 NONE
39 rows selected.