1、查看数据库表的占用磁盘情况

select segment_name, sum(bytes) / 1024 / 1024 as Mbytese  from user_segments where segment_type = 'TABLE' group by segment_name order by Mbytese desc;

2、查看数据库表空间

Select a.Tablespace_Name,       a.Size_Set,       b.Size_Use,       Decode(Sign(Size_Use), 0, 0, Round(b.Size_Use / a.Size_Set, 4) * 100) || '%' As Pre  From (Select Nvl(Tablespace_Name,'合计') Tablespace_Name,               Sum(Round(Bytes / 1024 / 1024, 0)) As Size_Set          From Dba_Data_Files         Group By Cube(TableSpace_Name)) a,       (Select Nvl(Tablespace_Name,'合计') Tablespace_Name,               Round(Sum(Bytes) / 1024 / 1024, 0) As Size_Use          From Dba_Segments         Group By Cube(TableSpace_Name)) b Where a.Tablespace_Name = b.Tablespace_Name Order By Pre desc;

3、清空回滚段数据

PURGE RECYCLEBIN;

4、移动某个表到特定的表空间

alter table TY1_JOIN move tablespace TBS_HZYL_ETL;

 

 

---

 

增加某个数据文件的大小

select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_filesorder by tablespace_name;alter database datafile '/u02/oradata/TBS_ZZ_YOUXIAN.ora' resize 10240m;alter tablespace game add datafile '/oracle/oradata/db/game02.dbf' size 1000m; alter database datafile '/oracle/oradata/db/game02.dbf'  autoextend on next 1m maxsize 10m;

创建表空间

create tablespace hyldims  datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\hyldims.dbf' size 1024m  autoextend on next 1m maxsize UNLIMITED;

 

---

 

 

批量移动表和索引到另外一个表空间

1)表

执行以下sql生成批量的sql语句，控制台结果输出为sql语句

select 'alter table '||table_name||' MOVE TABLESPACE ZJMZ;' from user_tables;

2)复制上一步生成的输出（sql格式），执行即可。

将索引批量移动到另一个表空间

1)执行以下sql生成批量的sql语句，

select 'ALTER INDEX ' ||index_name || ' REBUILD TABLESPACE ZJMZ;' from user_indexes;

2)复制上一步生成的输出（sql格式），执行即可。