8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for Extended Data Types in Oracle Database 12c Release 1 (12.1)


Shadab Khan said...

Hi,
On oracle 12c compatible 12.0.0.0, changed to extended with sysdba privileges.
I can create a table with varchar2(16000) as column now and insert a string > 4000 bytes; but only when connected as sysdba.
When connected as a normal user rather than sysdba, I cannot play with varchar2 >4000 bytes, an error ORA-60019 is thrown. Can you explain why?

Please reply to shadabzmail@gmail.com

Tim... said...

Hi.

Have you done the correct setup for the type of database you are using? The article states there is a different procedure depending on the style of database you use, but it only gives one example. Make sure you have done the right thing.

Cheers

Tim...

Lasse Jenssen said...

Had to this to set MAX_STRING_SIZE:
> Sqlplus “/as sysdba”
SQL> alter system set max_string_size=EXTENDED scope=spfile;
SQL> shutdown immediate;
SQL> startup upgrade;
SQL> alter pluggable database all open upgrade;
SQL> @?/rdbms/admin/utl32k.sql
SQL> alter session set container=orcl;
SQL> @?/rdbms/admin/utl32k.sql
SQL> alter session set container=cdb$root;
SQL> shutdown immediate;

Aaron said...

Hi Tim,

I followed your article, but failed half way when executing utl32k.sql.

Now I am not able to start up oracle in either normal or upgrade mode:

SQL> startup upgrade
ORA-00406: COMPATIBLE parameter needs to be 12.0.0.0.0 or greater
ORA-00722: Feature "extended character data types"

I have 12.1.0.2.0 on CentOS 6.7.
Your help will be highly appreciated

Tim... said...

Hi.

As the error suggests, you seem to have a COMPATIBLE parameter set to something lower than 12.0.0.0.0. Manually set it in your SPFILE and try again.

SHUTDOWN IMMEDIATE;
CREATE PFILE = '/tmp/pfile.txt' FROM SPFILE;
-- Edit the /tmp/pfile.txt
CREATE SPFILE FROM PFILE = '/tmp/pfile.txt';
STARTUP UPGRADE;

Cheers

Tim...

Aaron said...

That helped a lot :=)

Chris Saxon said...

Hi Tim,

Just a note to say extended data types are a requirement for default collations (case-insensitive searching) in 12.2:

http://docs.oracle.com/database/122/LNPLS/DEFAULT-COLLATION-clause.htm#BEGIN

Perhaps worth mentioning in thoughts about whether to use this or not.

Tim... said...

Hi.

Good call. I've added add it. Thanks. :)

Cheers

Tim...

Patrick Jolliffe said...

Tim,
You say:" Extended data types are the default from Oracle database 12cR2 onward,"
This seems to conflict with the documentation, and my testing:
https://docs.oracle.com/database/122/REFRN/MAX_STRING_SIZE.htm#REFRN10321
Regards
Patrick

Tim... said...

Hi.

You are correct. The 12.2 update was written when Database Cloud Service 12.2 was released, which has it on by default. It seems Oracle back-tracked for the on-prem release. I've altered the text accordingly. Thanks for pointing it out. :)

Cheers

Tim...

Tomasz Ziss said...

Hi,
Thanks for very nice article.
During my tests I found that implicit LOBs are stored In-Row.
LOB type (BasicFile vs SecureFile) is depended on value of db_securefile. When columns with extended data types are created Oracle doesn't ignore LOB type setting, but rather follows it.
My version is 12.1.0.2.0 with multitenant.
Maybe this is something Oracle added with patchset....



Tim... said...

Hi.

They are only stored inline if they are not considered extended. For example, a VARCHAR2(4000) or less is stored inline. Above that it is stored out of line.

The result you get will depend very much on the type of tests you did. :)

Cheers

Tim...

Hristiyan said...

Hello,
You may add a step for purging recycle bin before starting utl32k.sql. Otherwise it may fail. Excellent article, as always.
Regards,
Hristiyan

Tim... said...

Hi.

I've never had problems with this, but it sounds like a good idea. I've added it to the article and given you a shout out. :)

Cheers

Tim...

Petar Spasov said...

Hi Tim,

I was trying to enable the extended data types today and hit a bug (Doc ID 2092248.1) .The problem was with one of datapumps AQ tables (KUPC$DATAPUMP_QUETAB*) . Could not progress further as we needed to patch. And this is an exadata with around 20 DBs running from the same home. May be it is good to mention it in the article.

Regards,
Petar

Tim... said...

Hi.

Thanks for the heads-up. I added a mention of it to the bottom of the article. :)

Cheers

Tim...

Jwaleet said...

Hi Tim,
Excellent article, thank you for the purge recycle bin step which is not in Doc ID 1570297.1.
One more catch that we faced while executing utl32k.sql
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big
ORA-06512: at line 121
Had to raise a Sev 1 with Oracle.
Resolution: An MV had to be dropped to revive the DB. :)

Cheers,
Jwaleet

Tim... said...

Hi.

Sorry for the delay. I added a mention of this in the bugs section.

Cheers

Tim...

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.