查看表空间、统计信息

SELECT a.tablespace_name, round( a.bytes/ (1024 * 1024*1024), 2 ) total, round( (b.bytes * 100) / a.bytes, 2 ) "% USED " FROM sys.sm$ts_avail a, sys.sm$ts_used b WHERE a.tablespace_name = b.tablespace_name and a.tablespace_name = 'BLSEA'

Select * FROM DBA_DATA_FILES where tablespace_name = 'BLSEA' ;

ALTER TABLESPACE "BLSEA" ADD DATAFILE '/u02/app/ora11g/users03.dbf' SIZE 32G;

统计信息

select * from dba_tables where table_name = 

select table_name, partition_name, last_analyzed from dba_tab_partitions where table_name

select last_analyzed,sample_size,num_rows,round(sample_size/num_rows*100,2) "%" from dba_tables t where table_name = upper('BUSI_LOGS')

exec dbms_stats.gather_table_stats(ownname => 'BLSEA',tabname => 'KAFKA_COMM',estimate_percent => 10,method_opt=> 'for all indexed columns', degree=>32);

分区表

exec dbms_stats.gather_table_stats(ownname=>'SEA',tabname=>'CJ_RDJDNL',partname=>'PART_201907', method_opt=> 'for all indexed columns', degree=>32, estimate_percent => 10);

##统计信息联合查询

select * from (
select '0' table_type, table_name, sample_size, num_rows,round( sample_size/num_rows, 6 ) radio, last_analyzed from dba_tables where 
       table_name = upper('cj_rdjdnl' ) 
       and owner = 'SEA' 
union
select '1', partition_name, sample_size, num_rows, round( sample_size/num_rows, 3 ) radio, last_analyzed 
       from dba_tab_partitions where table_name = upper('cj_rdjdnl' ) 
       and partition_name = 'PART_201909' 
       and table_owner = 'SEA' 
union       
select '2', subpartition_name, sample_size, num_rows, round( sample_size/num_rows, 3 ) radio, last_analyzed 
       from dba_tab_subpartitions where table_name = upper('cj_rdjdnl' ) 
       and partition_name like 'PART_201909%'
       and table_owner = 'SEA'  
       order by last_analyzed 
       )
       order by 1, 2

转载于:https://my.oschina.net/sqhua/blog/3067710