select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
select * from dba_data_files t where t.tablespace_name = 'USERS'
/home/oracle/app/oradata/orcl/users01.dbf
|
-
select dbf.tablespace_name,
- dbf.totalspace "总量(M)",
- dbf.totalblocks as 总块数,
- dfs.freespace "剩余总量(M)",
- dfs.freeblocks "剩余块数",
- (dfs.freespace / dbf.totalspace) * 100 "空闲比例"
-
from (select t.tablespace_name,
- sum(t.bytes) / 1024 / 1024 totalspace,
- sum(t.blocks) totalblocks
-
from dba_data_files t
-
group by t.tablespace_name) dbf,
- (select tt.tablespace_name,
- sum(tt.bytes) / 1024 / 1024 freespace,
- sum(tt.blocks) freeblocks
-
from dba_free_space tt
-
group by tt.tablespace_name) dfs
-
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)
|
create tablespace shybt datafile '\home\oracle\app\oradata\orcl\shybt1.dbf' size 6000m;
create tablespace shybt01 datafile '\home\oracle\app\oradata\orcl\shybt01.dbf' size 5000m autoextend on next 5m