alter table t1 disable table lock;

可以禁止表锁定，也就是说可以防止session 对表进行DDL ，即禁止session 获得表lock

alter table t1 enable table lock;

session 可以在表上面获得锁

测试：

我先前在winxp + 9.2.0.1上测试没有任何问题

代码:


SQL> create table test(no int);


表已创建。



SQL> alter table test disable table lock;


表已更改。



SQL> insert into test select rownum from tabs;


已创建13行。



SQL> commit;


提交完成。



SQL> rename test to test_mid ;
rename test to test_mid
*
ERROR 位于第 1 行:
ORA-00069: 无法获得锁定 -- TEST禁用了表锁定





SQL> alter table test enable table lock;


表已更改。



SQL> rename test to test_mid;


表已重命名。



SQL>

生产库就不能随便测试了，我在测试库aix 5.2+ Oracle 9.2.0.6上测试的时候，缺遇到了这样的问题

代码:

[oracle@res2] /oracle> sqlplus tomsrep



SQL*Plus: Release 9.2.0.6.0 - Production on Wed Jun 21 13:57:11 2006



Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Enter password: 


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.6.0 - Production



SQL> create table test_lock(no int);


Table created.


SQL> alter table test_lock disable table lock;


Table altered.


SQL> insert into test_lock select rownum from tabs;


7 rows created.


SQL> commit;


Commit complete.


SQL> rename test_lock to test_lock_mid;
rename test_lock to test_lock_mid
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for TEST_LOCK





SQL> alter table test_lock enable table lock;
alter table test_lock enable table lock
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error



SQL> 


在alert中也报告了ora-00600的错误信息如下：

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [kcbgcur_9], [4219439], [1], [4294967250], [524288], [], [], []
ORA-00054: resource busy and acquire with 

select b.PGA_USED_MEM PGA_USED_MEM, b.PGA_ALLOC_MEM PGA_ALLOC_MEM,
b.PGA_FREEABLE_MEM PGA_FREEABLE_MEM,b.PGA_MAX_MEM PGA_MAX_MEM
from v$session a, v$process b
where a.PADDR=b.ADDR and a.sid ='&sid'

select a.sid, b.name, sum(value)/1024/1024
from v$sesstat a, v$statname b
where a.statistic#=b.statistic#
and b.name like '%pga%'
group by sid,name

declare
vsql varchar2(200);
cursor c1 is
select 'drop table '||table_name||' cascade constraints' v_name
from user_tables where tablespace_name='AFC_NEW';

BEGIN
for i in c1 loop
vsql:=i.v_name;
execute immediate vsql;
end loop;
end;
/

SQL> exec dbms_stats.GATHER_SCHEMA_STATS(ownname=>'sxitismg',method_opt=>'for all indexed columns',options=>'GATHER',cascade=>TRUE);

SQL>execute dbms_stats.gather_schema_stats(ownname => ‘scott’,estimate_percent => 20,method_opt=>‘for all indexed columns size 10’, cascade=>true);

exec dbms_stats.GATHER_SCHEMA_STATS(ownname=>'scott',method_opt=>'for all indexed columns',options=>'GATHER',cascade=>TRUE);

GATHER_SCHEMA_STATS 下有个参数： cascade

,设置此值为true 时，oracle才对index进行分析；

dbms_stats.gather_table_stats

dbms_stats.gather_index_stats

如:

GATHER_SCHEMA_STATS 下有个参数： cascade

,设置此值为true 时，oracle才对index进行分析；

最好还是用dbms_stats.gather_index_stats来进行分析，不要一次对一个用户下的对象进行分析，不好。写个脚本就行了

SQL> analyze table t3 compute statistics

for table

for all indexes

for all indexed columns size 254;

Automating histogram sampling with dbms_stats
May 14, 2003
Don Burleson



One exciting feature of dbms_stats is the ability to automatically look for columns that should have histograms, and create the histograms. Multi-bucket histograms add a huge parsing overhead to SQL statements, and histograms should ONLY be used when the SQL will choose a different execution plan based upon the column value.

To aid in intelligent histogram generation, Oracle uses the method_opt parameter of dbms_stats. There are also important new options within the method_opt clause, namely skewonly, repeat and auto.

method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'
In practice, there is a specific order to use the different options of dbms_stats. See this article for details. Let’s take a close look at each method option.



The method_opt=’SKEWONLY’ dbms_stats Option

The first is the “skewonly” option which very time-intensive because it examines the distribution of values for every column within every index. If dbms_stats discovers an index whose columns are unevenly distributed, it will create histograms for that index to aid the cost-based SQL optimizer in making a decision about index vs. full-table scan access. For example, if an index has one column that is in 50% of the rows, a full-table scan is faster than and index scan to retrieve these rows.

Histograms are also used with SQL that has bind variables and SQL with cursor_sharing enabled. In these cases, the CBO determines if the column value could affect the execution plan, and if so, replaced the bind variable with a literal and performs a hard parse.



--*************************************************************
-- SKEWONLY option – Detailed analysis
--
-- Use this method for a first-time analysis for skewed indexes
-- This runs a long time because all indexes are examined
--*************************************************************

begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
degree => 7
);
end;
/


The method_opt=’REPEAT’ dbms_stats Option

Following the one-time detailed analysis, the re-analyze task will be less resource intensive with the REPEAT option. Using the repeat option will only re-analyze indexes with existing histograms, and will not search for other histograms opportunities. This is the way that you will re-analyze you statistics on a regular basis.



--**************************************************************
-- REPEAT OPTION - Only re-analyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only re-analyze histograms for
-- indexes that have histograms.
--**************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 7
);
end;
/


The method_opt=’AUTO’ dbms_stats Option

The auto option is used when monitoring is implemented and creates histograms based upon data distribution and the manner in which the column is accessed by the application (e.g. the workload on the column as determined by monitoring, especially foreign keys to determine the cardinality of table join result sets). Using method_opt=>’auto’ is similar to using the gather auto in the option parameter of dbms_stats.

begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7
);
end;
/

Remember, analyzing for histograms is time-consuming, and histograms are used under two conditions:
Table join order – The CBO must know the size of the intermediate result sets (cardinality) to properly determine the correct join order the multi-table joins.

Table access method – The CBO needs to know about columns in SQL where clauses, where the column value is skewed such that a full-table scan might be faster than an index range scan. Oracle uses this skew information in conjunction with the clustering_factor columns of the dba_indexes view.

Hence, this is the proper order for using the dbms_stats package to locate proper columns for histograms:

1. Skewonly option - You want to use skewonly to do histograms for skewed columns, for cases where the value will make a difference between a full-table scan and an index scan.

2. Monitor - Next, turn-on monitoring. Issue an “alter table xx monitoring” and “alter index yyy monitoring” command for all segments in your schema. This will monitor workload against

3. Auto option - Once monitoring is in-place, you need to re-analyze with the "auto" option to create histograms for join columns within tables. This is critical for the CBO to determine the proper join order for finding the driving table in multi-table joins.

4. Repeat option - Finally, use the "repeat" option to re-analyze only the existing histograms.

Periodically you will want to re-run the skewonly and auto option to identify any new columns that require histograms. Once located, the repeat option will ensure that they are refreshed with current values.


--------------------------------------------------------------------------------

If you like DBA internal tricks, check-out my new book Creating a Self-tuning Oracle Database by Rampant TechPress. This book is now available at this link:

http://www.rampant-books.com/book_2...racle9i_sga.htm

Regards,