Oracle的DBA管理常用sql

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_space
from dba_data_files
order 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格式），执行即可。