select  tabschema,
        tabname,
        tabspace,
        numrow,
        tabsize（大小为字节),
        CREATED,
        LAST_DDL_TIME,
        tabtype------（1表示普通表,2表示分区表）
        from
(
select  tab.OWNER   as   tabschema,
        tab.TABLE_NAME    tabname,
        tab.TABLESPACE_NAME tabspace,
        tab.NUM_ROWS   as    numrow,
        tab.NUM_ROWS * tab.AVG_ROW_LEN  as tabsize ,
        obj.CREATED  as CREATED,
        obj.LAST_DDL_TIME  as LAST_DDL_TIME,
        1  as  tabtype
       from  dba_tables tab,
      dba_objects  obj 
 where tab.partitioned='NO'
 and  tab.OWNER=obj.OWNER
and  tab.TABLE_NAME=obj.object_name)  
/*查询所有的分区表 */

创新互联是专业的彝良网站建设公司，彝良接单;提供成都网站建设、成都网站制作,网页设计,网站设计,建网站,PHP网站建设等专业做网站服务;采用PHP框架,可快速的进行彝良网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,专业的做网站团队,希望更多企业前来合作!

union
select 
     partaowner,
     partatable_name,
     tablespace_name,
     partb.rownumber,
     partb.tabsize,
     parta.created,
     parta.last_ddl_time,
     tabtype
from
(select
       parttabname.owner  as   partaowner,
       parttabname.table_name as partatable_name,
       tabname.created as created,
       tabname.last_ddl_time as last_ddl_time
       from
       DBA_PART_TABLES parttabname,
       (select  owner,
             object_name,
             created,
             last_ddl_time,
             object_id,
             max(object_id) 
             over(partition by owner,object_name order by owner)  from dba_objects
             where subobject_name is  null  and  object_type='TABLE'  ) tabname
             where     parttabname.owner=tabname.owner
             and      parttabname.table_name=tabname.object_name   )  parta,    
      
 (select table_owner,
        table_name, 
        tablespace_name,
        sum(num_rows) as  rownumber,
        sum(num_rows*avg_row_len) as tabsize,
        2 as  tabtype
from   dba_tab_partitions 
group by 
table_owner,
table_name, 
tablespace_name  )  partb
where   partaowner= partb.table_owner
and     partatable_name= partb.table_name
/*查询所分区表 */

           

文章名称：oracle查看所有表信息
链接URL：http://scyanting.com/article/jdhhjd.html