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