如何查找和更改Oracle中的对象Owner？ – wiki大全

作者： /

Oracle 数据库指南：如何查找和更改对象的所有者（Owner）

在 Oracle 数据库管理中，每个对象（如表、视图、索引、存储过程等）都有一个“所有者”（Owner）。所有者通常是创建该对象的数据库用户（Schema）。随着时间的推移，由于人员变动、应用架构调整或安全策略变更，我们可能需要查询甚至更改某些对象的所有者。

本文将详细介绍如何在 Oracle 中查找对象的所有者，并探讨几种更改所有者的常用方法。

一、 查找对象的所有者

查找对象的所有者相对直接。Oracle 的数据字典视图为我们提供了完整的信息。最常用的视图是 DBA_OBJECTS、ALL_OBJECTS 和 USER_OBJECTS。

  • DBA_OBJECTS: 显示数据库中所有对象的信息。通常需要 DBA 权限才能查询。
  • ALL_OBJECTS: 显示当前用户有权限访问的所有对象。
  • USER_OBJECTS: 显示当前用户拥有的所有对象。

查询方法：

要查找特定对象的所有者，最有效的方法是查询 DBA_OBJECTS 视图。

示例：查找名为 EMPLOYEES 的表的所有者

sql
SELECT
owner,
object_name,
object_type,
created,
last_ddl_time
FROM
dba_objects
WHERE
object_name = 'EMPLOYEES';

注意事项：
1. Oracle 默认以大写形式存储对象名。如果不确定大小写，可以使用 UPPER 函数进行转换：
sql
SELECT owner, object_name, object_type
FROM dba_objects
WHERE UPPER(object_name) = 'EMPLOYEES';
2. 如果数据库中存在多个同名对象（例如，不同所有者下的同名表），上述查询可能会返回多行结果，你可以通过 owner 列来区分它们。

二、 更改对象的所有者

与某些其他数据库不同，Oracle 没有 提供一个直接的命令（如 ALTER OBJECT ... CHANGE OWNER TO ...）来更改对象的所有者。这是一个设计上的选择，主要出于安全和数据一致性的考虑。

因此，更改所有者通常需要通过“间接”的方式来完成。以下是几种主流的方法：

方法一：使用数据泵（Data Pump）进行导出和导入（官方推荐）

这是最安全、最完整、也是 Oracle 官方推荐的方法。它通过将对象从旧所有者（Schema）导出，然后使用 REMAP_SCHEMA 选项将其导入到新所有者，来完美地实现“所有权转移”。此方法会保留所有相关的元数据，如索引、约束、权限、触发器等。

步骤：

  1. 准备工作：

    • 确保新所有者（用户/Schema）已经创建。
    • 确保有一个可用的数据库目录对象（DIRECTORY）供数据泵使用。如果没有，请创建一个：
      sql
      -- 以 SYS 或 SYSTEM 用户执行
      CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'C:\your_path\dpump'; -- 路径需真实存在
      GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO your_user;

  2. 导出（expdp）：
    从旧所有者导出指定的对象。例如，我们将 OLD_USER 的 MY_TABLE 表导出。
    打开操作系统的命令行（CMD 或 Shell），执行以下命令：
    shell
    expdp system/password@db_sid SCHEMAS=OLD_USER INCLUDE=TABLE:"='MY_TABLE'" DIRECTORY=DATA_PUMP_DIR DUMPFILE=mytable.dmp LOGFILE=export.log

    • SCHEMAS: 指定要导出的源 Schema。
    • INCLUDE: 指定只导出特定对象。如果要导出该用户下的所有对象，则无需此参数。
    • DIRECTORY: 指定转储文件存放的目录对象。
    • DUMPFILE: 导出的数据文件名。

  3. 导入（impdp）：
    使用 REMAP_SCHEMA 参数将导出的对象导入到新所有者。
    shell
    impdp system/password@db_sid REMAP_SCHEMA=OLD_USER:NEW_USER DIRECTORY=DATA_PUMP_DIR DUMPFILE=mytable.dmp LOGFILE=import.log

    • REMAP_SCHEMA=OLD_USER:NEW_USER: 这是整个操作的核心。它告诉数据泵将源于 OLD_USER 的所有对象重新映射并创建到 NEW_USER 名下。

  4. 收尾工作：

    • 确认对象已成功创建在 NEW_USER 下。
    • 验证数据、索引、约束等是否完整。
    • （可选）如果不再需要，可以删除旧所有者下的原始对象：DROP TABLE OLD_USER.MY_TABLE;

