用这个SQL，把SCHNAME换成您的那个模式名就行了

SELECT NAME
  FROM SYSOBJECTS
 WHERE ID IN (SELECT URID
          FROM SYSGRANTS
         WHERE OBJID IN (SELECT ID FROM SYSOBJECTS WHERE NAME = 'SCHNAME' AND TYPE$ = 'SCH'));

select case when (colid!=-1 ) then 'grant ' || SF_GET_SYS_PRIV(PRIVID) || '('|| SUBSTRING_INDEX(SF_GET_OBJ_FULL_NAME(OBJID, COLID),'.',-1) ||') on ' || SUBSTRING_INDEX(SF_GET_OBJ_FULL_NAME(OBJID, COLID),'.',2) || ' to "'||REPLACE(u.username, '"', '""') || '"'
else 'grant ' || SF_GET_SYS_PRIV(PRIVID) || ' on ' ||
SF_GET_OBJ_FULL_NAME(OBJID, COLID) || ' to "'|| REPLACE(u.username, '"', '""')|| '"' --对象权限，2023新版用with grant option 旧版用with admin option
end || (case when GRANTABLE='Y' then ' with grant option;' else ';' end) as grant_sql

from SYS.SYSGRANTS GRANTS
join dba_users u
on GRANTS.urid=u.user_id
where (OBJID != -1 OR COLID != -1) and PRIVID != -1
-- and URID in (select user_id from dba_users where username='DMTEST1' )
and objid in ( SELECT ID FROM SYSOBJECTS WHERE NAME = 'A' AND TYPE$ = 'SCH' )
order by u.username,SF_GET_OBJ_FULL_NAME(OBJID, COLID),
PRIVID
;

这个可以查询到是grant select on schema X 还是grant update on schema X

查询哪些用户拥有PANDA模式的权限

10:48:00 panda@DAMENG:5236 SQL>  select * from dba_tab_privs where owner='PANDA';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
------- ----- ---------- ------- --------- --------- ---------
DBMT    PANDA TAB1       PANDA   SELECT    YES       NO

如上，DBMT用户拥有PANDA.TAB表的select权限，授权用户是PANDA，GRANTABLE=YES可以再授权

建议直接查询对象拥有的权限

select grantee, granted_role, admin_option, default_role from dba_role_privs where upper(grantee) like upper('%dbmt%');

select grantee, privilege, admin_option from dba_sys_privs where upper(grantee) like upper('%dbmt%');

select grantee, owner, table_name, privilege from dba_tab_privs where upper(grantee) like upper('%dbmt%');


GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
------- ------------ ------------ ------------
DBMT    DBA          N            NULL
DBMT    PUBLIC       N            NULL

used time: 3.995(ms). Execute id is 22823.

GRANTEE PRIVILEGE      ADMIN_OPTION
------- -------------- ------------
DBMT    CREATE SESSION NO

used time: 1.464(ms). Execute id is 22824.

GRANTEE OWNER TABLE_NAME PRIVILEGE
------- ----- ---------- ---------
DBMT    PANDA TAB1       SELECT