Select Default Tablespaces for Oracle User

Ideally, when you create an object such as a table or an index, you tell Oracle where to store that object by providing a tablespace name.
For example, the following table will be stored in the COIN_AUCTION tablespace:

CREATE TABLE test (
  X  NUMBER) TABLESPACE coin_auction;

Supplying a tablespace name in the CREATE command is certainly a best practice. However, for novice users or users running applications that create objects, a tablespace name may not always be supplied. Standard SQL does not require a tablespace name as part of a CREATE TABLE command. Because of this, Oracle creates a table whether you supply a tablespace or not. The default tablespace allows Oracle to do this. When a user creates an object without supplying a tablespace name, Oracle determines where to store the object. If the user has a default tablespace defined, then the object will be stored there. Otherwise, the object will be created in the SYSTEM tablespace. The SYSTEM tablespace is where Oracle keeps the data dictionary. Because all Oracle databases have a SYSTEM tablespace, and because Oracle always falls back on that tablespace, the lack of an explicit tablespace assignment will never prevent an object from being created. However, the SYSTEM tablespace is very heavily used, and placing user objects there can lead to poor performance due to disk contention^[1].
Most databases are created with at least one tablespace that can be used as the default for users who you expect to create only a few small objects. In the case of the COIN database, this catch-all tablespace is named USERS. You will want to make that the default for most users created in this course. If you are creating a user who you expect to own large amounts of data, you should consider creating one or more tablespaces specifically for that user and should make one of those the default.

Note:When you specify a locally managed SYSTEM tablespace, the SYSTEM tablespace cannot be used as a temporary tablespace. In this case the database creates a default temporary tablespace. You can add or change the default temporary tablespace after database creation. You do this by creating a new temporary tablespace or tablespace group with a

CREATE TEMPORARY TABLESPACE

statement, and then assign it as the temporary tablespace using the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement. Users will automatically be switched (or assigned) to the new default temporary tablespace. The following statement assigns a new default temporary tablespace:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempts2;

The new default temporary tablespace must already exist. When using a locally managed SYSTEM tablespace, the new default temporary tablespace must also be locally managed. You cannot drop or take offline a default temporary tablespace, but you can assign a new default temporary tablespace and then drop or take offline the former one. You cannot change a default temporary tablespace to a permanent tablespace. Users can obtain the name of the current default temporary tablespace by querying the PROPERTY_NAME and PROPERTY_VALUE columns of the DATABASE_PROPERTIES view. These columns contain the values "DEFAULT_TEMP_TABLESPACE" and the default temporary tablespace name, respectively.

[1]disk contention: The problem resulting when multiple processes are all trying to access data on the same disk, to the point where the disk becomes overloaded and can not respond to all the I/O requests in a timely fashion.

[2]default permanent tablespace: default permanent tablespace refers to a designated location where schema objects will be stored if no specific tablespace is specified during their creation. It plays a crucial role in managing data organization and allocation within the database.