表空间容量查看与增加undo表空间

(2013-02-06 07:52:15)

转载▼

分类：oracle

--查看数据文件存放位置

select * from dba_data_files;

--手动更改表空间容量

ALTER DATABASE DATAFILE '/u02/oradata/undotbs02.dbf'

RESIZE 10000M;

--查看表空间容量

SELECT a.tablespace_name,

          ROUND (a.total_size)"total_size(MB)",

          ROUND (a.total_size) - ROUND(b.free_size, 3) "used_size(MB)",

          ROUND (b.free_size, 3)"free_size(MB)",

          ROUND (b.free_size /total_size * 100, 2) || '%' free_rate

  FROM ( SELECT tablespace_name, SUM (bytes)/ 1024 / 1024 total_size

              FROM dba_data_files

          GROUP BY tablespace_name)a,

          ( SELECT tablespace_name, SUM(bytes) / 1024 / 1024 free_size

              FROM dba_free_space

          GROUP BY tablespace_name)b

  WHERE a.tablespace_name =b.tablespace_name(+);