oracle查看所有表信息

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)  
/*查詢所有的分區表 */

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
/*查詢所分區表 */