在使用expdp/impdp迁移的过程中,偶尔会遇到用户中关于sys对象的授权丢失导致不少pl/sql程序无效,通过测试重现sys授权丢失现象
创建用户并进行sys对象授权给该用户

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 19 12:04:22 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user xff identified by oracle;

User created.

SQL> grant resource,connect to xff;

Grant succeeded.

SQL> grant select on sys.obj$ to xff;

Grant succeeded.

SQL> grant execute on sys.dbms_lock to xff;

Grant succeeded.

SQL> grant select on sys.v_$session to xff;

Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

使用exp导出数据

C:\Users\XFF>expdp "'/ as sysdba'" dumpfile=xff.dmp schemas=xff

Export: Release 11.2.0.4.0 - Production on Sun Jan 19 12:05:35 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_04":  "/******** AS SYSDBA" dumpfile=xff.dmp schemas=xff
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master table "SYS"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_04 is:
  C:\APP\XFF\ADMIN\ORCL\DPDUMP\XFF.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_04" successfully completed at Sun Jan 19 12:05:37 2025 elapsed 0 00:00:02

使用impdp导入数据

C:\Users\XFF>impdp "'/ as sysdba'" dumpfile=xff.dmp remap_schema=xff:nxff

Import: Release 11.2.0.4.0 - Production on Sun Jan 19 12:06:22 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" dumpfile=xff.dmp remap_schema=xff:nxff
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Sun Jan 19 12:06:23 2025 elapsed 0 00:00:00

验证用户的权限

C:\Users\XFF>sqlplus xff/oracle

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 19 12:09:21 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user;
USER is "XFF"
SQL> select count(1) from v$session;

  COUNT(1)
----------
        26

SQL> select count(1) from sys.obj$;

  COUNT(1)
----------
     90656

SQL> desc sys.dbms_lock
PROCEDURE ALLOCATE_UNIQUE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKNAME                       VARCHAR2                IN
 LOCKHANDLE                     VARCHAR2                OUT
 EXPIRATION_SECS                NUMBER(38)              IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
 LOCKMODE                       NUMBER(38)              IN
 TIMEOUT                        NUMBER                  IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
 LOCKMODE                       NUMBER(38)              IN
 TIMEOUT                        NUMBER                  IN     DEFAULT
FUNCTION RELEASE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
FUNCTION RELEASE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
FUNCTION REQUEST RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
 LOCKMODE                       NUMBER(38)              IN     DEFAULT
 TIMEOUT                        NUMBER(38)              IN     DEFAULT
 RELEASE_ON_COMMIT              BOOLEAN                 IN     DEFAULT
FUNCTION REQUEST RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
 LOCKMODE                       NUMBER(38)              IN     DEFAULT
 TIMEOUT                        NUMBER(38)              IN     DEFAULT
 RELEASE_ON_COMMIT              BOOLEAN                 IN     DEFAULT
PROCEDURE SLEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SECONDS                        NUMBER                  IN

SQL> conn nxff/oracle
Connected.
SQL> desc sys.dbms_lock
ERROR:
ORA-04043: object sys.dbms_lock does not exist


SQL> select count(1) from sys.obj$;
select count(1) from sys.obj$
                         *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(1) from v$session;
select count(1) from v$session
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

确认通过impdp迁移过去的nxff用户没有之前xff用户里面sys授权的对象的访问权限.通过sqlfile查看expdp导出的dmp文件中ddl内容,确认确实没有sys部分的授权

SQL> select 'grant ' || privilege || ' on ' ||'"'||table_name ||'"'||
  2        ' to ' || grantee || ';' "GRANTS"
  3   from dba_tab_privs
  4  where owner = 'SYS' and privilege not in ('READ', 'WRITE')
  5    and grantee in ('XFF')
  6  order by 1;

GRANTS
--------------------------------------------------------------------------------
grant EXECUTE on "DBMS_LOCK" to XFF;
grant SELECT on "OBJ$" to XFF;
grant SELECT on "V_$SESSION" to XFF;