12c has finally extended some of the native datatypes to allow much longer 'things' to be out in them. Reading the docs VARCHAR/NVARCHAR and RAW datatypes have all been increased up to 32k.
So should you jump in with both feet and use this feature?
I'm not 100% convinced yet - there are some drawbacks with it. The first thing that sets warning bells off is that its a one way street if you do this..... There are also various caveats that it ay break stuff - see the parameter refernce note here http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10321.htm#REFRN10321
If you do want to go down this route the steps are mentioned in the above link - and you just do the following
Shut down the database.
Restart the database in UPGRADE mode.
Change the setting of MAX_STRING_SIZE to EXTENDED.
Run the rdbms/admin/utl32k.sql script. You must be connected AS SYSDBA to run the script.
Restart the database in NORMAL mode.
This produces the output below on a brand new database
SQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 2 20:37:41 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1169227776 bytes
Fixed Size 2680792 bytes
Variable Size 1107298344 bytes
Database Buffers 50331648 bytes
Redo Buffers 8916992 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utl32k
Session altered.
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database has not been opened for UPGRADE.
DOC>
DOC> Perform a "SHUTDOWN ABORT" and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database does not have compatible >= 12.0.0
DOC>
DOC> Set compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
PL/SQL procedure successfully completed.
Session altered.
1215 rows updated.
Commit complete.
System altered.
PL/SQL procedure successfully completed.
Commit complete.
System altered.
Session altered.
PL/SQL procedure successfully completed.
No errors.
Session altered.
PL/SQL procedure successfully completed.
Commit complete.
Package altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2014-02-02 20:38:03
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2014-02-02 20:40:21
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
3
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
3
Function created.
PL/SQL procedure successfully completed.
Function dropped.
Warning: XDB now invalid, could not find xdbconfig
PL/SQL procedure successfully completed.
SQL>
Hmm seems XDB is already broekn for some reason and I didn;t do anything to that - it;s just been created from catproc - anyhow I'll ignore that for now.
Lets do a quick restart and then do a quick test
SQL> create user demo identified by demo;
User created.
SQL> grant dba to demo;
Grant succeeded.
SQL> conn demo/demo
Connected.
SQL> create table demo (col1 number,col2 varchar2(32768));
create table demo (col1 number,col2 varchar2(32768))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
Ok - lets try again with the right size.......
SQL> create table demo (col1 number,col2 varchar2(32767));
Table created.
SQL> desc demo
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL2 VARCHAR2(32767)
Lets insert a couple of rows
SQL> insert into demo values (1,rpad(1,32767,1));
1 row created.
SQL> insert into demo values (1,1);
1 row created.
all looks good
Now lets delete the 'short' one:
SQL> delete from demo where col2=1;
delete from demo where col2=1
*
ERROR at line 1:
ORA-01426: numeric overflow
Hmm interesting - that never used to happen - an implicit dataype conversion was normally done for you - lets try again with it in quotes
SQL> delete from demo where col2='1';
1 row deleted.
SO that's OK now - but one to watch for if you have lazy coders - though i guess they'll find out pretty quickly.
I tried a few random charcater functions to see what happens as i had read that this is internally stored as a lob and some things wont work. I'm happy to report that substr/instr/trim/length/max all look ok as does group by and order by of the new column. stats gathering also seemed fine.
How about creating an index?
SQL> create index x on demo(col2);
create index x on demo(col2)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
Fails - but fair enough - index key length restrictions have been around for a long time.
SO on the whole the feature seems OK, but I don't think I'll be using it any time soon, there are bound to be glitches and featires that dont work because of the increased length so i think we'll wait for 12.1.0.2 at least.
It may also need some time for any drivers accessing the database to catch up and be able to process this increased string size.
*update - a second run of utlrp seemed to compile everything ok and DBA_REGISTRY showed all components were valid
pulcsuesuba Troy Boussad https://wakelet.com/wake/EdayPtuJ7ErzOXZT8S6-y
ReplyDeleteslugpanendpat