|Lesson 4||Default Tablespaces|
|Objective||Choose an appropriate Default Tablespace for a User|
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
CREATE TABLE test (
X NUMBER) TABLESPACE coin_auction;
Supplying a tablespace name in the
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
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
tablespace is where Oracle keeps the data dictionary.
Because all Oracle databases have a
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.
tablespace is very heavily used, and placing user objects there can lead to poor performance due to disk contention
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
database, this catch-all tablespace is named
. 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.
Creating a Default Permanent Tablespace
The DEFAULT TABLESPACE clause of the CREATE DATABASE statement specifies a default permanent tablespace for the database. Oracle Database assigns to this tablespace any non-SYSTEM users for whom you do not explicitly specify a different permanent tablespace. If you do not specify this clause, then the SYSTEM tablespace is the default permanent tablespace for non-SYSTEM users. Oracle strongly recommends that you create a default permanent tablespace.
Creating a Default Temporary Tablespace
The DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement creates a default temporary tablespace for the database. Oracle Database assigns this tablespace as the temporary tablespace for users who are not explicitly assigned a temporary tablespace.
You can explicitly assign a temporary tablespace or tablespace group to a user in the CREATE USER statement. However, if you do not do so, and if no default temporary tablespace has been specified for the database, then by default these users are assigned the SYSTEM tablespace as their temporary tablespace. It is not good practice to store temporary data in the SYSTEM tablespace, and it is cumbersome to assign every user a temporary tablespace individually. Therefore, Oracle recommends that you use the
DEFAULT TEMPORARY TABLESPACE clause of CREATE DATABASE.
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.
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.