今天在使用dbms_sqltune.create_stgtab_sqlset來建立staging table時由於表名使用的是小寫，發現建立之後，並不能查詢到該表，但透過檢視進行檢視發現又確實存在這個表，這個問題在oracle 10.2..0.5與11.2.0.4中都存在，其它的版本沒有測試過。

SQL> exec dbms_sqltune.create_stgtab_sqlset(table_name => 'stgtab_sqlset',schema_name => 'JY');

PL/SQL procedure successfully completed

SQL> exec dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'my_sql_tuning_set',staging_table_name => 'stgtab_sqlset',staging_schema_owner =>'JY');

PL/SQL procedure successfully completed

SQL> select * from jy.stgtab_sqlset;

select * from stgtab_sqlset

ORA-00942: table or view does not exist

SQL> desc jy.stgtab_sqlset
Object jy.stgtab_sqlset does not exist.

查詢dba_objects檢視確能找到該表stgtab_sqlset

SSQL> select * from dba_objects where owner='JY' and OBJECT_NAME in('stgtab_sqlset');

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
JY stgtab_sqlset 92747 92747 TABLE 2016/5/26 1 2016/5/26 16: 2016-05-26:16:04:49 VALID N N N 1

如是表名使用大寫
SQL> exec dbms_sqltune.create_stgtab_sqlset(table_name => ‘TABLE_SQLSET’,schema_name => ‘JY’);

PL/SQL procedure successfully completed

SQL> exec dbms_sqltune.pack_stgtab_sqlset(sqlset_name => ‘my_sql_tuning_set’,staging_table_name => ‘TABLE_SQLSET’,staging_schema_owner =>’JY’);

PL/SQL procedure successfully completed

SQL> select * from table_sqlset;

NAME OWNER DESCRIPTION SQL_ID FORCE_MATCHING_SIGNATURE SQL_TEXT PARSING_SCHEMA_NAME BIND_DATA BIND_LIST MODULE ACTION ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES EXECUTIONS END_OF_FETCH_COUNT OPTIMIZER_COST OPTIMIZER_ENV PRIORITY COMMAND_TYPE FIRST_LOAD_TIME STAT_PERIOD ACTIVE_STAT_PERIOD OTHER PLAN_HASH_VALUE PLAN SPARE1 SPARE2 SPARE3 SPARE4
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------- ------------------------ -------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- --------- ------------------------------------------------ -------------------------------- ------------ ---------- ----------- ---------- ------------- -------------- ---------- ---------- ------------------ -------------- -------------------------------------------------------------------------------- ---------- ------------ ------------------- ----------- ------------------ -------------------------------------------------------------------------------- --------------- ---- ---------- ---------- ------ --------------------------------------------------------------------------------
my_sql_tuning_set JY TEST 2823agph489xc 1.702412129134E19 select id,name,password from gl_czy where name ='系統管理' ZW4001 R9_AppSrv.EXE 124908 32105 324 48 0 21 42 21 21 3 E289FB89A4E49800CE001000AEF9C3E2CFFA3310564145555195A110555555154554555859155544 3 2543410975 <obj ="" 0="" 1=""

my_sql_tuning_set JY TEST 1hfffsrmgqhwp 1.11129101236943E19 select nvl(catalog_center,center_id) as catalog_center from bs_hospital_collate INSUR_CHANGDE JDBC Thin Client 141372 141372 11475 0 0 3825 3825 3825 3825 1 E289FB89A4E49800CE001000AEF9C3E2CFFA3310564145555195A110555555154554555859155544 3 2429242715 <obj ="" 0="" 1=""

查詢檢視dba_objects發現這兩個表的相關屬性除了表名與建立時間，物件ID之外沒有差異
SQL> select * from dba_objects where owner='JY' and OBJECT_NAME in('stgtab_sqlset','STGTAB_SQLSET');

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
JY STGTAB_SQLSET 92781 92781 TABLE 2016/5/26 1 2016/5/26 16: 2016-05-26:16:22:55 VALID N N N 1
JY stgtab_sqlset 92747 92747 TABLE 2016/5/26 1 2016/5/26 16: 2016-05-26:16:04:49 VALID N N N 1

