Where do stored procedures reside in? The quick answer is SYSTEM tablespace. Oracle documentation (Logical Storage Structures of 18c) mentions about SYSTEM tablespace as this:
The SYSTEM tablespace includes the following information, all owned by the SYS user:
- The data dictionary
- Tables and views that contain administrative information about the database
- Compiled stored objects such as triggers, procedures, and packages
That is, all stored procedures, dependencies, views (not materialized), sequences, synonyms, and database links, etc. are essentially owned by SYS and they will be stored in SYSTEM tablespace.
Let's rephrase it from the segment's point of views. (Oracle documentation: Overview of Segments)
- User Segments go to user tablespace, if any.
- Temporary Segments go to TEMP tablespace.
- Undo Segments go to UNDO tablespace.
Which means, the schema objects that are user segments will NOT go to SYSTEM tablespace. For example, tables, table partitions, table clusters, indexes, index partitions, LOBs, LOB partitions, and materialized views are all simply user segments. They have clear ownership, take considerable space in databases and therefore, need separate user tablespaces to store.
We should be aware of the nature of schema objects when migrating database from one to another. 3 phases should be clearly ordered in the processes of database migration under my definitions, you may refer to this post for more information.
A Systematic Approach to Migrate Databases