oracle 查询表里信息_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

/*查询所分区表 */