Oracle 表空间管理
33.12. Oracle 表空间
33.12.1. 查询空闲表空间
select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space; SQL> select file_name from dba_data_files;
FILE_NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/orcl/users01.dbf
/opt/oracle/oradata/orcl/undotbs01.dbf
/opt/oracle/oradata/orcl/sysaux01.dbf
/opt/oracle/oradata/orcl/system01.dbf
/opt/oracle/oradata/orcl/example01.dbf
/opt/oracle/oradata/orcl/neo.dbf
6 rows selected. 33.12.2. 创建表空间
create tablespace test
datafile '/opt/app/oracle/oradata/test.dbf' size 8M
autoextend on
next 5M
maxsize 10M; maxsize unlimited 是大小不受限制
create tablespace test
datafile '/opt/app/oracle/oradata/test.dbf' size 800M
autoextend on
next 50M
maxsize unlimited unform表示区的大小相同，默认为1M
create tablespace test
datafile '/opt/app/oracle/oradata/test.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
extent management local uniform; unform size 500K表示区的大小相同，为500K
create tablespace test
datafile '/opt/app/oracle/oradata/test.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
extent management local uniform size 500K; autoallocate 表示区的大小由随表的大小自动动态改变
create tablespace test
datafile '/opt/app/oracle/oradata/test.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
extent management local autoallocate; temporary 创建字典管理临时表空间
create tablespace test
datafile '/opt/app/oracle/oradata/test.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
temporary; 例 33.2. 创建表空间实例
SQL> create tablespace ts_b01_def datafile '/opt/oracle/oradata/orcl/ts_b01_def.dbf' size 100m autoextend on;
Tablespace created.
SQL> create tablespace ts_b01_idx datafile '/opt/oracle/oradata/orcl/ts_b01_idx.dbf' size 100m autoextend on;
Tablespace created. 33.12.2.1. 临时表空间
创建临时表空间，语句中的datafile都换为tempfile
create temporary tablespace test
tempfile '/opt/app/oracle/oradata/test.dbf' size 800M
autoextend on
next 50M
maxsize 1000M 33.12.3. 更改表空间属性
更改自动扩展属性
alter database datafile
'/opt/app/oracle/oradata/test.dbf',
'/opt/app/oracle/oradata/test01.dbf'
'/opt/app/oracle/oradata/test02.dbf'
autoextend off; 33.12.3.1. 修改表空间大小
先查询数据文件名称、大小和路径的信息，语句如下：
select tablespace_name,file_id,bytes,file_name from dba_data_files;
增加表空间，修改文件大小语句如下
alter database datafile '需要增加的数据文件路径，即上面查询出来的路径 ' resize 800M; 33.12.4. 删除表空间
drop tablespace "空间名" including contents and datafiles
drop tablespace test including contents and datafiles本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2017-05-31，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读