优点：
* 功能最完整，可以迁移表、索引、约束、触发器、授权等所有相关定义。
* 安全可靠，是生产环境的首选方案。

缺点：
* 操作步骤相对繁琐，需要命令行操作和文件系统访问权限。

方法二：使用 CREATE TABLE AS SELECT (CTAS)

对于表（Table）对象，这是一种快速简便的方法。其原理是以新所有者的身份，基于旧表的数据创建一个新表。

步骤：

  1. 授予权限： 确保新所有者对旧表有 SELECT 权限。
    sql
    -- 以 OLD_USER 或 DBA 身份执行
    GRANT SELECT ON OLD_USER.MY_TABLE TO NEW_USER;

  2. 创建新表： 以 NEW_USER 身份登录并执行 CREATE TABLE ... AS SELECT (CTAS) 语句。
    sql
    -- 以 NEW_USER 身份执行
    CREATE TABLE MY_TABLE AS SELECT * FROM OLD_USER.MY_TABLE;

重要警告：CTAS 的局限性

这种方法虽然简单，但只会复制表结构和数据，以下重要的元数据将会丢失：
* 索引（Indexes）
* 主键、外键、唯一键等约束（Constraints）
* 触发器（Triggers）
* 对象授权（Grants）
* 默认值（Default Values）
* 分区信息（Partitions）

因此，使用此方法后，你必须手动为新表重新创建所有这些丢失的元数据。这使得它在复杂表的迁移场景下非常容易出错。

优点：
* 语法简单，执行快速。

缺点：
* 只适用于表，且只迁移数据和基本列定义。
* 大量元数据丢失，需要大量的手动后续工作，风险高。

方法三：使用 PL/SQL Developer、Toad 等第三方工具

许多图形化的数据库管理工具（如 PL/SQL Developer, Toad, DBeaver）提供了“复制对象”或“生成 DDL”的功能，可以简化这个过程。

这些工具通常在后台自动执行以下操作：
1. 为选定的对象生成完整的 DDL 创建脚本。
2. 对脚本进行文本替换，将旧的 Schema 名替换为新的 Schema 名。
3. 执行脚本在新 Schema 下创建对象。
4. 执行 INSERT INTO ... SELECT ... 语句来迁移数据。

这本质上是 CTAS 方法的增强版，因为它会帮你处理索引、约束等元数据，但仍然建议在操作后仔细核对。

总结

方法 优点 缺点 推荐场景
数据泵 (expdp/impdp) 功能最完整、安全可靠、官方标准 操作步骤多，需要访问服务器 生产环境、复杂对象、或批量迁移时的首选
CTAS (CREATE TABLE ...) 简单快速 仅限表，丢失大量元数据，风险高 开发/测试环境，或只关心数据的临时简单表
第三方工具 图形化操作，比手动 CTAS 方便 依赖特定工具，底层实现可能存在差异 单个或少量对象的快速迁移，但需仔细验证

总而言之，当需要更改 Oracle 对象的所有者时，数据泵（Data Pump）的 REMAP_SCHEMA 功能是毋庸置疑的最佳实践。虽然它需要更多的准备工作，但其完整性和可靠性可以确保数据和应用逻辑在迁移后保持一致，从而避免在生产环境中引入难以排查的潜在问题。

滚动至顶部