ORACLE ASM环境中删除和创建表空间

最新推荐文章于 2024-07-28 21:15:38 发布

civudgl81761

最新推荐文章于 2024-07-28 21:15:38 发布

阅读量134

点赞数

在ASM上创建表空间，删除表空间等的基本操作

1、列出表空间

SQL> select name from v$tablespace;

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

7 rows selected.

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

SQL> drop tablespace TEST 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 TEST 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/test.dbf

6 rows selected.

SQL> select name from v$tablespace;

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

7 rows selected.

SQL>

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23862439/viewspace-1058093/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23862439/viewspace-1058093/