June 16, 2005
Oracle10g ASM数据库中表空间的维护
作者:eygle
出处:http://blog.eygle.com
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 Copyright (c) 1982, 2004, Oracle. All rights reserved. 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 2 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. |
3.更改文件大小
SQL> alter database datafile '+DATADG/mmsdb/datafile/cumail.280.3' resize 300m; Database altered. SQL> |
4.设置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 25 +DATADG/mmsdb/datafile/sysaux.257.3 330 +DATADG/mmsdb/datafile/users.256.3 5 +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. |
简单操作，供参考。
Posted by eygle at 9:46 AM | Comments (0)