5. Tablespace Creation and Configurationnote: Tablespaces must be named as specified in each task to receive credit.
- 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.
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.
5.1创建临时表空间组，包含2个临时表空间来支持批处理，大的索引创建，表的分析，按照以下的说明来创建：
5.1.1 临时表空间组的名称叫TEMP_GRP,包含TEMP1，TEMP2这两个临时表空间。
5.1.2 让TEMP_GRP作为新用户的默认临时表空间。
参考联机文档：
Administrator's Guide ==> Managing Tablespaces==>Creating a Locally Managed Temporary Tablespace
参照：
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 lmtemp2and lmtemp3.
CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf'
SIZE 25M
TABLESPACE GROUP group1;
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:
ALTER TABLESPACE lmtemp2 TABLESPACE GROUP 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:
ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';
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:
ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;
首先添加两个临时表空间TMEP1和TEMP2到临时表空间组TEMP_GRP组
然后再让临时表空间组TEMP_GRP更改为默认的临时表空间
操作如下：
SQL> select * from DATABASE_PROPERTIES;
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMPTS1
Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE SYSTEM Name of default permanent tablespace
DBTIMEZONE +08:00 DB time zone
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET AL32UTF8 Character set
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_RDBMS_VERSION 10.2.0.1.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME PROD Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
27 rows selected.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMPTS1
SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/PROD/Disk1/temp11.dbf'
size 20m
2 tablespace group temp_grp;
Tablespace created.
SQL> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/PROD/Disk1/temp12.dbf'
size 20m
2 tablespace group temp_grp;
Tablespace created.
SQL> alter database default temporary tablespace temp_grp;
Database altered.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMPTS1
TEMP1
TEMP2
6 rows selected.
SQL> select * from DATABASE_PROPERTIES;
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP_GRP
Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE SYSTEM Name of default permanent tablespace
DBTIMEZONE +08:00 DB time zone
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET AL32UTF8 Character set
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_RDBMS_VERSION 10.2.0.1.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME PROD Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
27 rows selected.
SQL>
- 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
参考联机文档：
Administrator's Guide ==> Managing Tablespaces==>Creating a Bigfile Tablespace
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 SPACEMANAGEMENT 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:
CREATE BIGFILE TABLESPACE bigtbs
DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G
...
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 CREATESMALLFILE TABLESPACE statement
to override the default tablespace type for the tablespace that you are creating.
操作如下：
SQL> create bigfile tablespace example datafile
'/u01/app/oracle/oradata/PROD/Disk1/example01.dbf' size 400m
2 autoextend on next 1m maxsize 4t extent management local uniform size 1m;
Tablespace created.
- 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
参考联机文档：
Administrator's Guide ==> Managing Tablespaces==>Creating a Locally Managed Tablespace
操作如下：
SQL> create tablespace indx datafile '/u01/app/oracle/oradata/PROD/Disk1/indx01.dbf' size 40m;
Tablespace created.
- 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
操作如下：
SQL> create tablespace tools datafile '/u01/app/oracle/oradata/PROD/Disk1/tools01.dbf' size 10m;
Tablespace created.
- 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
操作如下：
SQL> create tablespace users datafile '/u01/app/oracle/oradata/PROD/Disk1/users01.dbf' size 48m
2 extent management local uniform size 4m;
Tablespace created.
SQL> alter database default tablespace users;
Database altered.
SQL> select * from DATABASE_PROPERTIES;
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP_GRP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DBTIMEZONE +08:00
...
- 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
(ASSM)
操作如下：
SQL> create tablespace oltp datafile '/u01/app/oracle/oradata/PROD/Disk1/oltp01.dbf' size 48m
2 extent management local uniform size 2m segment space management auto;
Tablespace created.