Re: Metalink disaster

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sat, 14 Nov 2009 07:33:09 -0800 (PST)
Message-ID: <33f79682-7da3-41b8-b9b0-d3e7fa835982_at_h34g2000yqm.googlegroups.com>

On Nov 14, 12:57 am, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> This thing with the Metalink is turning into an unmitigated disaster of
> biblical proportions. I tried to log into Metalink because I want to open
> a TAR, but I can't get in. Here is my problem:
>
> SQL> create cluster print_clu(active char(1))
>   2  tablespace imi
>   3  single table hashkeys 3;
>
> Cluster created.
>
> Elapsed: 00:00:00.12
> SQL> create table print_tmp
>   2  cluster print_clu(active)
>   3  as select * from print where rownum<0;
> create table print_tmp
> *
> ERROR at line 1:
> ORA-03001: unimplemented feature
>
> The version is 10.2.0.4 on Linux, 64 bit. I want to know what exactly is
> the "unimplemented feature" and why is it not possible to populate the
> cluster? Of course, ML being out, I can't really ask  anybody. Are we
> going to get some discounts for this support disaster? Who is the idiot
> who has insisted on replacing the old Metalink and why exactly was it
> done? Will the guilty be fired? This shows a serious lack of judgment and
> arrogance on the part of the Oracle Corp. This is not just a joke, we're
> paying significant amounts of money for support!

Mladen,

You might want to check the column definitions for the table PRINT. While I have little experience working with clusters, I think that I was able to reproduce the problem that you are experiencing.

Here is a test case I created:
CREATE CLUSTER PRINT_CLU(ACTIVE CHAR(1)) TABLESPACE USER_DATA
SINGLE TABLE HASHKEYS 3; CREATE TABLE PRINT AS
SELECT
  *
FROM
  DBA_OBJECTS; ALTER TABLE PRINT RENAME COLUMN OBJECT_TYPE TO ACTIVE; CREATE TABLE PRINT_TMP
CLUSTER PRINT_CLU(ACTIVE)
AS SELECT * FROM PRINT WHERE ROWNUM<0;

ERROR at line 2:
ORA-01753: column definition incompatible with clustered column definition

/* OK, not the error, so eliminates that as a possibility */

DROP TABLE PRINT; CREATE TABLE PRINT AS
SELECT
  *
FROM
  DBA_OBJECTS; ALTER TABLE PRINT ADD (ACTIVE CHAR(1)); CREATE TABLE PRINT_TMP
CLUSTER PRINT_CLU(ACTIVE)
AS SELECT * FROM PRINT WHERE ROWNUM<0;

Table created.

/* OK, that worked */

DROP TABLE PRINT_TMP; ALTER TABLE PRINT MODIFY ACTIVE VARCHAR2(1); CREATE TABLE PRINT_TMP
CLUSTER PRINT_CLU(ACTIVE)
AS SELECT * FROM PRINT WHERE ROWNUM<0;

ERROR at line 2:
ORA-01753: column definition incompatible with clustered column definition

/* OK, column definition must be the same, different error, so eliminates that as a possibility */

ALTER TABLE PRINT MODIFY ACTIVE CHAR(1); ALTER TABLE PRINT ADD LONG_RAW LONG RAW; CREATE TABLE PRINT_TMP
CLUSTER PRINT_CLU(ACTIVE)
AS SELECT * FROM PRINT WHERE ROWNUM<0;

ERROR at line 3:
ORA-00997: illegal use of LONG datatype

/* OK, table cannot have a long raw, different error, so eliminates that as a possibility */

ALTER TABLE PRINT DROP COLUMN LONG_RAW; ALTER TABLE PRINT ADD BLOB_TEST BLOB; CREATE TABLE PRINT_TMP
CLUSTER PRINT_CLU(ACTIVE)
AS SELECT * FROM PRINT WHERE ROWNUM<0;

ERROR at line 3:
ORA-03001: unimplemented feature

/* OK, same error, I think we found a possible cause of the error */

The above test case was performed on 10.2.0.4.

Would you be able to post the table definition using a script like this:
SET PAGESIZE 0
SET LONG 90000
SELECT DBMS_METADATA.GET_DDL('TABLE','PRINT',USER) FROM DUAL; The output from my test case looks like this:  CREATE TABLE "TESTUSER"."PRINT"

  (    "OWNER" VARCHAR2(30),
       "OBJECT_NAME" VARCHAR2(128),
       "SUBOBJECT_NAME" VARCHAR2(30),
       "OBJECT_ID" NUMBER,
       "DATA_OBJECT_ID" NUMBER,
       "OBJECT_TYPE" VARCHAR2(19),
       "CREATED" DATE,
       "LAST_DDL_TIME" DATE,
       "TIMESTAMP" VARCHAR2(19),
       "STATUS" VARCHAR2(7),
       "TEMPORARY" VARCHAR2(1),
       "GENERATED" VARCHAR2(1),
       "SECONDARY" VARCHAR2(1),
       "ACTIVE" CHAR(1),
       "BLOB_TEST" BLOB

  ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "USER_DATA"
LOB ("BLOB_TEST") STORE AS (
 TABLESPACE "USER_DATA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10  NOCACHE LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Nov 14 2009 - 09:33:09 CST