If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > ORA-12060: shape of prebuilt table does not match definition query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 01-28-03, 16:31
Wale Wale is offline
Registered User
 
Join Date: Mar 2002
Posts: 162
ORA-12060: shape of prebuilt table does not match definition query

I am trying to test out the DBMS_REDEFINITION PACKAGE. on My Oracle 9i release 2 enterprise edition install

these are the scripts I have run in order:
************************************************** *******************
create table tbl_bureau_bck as select * from tbl_bureau

alter table tbl_bureau_bck ADD PRIMARY KEY (BURE_BUREAU_ID)
************************************************** **********
tbl_bureau ddl looks like this:

************************************************** **********
CREATE TABLE TBL_BUREAU_BCK (
BURE_BUREAU_ID NUMBER (3) NOT NULL,
BURE_NAME VARCHAR2 (100),
BURE_AGENCY_ID NUMBER (3),
BURE_CODE VARCHAR2 (2),
BURE_ADDRESS VARCHAR2 (200),
BURE_ACODE VARCHAR2 (1),
BURE_BCODE VARCHAR2 (6),
BURE_AB_CODE VARCHAR2 (4),
PRIMARY KEY ( BURE_BUREAU_ID )
USING INDEX
TABLESPACE INTE01_DATA PCTFREE 10
STORAGE ( INITIAL 128K NEXT 128K PCTINCREASE 0 ))
TABLESPACE INTE01_DATA
PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255
STORAGE (
INITIAL 128K NEXT 128K PCTINCREASE 0
MINEXTENTS 1 MAXEXTENTS 2147483645 )
NOCACHE;

************************************************** **********
Then I ran this:

************************************************** **********
exec dbms_redefinition.can_redef_table('inte01','TBL_BU REAU_BCK');
************************************************** **********

it returns :

************************************************** **********
PL/SQL procedure successfully completed.
************************************************** **********

Then I created my interim table like this:

************************************************** *********
CREATE TABLE REDEF_BUREAU -- interim table
(
BURE_BUREAU_KEY NUMBER(3) PRIMARY KEY,
BURE_NAME VARCHAR2(100),
BURE_AGENCY_ID NUMBER(3),
BURE_NEW_CODE VARCHAR2 (2),
BURE_ADDRESS VARCHAR2(200),
BURE_ACODE VARCHAR2(1),
BURE_BCODE VARCHAR2(6) DEFAULT 10
)
TABLESPACE INTE01_DATA ;
************************************************** **********

basically the differences are :

rename BURE_BUREAU_ID to BURE_BUREAU_KEY
Multiply BURE_CODE by 2 and Rename to BURE_NEW_CODE (BURE_CODE*2 BURE_NEW_CODE)
Change BURE_BCODE to have a default number of 10
Drop column BURE_AB_CODE

I checked to see if i could carry out task again by running

************************************************** **********
exec dbms_redefinition.can_redef_table('inte01','TBL_BU REAU_BCK');
************************************************** **********

it came back fine.

but when I try to run either of these statements

************************************************** **********
Execute dbms_redefinition.START_REDEF_TABLE( -
'inte01','tbl_bureau_bck','redef_bureau', -
'BURE_BUREAU_ID BURE_BUREAU_KEY, -
BURE_NAME BURE_NAME, -
BURE_AGENCY_ID BURE_AGENCY_ID, -
BURE_BCODE*2 BURE_NEW_CODE, -
BURE_ADDRESS BURE_ADDRESS, -
BURE_ACODE BURE_ACODE, -
BURE_BCODE BURE_BCODE')

or

Execute dbms_redefinition.START_REDEF_TABLE( -
'inte01','tbl_bureau_bck','redef_bureau', -
'BURE_BUREAU_ID BURE_BUREAU_KEY, -
BURE_BCODE*2 BURE_NEW_CODE')
************************************************** **********

I get this error over and over again:

ERROR at line 1:
ORA-12060: shape of prebuilt table does not match definition query
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
ORA-06512: at line 1

I have dropped the materialized views and tried but to know avail
does anyone see the mistake I may be making in my definition query?

Thanks sooo much
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On