SQL> desc jy.stgtab_sqlset
Name Type Nullable Default Comments
--------------------------- -------------- -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(30) Y
OWNER VARCHAR2(30) Y
DESCRIPTION VARCHAR2(256) Y
SQL_ID VARCHAR2(13) Y
SQL_SEQ NUMBER Y
FORCE_MATCHING_SIGNATURE NUMBER Y
SQL_TEXT CLOB Y
PARSING_SCHEMA_NAME VARCHAR2(30) Y
BIND_DATA RAW(2000) Y
BINDS_CAPTURED CHAR(1) Y
BIND_POSITION NUMBER Y
BIND_VALUE ANYDATA Y
MODULE VARCHAR2(64) Y
ACTION VARCHAR2(64) Y
ELAPSED_TIME NUMBER Y
CPU_TIME NUMBER Y
BUFFER_GETS NUMBER Y
DISK_READS NUMBER Y
DIRECT_WRITES NUMBER Y
ROWS_PROCESSED NUMBER Y
FETCHES NUMBER Y
EXECUTIONS NUMBER Y
END_OF_FETCH_COUNT NUMBER Y
OPTIMIZER_COST NUMBER Y
OPTIMIZER_ENV RAW(2000) Y
PRIORITY NUMBER Y
COMMAND_TYPE NUMBER Y
FIRST_LOAD_TIME VARCHAR2(19) Y
STAT_PERIOD NUMBER Y
ACTIVE_STAT_PERIOD NUMBER Y
OTHER CLOB Y
PLAN_HASH_VALUE NUMBER Y
PLAN_STATEMENT_ID VARCHAR2(30) Y
PLAN_PLAN_ID NUMBER Y
PLAN_TIMESTAMP DATE Y
PLAN_REMARKS VARCHAR2(4000) Y
PLAN_OPERATION VARCHAR2(30) Y
PLAN_OPTIONS VARCHAR2(255) Y
PLAN_OBJECT_NODE VARCHAR2(128) Y
PLAN_OBJECT_OWNER VARCHAR2(30) Y
PLAN_OBJECT_NAME VARCHAR2(30) Y
PLAN_OBJECT_ALIAS VARCHAR2(65) Y
PLAN_OBJECT_INSTANCE NUMBER Y
PLAN_OBJECT_TYPE VARCHAR2(30) Y
PLAN_OPTIMIZER VARCHAR2(255) Y
PLAN_SEARCH_COLUMNS NUMBER Y
PLAN_ID NUMBER Y
PLAN_PARENT_ID NUMBER Y
PLAN_DEPTH NUMBER Y
PLAN_POSITION NUMBER Y
PLAN_COST NUMBER Y
PLAN_CARDINALITY NUMBER Y
PLAN_BYTES NUMBER Y
PLAN_OTHER_TAG VARCHAR2(255) Y
PLAN_PARTITION_START VARCHAR2(255) Y
PLAN_PARTITION_STOP VARCHAR2(255) Y
PLAN_PARTITION_ID NUMBER Y
PLAN_DISTRIBUTION VARCHAR2(30) Y
PLAN_CPU_COST NUMBER Y
PLAN_IO_COST NUMBER Y
PLAN_TEMP_SPACE NUMBER Y
PLAN_ACCESS_PREDICATES VARCHAR2(4000) Y
PLAN_FILTER_PREDICATES VARCHAR2(4000) Y
PLAN_PROJECTION VARCHAR2(4000) Y
PLAN_TIME NUMBER Y
PLAN_QBLOCK_NAME VARCHAR2(30) Y
PLAN_OTHER_XML CLOB Y
PLAN_EXECUTIONS NUMBER Y
PLAN_STARTS NUMBER Y
PLAN_OUTPUT_ROWS NUMBER Y
PLAN_CR_BUFFER_GETS NUMBER Y
PLAN_CU_BUFFER_GETS NUMBER Y
PLAN_DISK_READS NUMBER Y
PLAN_DISK_WRITES NUMBER Y
PLAN_ELAPSED_TIME NUMBER Y
PLAN_LAST_STARTS NUMBER Y
PLAN_LAST_OUTPUT_ROWS NUMBER Y
PLAN_LAST_CR_BUFFER_GETS NUMBER Y
PLAN_LAST_CU_BUFFER_GETS NUMBER Y
PLAN_LAST_DISK_READS NUMBER Y
PLAN_LAST_DISK_WRITES NUMBER Y
PLAN_LAST_ELAPSED_TIME NUMBER Y
PLAN_POLICY VARCHAR2(10) Y
PLAN_ESTIMATED_OPTIMAL_SIZE NUMBER Y
PLAN_ESTIMATED_ONEPASS_SIZE NUMBER Y
PLAN_LAST_MEMORY_USED NUMBER Y
PLAN_LAST_EXECUTION VARCHAR2(10) Y
PLAN_LAST_DEGREE NUMBER Y
PLAN_TOTAL_EXECUTIONS NUMBER Y
PLAN_OPTIMAL_EXECUTIONS NUMBER Y
PLAN_ONEPASS_EXECUTIONS NUMBER Y
PLAN_MULTIPASSES_EXECUTIONS NUMBER Y
PLAN_ACTIVE_TIME NUMBER Y
PLAN_MAX_TEMPSEG_SIZE NUMBER Y
PLAN_LAST_TEMPSEG_SIZE NUMBER Y
SPARE1 NUMBER Y
SPARE2 NUMBER Y
SPARE3 BLOB Y
SPARE4 CLOB Y
SPARE5 NUMBER Y
SPARE6 NUMBER Y
SPARE7 CLOB Y
SPARE8 CLOB Y

從plsql對dbms_sqltune.create_stgtab_sqlset的引數table_name的描述中提到了大小寫敏感，但是從實際情況來看只能用大寫才能方便執行遷移。

dbms_sqltune.create_stgtab_sqlset需要注意的問題

相關文章