oracle-admin-查看表、索引、分区表用空间大小

oracle下查看表、索引、分区表用空间大小

1.查看表和索引占用的空间

set line 300
col segment_name for a30
  SELECT s.segment_name,
         s.segment_type,
         s.tablespace_name,
         ROUND (SUM (s.bytes) / 1024 / 1024) Mbytes
    FROM dba_segments s, dba_indexes i
   WHERE     (s.segment_name = i.index_name OR s.segment_name = i.table_name)
         AND i.table_name IN ('tableB', 'tablA')
GROUP BY s.segment_name, s.segment_type, s.tablespace_name
ORDER BY 2,1;

2.查看用户表、索引、分区表占用空间

select owner,segment_type,segment_name, sum(bytes)/1024/1024/1024 siez_Gb from dba_segments group by owner,segment_type,segment_name order by 4 desc;

3.表占用空间

select segment_name, sum(bytes)/1024/1024 Mbytese from dba_segments where segment_type='TABLE' group by segment_name;

4.索引占用空间

select segment_name ,sum(bytes)/1024/1024 from dba_segments where segment_type ='INDEX' group by segment_name;

5.分区表TABLE PARTITION占用空间

select segment_name,sum(bytes)/1024/1024 Mbytes from dba_segments where segment_type='TABLE PARTITION' group by segment_name;

6.查看表的创建时间：

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select created,last_ddl_time from dba_objects where owner='NINVOICE' and object_name='TMP_VER';

7.查看表的表的大小：

select sum(bytes)/1024/1024||'M' from dba_segments where owner='NINVOICE' and segment_name='TMP_VER';

8.查看某个时间创建的对象

SQL> col object_name for a20;
SQL>  select object_name,created,last_ddl_time from dba_objects where owner='NINVOICE' and created>=to_date('2013-05-24 00:28:19','yyyy-mm-dd hh24:mi:ss')

OBJECT_NAME          CREATED             LAST_DDL_TIME
-------------------- ------------------- -------------------
TMP_VER              2013-05-24 10:28:19 2013-05-24 10:28:19
TMP_VER2             2013-05-24 11:36:59 2013-05-24 11:36:59
TMP_VER3             2013-05-24 11:45:17 2013-05-24 11:45:17
TMP_VER5             2013-05-24 11:45:47 2013-05-24 11:45:47

9.查看表大小排序（包括分区表，但不统计索引）

  SELECT owner,
         segment_name,
       -- partition_name,
         segment_type,
         --tablespace_name,
         round(SUM (bytes / 1024 / 1024 / 1024)) size_g
    FROM dba_segments
   WHERE owner = 'MESRPT' AND segment_type  like  'TABLE%' --and segment_name='tableA'
GROUP BY owner,
         segment_name
       -- ,partition_name
         ,segment_type
        -- ,tablespace_name
         order by size_g desc;

10.按表行数排序（依赖数据统计信息收集是否最新）

select table_name,num_rows,last_analyzed from user_tables where num_rows>200000 and last_analyzed>sysdate-200 order by 2 desc;


--
select owner,table_name,tablespace_name,status,num_rows,last_analyzed,partitioned,read_only,blocks,blocks*8/1024/1024 G from dba_tables where table_name='tableA';

select owner,segment_name,partition_name,segment_type,tablespace_name,blocks,bytes,bytes/1024/1024/1024 G from dba_segments where segment_name='tableA';