Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Why You Should Never Put Objects Into the SYSTEM or SYSAUX Tablespace

DZone's Guide to

Why You Should Never Put Objects Into the SYSTEM or SYSAUX Tablespace

Just because you *can* doesn't mean you *should*! Come learn about the many reasons why you should never, ever, ever put objects into those tablespaces.

· Database Zone
Free Resource

Learn how to create flexible schemas in a relational database using SQL for JSON.

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.
    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 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!

Create flexible schemas using dynamic columns for semi-structured data. Learn how.

Topics:
database ,objects ,oracle ,tablespace

Published at DZone with permission of Gerald Venzl, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}