一次把一个普通表在线重定义为hash partition table引起的ORA-07445错误-CSDN博客

本文链接：https://blog.csdn.net/zhpsam109/article/details/4445195

采用了基于rowid的方式做过在线重定义

而多出来的一个隐藏列。这个隐藏列通过desc是看不到的。而metadata.get_ddl可

以读取到,下面再现一下：

目前：把一个普通表在线重定义为hash partition table.

测试环境：xp+10.2.0.1

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL>

SQL> create table zrp

2 (oid number,

3 dealdate date default sysdate

4 );

Table created

SQL> begin

2 for i in 1..100 loop

3 insert into zrp (oid) values(i);

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed

SQL>

SQL> create table ZRP_MID

2 (

3 OID NUMBER,

4 DEALDATE DATE default sysdate

5 )

6 partition by hash (OID)

7 ( partition p1 tablespace STUDY,

8 partition p2 tablespace STUDY,

9 partition p3 tablespace STUDY,

10 partition p4 tablespace STUDY

11 );

Table created

SQL> exec dbms_redefinition.can_redef_table(

'STUDY',

'ZRP',

dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed

SQL> exec dbms_redefinition.start_redef_table(

'study',

'ZRP',

'ZRP_MID',

'oid oid,dealdate dealdate',

dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed

SQL> exec dbms_redefinition.finish_redef_table('STUDY','ZRP','ZRP_MID');

PL/SQL procedure successfully completed

SQL> desc zrp

Name Type Nullable Default Comments

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

OID NUMBER Y

DEALDATE DATE Y sysdate

SQL> desc zrp_mid

Name Type Nullable Default Comments

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

OID NUMBER Y

DEALDATE DATE Y sysdate

SQL>

SQL> select dbms_metadata.get_ddl('TABLE','ZRP') from dual;

DBMS_METADATA.GET_DDL('TABLE',

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

CREATE TABLE "STUDY"."ZRP"

( "SYS_C00003_07083112:38:30$" VARCHAR2(255),

"OID" NUMBER,

"DEALDATE" DATE DEFAULT sysdate

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(BUFFER_POOL DEFAULT)

TABLESPACE "STUDY"

PARTITION BY HASH ("OID")

(PARTITION "P1" TABLESPACE "STUDY",

PARTITION "P2" TABLESPACE "STUDY",

PARTITION "P3" TABLESPACE "STUDY",

PARTITION "P4" TABLESPACE "STUDY")

SQL>

这个时候我们看到，多出了一列，其实这一列是因为我们采用基于rowid的

方式在线重定义而增加的一个隐藏列。在9i中这个列名为M_ROW$$，在10g中

采用了新的命名机制：

SYS_C<5位的数字>_yymmddhh24:mi:ss$

比如：SYS_C00003_07083112:38:30$

这个格式的官方说明我还不曾看到，是我猜出来的，其中5位数字编码

应该是列的序号。

SQL> select col#,name,type#

2 from SYS.COL$

3 WHERE OBJ#=(select object_id

4 from dba_objects

5 where owner='STUDY' and

6 object_name='ZRP' And

7 object_type='TABLE');

COL# NAME TYPE#

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

1 OID 2

2 DEALDATE 12

0 SYS_C00003_07083112:38:30$ 1

SQL>

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as study

SQL> alter table zrp set unused column "SYS_C00003_07083112:38:30$";

Table altered

SQL> alter table zrp drop unused columns;

Table altered

SQL> desc zrp

Name Type Nullable Default Comments

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

OID NUMBER Y

DEALDATE DATE Y sysdate

SQL>

SQL> select col#,name,type#

2 from SYS.COL$

3 WHERE OBJ#=(select object_id

4 from dba_objects

5 where owner='STUDY' and

6 object_name='ZRP' And

7 object_type='TABLE');

COL# NAME TYPE#

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

1 OID 2

2 DEALDATE 12

SQL> insert into zrp values(2000,sysdate);

1 row inserted

SQL> commit;

Commit complete

SQL>

在看一下通过metadata.get_ddl得到的结果：

SQL> Select dbms_metadata.get_ddl('TABLE','ZRP') From dual;

DBMS_METADATA.GET_DDL('TABLE',

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

CREATE TABLE "STUDY"."ZRP"

( "OID" NUMBER,

"DEALDATE" DATE DEFAULT sysdate

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(BUFFER_POOL DEFAULT)

TABLESPACE "STUDY"

PARTITION BY HASH ("OID")

(PARTITION "P1" TABLESPACE "STUDY",

PARTITION "P2" TABLESPACE "STUDY",

PARTITION "P3" TABLESPACE "STUDY",

PARTITION "P4" TABLESPACE "STUDY")

--自己的测试:

--直接DROP隐藏列报错

create table zrp

(oid number,

dealdate date default sysdate

);

begin

for i in 1..100 loop

insert into zrp (oid) values(i);

end loop;

commit;

end;

create table ZRP_MID

(

OID NUMBER,

DEALDATE DATE default sysdate

)

partition by hash (OID)

( partition p1 ,

partition p2

);

SQL> exec dbms_redefinition.can_redef_table('LPPMTEST','ZRP',dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed

SQL> exec dbms_redefinition.start_redef_table('LPPMTEST','ZRP','ZRP_MID','oid oid,dealdate dealdate',dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed

SQL> exec dbms_redefinition.finish_redef_table('LPPMTEST','ZRP','ZRP_MID');

PL/SQL procedure successfully completed

SQL> desc zrp

Name Type Nullable Default Comments

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

OID NUMBER Y

DEALDATE DATE Y sysdate

SQL> desc zrp_mid

Name Type Nullable Default Comments

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

OID NUMBER Y

DEALDATE DATE Y sysdate

SQL> select column_name from user_tab_columns where table_name='ZRP';

COLUMN_NAME

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

OID

DEALDATE

SQL> select * from user_tab_cols where table_name='ZRP';

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_TYPE_MOD DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE COLUMN_ID DEFAULT_LENGTH DATA_DEFAULT NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLOBAL_STATS USER_STATS AVG_COL_LEN CHAR_LENGTH CHAR_USED V80_FMT_IMAGE DATA_UPGRADED HIDDEN_COLUMN VIRTUAL_COLUMN SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID HISTOGRAM QUALIFIED_COL_NAME

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

ZRP OID NUMBER 22 Y 1 NO NO 0 NO YES NO NO 1 1 NONE OID

ZRP DEALDATE DATE 7 Y 2 12 sysdate NO NO 0 NO YES NO NO 2 2 NONE DEALDATE

ZRP SYS_C00003_07083113:37:43$ VARCHAR2 255 Y CHAR_CS 255 NO NO 255 B NO YES YES NO 3 3 NONE SYS_C00003_07083113:37:43$

SQL> insert into zrp select 1,sysdate from dual;

1 row inserted

SQL> commit;

Commit complete

SQL> alter table zrp drop column "SYS_C00003_07083113:37:43$";

Table altered

SQL> insert into zrp select 1,sysdate from dual;

insert into zrp select 1,sysdate from dual

ORA-03113: end-of-file on communication channel