在oracle中表空间是必不可少的。但是怎么查看表空间呢
简单的查看方式是:
SQL> select tablespace_name from dba_tablespaces;
想要查看表空间对应的物理文件是什么需要这样查看:
SQL> select file_name ,tablespace_name,bytes/1024/1024 "bytes MB" ,max_bytes/1024/1024 " max_bytes MB "from dba_data_files where tablespace_name='表空间名';
想要查看数据库表空间的使用情况药这样查看:
SQL> set pages 1000;
SQL> set lines 100;
SQL> col tablespace_name for a20;
SQL> col total_sizes for a10;
SQL> col free_sizes for a10;
SQL> col max_continue_sizes for a20;
SQL> select (tablespace_name) "tablespace_name",
sum(total_size) || 'M' as "total_sizes",
sum(total_free) || 'M' as "free_sizes",
sum(max_continue) || 'M' as "max_continue_sizes",
round(sum(total_free) / sum(total_size) * 100) as "free(%)"
from ((select tablespace_name,
(0) total_size,
round(sum(bytes) / 1024 / 1024, 2) total_free,
round(max(bytes) / 1024 / 1024, 2) max_continue
from dba_free_space
group by tablespace_name) union all
(select tablespace_name, round(sum(bytes) / 1024 / 1024, 2), 0, 0
from dba_data_files
group by tablespace_name))
group by tablespace_name
order by 5 asc;
想要增加表空间有两种方式:
一、将原表空间的文件resize更大(只有从小到大,尽量不要从大到小)
二、增加新的数据文件datafile
具体操作:
一、原表空间数据文件增大
SQL> alter database datafile '/u01/app/oracle/oradata/xxxx/xxx.dbf' resize 10g;
二、增加datafile
SQL> alter tablespace 表空间名 add datafile'/u01/app/oracle/oradata/xxxx/xxxx02.dbf' size 10g;
其实还有一种是表空间自动增长
SQL> alter database datafile '/u01/app/oracle/oradata/xxx/xxxx.dbf' autoextend on next 1g maxsize 20g;