Shadab Khan said...Hi,
On oracle 12c compatible 22.214.171.124, 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 firstname.lastname@example.org
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.
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> alter session set container=orcl;
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 126.96.36.199.0 or greater
ORA-00722: Feature "extended character data types"
I have 188.8.131.52.0 on CentOS 6.7.
Your help will be highly appreciated
As the error suggests, you seem to have a COMPATIBLE parameter set to something lower than 184.108.40.206.0. Manually set it in your SPFILE and try again.
CREATE PFILE = '/tmp/pfile.txt' FROM SPFILE;
-- Edit the /tmp/pfile.txt
CREATE SPFILE FROM PFILE = '/tmp/pfile.txt';
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:
Perhaps worth mentioning in thoughts about whether to use this or not.
Good call. I've added add it. Thanks. :)
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:
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. :)
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 220.127.116.11.0 with multitenant.
Maybe this is something Oracle added with patchset....
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. :)
You may add a step for purging recycle bin before starting utl32k.sql. Otherwise it may fail. Excellent article, as always.
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. :)
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.
Thanks for the heads-up. I added a mention of it to the bottom of the article. :)
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. :)
Sorry for the delay. I added a mention of this in the bugs section.
Greg said...You should avoid SHUTDOWN IMMEDIATE after running the utl32k.sql script. You need all of the transactions to complete or you will get a message relating to the conversion not being done. Just use SHUTDOWN and be patient.
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.