Home » Server Options » Text & interMedia » dbms_redefinition (Oracle 10g OC: CENT OS 5)
|
|
|
Re: dbms_redefinition [message #524193 is a reply to message #524068] |
Thu, 22 September 2011 03:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Combining this with your other recent post about converting varchar2 to nvarchar2, I gather that you are trying to use dbms_redefinition to convert a table that contains a varchar2 to a table that contains nvarchar2 and that this same table has a context index and you are getting the error that you posted during redefinition. I am unable to reproduce the error. The error referes to "REPLACE". I don't see where that would be used, unless you have used it as a table or column name and that causes confusion. I have provided as simple an example as I could below. You need to provide a similar copy and paste that demonstrates how you are getting the error.
SCOTT@orcl_11gR2> -- original table
SCOTT@orcl_11gR2> -- including varchar2 to be converted to nvarchar2
SCOTT@orcl_11gR2> -- and clob column for text index:
SCOTT@orcl_11gR2> CREATE TABLE T_TEST
2 (COL1 NUMBER PRIMARY KEY,
3 COL2 VARCHAR2 (10),
4 COL3 CLOB)
5 /
Table created.
SCOTT@orcl_11gR2> -- test data:
SCOTT@orcl_11gR2> INSERT INTO T_TEST VALUES (1, 'A', 'TEST DATA')
2 /
1 row created.
SCOTT@orcl_11gR2> -- text index:
SCOTT@orcl_11gR2> CREATE INDEX T_IDX ON T_TEST (COL3) INDEXTYPE IS CTXSYS.CONTEXT
2 /
Index created.
SCOTT@orcl_11gR2> SELECT * FROM T_TEST WHERE CONTAINS (COL3, 'TEST DATA') > 0
2 /
COL1 COL2 COL3
---------- ---------- ----------
1 A TEST DATA
1 row selected.
SCOTT@orcl_11gR2> -- create interim table:
SCOTT@orcl_11gR2> CREATE TABLE tmp_1_t_test
2 (col1 NUMBER,
3 col2 NVARCHAR2(10),
4 col3 CLOB)
5 /
Table created.
SCOTT@orcl_11gR2> -- redefinition:
SCOTT@orcl_11gR2> DECLARE
2 num_errors NUMBER;
3 BEGIN
4 DBMS_REDEFINITION.CAN_REDEF_TABLE
5 (USER, 'T_TEST', DBMS_REDEFINITION.CONS_USE_PK);
6 DBMS_REDEFINITION.START_REDEF_TABLE
7 (USER, 'T_TEST', 'TMP_1_T_TEST',
8 'COL1 COL1,TO_NCHAR(COL2) COL2,COL3 COL3',
9 DBMS_REDEFINITION.CONS_USE_PK);
10 DBMS_REDEFINITION.SYNC_INTERIM_TABLE
11 (USER, 'T_TEST', 'TMP_1_T_TEST');
12 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
13 (USER, 'T_TEST', 'TMP_1_T_TEST',
14 DBMS_REDEFINITION.CONS_ORIG_PARAMS,
15 TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
16 DBMS_REDEFINITION.FINISH_REDEF_TABLE
17 (USER, 'T_TEST', 'TMP_1_T_TEST');
18 END;
19 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> -- drop interim table:
SCOTT@orcl_11gR2> DROP TABLE tmp_1_t_test CASCADE CONSTRAINTS
2 /
Table dropped.
SCOTT@orcl_11gR2> -- results are new table with nvarchar2 and working text index:
SCOTT@orcl_11gR2> DESC T_TEST
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL2 NVARCHAR2(10)
COL3 CLOB
SCOTT@orcl_11gR2> COLUMN col3 FORMAT A10
SCOTT@orcl_11gR2> SELECT * FROM T_TEST WHERE CONTAINS (COL3, 'TEST DATA') > 0
2 /
COL1 COL2 COL3
---------- ---------- ----------
1 A TEST DATA
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
Re: dbms_redefinition [message #524297 is a reply to message #524227] |
Thu, 22 September 2011 15:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I think you may have encountered a bug. It appears that if you use dbms_redefinition on a table with a text index that has been altered using replace, it does not recognize that syntax. The only workaround that I can think of is to drop the index, recreate the index with all of the parameters, without using any alter index statements, then run the redefinition. Please see the reproduction of the problem followed by the workaround below.
-- reproduction of problem:
SCOTT@orcl_11gR2> -- table, data, altered index:
SCOTT@orcl_11gR2> CREATE TABLE T_TEST
2 (COL1 NUMBER PRIMARY KEY,
3 COL2 VARCHAR2 (10),
4 COL3 CLOB)
5 /
Table created.
SCOTT@orcl_11gR2> INSERT INTO T_TEST VALUES (1, 'A', 'TEST DATA')
2 /
1 row created.
SCOTT@orcl_11gR2> CREATE INDEX I1 ON T_TEST (COL3) INDEXTYPE IS CTXSYS.CONTEXT
2 /
Index created.
SCOTT@orcl_11gR2> alter index I1 REBUILD parameters('REPLACE sync(on commit)')
2 /
Index altered.
SCOTT@orcl_11gR2> SELECT * FROM T_TEST WHERE CONTAINS (COL3, 'TEST DATA') > 0
2 /
COL1 COL2 COL3
---------- ---------- ----------
1 A TEST DATA
1 row selected.
SCOTT@orcl_11gR2> -- redefinition that fails:
SCOTT@orcl_11gR2> CREATE TABLE tmp_1_t_test
2 (col1 NUMBER,
3 col2 NVARCHAR2(10),
4 col3 CLOB)
5 /
Table created.
SCOTT@orcl_11gR2> DECLARE
2 num_errors NUMBER;
3 BEGIN
4 DBMS_REDEFINITION.CAN_REDEF_TABLE
5 (USER, 'T_TEST', DBMS_REDEFINITION.CONS_USE_PK);
6 DBMS_REDEFINITION.START_REDEF_TABLE
7 (USER, 'T_TEST', 'TMP_1_T_TEST',
8 'COL1 COL1,TO_NCHAR(COL2) COL2,COL3 COL3',
9 DBMS_REDEFINITION.CONS_USE_PK);
10 DBMS_REDEFINITION.SYNC_INTERIM_TABLE
11 (USER, 'T_TEST', 'TMP_1_T_TEST');
12 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
13 (USER, 'T_TEST', 'TMP_1_T_TEST',
14 DBMS_REDEFINITION.CONS_ORIG_PARAMS,
15 TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
16 DBMS_REDEFINITION.FINISH_REDEF_TABLE
17 (USER, 'T_TEST', 'TMP_1_T_TEST');
18 END;
19 /
DECLARE
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-11000: invalid keyword REPLACE
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1364
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2025
ORA-06512: at line 12
-- clean up the mess:
SCOTT@orcl_11gR2> DROP MATERIALIZED VIEW tmp_1_t_test
2 /
Materialized view dropped.
SCOTT@orcl_11gR2> DROP TABLE tmp_1_t_test CASCADE CONSTRAINTS
2 /
Table dropped.
SCOTT@orcl_11gR2> BEGIN
2 DBMS_REDEFINITION.ABORT_REDEF_TABLE
3 (USER, 'T_TEST', 'TMP_1_T_TEST');
4 END;
5 /
PL/SQL procedure successfully completed.
-- workaround:
SCOTT@orcl_11gR2> -- drop and recreate index with all parameters without altering:
SCOTT@orcl_11gR2> DROP INDEX I1
2 /
Index dropped.
SCOTT@orcl_11gR2> CREATE INDEX I1 ON T_TEST (COL3) INDEXTYPE IS CTXSYS.CONTEXT
2 parameters('sync(on commit)')
3 /
Index created.
SCOTT@orcl_11gR2> -- redo redefinition:
SCOTT@orcl_11gR2> CREATE TABLE tmp_1_t_test
2 (col1 NUMBER,
3 col2 NVARCHAR2(10),
4 col3 CLOB)
5 /
Table created.
SCOTT@orcl_11gR2> DECLARE
2 num_errors NUMBER;
3 BEGIN
4 DBMS_REDEFINITION.CAN_REDEF_TABLE
5 (USER, 'T_TEST', DBMS_REDEFINITION.CONS_USE_PK);
6 DBMS_REDEFINITION.START_REDEF_TABLE
7 (USER, 'T_TEST', 'TMP_1_T_TEST',
8 'COL1 COL1,TO_NCHAR(COL2) COL2,COL3 COL3',
9 DBMS_REDEFINITION.CONS_USE_PK);
10 DBMS_REDEFINITION.SYNC_INTERIM_TABLE
11 (USER, 'T_TEST', 'TMP_1_T_TEST');
12 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
13 (USER, 'T_TEST', 'TMP_1_T_TEST',
14 DBMS_REDEFINITION.CONS_ORIG_PARAMS,
15 TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
16 DBMS_REDEFINITION.FINISH_REDEF_TABLE
17 (USER, 'T_TEST', 'TMP_1_T_TEST');
18 END;
19 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> DROP TABLE tmp_1_t_test CASCADE CONSTRAINTS
2 /
Table dropped.
SCOTT@orcl_11gR2> -- results:
SCOTT@orcl_11gR2> DESC T_TEST
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL2 NVARCHAR2(10)
COL3 CLOB
SCOTT@orcl_11gR2> COLUMN col3 FORMAT A10
SCOTT@orcl_11gR2> SELECT * FROM T_TEST WHERE CONTAINS (COL3, 'TEST DATA') > 0
2 /
COL1 COL2 COL3
---------- ---------- ----------
1 A TEST DATA
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
|
Re: dbms_redefinition [message #524442 is a reply to message #524301] |
Fri, 23 September 2011 12:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I received the following response to my post on the OTN forums, so if you have Oracle support, you can look up the bugs for any patches or workarounds.
"yes, we have bug 10355282 and bug 8660944 for the dictionary metadata inconsistency after "ALTER INDEX ... REBUILD REPLACE" command"
|
|
|
Goto Forum:
Current Time: Thu Jul 04 01:50:23 CDT 2024
|