Oracle查询编写求助：获取全部对象及指定Schema的对象详情

Hey there! Let's break down your Oracle questions with practical, ready-to-use SQL statements. These rely on Oracle's built-in data dictionary views, which are your best friends as a new Oracle developer.

1. 编写可返回全部对象的SELECT语句 #

To fetch all objects (tables, views, indexes, stored procedures, etc.) in Oracle, you'll use one of three core data dictionary views depending on your access level:

• USER_OBJECTS: Shows objects owned by your current user.
• ALL_OBJECTS: Shows all objects you have permission to access (including those from other schemas).
• DBA_OBJECTS: Shows every object in the entire database (requires DBA privileges).

Example Queries: #

-- 1. 列出当前用户拥有的所有对象
SELECT 
  object_name, 
  object_type, 
  created, 
  last_ddl_time
FROM user_objects
ORDER BY object_type, object_name;

-- 2. 列出当前用户有权访问的所有对象（可选排除SYS/SYSTEM等系统用户）
SELECT 
  owner, 
  object_name, 
  object_type, 
  created, 
  last_ddl_time
FROM all_objects
WHERE owner NOT IN ('SYS', 'SYSTEM') -- 可选过滤非系统对象
ORDER BY owner, object_type, object_name;

-- 3. 列出数据库中所有对象（需要DBA权限）
SELECT 
  owner, 
  object_name, 
  object_type, 
  created, 
  last_ddl_time
FROM dba_objects
ORDER BY owner, object_type, object_name;

2. 获取指定Schema下的表名、列名、约束、索引及分区信息 #

Let's split this into targeted queries for each type of metadata. Replace YOUR_SCHEMA_NAME with the actual schema you want to inspect.

表名与基础表信息 #

获取Schema下所有表的核心详情：

SELECT 
  table_name,
  tablespace_name,
  num_rows, -- 近似行数（需通过ANALYZE或DBMS_STATS更新）
  created,
  last_ddl_time
FROM all_tables
WHERE owner = 'YOUR_SCHEMA_NAME'
  AND temporary = 'N' -- 排除临时表
ORDER BY table_name;

列名与属性信息 #

获取Schema下所有表的列级详细数据：

SELECT 
  t.table_name,
  c.column_name,
  c.data_type,
  c.data_length,
  c.data_precision, -- 数值类型精度
  c.data_scale, -- 数值类型小数位
  c.nullable, -- 'Y'表示允许空值，'N'表示不允许
  c.column_id, -- 列在表中的位置
  c.default_value -- 列的默认值（如果有）
FROM all_tables t
JOIN all_tab_columns c 
  ON t.owner = c.owner AND t.table_name = c.table_name
WHERE t.owner = 'YOUR_SCHEMA_NAME'
ORDER BY t.table_name, c.column_id;

约束信息 #

获取Schema下所有表的约束（主键、外键、唯一约束、检查约束等）：

SELECT
  t.table_name,
  c.constraint_name,
  c.constraint_type, -- 'P'=主键, 'R'=外键, 'U'=唯一约束, 'C'=检查约束
  c.search_condition, -- 检查约束的逻辑条件
  cc.column_name, -- 约束关联的列
  r.r_owner AS referenced_schema,
  r.r_table_name AS referenced_table,
  r.r_column_name AS referenced_column
FROM all_tables t
JOIN all_constraints c 
  ON t.owner = c.owner AND t.table_name = c.table_name
LEFT JOIN all_cons_columns cc 
  ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
LEFT JOIN (
  -- 子查询获取外键关联的表和列
  SELECT
    rc.owner,
    rc.constraint_name,
    rc.r_owner,
    rc.r_table_name,
    rcc.column_name AS r_column_name
  FROM all_constraints rc
  JOIN all_cons_columns rcc 
    ON rc.r_owner = rcc.owner AND rc.r_constraint_name = rcc.constraint_name
) r 
  ON c.owner = r.owner AND c.constraint_name = r.constraint_name
WHERE t.owner = 'YOUR_SCHEMA_NAME'
ORDER BY t.table_name, c.constraint_type, c.constraint_name;

索引信息 #

获取Schema下所有表的索引及关联列：

SELECT
  t.table_name,
  i.index_name,
  i.index_type, -- 例如'NORMAL'普通索引, 'BITMAP'位图索引
  i.uniqueness, -- 'UNIQUE'唯一索引或'NONUNIQUE'非唯一索引
  ic.column_name,
  ic.column_position -- 列在索引中的位置
FROM all_tables t
JOIN all_indexes i 
  ON t.owner = i.owner AND t.table_name = i.table_name
JOIN all_ind_columns ic 
  ON i.owner = ic.owner AND i.index_name = ic.index_name AND i.table_name = ic.table_name
WHERE t.owner = 'YOUR_SCHEMA_NAME'
ORDER BY t.table_name, i.index_name, ic.column_position;

分区信息 #

如果Schema下有分区表，用以下查询获取分区/子分区详情：

-- 获取分区表的分区信息
SELECT
  t.table_name,
  tp.partition_name,
  tp.partition_position,
  tp.tablespace_name,
  tp.high_value, -- 分区边界值（例如范围分区的上限）
  tp.num_rows,
  tp.created
FROM all_tables t
JOIN all_tab_partitions tp 
  ON t.owner = tp.table_owner AND t.table_name = tp.table_name
WHERE t.owner = 'YOUR_SCHEMA_NAME'
  AND t.partitioned = 'YES'
ORDER BY t.table_name, tp.partition_position;

-- 获取复合分区表的子分区信息
SELECT
  t.table_name,
  tp.partition_name,
  tsp.subpartition_name,
  tsp.subpartition_position,
  tsp.tablespace_name,
  tsp.high_value,
  tsp.num_rows
FROM all_tables t
JOIN all_tab_partitions tp 
  ON t.owner = tp.table_owner AND t.table_name = tp.table_name
JOIN all_tab_subpartitions tsp 
  ON tp.table_owner = tsp.table_owner AND tp.table_name = tsp.table_name AND tp.partition_name = tsp.partition_name
WHERE t.owner = 'YOUR_SCHEMA_NAME'
  AND t.partitioned = 'YES'
ORDER BY t.table_name, tp.partition_position, tsp.subpartition_position;

内容的提问来源于stack exchange，提问作者Sudhakar Nelapati