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.
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.
创建临时表空间,语句中的datafile都换为tempfile
create temporary tablespace test
tempfile '/opt/app/oracle/oradata/test.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
更改自动扩展属性
alter database datafile
'/opt/app/oracle/oradata/test.dbf',
'/opt/app/oracle/oradata/test01.dbf'
'/opt/app/oracle/oradata/test02.dbf'
autoextend off;
先查询数据文件名称、大小和路径的信息,语句如下:
select tablespace_name,file_id,bytes,file_name from dba_data_files;
增加表空间,修改文件大小语句如下
alter database datafile '需要增加的数据文件路径,即上面查询出来的路径 ' resize 800M;
drop tablespace "空间名" including contents and datafiles
drop tablespace test including contents and datafiles