- 5.1 Create a temporary tablespace group that contains two(2) temporary tablespaces to support batch processing, the creation of large indexes,and analyzing tables, Use the following specifications:
- 5.1.1 Temporary tablespace group named TEMP_GRP containing temporary tablespaces TEMP1 and TEMP2.
- 5.1.2 Make TEMP_GRP the default temporary tablespace for all new users.
Changing Members of a Tablespace Group
You can add a tablespace to an existing tablespace group by specifying the existing group name in the TABLESPACE GROUP
clause of the CREATE TEMPORARY TABLESPACE
or ALTER TABLESPACE
statement.
The following statement adds a tablespace to an existing group. It creates and adds tablespace lmtemp3
to group1
, so that group1
contains tablespaces lmtemp2
and lmtemp3
.
The following statement also adds a tablespace to an existing group, but in this case because tablespace lmtemp2
already belongs to group1
, it is in effect moved from group1
to group2
:
Now group2
contains both lmtemp
and lmtemp2
, while group1
consists of only tmtemp3
.
You can remove a tablespace from a group as shown in the following statement:
Tablespace lmtemp3
no longer belongs to any group. Further, since there are no longer any members of group1
, this results in the implicit deletion of group1
.
Assigning a Tablespace Group as the Default Temporary Tablespace
Use the ALTER DATABASE...DEFAULT
TEMPORARY
TABLESPACE
statement to assign a tablespace group as the default temporary tablespace for the database. For example:
- 5.2 Create a permanent tablespace to store sample test data. Use the following specifications:
- 5.2.1 Tablespace name of EXAMPLE
- 5.2.2 Initial datafile size of 400MB with the file expectd to grow to 4TB.
- 5.2.3 Initial extent size of 1MB
- 5.2.4 next extent size of 1MB
Creating a Bigfile Tablespace
To create a bigfile tablespace, specify the BIGFILE
keyword of the CREATE
TABLESPACE
statement (CREATE
BIGFILE
TABLESPACE
...). Oracle Database automatically creates a locally managed tablespace with automatic segment space management. You can, but need not, specify EXTENT
MANAGEMENT
LOCAL
and SEGMENT
SPACE
MANAGEMENT
AUTO
in this statement. However, the database returns an error if you specify EXTENT
MANAGEMENT
DICTIONARY
or SEGMENT
SPACE
MANAGEMENT
MANUAL
. The remaining syntax of the statement is the same as for the CREATE TABLESPACE
statement, but you can only specify one datafile. For example:
You can specify SIZE
in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).
If the default tablespace type was set to BIGFILE
at database creation, you need not specify the keyword BIGFILE
in the CREATE TABLESPACE
statement. A bigfile tablespace is created by default.
If the default tablespace type was set to BIGFILE
at database creation, but you want to create a traditional (smallfile) tablespace, then specify a CREATE
SMALLFILE
TABLESPACE
statement to override the default tablespace type for the tablespace that you are creating.
操作如下:
- 5.3 Create a permanent tablespace to store indexes, Use the following specifications:
- 5.3.1 Tablespace name of INDX
- 5.3.2 File size of 40MB
- 5.4 Create a permanent tablespace to store data collected from various Oracle tools. Use the following specifications:
- 5.4.1 Tablespace name of TOOLS
- 5.4.2 File size of 10MB
- 5.5 Create a default permanent tablespace using the following specifications:
- 5.5.1 Tablespace name of USERS
- 5.5.2 File size of 48MB
- 5.5.3 Initial extent size of 4MB
- 5.5.4 Next extent size of 4MB
- 5.6 Create a permanent tablespace for storing segments associated with online transaction processing with high insert rates. Due to the potential high volume of concurrent inserts, every effort should be taken to reduce contention for each of the tables that will be stored in this tablespace. Use the following specifications:
- 5.6.1 Tablespace name of OLTP
- 5.6.2 File size of 48MB
- 5.6.3 Initial extent size of 2MB
- 5.6.4 Next extent size of 2MB