查询表空间的名字,
select name from v$datafile;
创建tablespace
create tablespace mytbs
datafile '/u01/oradata/wilson/mytbs01.dbf' size 100M
extent management local uniform size 128k;
create tablespace mytbs
datafile '/u01/oradata/wilson/mytbs01.dbf' size 100M
autoextend on next 10M maxsize 200M;
创建temporary tablespace
create temporary tablespace mytemp
tempfile '/u01/oradata/wilson/mttemp01.dbf' size 20M
extent management local uniform size 4M;
将mytemp改为default temporary tablespace
alter database default temporary tablespace mytemp;
可查database_properties的DEFAULT_TEMP_TABLESPACE来验证是否改变
让tablespace offline
alter tablespace mytbs offline;
让tablespace online
alter tablespace mytbs online;
让tablespace read only 或 read write
alter tablespace mytbs read only;
alter tablespace mytbs read write;
查看表空间使用情况
select a.tablespace_name, a.bytes bytes_used, b.largest, round(((a.bytes -b.bytes)/a.bytes)*100,2) percent_used from
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest from dba_free_space group by
tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes)/a.bytes) desc ;
对于表空间使用率较高的表空间可扩容
alter tablespace liuy add datafile
'/u01/oradata/wilson/liu02.dbf' size 50M
autoextend on next 10 maxsize 100M;
查表空间是否为自动增长
select file_name, tablespace_name, autoextensible from dba_data_files;