20120813 i2000上面一个表空间满了，被索引占了

问题描述：
本地员工反应一个表空间满了，查了一下，被索引占了，由于这个是老的I2000，几乎不用，而且看两个表基本都是存报警信息的。如果是现网情况，还是需要确定下再进行truncate的

 

----------------------------------------------------------------------

----查询表空间包含的对象  以前那个sql查询不到索引信息
SELECT t.owner, t.segment_name, SUM(bytes) / 1024 / 1024 size_MB
  From dba_segments t
 WHERE t.tablespace_name = 'PERFDB_IDX'
 GROUP BY t.owner, t.segment_name
 having SUM(bytes) >1024*1024*1024
 ORDER BY SUM(bytes) desc;

--查询对象信息
select * from dba_segments a where a.segment_name in ('P_TBL_ORACLE_FILE','W_TBL_ORACLE_FILE','P_TBL_IBM_DISK');
--查询对象所在的表信息
select a.owner,a.index_name,a.table_name from all_indexes a where a.index_name in ('P_TBL_ORACLE_FILE','W_TBL_ORACLE_FILE','P_TBL_IBM_DISK');

select * from PERFDB.TBL_IBM_DISK where rownum < 2;
select * from PERFDB.TBL_ORACLE_FILE where rownum < 2;
select * from PERFDB.TBL_ORACLE_FILE where rownum < 2;

--查看索引是否是分区索引，如果表里面的索引都是分区索引的话，可以truncate老的分区，索引不受影响
SELECT a.owner,a.segment_name,a.partition_name,b.table_name,b.status
  FROM dba_segments a,
       Dba_Indexes   b
WHERE a.owner = b.owner
   AND a.segment_name = b.index_name
   AND b.table_name in ('TBL_IBM_DISK','TBL_ORACLE_FILE')
ORDER BY a.segment_name,a.partition_name;

select * from PERFDB.TBL_IBM_DISK;
select * from PERFDB.TBL_ORACLE_FILE;
--truncate 表，然后分析表吧
--truncate table PERFDB.TBL_IBM_DISK;
--truncate table PERFDB.TBL_ORACLE_FILE;

--查询索引状态，以防失效
SELECT a.owner,a.segment_name,a.partition_name,b.table_name,b.status
  FROM dba_segments a,
       Dba_Indexes   b
WHERE a.owner = b.owner
   AND a.segment_name = b.index_name
   AND b.table_name in ('TBL_IBM_DISK','TBL_ORACLE_FILE')
ORDER BY a.segment_name,a.partition_name;

--查看表空间的使用状态
select df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Total Size(MB)",
       sum(fs.bytes) / (1024 * 1024) "Free Size(MB)",
       round(sum(fs.bytes) * 100 / df.bytes) "% Free",
       round((df.bytes - sum(fs.bytes)) * 100 / df.bytes) "% Used"
  from dba_free_space fs,
       (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) df
 where fs.tablespace_name = df.tablespace_name
 group by df.tablespace_name, df.bytes
 order by df.bytes desc;