ORACLE ASM环境中如何删除和创建表空间（数据文件）

三年前曾经花了点时间去学ORACLE，最近慢慢发现忘记了很多，比如在ASM上创建表空间，删除表空间等的基本操作，连语法的忘了；今天做了个测试，记录一下

 

1、列出表空间

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
UNDOTBS2
WOFSSPACE

7 rows selected.

 

2、下面删除WOFSSPACE这个表空间，包括属于他的数据文件（物理上也同时删除）

SQL> drop tablespace WOFSSPACE including contents and datafiles;

Tablespace dropped.

 

3、删除之后，查询表空间视图确认

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
UNDOTBS2

6 rows selected.

 

4、到ASM目录去验证一把，看物理文件是否真的已经删除

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@racdb1 ~]$ export ORACLE_SID=+ASM1
[oracle@racdb1 ~]$ asmcmd
ASMCMD> ls
ARCH/
DATA/
ASMCMD> cd DATA
ASMCMD> ls
RACDB/
ASMCMD> cd RACDB
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileracdb.ora
ASMCMD> cd DATAFILE
ASMCMD> ll
        commands:
        -------- 
        cd
        du
        find
        help
        ls
        lsct
        lsdg
        mkalias
        mkdir
        pwd
        rm
        rmalias
ASMCMD> ls
SYSAUX.262.774579675
SYSTEM.264.774579675
UNDOTBS1.267.774579675
UNDOTBS2.265.774579743
USERS.259.774579675
ASMCMD>

可以可以到物理文件也已经被删除了；如果没有加INCLUDING这个参数，那么可以进去ASM目录，用RM删除之；

 

5、在ASM上创建表空间，自定义目录及DATAFILE的名字

SQL> create tablespace WOFSSPACE datafile '+DATA/RACDB/DATAFILE/WOFSSPACE.dbf' size 4096m autoextend on;

Tablespace created.

 

6、查询确认OK

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/racdb/datafile/system.264.774579675
+DATA/racdb/datafile/undotbs1.267.774579675
+DATA/racdb/datafile/sysaux.262.774579675
+DATA/racdb/datafile/users.259.774579675
+DATA/racdb/datafile/undotbs2.265.774579743
+DATA/racdb/datafile/wofsspace.dbf

6 rows selected.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
UNDOTBS2
WOFSSPACE

7 rows selected.

SQL>