–查询用户属于哪个表空间
select username,default_tablespace from dba_users order by username ;
–1、使用率、空闲率。
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(+);
–2、203前置机oracle数据库表空间满了, 增加表空间的方法:
– select * from Dba_Tablespaces
select tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
from sys.dba_data_files
order by tablespace_name ;
–3、变更数据文件大小,或者给表空间增加数据文件
alter database datafile ‘D:\APP\ORACLE_DATA_FILE\11.0.2\USERDATA\BYYKG_DATA.DBF’ resize 2048m;
alter tablespace BYYKG add datafile ‘D:\ORACLE\BZ_QIANZHIJI03.DBF’ size 30000m;