Shulou(Shulou.com)06/01 Report--

Select tabschema

Tabname

Tabspace

Numrow

Tabsize (size in bytes)

CREATED

LAST_DDL_TIME

Tabtype- (1 for normal table, 2 for partitioned table)

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)

/ * query all partition tables * /

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

/ * query the partitioned table * /

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.