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.