数据文件表:dba_data_files
表空间表:dba_tablespaces
创经表空间:
create tablespace tableSpaceName datafile '< data_file_name >' size 1000M;
重新定义表空间数据文件大小:
alter database datafile '' resize 5000M;
改变表空间大小:
alter tablespace tableSpaceName add datafile '< data_file_name >' resize 1024M;
查询表空间与数据文件对应关系:
select tablespace_name, bytes, file_name from dba_data_files;
查询用户缺省表空间:
select username, default_tablespace from dba_users;
查询表与存储该表的表空间:
select table_name, tablespace_name from user_tables;
修改用户缺省表空间:
alter user username default tablespace tablespace_name;
将数据从一个表空间移动到另一个表空间:
alter table table_name move tablespace tablespace_name;
检查是否有业务用户的默认表空间设置在了system上。
select * from dba_users t where t.default_tablespace='SYSTEM';
检查是否有业务用户的表被创建在了system上:
select * from dba_tables w where w.owner not in ('SYSTEM', 'SYS', 'MGMT_VIEW','OUTLN') and w.tablespace_name='SYSTEM';
查询表空间的数据文件大小
select tablespace_name,(bytes/1024/1024) M from dba_data_files;
查询数据文件已使用空间
select file_name, sum(e.bytes)/1024/1024 as MB from dba_extents e join dba_data_files f on e.file_id=f.file_id group by file_name;
表空间使用率
select a.tablespace_name,
total / 1024 / 1024 / 1024,
free / 1024 / 1024 / 1024,
(total - free) / 1024 / 1024 / 1024,
round((total - free) / total, 4) * 100
from (select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes) total
from dba_data_files
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;
系统表空间占用TOP9
SELECT *
FROM (SELECT BYTES, segment_name, segment_type, owner
FROM dba_segments
WHERE tablespace_name = 'SYSTEM'
ORDER BY BYTES DESC)
WHERE ROWNUM < 10;
system空间中具体是什么对象占用较高:
select sum(bytes) as bt, s.owner, s.segment_name from dba_segments s where s.tablespace_name='SYSTEM'
group by s.owner, s.segment_name
order by sum(bytes) desc;
将审计功能关闭:
alter system set audit_trail='NONE' scope=spfile;