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
/*查询所分区表 */