本文包括创建表空间，添加数据文件，表空间重命名，表空间调整，表空间组，大文件表空间，裸设备表空间等。
创建表空间
CREATE TABLESPACE "EALLTEST"
LOGGING
DATAFILE '/opt/app/oracle/oradata/ealltest/test5.dbf' SIZE
2000M REUSE, '/opt/app/oracle/oradata/ealltest/test4.dbf'
SIZE 2000M REUSE, '/opt/app/oracle/oradata/ealltest/
test1.dbf' SIZE 2000M REUSE, '/opt/app/oracle/oradata/
ealltest/test2.dbf' SIZE 2000M REUSE, '/opt/app/oracle/
oradata/ealltest/test3.dbf' SIZE 2000M REUSE, '/home/oracle/
ealltest/test6.dbf' SIZE 2000M REUSE, '/opt/app/oracle/
oradata/ealltest/test7.dbf' SIZE 800M REUSE EXTENT MANAGEMENT
DICTIONARY DEFAULT STORAGE ( INITIAL 40K NEXT 40K MINEXTENTS
1 MAXEXTENTS 505 PCTINCREASE 50 ) MINIMUM EXTENT 0K
**********************************************************************
SQL> show user;
USER 为"SYS"
SQL> create tablespace ealltst datafile
2 '/opt/app/oracle/oradata/as2pro/ealltst01.dbf' size 2000m,
3 '/opt/app/oracle/oradata/as2pro/ealltst02.dbf' size 2000m,
4 '/opt/app/oracle/oradata/as2pro/ealltst03.dbf' size 2000m,
5 '/opt/app/oracle/oradata/as2pro/ealltst04.dbf' size 2000m,
6 '/opt/app/oracle/oradata/as2pro/ealltst05.dbf' size 2000m,
7 '/opt/app/oracle/oradata/as2pro/ealltst06.dbf' size 2000m,
8 '/opt/app/oracle/oradata/as2pro/ealltst07.dbf' size 2000m,
9 '/opt/app/oracle/oradata/as2pro/ealltst08.dbf' size 2000m;
表空间已创建。
create tablespace tablespacename datafile '/u01/app/oracle/oradata/rmanrep/users01.dbf' autoextend on next 20m maxsize 1g;
添加一个数据文件到现有表空间
SQL> alter tablespace users add datafile '/u03/oradata/rh1dev1/users02.dbf' size 100M;
修改表空间的名字
SQL> alter tablespace ceshits2 rename to becvxts2;
Tablespace altered.
给表空间加文件
SQL> alter tablespace eall add datafile
2 '/opt/app/oracle/oradata/eall79/eall05.dbf'
3 size 2000m;
表空间删除后怎样删除表空间在硬盘里的文件？
The tablespace can be online or offline, but it is best to take the tablespace offline before dropping it.
加个参数就可以直接同时删除数据文件
SQL> drop user sjzeall cascade;
用户已丢弃
SQL> drop tablespace sjzeall including contents and datafiles;
表空间已丢弃。
Tue May 22 19:18:07 2007
drop tablespace sjzeall including contents and datafiles
Tue May 22 19:18:09 2007
Deleted file /opt/app/oracle/oradata/eall62/sjzeall.dbf
Completed: drop tablespace sjzeall including contents and dat
修改表空间大小
alter database datafile '/u01/app/oracle/oradata/rmanrep/users01.dbf' resize 15m;
alter tablespace users add datafile '/u03/oradata/users02.dbf' size 50m autoextend on next 10m maxsize 200m;
11g创建表空间组
alter tablespace temp1 tablespace group tempgrp;
alter tablespace temp2 tablespace group tempgrp;
alter tablespace temp3 tablespace group tempgrp;
指定表空间组
alter database default temporary tablespace tempgrp;
删除表空间组成员
alter tablespace temp3 tablespace group '';
使用示例
create user jenweb identified by pi4001 default tablespace users temporary tablespace tempgrp;
select username, default_tablespace, temporary_tablespace from dba_users where username = 'JENWEB';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
-------------------- ------------------ --------------------
JENWEB USERS TEMPGRP
select group_name, tablespace_name from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
---------------------------- ----------------------------
TEMPGRP TEMP1
TEMPGRP TEMP2
TEMPGRP TEMP3
创建表空间
SQL> show user;
USER 为"SYS"
SQL> create tablespace ealltst datafile
2 '/opt/app/oracle/oradata/as2pro/ealltst01.dbf' size 200ib0m,
3 '/opt/app/oracle/oradata/as2pro/ealltst02.dbf' size 2000m,
4 '/opt/app/oracle/oradata/as2pro/ealltst03.dbf' size 2000m,
5 '/opt/app/oracle/oradata/as2pro/ealltst04.dbf' size 2000m,
6 '/opt/app/oracle/oradata/as2pro/ealltst05.dbf' size 2000m,
7 '/opt/app/oracle/oradata/as2pro/ealltst06.dbf' size 2000m,
8 '/opt/app/oracle/oradata/as2pro/ealltst07.dbf' size 2000m,
9 '/opt/app/oracle/oradata/as2pro/ealltst08.dbf' size 2000m;
表空间已创建。
CREATE TABLESPACE "EALLTEST"
LOGGING
DATAFILE '/opt/app/oracle/oradata/ealltest/test5.dbf' SIZE
2000M REUSE, '/opt/app/oracle/oradata/ealltest/test4.dbf'
SIZE 2000M REUSE, '/opt/app/oracle/oradata/ealltest/
test1.dbf' SIZE 2000M REUSE, '/opt/app/oracle/oradata/
ealltest/test2.dbf' SIZE 2000M REUSE, '/opt/app/oracle/
oradata/ealltest/test3.dbf' SIZE 2000M REUSE, '/home/oracle/
ealltest/test6.dbf' SIZE 2000M REUSE, '/opt/app/oracle/
oradata/ealltest/test7.dbf' SIZE 800M REUSE EXTENT MANAGEMENT
DICTIONARY DEFAULT STORAGE ( INITIAL 40K NEXT 40K MINEXTENTS
1 MAXEXTENTS 505 PCTINCREASE 50 ) MINIMUM EXTENT 0K
大文件表空间
create bigfile tablespace whs01 datafile '/u06/oradata/whs01.dbf' size 10g;
alter tablespace whs01 resize 80g;
alter tablespace whs01 autoextend on next 20g;
大文件表空间从某种角度来说提高了 Oracle 在 VLDB 上的管理能力。 只有自动段空间管理的 LMT (Locally Managed Tablespaces ) 支持 BIGFILE 表空间。 大文件表空间只能包含一个文件，但是文件可以达到4G 个数据块大小。(以下用 BFT 指代 BIGFILE Tablespace。)
BFT 可以和以下存储技术结合使用：
自动存储管理(ASM)
LVM
OMF
理论上的 BFT 可以达到下面所列的值：
数据块大小(单位：K)BFT 最大值(单位：T)
2k8T
4k16T
8k32T
16k64T
32k128T
在实际环境中，这还受到操作系统的文件系统的限制。
测试：
Platform: 102040
（1）只允许创建一个数据文件
SQL> create tablespace test1 datafile
2 '/u02/oradata/tsdb/test101.dbf' size 50m,
3 '/u02/oradata/tsdb/test102.dbf' size 50m;
Tablespace created.
SQL> create bigfile tablespace test2 datafile
2 '/u02/oradata/tsdb/test201.dbf' size 50m,
3 '/u02/oradata/tsdb/test202.dbf' size 50m;
create bigfile tablespace test2 datafile
*
ERROR at line 1:
ORA-32774: more than one file was specified for bigfile tablespace TEST2
SQL>
（2）验证区和段的管理方式
SQL> create bigfile tablespace ceshidata datafile '/u02/oradata/tsdb/ceshidata01.dbf' size 50m;
Tablespace created.
SQL> select tablespace_name, extent_management, allocation_type, segment_space_management, bigfile from dba_tablespaces;
TABLESPACE_NAME EXTENT_MAN ALLOCATIO SEGMEN BIG
------------------------------ ---------- --------- ------ ---
SYSTEM LOCAL SYSTEM MANUAL NO
UNDOTBS1 LOCAL SYSTEM MANUAL NO
SYSAUX LOCAL SYSTEM AUTO NO
TEMP LOCAL UNIFORM MANUAL NO
USERS LOCAL SYSTEM AUTO NO
EXAMPLE LOCAL SYSTEM AUTO NO
CROSSPF LOCAL SYSTEM AUTO NO
TRAFFIC LOCAL SYSTEM AUTO NO
ceshiDATA LOCAL SYSTEM AUTO YES
TEST1 LOCAL SYSTEM AUTO NO
10 rows selected.
裸设备创建表空间实例
lvs 查看逻辑卷序列
lvcreate -L 5120M -n lvdb_data20 vg00 按照编号创建下一个逻辑卷
运行
raw /dev/raw/raw120 /dev/vg00/lvdb_data20
raw /dev/raw/raw121 /dev/vg00/lvdb_data21
把运行的命令在文件中添加 vi /etc/rc.local 确保系统重启时自动挂载裸设备
[root@mail ~]# ll /dev/raw* 查看裸设备的权限
[root@mail ~]# chown oracle:oinstall /dev/raw/raw120 将裸设备权限赋予oracle
[root@mail ~]# chown oracle:oinstall /dev/raw/raw121
[root@mail ~]# chmod 660 /dev/raw/raw120 权限赋予
[root@mail ~]# chmod 660 /dev/raw/raw121
cd /u02/oradata/mesdb 进入oracle数据文件目录
ll |awk '{print $11 "\t\t" $9}' |sort 查看已使用的数据文件号
su - oracle
cd /u02/oradata/mesdb
ln -s /dev/raw/raw120 tsabc.dbf 将准备使用的裸设备链接到abc.dbf以方便使用
sqlplus '/as sysdba'
create tablespace tsabc datafile '/u02/oradata/mesdb/tsabc.dbf' size 5000m;
create user abc identified by 123456 default tablespace tsabc temporary tablespace temp;
grant connect, resource to abc;
revoke unlimited tablespace from abc; 收回abc访问所有表空间权限
alter user abc quota unlimited on tsabc; 赋予abc只能访问tsabc表空间
***************
查看裸设备与数据库文件的对应情况
su - oracle
[oracle@db106 mesdb]$ ll |awk '{print $11 "\t\t" $9}' |sort
/dev/raw/raw101 system01.dbf
/dev/raw/raw102 sysaux01.dbf
/dev/raw/raw103 undotbs01.dbf
/dev/raw/raw104 users01.dbf
/dev/raw/raw105 temp01.dbf
/dev/raw/raw106 mbsms01.dbf
/dev/raw/raw107 mbmms01.dbf
/dev/raw/raw108 mbwap01.dbf
/dev/raw/raw109 mbstat01.dbf
/dev/raw/raw110 misc01.dbf
/dev/raw/raw1 control01.ctl
/dev/raw/raw21 redo01.log
/dev/raw/raw22 redo02.log
/dev/raw/raw23 redo03.log
/dev/raw/raw24 redo04.log
/dev/raw/raw25 redo05.log
/dev/raw/raw2 control02.ctl
/dev/raw/raw3 control03.ctl
[root@db106 ~]# raw -qa
/dev/raw/raw24: bound to major 253, minor 15
/dev/raw/raw25: bound to major 253, minor 16
/dev/raw/raw101: bound to major 253, minor 22
/dev/raw/raw102: bound to major 253, minor 23
/dev/raw/raw103: bound to major 253, minor 24
数据文件
lvcreate -L 5120M -n lvdb_data01 vg00
lvcreate -L 5120M -n lvdb_data02 vg00
lvcreate -L 5120M -n lvdb_data03 vg00
裸设备新增5个，如下所示：
raw /dev/raw/raw109 /dev/vg00/lvdb_data09
raw /dev/raw/raw110 /dev/vg00/lvdb_data10
数据库的使用新增5个，从raw106至raw110
ln -s /dev/raw/raw109 /u02/oradata/mesdb/mbstat01.dbf
ln -s /dev/raw/raw110 /u02/oradata/mesdb/misc01.dbf
欢迎关注我的公众号 扫描二维码或公众号搜索 “我的工作”
