oracle 表空间管理

1、1：查看表空间：

select a.tablespace_name,

       a.bytes / 1024 / 1024 "Sum MB",

       (a.bytes - b.bytes) / 1024 / 1024 "used MB",

       b.bytes / 1024 / 1024 "free MB",

       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"

  from (select tablespace_name, sum(bytes) bytes

          from dba_data_files

         group by tablespace_name) a,

       (select tablespace_name, sum(bytes) bytes, max(bytes) largest

          from dba_free_space

         group by tablespace_name) b

 where a.tablespace_name = b.tablespace_name

 order by ((a.bytes - b.bytes) / a.bytes) desc;

2、2：查看表空间下的表：

select   *  from   dba_tables  where   tablespace_name = 'XXXXX'

and   table_name  like  '%2014%'

3、3：查看表空间下表的使用情况

select segment_name,bytes/1024/1024  from dba_segments where tablespace_name

='SYSTEM' and segment_type='TABLE'  order  by   bytes   desc 

4、4：删除表：

  select 'drop  table  ' || table_name  || ' cascade constraints   purge ;  '  from   dba_tables  where   tablespace_name = 'XXXXX'

and   table_name  like  '%2014%'

5、5：收缩表空间

5.1：查看表在哪个数据文件：

 select file_name,file_id from dba_data_files where file_id in (

select distinct file_id from dba_extents where segment_name='t_table_name')

5.2：

select   *  from   dba_data_files;

SELECT MAX(block_id)  ,MAX(block_id)  *8192/1024/1024/1024 as  "G"  , MAX(block_id)  *8192/1024/1024 as  "M" 

  FROM dba_extents

 WHERE tablespace_name = 'xxxxxx

alter  database datafile  '/opt/oracle/oradata/xxx/xxxxx.dbf'   RESIZE 508200M;

 ---- table 

 alter table t_table_nameenable row movement;

 select blocks from user_segments where segment_name=upper('t_table_name');

 11264

 alter table t_table_nameshrink space;