User Information

This table gives you an overview on all users (schemata) existing within your database. Even if there is no much statistical data, you should check here for a few things:

  • inactive users should be locked (column Account Status)
  • "temporary users" (created e.g. for a task that has a deadline) should have an Expiry Date set
  • The Default TS should never be SYSTEM (except for SYS and SYSTEM)
  • The Temporary TS should always point to a non-permanent (i.e. a temporary) tablespace (which is usually named TEMP on most systems)

Up to Oracle version 8, users are created with default and temporary tablespaces set to SYSTEM (if not explicitely specified otherwise), causing fragmentation of the system tablespace resulting in a performance decrease as well - so when creating a new user you should make sure to explicitely set at least the temporary tablespace to a non-permanent tablespace. Starting with Oracle 9i, the DBA can (and should!) create a default temporary TS using the command CREATE DEFAULT TEMPORARY TABLESPACE. Oracle then will implicitly assume this setting for all CREATE USER statements having no explicit temporary TS.