3.6 How do I…Determine user defaults?

Problem

When a user connects to the database, the session acquires specific default privileges and system limitations. When any new objects such as tables are created, they are put into the users default tablespace. Also, any sorting processes that need temporary objects are placed in default tablespaces. How do I determine user defaults?

Technique

By querying the DBA_USERS view, you can determine the temporary tablespace, the default tablespace, and the assigned profile.

Steps

1. Execute the CHP3_8.sql script, shown in Figure 3.9, to query the DBA_USERS view. When prompted, enter the username for whom you want to determine the user default. Figure 3.10 shows a sample result.

How It Works

Each user has several defaults that are assigned when the user logs on to the database. The TEMPORARY_TABLESPACE column of the DBA_USERS view determines where temporary sort segments will be created by Oracle in the event that memory cannot be allocated into the sort area. The size of the temporary segments will be determined by the default storage parameters defined for the temporary tablespace.

The DEFAULT_TABLESPACE column of the DBA_USERS view determines where objects are stored when created by the user without specifying another tablespace. All objects are stored in the DEFAULT_TABLESPACE location, with the exception of functions, stored procedures, and packages. These are always stored within the data dictionary in the SYSTEM tablespace.

The PROFILE column of the DBA_USERS view determines which profile is assigned to a user. The profile stores all limitations to the user, such as disk space, CPU quotas, and maximum connection time.

Comments

The process of creating, modifying, and associating user profiles is discussed in How-To 3.9. Granting and determining user disk quotas are explained in How-To 3.2.