Return
-
Title
Executing a table script created using 'Generate SQL | Creation Script' results in error "ORA-02143: Invalid STORAGE option" -
Description
The following table is created:
CREATE TABLE TEST_STO(COL1 VARCHAR2(32 CHAR) NOT NULL,COL2 VARCHAR2(4 CHAR) NOT NULL,COL3 VARCHAR2(4 CHAR) NOT NULL,COL4 DATE NOT NULL)TABLESPACE USERSPCTUSED 0PCTFREE 10INITRANS 1MAXTRANS 255STORAGE (INITIAL 112KNEXT 128KMINEXTENTS 1MAXEXTENTS UNLIMITEDPCTINCREASE 0BUFFER_POOL DEFAULTFLASH_CACHE DEFAULTCELL_FLASH_CACHE DEFAULT)LOGGINGNOCOMPRESSNOCACHERESULT_CACHE (MODE DEFAULT)NOPARALLELMONITORINGENABLE ROW MOVEMENT;When using Generate SQL | Create Script, the following script is generated:CREATE TABLE "USER"."TEST_STO"("COL1" VARCHAR2(32 CHAR) NOT NULL ENABLE,"COL2" VARCHAR2(4 CHAR) NOT NULL ENABLE,"COL3" VARCHAR2(4 CHAR) NOT NULL ENABLE,"COL4" DATE NOT NULL ENABLE ) SEGMENT CREATION DEFERREDPCTFREE 10 PCTUSED 0INITRANS 1MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 114688NEXT 131072MINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0BUFFER_POOLDEFAULT FLASH_CACHEDEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" ENABLE ROW MOVEMENTALTER TABLE "SUSAN"."TEST_STO" MODIFY ("COL4" NOT NULL ENABLE)ALTER TABLE "SUSAN"."TEST_STO" MODIFY ("COL3" NOT NULL ENABLE)ALTER TABLE "SUSAN"."TEST_STO" MODIFY ("COL2" NOT NULL ENABLE)ALTER TABLE "SUSAN"."TEST_STO" MODIFY ("COL1" NOT NULL ENABLE);When script is run, it fails with "ORA-02143: Invalid STORAGE option" -
Cause
- DEFAULT_FLACHE_CACHE and DEFAULT_CELL_FLACHE_CACHE are being split over multiple lines at the underscore
- ALTER TABLE statements are being added when they are not needed
-
Resolution
WORKAROUND
- Edit the script to put DEFAULT_FLACHE_CACHE and DEFAULT_CELL_FLACHE_CACHE on the same line
- Remove the ALTER TABLE statements
STATUS
Waiting for fix in a future release of Toad Data Point
-
Defect ID
QAT-8466