Tuesday, October 8, 2013

mix of locked and unlocked table and partition statistics can make me grumpy

Well almost anything can make me grumpy ... there may be good specific reasons in your shop/application why statistics on some tables ( yes I guess even some partitions within tables ) may be locked and others not ... getting explanations about the how and why and history ( if any of that is known or understood ) ... that can get complicated also.

Here's a pretty basic query to show which TABLES/PARTITIONS are locked or NOT ... by schema ... and skips over anything in recycle.

select * from
(
select owner, table_name, partition_name, stattype_locked, 'LOCKED' AS status
 from dba_tab_statistics
 where stattype_locked is not null and owner like 'SCHEMA_OWNER%' and table_name not like 'BIN%'
union all
select owner, table_name, partition_name, stattype_locked, 'NOT LOCKED' AS status
 from dba_tab_statistics
 where stattype_locked is null and owner like 'SCHEMA_OWNER%' and table_name not like 'BIN%'
)
order by 1, 2, 3
 

No comments:

Post a Comment