当前位置导航:炫浪网>>网络学院>>编程开发>>Oracle教程

Oracle10g ASM数据库中表空间的维护


  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.
  
  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         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.
  
  简单操作,供参考。
相关内容
赞助商链接