Still the same cluster limitations

I am slightly disappointed to report that the new extended data types cannot be used in the clustered tables. CLOB columns have never been allowed, and are still not allowed in 12c, but I expected the new data types to pass seamlessly. That is not the case:

SQL> create cluster testclu(intkey number(30,0));

Cluster created.

Elapsed: 00:00:00.03
SQL> create index testclu_ind on cluster testclu;

Index created.

Elapsed: 00:00:00.03

Now, let’s create a normal table with the extended character type:

create table testtab1(
intkey number(30,0),
big_text varchar2(32767))
/
Table created.

Elapsed: 00:00:00.02
SQL>
SQL> desc testtab1
Name                       Null?    Type
—————————————– ——– —————————-
INTKEY                         NUMBER(30)
BIG_TEXT                     VARCHAR2(32767)

SQL>

Now, let’s try and put the table with an extended data type into a cluster.

SQL> create table testtab2
2  (intkey number(30),
3   big_text varchar2(32767))
4  cluster testclu(intkey);
create table testtab2
*
ERROR at line 1:
ORA-14691: Extended character types are not allowed in this table.

If the size of the big_text column is reduced to 4000, the problem is gone:

1  create table testtab2
2  (intkey number(30),
3   big_text varchar2(4000))
4* cluster testclu(intkey)
SQL> /

Table created.

Elapsed: 00:00:00.03
SQL>

I was unable to find any documentation on this restriction.

About these ads

About mgogala

I am a long time Oracle DBA, who has worked on very large databases. I have worked with OPS and RAC since its inception.I am also a published book writer, having published two books about PHP. This blog is about the challenges and adventures in my professional life. Sorry, no family pictures here.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s