Oracle10gASM数据库中表空间的维护

王朝oracle·作者佚名 2008-05-19

宽屏版字体: 小|中|大|超大

1.缺省的Oracle命名

当使用ASM + OMF管理时，Oracle会自动分配文件名称，创建相应的文件。

我们创建表空间的工作可以简化为:

create tablespace tablespace_name;

一个命令。

以下是Oracle的缺省命名示例:

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.3.0 - Production on Thu Jun 16 09:26:22 2005

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production

With the Partitioning and Data Mining options

SQL select name from v$datafile;

NAME

--------------------------------------------------------------------------------

+DATADG/mmsdb/datafile/system.259.3

+DATADG/mmsdb/datafile/undotbs1.258.3

+DATADG/mmsdb/datafile/sysaux.257.3

+DATADG/mmsdb/datafile/users.256.3

+DATADG/mmsdb/datafile/platform.278.1

+DATADG/mmsdb/datafile/gateway.279.1

6 rows selected.

2.自定义名称和文件大小创建

如果我们想自定义文件大小及名称，可以使用如下方法创建:

SQL create tablespace cumail datafile '+DATADG/mmsdb/datafile/cumail'size 100m;

Tablespace created.

SQL select name from v$datafile;

NAME

--------------------------------------------------------------------------------

+DATADG/mmsdb/datafile/system.259.3

+DATADG/mmsdb/datafile/undotbs1.258.3

+DATADG/mmsdb/datafile/sysaux.257.3

+DATADG/mmsdb/datafile/users.256.3

+DATADG/mmsdb/datafile/platform.278.1

+DATADG/mmsdb/datafile/gateway.279.1

+DATADG/mmsdb/datafile/cumail

7 rows selected.

SQL drop tablespace cumail including contents and datafiles;

Tablespace dropped.

3.创建ASSM管理表空间

需要指定segment space子句:

SQL create tablespace cumail

segment space management auto;

Tablespace created.

SQL select name from v$datafile;

NAME

------------------------------------------------------

+DATADG/mmsdb/datafile/system.259.3

+DATADG/mmsdb/datafile/undotbs1.258.3

+DATADG/mmsdb/datafile/sysaux.257.3

+DATADG/mmsdb/datafile/users.256.3

+DATADG/mmsdb/datafile/platform.278.1

+DATADG/mmsdb/datafile/gateway.279.1

+DATADG/mmsdb/datafile/cumail.280.3

7 rows selected.

SQL select tablespace_name,extent_management,segment_space_management from dba_tablespaces;

TABLESPACE_NAME

EXTENT_MAN SEGMEN

------------------------------ ---------- ------

SYSTEM

LOCAL

MANUAL

UNDOTBS1

LOCAL

MANUAL

SYSAUX

LOCAL

AUTO

TEMP

LOCAL

MANUAL

USERS

LOCAL

AUTO

PLATFORM

LOCAL

MANUAL

GATEWAY

LOCAL

MANUAL

CUMAIL

LOCAL

AUTO

8 rows selected.

4.更改文件大小

SQL alter database datafile '+DATADG/mmsdb/datafile/cumail.280.3' resize 300m;

Database altered.

SQL

5.设置UNIFORM SIZE属性

如果需要设置extent的一些属性，那么必须显示的指定。

SQL create tablespace ticket extent management local uniform size 128k segment space management auto;

Tablespace created.

SQL select name ,bytes/1024/1024 from v$datafile;

NAME

BYTES/1024/1024

---------------------------------------- ---------------

+DATADG/mmsdb/datafile/system.259.3

450

+DATADG/mmsdb/datafile/undotbs1.258.3

+DATADG/mmsdb/datafile/sysaux.257.3

330

+DATADG/mmsdb/datafile/users.256.3

+DATADG/mmsdb/datafile/platform.278.1

1000

+DATADG/mmsdb/datafile/gateway.279.1

1000

+DATADG/mmsdb/datafile/cumail.280.3

300

+DATADG/mmsdb/datafile/ticket.281.1

100

8 rows selected.

SQL alter database datafile '+DATADG/mmsdb/datafile/ticket.281.1' resize 300m;

Database altered.

简单操作，供参考。