Oracle数据库统计信息

查询数据库编码格式。

SQL> select * from v$nls_parameters a where a.PARAMETER='NLS_CHARACTERSET';

统计数据库中的对象及个数。查看指定用户下的各对象类型和数目，例如用户名为USER。
```
SQL>  select object_type,count(*) from all_objects where owner='USER' group by object_type;
```

统计表的数据量。
a. 首先分析该模式的所有表，手动更新dba_tables的统计信息。

SQL> BEGIN
        FOR i IN (SELECT table_name FROM dba_tables WHERE owner='USER') LOOP 
           EXECUTE IMMEDIATE ('analyze table '|| i.table_name ||' compute statistics');
        END LOOP;
     END;
     /

b. 统计各表的数据量。

SQL> SELECT table_name,num_rows FROM dba_tables WHERE OWNER='USER';

统计所有用户下的表对象使用空间大小。

SQL> SELECT OWNER, concat(round(sum(bytes)/1024/1024,2),'MB') AS data FROM dba_segments 
     WHERE SEGMENT_TYPE LIKE 'TABLE%' GROUP BY OWNER ORDER BY DATA desc;

查看数据库表对象使用的列名称（关键字排查）。

SQL> SELECT  column_name FROM dba_tab_columns WHERE owner='USER'
    GROUP BY column_name ORDER BY column_name desc;

查看数据库表对象使用的数据类型。

SQL> SELECT  DATA_TYPE,count(*) FROM dba_tab_columns WHERE OWNER='USER' GROUP BY DATA_TYPE ORDER BY  COUNT(*) desc;

查询创建用户模式下的主键信息和外键信息。

SQL> SELECT	c.OWNER AS 约束拥有者,
        c.CONSTRAINT_name AS 约束名称,
        c.TABLE_NAME AS 表名,
        cc.COLUMN_NAME AS 列名,
        c.R_OWNER AS 外键表拥有者,
        d.TABLE_NAME AS 外键表名,
        dd.COLUMN_NAME AS 外键表列名,
        c.DELETE_RULE AS 约束删除规则,
        c.R_CONSTRAINT_NAME AS 唯一约束名称,
        c.CONSTRAINT_TYPE AS 约束类型,
        c.STATUS AS 约束状态
     FROM dba_constraints c LEFT JOIN DBA_CONS_COLUMNS cc ON c.OWNER=cc.OWNER AND c.CONSTRAINT_NAME=cc.CONSTRAINT_NAME 
     LEFT JOIN dba_constraints d  ON c.R_OWNER=d.OWNER AND c.R_CONSTRAINT_NAME=d.CONSTRAINT_NAME 
     LEFT JOIN DBA_CONS_COLUMNS dd ON d.OWNER=dd.OWNER AND d.CONSTRAINT_NAME=dd.CONSTRAINT_NAME 
     WHERE c.OWNER IN (
     SELECT username FROM dba_users WHERE created>=(SELECT created FROM v$database) AND common='NO' 	AND username!='HR')
     ORDER BY c.OWNER;

Oracle数据库统计信息 ​

Oracle数据库统计信息