表名、DBMS_STATS.GATHER_TABLE_STATS

显示全部楼层 · 2013-8-26 10:32:46

FOR STALE IN STALE_TABLE LOOP
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => STALE.OWNER,

TABNAME => STALE.SEGMENT_NAME,

ESTIMATE_PERCENT => STALE.PERCENT,

METHOD_OPT => 'for all columns size repeat', --坑

DEGREE => 8,

GRANULARITY => 'ALL',

CASCADE => TRUE);
END LOOP;
TABNAME => STALE.SEGMENT_NAME,
STALE.SEGMENT_NAME 是从dba_segments里面查出来的一个segment_name。
由于是循环的索引使用的游标。
想问：如果segment_name由大小写组成。该怎么改TABNAME使得不会报错。
ORA-20000: Unable to analyze TABLE "SMSPORTAL"."TP_PHONENUMGRPMEM_T_TRANS", insufficient privileges or does not exist
ORA-06512: 在 "SYS.DBMS_STATS", line 23818
ORA-06512: 在 "SYS.DBMS_STATS", line 23880
ORA-06512: 在 "SYS.COLLECT_STATISTICS", line 34
ORA-06512: 在 line 1
SMSPORTAL"."TP_PHONENUMGRPMEM_T_TRANS 在dba_segments里面存的是SMSPORTAL"."TP_PHONENUMGRPMEM_T_trans"

千问 · 2013-8-26 10:32:46

TABNAME => 用"字面量"

千问 · 2013-8-26 10:32:46

本帖最后由〇〇于 2014-1-23 12:51 编辑
SQL> create table "Bad Name"(a int);
表已创建。
已用时间:00: 00: 02.12
SQL> select segment_name from user_segments where upper(segment_name) like 'BAD%';
SEGMENT_NAME
---------------------------------------------------------------------------------
BADGES
BADGES_EXCHANGE
已用时间:00: 00: 00.08
SQL> insert into "Bad Name" values(1);
已创建 1 行。
已用时间:00: 00: 00.01
SQL> commit;
提交完成。
已用时间:00: 00: 00.01
--11g段延时创建，所以插入1行
SQL> select segment_name from user_segments where upper(segment_name) like 'BAD%';
SEGMENT_NAME
---------------------------------------------------------------------------------
BADGES
BADGES_EXCHANGE
Bad Name
已用时间:00: 00: 00.05
SQL> exec dbms_STATS.GATHER_TABLE_STATS('SCOTT','BADGES');
PL/SQL 过程已成功完成。
已用时间:00: 00: 00.21
SQL> exec dbms_STATS.GATHER_TABLE_STATS('SCOTT','Bad Name');
BEGIN dbms_STATS.GATHER_TABLE_STATS('SCOTT','Bad Name'); END;
*
第 1 行出现错误:
ORA-20001: BAD NAME is an invalid identifier
ORA-06512: 在 "SYS.DBMS_STATS", line 23154
ORA-06512: 在 "SYS.DBMS_STATS", line 23205
ORA-06512: 在 line 1

已用时间:00: 00: 00.00
SQL> exec dbms_STATS.GATHER_TABLE_STATS('SCOTT','"Bad Name"');
PL/SQL 过程已成功完成。
已用时间:00: 00: 00.03

千问 · 2013-8-26 10:32:46

SQL> create table "BAD NAME2" as select * from "Bad Name";
表已创建。
已用时间:00: 00: 00.04
SQL> begin
2FOR STALE IN (select OWNER,SEGMENT_NAME,1 PERCENT from dba_segments where upper(segment_name) like 'BAD%') LOOP
3DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => STALE.OWNER,
4
TABNAME => case when STALE.SEGMENT_NAME=upper(replace(STALE.SEGMENT_NAME,' '))then STALE.SEGMENT_NAME else '"'||STALE.SEGMENT_NAME||'"' end,
5
ESTIMATE_PERCENT => STALE.PERCENT,
6
METHOD_OPT => 'for all columns size repeat', --坑
7
DEGREE => 1,
8
GRANULARITY => 'ALL',
9
CASCADE => TRUE);
10dbms_output.put_line(case when STALE.SEGMENT_NAME=upper(replace(STALE.SEGMENT_NAME,' '))then STALE.SEGMENT_NAME else '"'||STALE.SEGMENT_NAME||'"' end);
11END LOOP;
12end;
13/
BADGES_EXCHANGE
BADGES
"Bad Name"
"BAD NAME2"
PL/SQL 过程已成功完成。
已用时间:00: 00: 00.72
SQL> SQL> exec dbms_STATS.GATHER_TABLE_STATS('SCOTT','BAD NAME2');
BEGIN dbms_STATS.GATHER_TABLE_STATS('SCOTT','BAD NAME2'); END;
*
第 1 行出现错误:
ORA-20001: BAD NAME2 is an invalid identifier
ORA-06512: 在 "SYS.DBMS_STATS", line 23154
ORA-06512: 在 "SYS.DBMS_STATS", line 23205
ORA-06512: 在 line 1

