Oracle COLLECT函数为何生成内部类型并赋予PUBLIC执行权限？

I’ve run into this exact behavior before—let’s break down what’s happening and why those odd system-generated types are showing up, even when you’re casting to your predefined t_dds_number collection:

1. The COLLECT Function’s Hidden Intermediate Step #

When you write CAST(collect(opentrades_id) AS t_dds_number), Oracle doesn’t jump straight to using your custom type. Here’s the play-by-play:

• First, the COLLECT function builds an anonymous, temporary collection (that’s the SYSTP... type you’re seeing) to hold the aggregated rows during query execution. This is Oracle’s internal way of handling in-memory grouping before applying your explicit cast.
• Even though you’re converting it to your predefined type at the end, Oracle needs this transient system type to manage the aggregation process. These types are supposed to be short-lived, but they can linger in the data dictionary if the query is cached or if permissions get attached to them.

2. Why PUBLIC Ends Up With Execute Permissions #

If you’re seeing PUBLIC has execute rights on these SYSTP... types, it’s almost always one of these scenarios:

• Implicit Permission Spillover: If you grant EXECUTE on a stored procedure, view, or function that runs this query, Oracle might automatically propagate execute rights to the dependent system types. This is especially likely if the object owner has broad privileges like GRANT ANY EXECUTE.
• High-Privilege Execution: If you ran the query as a superuser (like SYS or SYSTEM) that can grant to PUBLIC, the system type might inherit those permissions when it’s created.
• Cached Metadata: Once the query is stored in the shared pool, the system type sticks around with whatever permissions were set during its initial creation—even if you didn’t explicitly grant them to PUBLIC.

3. Should You Worry About This? #

For the most part, these system types are harmless. Oracle usually cleans them up when the shared pool is flushed or when the dependent object is dropped. That said, granting EXECUTE to PUBLIC on any type (even internal ones) could expose minor metadata details, so it’s better to avoid it if you can.

4. How to Fix or Prevent It #

Here are a few practical steps to handle this:

• Narrow Down Permissions: Stop granting broad privileges like EXECUTE ANY TYPE to users. Instead, only grant EXECUTE on your predefined t_dds_number type to the specific users who need it.
• Flush the Shared Pool (Temporary Fix): If you want to get rid of the existing SYSTP... types right now, run ALTER SYSTEM FLUSH SHARED_POOL;—just note that this will clear cached queries and might slow things down temporarily, so do it during off-peak hours.
• Use a Wrapper Function: Instead of casting COLLECT directly in your ad-hoc query, create a function that returns your t_dds_number type. This forces Oracle to use your custom type consistently and reduces the chance of those transient system types sticking around with wrong permissions.

Example wrapper function:

CREATE OR REPLACE FUNCTION fetch_opentrades_ids RETURN t_dds_number IS
  collected_ids t_dds_number;
BEGIN
  SELECT collect(opentrades_id) INTO collected_ids FROM your_trades_table;
  RETURN collected_ids;
END;
/

Then grant EXECUTE on this function only to the users who need access, not to PUBLIC.

内容的提问来源于stack exchange，提问作者Morien Moses