create tablespace asom nologging datafile
'D:\oracle\product\10.2.0\oradata\orcl\asom01.dbf' size 1000M autoextend on next 100M maxsize 20G,
'D:\oracle\product\10.2.0\oradata\orcl\asom02.dbf' size 1000M autoextend on next 100M maxsize 20G,
'D:\oracle\product\10.2.0\oradata\orcl\asom03.dbf' size 1000M autoextend on next 100M maxsize 20G
-- 新增数据文件
alter tablespace asom add datafile
'D:\oracle\product\10.2.0\oradata\orcl\asom04.dbf' size 1000M autoextend on next 100M maxsize 20G,
'D:\oracle\product\10.2.0\oradata\orcl\asom05.dbf' size 1000M autoextend on next 100M maxsize 20G,
'D:\oracle\product\10.2.0\oradata\orcl\asom06.dbf' size 1000M autoextend on next 100M maxsize 20G,
'D:\oracle\product\10.2.0\oradata\orcl\asom07.dbf' size 1000M autoextend on next 100M maxsize 20G,
'D:\oracle\product\10.2.0\oradata\orcl\asom08.dbf' size 1000M autoextend on next 100M maxsize 20G
-- 查询表空间对应的数据文件
select tablespace_name,file_id,bytes,file_name from dba_data_files
-- 查询表空间利用率
SELECT a.tablespace_name as tablespace_name,
to_char(b.total/1024/1024,999999.99) as Total,
to_char((b.total-a.free)/1024/1024,999999.99) as Used,
to_char(a.free/1024/1024,999999.99) as Free,
to_char(round((total-free)/total,4)*100,999.99) as Used_Rate
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
ORDER BY a.tablespace_name;
--查询各表的表空间使用情况
select t.table_name,t.tablespace_name,t.num_rows,a.bytes/1024/1024 "物理空间(M)",
round(t.num_rows*t.avg_row_len/1024/1024,2) "实际使用(M)",
a.bytes/1024/1024-round(t.num_rows*t.avg_row_len/1024/1024,2) "差值(M)"
from all_tables t,user_segments a
where t.owner='ASOM' /*and t.table_name like 'MTR%'*/ and t.num_rows>200*10000
and t.table_name=a.segment_name and a.segment_type = 'TABLE'
order by t.table_name;
--释放表空间
alter table TABLE_NAME enable row movement;
alter table TABLE_NAME shrink space;
alter table TABLE_NAME shrink space cascade; 索引也能缩小,效率很低,慎重。