Oracle ASM表空间管理

2022 年 02 月 24 日

335 次浏览

2923字数

一 ASM 创建表空间

SQL> create tablespace chen datafile '+SYSTEDG' size 10M;
SQL> col tablespace_name for a15
SQL> col file_name for a50
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
EXAMPLE +SYSTEDG/prod1/datafile/example.265.885576627
USERS +SYSTEDG/prod1/datafile/users.259.885576451
UNDOTBS1 +SYSTEDG/prod1/datafile/undotbs1.258.885576451
SYSAUX +SYSTEDG/prod1/datafile/sysaux.257.885576451
SYSTEM +SYSTEDG/prod1/datafile/system.256.885576449
CHEN +SYSTEDG/prod1/datafile/chen.269.885721811
6 rows selected.

二增加数据文件

名字可以自动生成，也可以自己设定

SQL> alter tablespace chen add datafile '+SYSTEDG' size 5M;
SQL> alter tablespace chen add datafile '+SYSTEDG/chen03.dbf' size 5M;
SQL> alter tablespace chen add datafile '/home/oracle/chen04.dbf' size 5M;

三查看数据文件

SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
EXAMPLE +SYSTEDG/prod1/datafile/example.265.885576627
USERS +SYSTEDG/prod1/datafile/users.259.885576451
UNDOTBS1 +SYSTEDG/prod1/datafile/undotbs1.258.885576451
SYSAUX +SYSTEDG/prod1/datafile/sysaux.257.885576451
SYSTEM +SYSTEDG/prod1/datafile/system.256.885576449
CHEN +SYSTEDG/prod1/datafile/chen.269.885721811
CHEN +SYSTEDG/prod1/datafile/chen.270.885722527
CHEN +SYSTEDG/chen03.dbf
CHEN /home/oracle/chen04.dbf
9 rows selected.

四创建用户，创建表

( 测试一下数据是否平均分配在不同的数据文件中)

SQL> create user chen identified by chen default tablespace chen;
SQL> grant dba,connect,resource to chen;
SQL>
select a.tablespace_name,
a.file_name,
a.bytes / 1024 / 1024 || 'M' as z_s,
b.bytes / 1024 / 1024 || 'M' as fr_z
from dba_data_files a, dba_free_space b
where a.tablespace_name = 'CHEN'
and a.file_id = b.file_id(+);

五为什么要用左连接？

因为 当数据文件满了，剩余空间为零时，dba_free_space 将不会出现此数据文件；
TABLESPACE_NAME FILE_NAME Z_S FR_Z
1 CHEN /home/oracle/chen04.dbf 5M 4M
2 CHEN +SYSTEDG/prod1/datafile/chen.269.885721811 10M 9M
3 CHEN +SYSTEDG/prod1/datafile/chen.270.885722527 5M 4M
4 CHEN +SYSTEDG/chen03.dbf 5M 4M
SQL> conn chen/chen
SQL> create table t1 as select level as id,sysdate as d from dual connect by level<=100000;
SQL>
select a.tablespace_name,
a.file_name,
a.bytes / 1024 / 1024 || 'M' as z_s,
b.bytes / 1024 / 1024 || 'M' as fr_z
from dba_data_files a, dba_free_space b
where a.tablespace_name = 'CHEN'
and a.file_id = b.file_id(+);
TABLESPACE_NAME FILE_NAME Z_S FR_Z
1 CHEN /home/oracle/chen04.dbf 5M 3M
2 CHEN +SYSTEDG/prod1/datafile/chen.269.885721811 10M 8M
3 CHEN +SYSTEDG/prod1/datafile/chen.270.885722527 5M 3M
4 CHEN +SYSTEDG/chen03.dbf 5M 4M
数据几乎平均分配到不同的数据文件
转载自chenoracle

Oracle ASM表空间管理