Oracle Magazine, Nov/Dec 2017
Database Developer and DBA ETL SQL alter table customers shrink space ERROR at line 1 ORA 10631 SHRINK clause should not be specified for this object As in the airline metaphor two seemingly unrelated features function based indexes and TABLE SHRINK SPACE clash when combined In this case the side effect is a restriction on the SHRINK SPACE functionality it cannot be performed if a function based index is in place Whenever you are using any feature in Oracle Database it is critical that you perform integration testing across your entire application including the maintenance activities of the DBA COLUMN COLLATION IN ORACLE DATABASE 12c RELEASE 2 Integration testing would have revealed the conflict between function based indexes and SHRINK SPACE but that does not solve the underlying question how to handle mixed case data in a performant way without resorting to additional indexes or a large refactoring effort Oracle Database 12c Release 2 includes the new column level collation feature to help developers tackle this issue With column level collation it is now possible to specify table columns as case insensitive Returning to the example the CUSTOMERS table is now re created with the CUST_ NAME column using the new COLLATE clause and with a single index on the CUST_ NAME column ORACLE MAGAZINE NOVEMBER DECEMBER 2017 81 SQL CREATE TABLE CUSTOMERS 2 3 COUNTRY VARCHAR2 128
You must have JavaScript enabled to view digital editions.