OCM_Session1_5_Tablespace Creation and Configurationnote

最新推荐文章于 2024-06-01 15:56:03 发布

原创最新推荐文章于 2024-06-01 15:56:03 发布 · 3.1k 阅读

1 ·

CC 4.0 BY-SA版权

OCM experiment 专栏收录该内容

42 篇文章

订阅专栏

本文详细介绍了如何在Oracle数据库中创建临时表空间组，包括添加临时表空间到组、设置默认临时表空间；同时阐述了如何创建永久表空间以存储不同类型的测试数据，包括样本测试数据、索引、数据收集工具产生的数据和在线交易处理数据，并指出了每个步骤的关键参数和操作方法。

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

http://docs.oracle.com/cd/B19306_01/server.102/b14231/tspaces.htm#i1013552

参照：

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

http://docs.oracle.com/cd/B19306_01/server.102/b14231/tspaces.htm#i1010733

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.