好 — 下面我把 Oracle 数据字典（Data Dictionary）概念 + 常用视图分类、以及大量实战查询语句模板与说明整理成一份可直接复制运行的速查手册。适合日常运维、开发与审计使用。每段都标注目的与注意点，直接放进 SQL 工具里跑就行。

一、核心概念（非常简短）

  • 数据字典：Oracle 内置的一组只读视图，记录数据库中对象（表、索引、视图、约束、用户、表空间、段等）的元数据与状态。
  • 按可见范围分三类常用前缀：
    • USER_*：当前连接用户拥有或创建的对象（无 DBA 权限也可用）。
    • ALL_*：当前用户能访问的对象（包括别人授权的）。
    • DBA_*：数据库全局视图，需 DBA 权限（或 SELECT_CATALOG_ROLE）。
  • 还有性能与运行时视图：V$* 与 GV$*（实例/性能相关，需权限）。

二、常用视图一览（关键词速查）

  • 对象/类型：USER_OBJECTS / ALL_OBJECTS / DBA_OBJECTS
  • 表相关：USER_TABLES / ALL_TABLES / DBA_TABLES
  • 列：USER_TAB_COLUMNS / ALL_TAB_COLUMNS / DBA_TAB_COLUMNS
  • 索引：USER_INDEXES / ALL_INDEXES / DBA_INDEXES
  • 约束：USER_CONSTRAINTS / ALL_CONSTRAINTS / DBA_CONSTRAINTS
  • 触发器：USER_TRIGGERS / ALL_TRIGGERS / DBA_TRIGGERS
  • 同义词：USER_SYNONYMS / ALL_SYNONYMS / DBA_SYNONYMS
  • 表空间与段：DBA_TABLESPACES / DBA_DATA_FILES / DBA_SEGMENTS / USER_SEGMENTS
  • 用户权限：DBA_USERS / DBA_ROLE_PRIVS / DBA_TAB_PRIVS
  • 执行计划/统计：DBA_HIST_*, V$SQL（性能相关）
  • DDL 导出：DBMS_METADATA.GET_DDL（取对象 DDL）

三、常用查询模板（复制即用）

提示：DBA_ 视图需要 DBA 权限；若没有，可以把 DBA_ 换为 ALL_ 或 USER_。

1) 列出当前用户所有表（含行、段信息）

SELECT table_name,
       num_rows,             -- 统计信息（可能是过期的）
       blocks,
       EMPTY_BLOCKS,
       AVG_ROW_LEN
FROM user_tables
ORDER BY num_rows DESC NULLS LAST;

2) 精确行数（单表 COUNT）

SELECT COUNT(*) AS exact_count FROM schema_name.table_name;

注意：COUNT(*) 对大表成本高，生产环境慎用。可借助 num_rows 作近似估计（需定期 ANALYZE 或 DBMS_STATS.GATHER_TABLE_STATS）。

3) 列出表的列信息（类型、是否可空、默认）

SELECT column_id, column_name, data_type, data_length, data_precision, data_scale,
       nullable, data_default
FROM all_tab_columns
WHERE owner = 'SCOTT' AND table_name = 'EMP'
ORDER BY column_id;

4) 查找某列名在哪些表出现（按片段搜索）

SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE LOWER(column_name) LIKE '%cust_id%'
ORDER BY owner, table_name;

5) 查看表所有索引与索引列

SELECT index_name, uniqueness, index_type, column_name, column_position
FROM all_ind_columns a
JOIN all_indexes i ON a.index_name = i.index_name AND a.index_owner = i.owner
WHERE a.table_owner = 'SCOTT' AND a.table_name = 'EMP'
ORDER BY index_name, column_position;

6) 找出没有被使用或可能冗余的索引（基于统计/最近使用）

-- 依据 v$object_usage 需要先启用监控： ALTER INDEX idx_name MONITORING USAGE;
SELECT * FROM dba_indexes WHERE owner = 'SCOTT' AND table_name = 'EMP';
-- 再结合 V$OBJECT_USAGE 或 AWR 来判断使用频率（需权限）

复杂，通常结合 AWR/ASH/V$SQL_PLAN 分析。

7) 查看表约束（主外键、唯一、检查）

SELECT constraint_name, constraint_type, status, deferrable, search_condition, r_constraint_name
FROM all_constraints
WHERE owner='SCOTT' AND table_name='EMP';
  • constraint_type: P 主键, R 外键, U 唯一, C 检查

8) 查找表的外键依赖（某表被哪些外键引用）

SELECT a.owner, a.constraint_name, a.table_name, a.column_name,
       c.owner as ref_owner, c_pk.table_name as referenced_table
FROM all_cons_columns a
JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R' AND c_pk.table_name = 'PARENT_TABLE';

9) 导出对象 DDL（表/索引/触发器等）

SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('INDEX','IDX_EMP_NAME','SCOTT') FROM DUAL;

可用 SET LONG 在 SQL*Plus 中显示完整 DDL。

10) 列出某 Schema 下的对象类型与最后 DDL 修改时间

SELECT object_type, object_name, last_ddl_time
FROM all_objects
WHERE owner='SCOTT'
ORDER BY last_ddl_time DESC;

11) 表空间 & 段空间使用（按表）

SELECT segment_name, segment_type, bytes/1024/1024 AS mb
FROM dba_segments
WHERE owner='SCOTT'
ORDER BY bytes DESC;

12) 查询表大小（数据文件层面）

