oracle创建表空间语句

-- 创建表空间
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;   索引也能缩小，效率很低，慎重。