Hi Guys,
Toad 13.0.0.80 x64
I do not know how this happened in latest PROD version, but I am pretty sure that this was not happening before (or at least in some of previous version).
In this case in Toad script are missing part
storage (buffer_pool KEEP)
PCTFREE 0
Steps to see that:
- original script:
CREATE TABLE SOME_TABLE (
SOC CHAR(9 BYTE) NOT NULL,
FINAL_EXPIRATION_DATE DATE,
SOC_ANALYSE_GROUP VARCHAR2(30),
FOR_SALE_IND CHAR(1 BYTE),
SALE_EXP_DATE DATE
)
storage (buffer_pool KEEP)
PCTFREE 0
TABLESPACE BATCH
LOGGING
NOCOMPRESS
CACHE
RESULT_CACHE (MODE DEFAULT)
NOPARALLEL
MONITORING
/
Look in Toad script:
SET DEFINE OFF;
Prompt Table SOME_TABLE;
– SOME_TABLE (Table)
CREATE TABLE SOME_TABLE
(
SOC CHAR(9 BYTE) NOT NULL,
FINAL_EXPIRATION_DATE DATE,
SOC_ANALYSE_GROUP VARCHAR2(30 CHAR),
FOR_SALE_IND CHAR(1 BYTE),
SALE_EXP_DATE DATE
)
TABLESPACE BATCH
LOGGING
NOCOMPRESS
CACHE
RESULT_CACHE (MODE DEFAULT)
NOPARALLEL
MONITORING
/
there is no part for buffer_pool as well as storage.
Oracle metadata:
select dbms_metadata.get_ddl(‘TABLE’,‘SOME_TABLE’,user) from dual;
CREATE TABLE “XXX”.“SOME_TABLE”
( “SOC” CHAR(9) NOT NULL ENABLE,
“FINAL_EXPIRATION_DATE” DATE,
“SOC_ANALYSE_GROUP” VARCHAR2(30 CHAR),
“FOR_SALE_IND” CHAR(1),
“SALE_EXP_DATE” DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL KEEP FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “BATCH”
CACHE
and Toad rebuild script seems to work ok:
CREATE TABLE SOME_TABLE
(
SOC CHAR(9 BYTE) NOT NULL,
FINAL_EXPIRATION_DATE DATE,
SOC_ANALYSE_GROUP VARCHAR2(30 CHAR),
FOR_SALE_IND CHAR(1 BYTE),
SALE_EXP_DATE DATE
)
TABLESPACE BATCH
PCTUSED 40
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL KEEP
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
CACHE
RESULT_CACHE (MODE DEFAULT)
NOPARALLEL
MONITORING;
Once again, apologize if this is normal behavior (as it should be) but then please put in script header that this should not be used for rebuild object.