oracle tablesapce

查询表空间的名字，
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;