I still see some people putting objects, i.e. tables, in the SYSTEM or SYSAUX tablespace. Sometimes, it’s done deliberately; but sometimes, it happens automatically by creating a table in the SYS schema. Well, let me tell you — this is 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.
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 or her data. However, Oracle doesn’t stop you from not doing so, and that is mainly because of legacy/upgrade reasons — yeah, there are still people out there happily running very, very old Oracle DB versions and of course, it should be easy for them to upgrade if they ever decide to.
All that said, there are other legitimate, less well-documented reasons why you should never put objects into those tablespaces:
- The SYS and SYSTEM tablespaces cannot be transported cross-endian (i.e. HP-UX [big endian] to Linux [small endian]), so if you want to migrate, you will have lots of extra work to do.
- Data pumps (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. Also, 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 and says that putting objects in the SYSTEM or SYSAUX tablespace, please point them to this blog post. And if they still don’t believe you, have them come talk to me!