Oracle数据治理

发表于 | 分类于 | 本文总阅读量 次

表相关

| 系统名 | 表名 | 表类型 | 记录数 | 是否空表 | 表说明 |

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
select 'analyze table '||table_name||' compute statistics;' from user_tables where tablespace_name='PMSAlex' and INSTR(table_name, 'W_')>0;

SELECT
    t1.SYSTEM_NAME 系统名,
    t1.OBJECT_NAME 表名,
    t1.OBJECT_TYPE 表类型,
    t2.num_rows 记录数,
    t2.ISNULLTB 是否空表,
    t3.COMMENTS 表说明
FROM (SELECT
        'Alex' SYSTEM_NAME,
        OBJECT_NAME,
        OBJECT_TYPE
    FROM USER_OBJECTS 
    WHERE OBJECT_TYPE='TABLE' OR OBJECT_TYPE='VIEW'
    ORDER BY OBJECT_TYPE,OBJECT_NAME) t1

LEFT JOIN (SELECT
        TABLE_NAME,
        num_rows,
        (CASE num_rows WHEN 0 THEN 'Y' ELSE 'N' END) ISNULLTB
    FROM user_tables) t2
ON t1.OBJECT_NAME = t2.TABLE_NAME

LEFT JOIN USER_TAB_COMMENTS t3
ON t1.OBJECT_NAME = t3.TABLE_NAME;

字段相关

| 系统名称 | 表名称 | 表类型 | 字段名称 | 字段类型 | 是否允许空 | 是否主键 |

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT
    'Alex' AS 系统名称,
    an.TABLE_NAME AS 表名称,
    an.TABLE_TYPE AS 表类型,
    an.COLUMN_NAME AS 字段名称,
    an.THE_TYPE AS 字段类型,
    an.NULLABLE AS 是否允许空,
    (CASE WHEN an.TABLE_NAME=b.TABLE_NAME AND an.COLUMN_NAME = b.COLUMN_NAME THEN 'Y' ELSE 'N' END ) AS 是否主键
FROM (SELECT
        t1.TABLE_NAME,
        (CASE t2.OBJECT_TYPE WHEN 'TABLE' THEN '原表' ELSE '视图' END) TABLE_TYPE,
        t1.COLUMN_NAME,
        t1.DATA_TYPE || '(' || t1.DATA_LENGTH || ')' AS THE_TYPE,
        t1.NULLABLE
    FROM USER_TAB_COLS t1,
        (SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE' OR OBJECT_TYPE='VIEW') t2
    WHERE t1.TABLE_NAME = t2.OBJECT_NAME) an 

LEFT JOIN (SELECT 
        cu.TABLE_NAME,
        cu.COLUMN_NAME 
    FROM USER_CONS_COLUMNS cu,USER_CONSTRAINTS au
    WHERE cu.constraint_name = au.constraint_name
    AND cu.table_name = au.table_name
    AND cu.owner = au.owner
    AND CONSTRAINT_TYPE = 'P') b

ON an.table_name = b.table_name AND an.column_name = b.column_name
ORDER BY 表名称;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
    b.TABLE_NAME AS 表名,
    b.COLUMN_NAME AS 列名,
    b.DATA_TYPE AS 类型,
    b.DATA_LENGTH AS 长度,
    A .COMMENTS AS 描述,
    b.NULLABLE AS 是否可空
FROM
    USER_TAB_COLUMNS b,
    USER_COL_COMMENTS A
WHERE
    b.TABLE_NAME IN ('TABLENAME')
    AND b.TABLE_NAME = A .TABLE_NAME
    AND b.COLUMN_NAME = A .COLUMN_NAME
ORDER BY
    b.TABLE_NAME,
    b.COLUMN_ID

表使用情况

| 系统名 | 数据库类型 | 用户名 | 表名 | 字段数 | 记录数 | B | KB | MB | 是否空表 |

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    'Alex' AS 系统名,
    'oracle' AS 数据库类型,
    t1.TABLESPACE_NAME AS 用户名,
    t1.TABLE_NAME AS 表名,
    t2.column_num AS 字段数,
    t1.num_rows AS 记录数,
    ROUND((t1.num_rows*t1.avg_row_len)/8,0) AS B,
    ROUND((t1.num_rows*t1.avg_row_len)/8/1024,8) AS KB,
    ROUND((t1.num_rows*t1.avg_row_len)/8/1024/1024,8) AS MB,
    (CASE num_rows WHEN 0 THEN 'Y' ELSE 'N' END) AS 是否空表
FROM user_tables t1
INNER JOIN
(SELECT table_name,count(*) column_num FROM user_tab_columns GROUP BY table_name) t2
ON t1.TABLE_NAME = t2.table_name;
0%