已用时间:00: 00: 00.01

千问 · 2013-8-26 10:32:46

，牛。谢谢版主。

千问 · 2013-8-26 10:32:46

schema下要是需要收集的表很多的话，
直接
DBMS_STATS.GATHER_schema_STATS

千问 · 2013-8-26 10:32:46

oracle_cj 发表于 2014-1-23 20:16
schema下要是需要收集的表很多的话，
直接
DBMS_STATS.GATHER_schema_STATS
还有数据库级的
PROCEDURE GATHER_SCHEMA_STATS
参数名称
类型
输入/输出默认值?
------------------------------ ----------------------- ------ --------
OWNNAME
VARCHAR2
IN
ESTIMATE_PERCENT
NUMBER
IN DEFAULT
BLOCK_SAMPLE
BOOLEAN
IN DEFAULT
METHOD_OPT
VARCHAR2
IN DEFAULT
DEGREE
NUMBER
IN DEFAULT
GRANULARITY
VARCHAR2
IN DEFAULT
CASCADE
BOOLEAN
IN DEFAULT
STATTAB
VARCHAR2
IN DEFAULT
STATID
VARCHAR2
IN DEFAULT
OPTIONS
VARCHAR2
IN DEFAULT
OBJLIST
OBJECTTAB
OUT
STATOWN
VARCHAR2
IN DEFAULT
NO_INVALIDATE
BOOLEAN
IN DEFAULT
GATHER_TEMP
BOOLEAN
IN DEFAULT
GATHER_FIXED
BOOLEAN
IN DEFAULT
STATTYPE
VARCHAR2
IN DEFAULT
FORCE
BOOLEAN
IN DEFAULT
OBJ_FILTER_LIST
OBJECTTAB
IN DEFAULT
PROCEDURE GATHER_SCHEMA_STATS
参数名称
类型
输入/输出默认值?
------------------------------ ----------------------- ------ --------
OWNNAME
VARCHAR2
IN
ESTIMATE_PERCENT
NUMBER
IN DEFAULT
BLOCK_SAMPLE
BOOLEAN
IN DEFAULT
METHOD_OPT
VARCHAR2
IN DEFAULT
DEGREE
NUMBER
IN DEFAULT
GRANULARITY
VARCHAR2
IN DEFAULT
CASCADE
BOOLEAN
IN DEFAULT
STATTAB
VARCHAR2
IN DEFAULT
STATID
VARCHAR2
IN DEFAULT
OPTIONS
VARCHAR2
IN DEFAULT
STATOWN
VARCHAR2
IN DEFAULT
NO_INVALIDATE
BOOLEAN
IN DEFAULT
GATHER_TEMP
BOOLEAN
IN DEFAULT
GATHER_FIXED
BOOLEAN
IN DEFAULT
STATTYPE
VARCHAR2
IN DEFAULT
FORCE
BOOLEAN
IN DEFAULT
OBJ_FILTER_LIST
OBJECTTAB
IN DEFAULT
PROCEDURE GATHER_SYSTEM_STATS
参数名称
类型
输入/输出默认值?
------------------------------ ----------------------- ------ --------
GATHERING_MODE
VARCHAR2
IN DEFAULT
INTERVAL
NUMBER(38)
IN DEFAULT
STATTAB
VARCHAR2
IN DEFAULT
STATID
VARCHAR2
IN DEFAULT
STATOWN
VARCHAR2
IN DEFAULT
PROCEDURE GATHER_TABLE_STATS
参数名称
类型
输入/输出默认值?
------------------------------ ----------------------- ------ --------
OWNNAME
VARCHAR2
IN
TABNAME
VARCHAR2
IN
PARTNAME
VARCHAR2
IN DEFAULT
ESTIMATE_PERCENT
NUMBER
IN DEFAULT
BLOCK_SAMPLE
BOOLEAN
IN DEFAULT
METHOD_OPT
VARCHAR2
IN DEFAULT
DEGREE
NUMBER
IN DEFAULT
GRANULARITY
VARCHAR2
IN DEFAULT
CASCADE
BOOLEAN
IN DEFAULT
STATTAB
VARCHAR2
IN DEFAULT
STATID
VARCHAR2
IN DEFAULT
STATOWN
VARCHAR2
IN DEFAULT
NO_INVALIDATE
BOOLEAN
IN DEFAULT
STATTYPE
VARCHAR2
IN DEFAULT
FORCE
BOOLEAN
IN DEFAULT
CONTEXT
CCONTEXT
IN DEFAULT
OPTIONS
VARCHAR2
IN DEFAULT

千问 · 2013-8-26 10:32:46

趁早把表名给改成全大写，这就是个坑。

千问 · 2013-8-26 10:32:46

,是啊。大小写混合就是个坑。