declare
*
ERROR at line 1:
ORA-00081: address range [0x60000000000D5E80, 0x60000000000D5E84) is not
readable
ORA-00600: internal error code, arguments: [4832], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 6518
ORA-06512: at "SYS.DBMS_STATS", line 7254
ORA-06512: at line 30
Note: dababase version: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit
I have no idea at all about this. Later, the requester suggested to unlock the statistics before running the script. After unlocking the schema statistics, the script went through. I don't know lock and unlock schema or table statistics before, so I decided to explore this feature a little bit.
First of all, in the Oracle documentation:
Statistics for a table or schema can be locked. Once statistics are locked, no modifications can be made to those statistics until the statistics have been unlocked. These locking procedures are useful in a static environment in which you want to guarantee that the statistics never change.
The DBMS_STATS package provides two procedures for locking and two procedures for unlocking statistics:
LOCK_SCHEMA_STATS
LOCK_TABLE_STATS
UNLOCK_SCHEMA_STATS
UNLOCK_TABLE_STATS
From another Oracle do:
The LOCK_* procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected).When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
I build up a test case trying to simulate the situaltion. However, I got more descriptive error message instead of the one presented above: (my test db is 32-bit 10g)
denis@TEST10G> @lock_stats2
denis@TEST10G> set echo on
denis@TEST10G> rem lock_stats2.sql
denis@TEST10G> rem -- simulate copy stats from one partition to other partitions that have zero rows
denis@TEST10G> rem
denis@TEST10G>
denis@TEST10G> drop table t purge;
Table dropped.
denis@TEST10G> drop table my_stat_tab purge;
Table dropped.
denis@TEST10G>
denis@TEST10G> create table t nologging
2 partition by range(id)
3 (
4 partition p1 values less than (100),
5 partition p2 values less than (200),
6 partition p3 values less than (300),
7 partition p4 values less than (400),
8 partition p5 values less than (500),
9 partition p_maxval values less than (MAXVALUE)
10 )
11 as
12 select
13 rownum id, a.*
14 from dba_objects a
15 where rownum < 1000;
Table created.
denis@TEST10G>
denis@TEST10G>
denis@TEST10G> Prompt >>> only analyze one partition
>>> only analyze one partition
denis@TEST10G> exec dbms_stats.gather_table_stats(user, 'T', 'P1');
PL/SQL procedure successfully completed.
denis@TEST10G>
denis@TEST10G> select partition_name, num_rows from user_tab_partitions where table_name='T';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P1 99
P2
P3
P4
P5
P_MAXVAL
6 rows selected.
denis@TEST10G>
denis@TEST10G>
denis@TEST10G>
denis@TEST10G> Prompt >>> in the case of table stats is locked
>>> in the case of table stats is locked
denis@TEST10G> exec dbms_stats.lock_table_stats(user, 'T');
PL/SQL procedure successfully completed.
denis@TEST10G>
denis@TEST10G> exec dbms_stats.create_stat_table(user,'my_stat_tab')
PL/SQL procedure successfully completed.
denis@TEST10G>
denis@TEST10G>
denis@TEST10G> begin
2 for part_rec in ( select partition_name from user_tab_partitions where table_name= 'T'
3 and nvl(num_rows,0) <= 75) loop
4 begin
5 dbms_stats.export_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
6 execute immediate 'update my_stat_tab set c2='||''''||part_rec.partition_name||''''|| ' where c1 = ''T''';
7 dbms_stats.import_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
8 end;
9 end loop;
10 end;
11 /
begin
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 1607
ORA-06512: at "SYS.DBMS_STATS", line 2117
ORA-06512: at "SYS.DBMS_STATS", line 7249
ORA-06512: at line 7
denis@TEST10G> select partition_name, num_rows from user_tab_partitions where table_name='T';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P1 99
P2
P3
P4
P5
P_MAXVAL
6 rows selected.
denis@TEST10G>
denis@TEST10G> Prompt >>> in the case of table stats is unlocked
>>> in the case of table stats is unlocked
denis@TEST10G> exec dbms_stats.unlock_table_stats(user, 'T');
PL/SQL procedure successfully completed.
denis@TEST10G>
denis@TEST10G> begin
2 for part_rec in ( select partition_name from user_tab_partitions where table_name= 'T'
3 and nvl(num_rows,0) <= 75) loop
4 begin
5 dbms_stats.export_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
6 execute immediate 'update my_stat_tab set c2='||''''||part_rec.partition_name||''''|| ' where c1 = ''T''';
7 dbms_stats.import_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
8 end;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
denis@TEST10G> select partition_name, num_rows from user_tab_partitions where table_name='T';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P1 99
P2 99
P3 99
P4 99
P5 99
P_MAXVAL 99
6 rows selected.
denis@TEST10G> spool off
The following test shows that we can query the stattype_locked column of user_tab_statistics to know if the table stats is locked or not:
denis@TEST10G> exec dbms_stats.lock_table_stats(user, 'T');
PL/SQL procedure successfully completed.
denis@TEST10G> select table_name,partition_name, stattype_locked from user_tab_statistics;
TABLE_NAME PARTITION_NAME STATT
------------------------------ ------------------------------ -----
T ALL
MY_STAT_TAB
T P_MAXVAL ALL
T P5 ALL
T P4 ALL
T P3 ALL
T P2 ALL
T P1 ALL
8 rows selected.
denis@TEST10G> exec dbms_stats.unlock_table_stats(user, 'T');
PL/SQL procedure successfully completed.
denis@TEST10G> select table_name,partition_name, stattype_locked from user_tab_statistics;
TABLE_NAME PARTITION_NAME STATT
------------------------------ ------------------------------ -----
T
MY_STAT_TAB
T P_MAXVAL
T P5
T P4
T P3
T P2
T P1
8 rows selected.
No comments:
Post a Comment