SELECT owner, segment_name AS table_name,
       SUM(bytes)/1024/1024 AS size_mb
FROM dba_segments
WHERE owner = 'SCOTT' AND segment_type='TABLE'
GROUP BY owner, segment_name
ORDER BY size_mb DESC;

13) 查看表的分区信息

SELECT table_name, partition_name, high_value, num_rows
FROM all_tab_partitions
WHERE table_owner='SCOTT' AND table_name='ORDERS';

14) 查找给定 SQL 使用了哪些对象（依赖）

SELECT referenced_owner, referenced_name, referenced_type
FROM dba_dependencies
WHERE owner='SCOTT' AND name='MY_PKG' AND type='PACKAGE';

15) 查看用户权限（对象级与角色）

-- 用户拥有的角色
SELECT * FROM dba_role_privs WHERE grantee='SCOTT';

-- 对象权限（谁对我的表有什么权限）
SELECT grantee, privilege FROM dba_tab_privs WHERE owner='SCOTT' AND table_name='EMP';

16) 查找包含某文本的存储过程/触发器/视图定义

SELECT owner, name, type
FROM dba_source
WHERE LOWER(text) LIKE '%some_text%'
GROUP BY owner, name, type;

或

SELECT owner, object_name, dbms_metadata.get_ddl(object_type, object_name, owner)
FROM dba_objects
WHERE object_type IN ('PROCEDURE','FUNCTION','PACKAGE','VIEW') AND owner='SCOTT';

17) 列出所有 Materialized View / 刷新信息

SELECT owner, mview_name, rewrite_enabled, refresh_mode, last_refresh_type, last_refresh_date
FROM dba_mviews;

18) 查找未收集统计信息的表（或最近收集时间）

SELECT owner, table_name, last_analyzed FROM dba_tables
WHERE owner = 'SCOTT'
ORDER BY last_analyzed NULLS FIRST;

19) 获取当前会话/实例信息（V$视图示例）

SELECT instance_name, version FROM v$instance;
SELECT sid, serial#, username, osuser, machine FROM v$session WHERE username IS NOT NULL;

需相应权限（通常 DBA 可用）。

20) 快速查找列含空值比例（样例）

SELECT
  COUNT(*) AS total_rows,
  SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END) AS null_count,
  SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END) / COUNT(*) * 100 AS null_pct
FROM schema.table;

对大表做全表扫描成本高，可取样本。

四、进阶实用脚本（批量辅助 & 审计）

A. 批量导出某 schema 下所有表的 DDL（PL/SQL）

SET LONG 100000
SET PAGESIZE 0
SPOOL all_tables_ddl.sql
BEGIN
  FOR r IN (SELECT table_name FROM user_tables ORDER BY table_name) LOOP
    DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.GET_DDL('TABLE', r.table_name));
  END LOOP;
END;
/
SPOOL OFF

若在 SQL Developer/Toad，用 GUI 导出也方便。

B. 生成删除某 schema 下所有表的脚本（小心使用）

SELECT 'DROP TABLE ' || owner || '.' || table_name || ' CASCADE CONSTRAINTS;' 
FROM all_tables WHERE owner = 'SCOTT';

C. 查找长时间未访问的表（借助 AUDIT 或 AWR）

  • Oracle 默认不记录表级访问时间；需要在应用层或通过 AUDIT SELECT ON schema.table 开启审计，或通过触发器/应用日志统计。

D. 查找可能的性能问题：缺失索引候选（基于全表扫描统计）

-- 简单找出最近大量全表扫描的 SQL（需 AWR/DBA权限）
SELECT sql_id, executions, disk_reads, buffer_gets
FROM v$sql
WHERE command_type = 3 -- SELECT, etc.
ORDER BY disk_reads DESC FETCH FIRST 50 ROWS ONLY;

五、常见注意事项与最佳实践

  1. 统计信息（STATS）很重要：DBMS_STATS.GATHER_SCHEMA_STATS 应按策略定期运行，影响优化器选择。
  2. 不要频繁用 COUNT(*) 估行数：用 NUM_ROWS（近似）或维护业务层统计。
  3. DDL 与权限：DBA_* 视图需要更高权限，审计查询注意最小权限原则。
  4. 分区表与大表：对大表使用分区和局部索引以提高维护与查询性能。
  5. 索引不是越多越好：写放大与维护成本，结合 v$sql_plan/AWR 分析删除冷索引。
  6. 使用 DBMS_METADATA 导出 DDL：比手工拼接更可靠（保留约束、存储参数等）。
  7. 备份与变更管理：变更前请先备份 DDL（或使用版本控制），生产改动走变更流程。

六、如果你想要我继续做（选项）

我可以立即为你生成并输出（在当前对话中）下列任一项结果（直接给可执行 SQL / 脚本）：

  1. 为某个 schema（请给出 schema 名）导出所有对象的 DDL 脚本（DBMS_METADATA 脚本）。
  2. 给出一个用于查找“冗余/未使用索引”的完整脚本（结合 V$OBJECT_USAGE / AWR 指标的模版）。
  3. 生成一份大表巡检报告 SQL，包括表大小、索引、分区、统计时间、约束、最近 DDL 时间。
  4. 按你的要求定制查询（例如：找出所有 VARCHAR2 超过 1000 的列，或查找引用某个 column 的所有对象）。

直接回复你选的编号或直接给出 schema / 对象 名称，我会把对应脚本/报告贴在这条回复里。