I still see it happening that people put object, i.e. tables, etc. in the SYSTEM or SYSAUX tablespace. Sometimes it’s done deliberately, sometimes it happens automatically by creating a table in the SYS schema. Well, let me tell you, it’s a really bad idea. You should never, ever, ever put any kind of user object into those tablespaces. Even the Oracle Database Documentation warns you of doing so:
7.3.1 SYS and SYSTEM Users
The SYS and SYSTEM administrative user accounts are automatically created when you install Oracle Database. They are both created with the password that you supplied upon installation, and they are both automatically granted the DBA role.
- SYS: This account can perform all administrative functions. All base (underlying) tables and views for the database data dictionary are stored in the SYS schema. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator. You must not create any tables in the SYS schema.The SYS user is granted the SYSDBA privilege, which enables a user to perform high-level administrative tasks such as backup and recovery.
- SYSTEM: This account can perform all administrative functions except the following:
- Backup and recovery
- Database upgrade
While you can use this account to perform day-to-day administrative tasks, Oracle strongly recommends creating named user accounts for administering the Oracle database to enable monitoring of database activity.
The simplest explanation is that those tablespaces are Oracle internal tablespaces. They are there for Oracle to do its job, not for the user to store his data. However, Oracle doesn’t stop you from not doing so and that is mainly because of legacy/upgrade reasons. That’s right, there are still people out there happily running very, very old Oracle database versions and some of those old versions didn’t have the concept of system and user tablespaces back then. In order to allow a smooth(er) upgrade to a newer version Oracle still provides you the possibility to upgrade to the newer version, having your data in what would then become a system tablespace, and then move your tables and data over into user tablespaces after the upgrade is finished.
All that said, there are other legitimate, less well documented reasons why you should never put object into those tablespaces:
- The SYS and SYSTEM tablespaces cannot be transported cross-endian (e.g. HP-UX (big endian) to Linux (small endian)), so if you want to migrate you will have lots of extra work to do.
- Data Pump (and even back to original exp/imp) do not export SYS-owned objects, or even grants on SYS-owned objects, because doing so would open up lots of security problems.
- Putting application or user objects in SYS or SYSTEM means granting access to those schemas (probably) that would not be appropriate for most applications or users. Or, to void that, it’s a lot more work to manage access to objects at an individual object level.
I hope this list helps and if anybody ever walks up to you, or you decide that it would be a good idea, please point them to this blog post and if they still don’t believe it, have them come talk to me.