I'm trying to introduce new audit tables for our project. In the deployment process we use redgate's Schema Compare for Oracle (version 4.0.8.420).
The table looks something like this:
create table MY_SCHEMA.MY_TEST_AUDT (
TEST_ID NUMBER(19),
-- all sorts of business fields, omitted for clarity
AUDT_CRT_DTM TIMESTAMP DEFAULT SYSTIMESTAMP,
AUDT_ACTN_CODE VARCHAR2(1),
AUDT_CRT_USR_NM VARCHAR2(128) DEFAULT USER,
AUDT_CLIENT_IDENTIFIER VARCHAR2(256),
AUDT_CLIENT_INFO VARCHAR2(256)
)
TABLESPACE MY_TABLESPACE
PCTFREE 0
INITRANS 10
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
COMPRESS FOR OLTP
NOCACHE
PARTITION BY RANGE (AUDT_CRT_DTM)
INTERVAL(interval '1' month)
(
PARTITION P0 VALUES LESS THAN (date '2018-11-01')
PCTFREE 0
INITRANS 10
)
/
The first time I ran it I got an error concerning the storage clause
Parsing failed with message SyntaxError. Unexpected token 'K'
When I got rid of the storage clause (since I can use the defaults) it started complaining about the partitioning clause and that's where I am not very happy with the software.
Parsing failed with message SyntaxError. Unexpected token 'PARTITION' (Line 35, Col 1) symbol Id
I tried turning all the storage options on and off, nothing worked. I tried the latest version 5.2 with a simple compare of files and it didn't work either. I tried to post it on the redgate forums and my post has been stuck as drafted for two days now.
I'm using the scripts folder comparison, the above mentioned file for source and no file for the target, Oracle 11g scripts.
I have managed to get it working without the partition. I had to replace the slash with a semicolon and switch the 8K and 1M to the full values. But I'm still not able to create partitions.
create table MY_SCHEMA.MY_TEST_AUDT (
TEST_ID NUMBER(19),
-- all sorts of business fields, omitted for clarity
AUDT_CRT_DTM TIMESTAMP DEFAULT SYSTIMESTAMP,
AUDT_ACTN_CODE VARCHAR2(1),
AUDT_CRT_USR_NM VARCHAR2(128) DEFAULT USER,
AUDT_CLIENT_IDENTIFIER VARCHAR2(256),
AUDT_CLIENT_INFO VARCHAR2(256)
)
TABLESPACE MY_TABLESPACE
PCTFREE 0
INITRANS 10
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
COMPRESS FOR OLTP
NOCACHE;
Any help is very much appreciated.
Alain
For completeness here's my DatabaseInformation.xml file
<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<ScriptsFolderInformation version="2" type="ScriptsFolderInformation">
<DatabaseVersion>ElevenG</DatabaseVersion>
</ScriptsFolderInformation>
I asked the redgate support and got a reply.
It seems to work just fine when you work with the schemas directly (haven't tried it myself). The problem only happens when you do the scripts folder to scripts folder comparison.
To get the partition working you have to drop the NOCACHE keyword. Then everything is working.
Redgate now has a bug report for the support of those keywords (OC-1026)
Here's the version that works:
create table MY_SCHEMA.MY_TEST_AUDT (
TEST_ID NUMBER(19),
-- all sorts of business fields, omitted for clarity
AUDT_CRT_DTM TIMESTAMP DEFAULT SYSTIMESTAMP,
AUDT_ACTN_CODE VARCHAR2(1),
AUDT_CRT_USR_NM VARCHAR2(128) DEFAULT USER,
AUDT_CLIENT_IDENTIFIER VARCHAR2(256),
AUDT_CLIENT_INFO VARCHAR2(256)
)
TABLESPACE MY_TABLESPACE
PCTFREE 0
INITRANS 10
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
COMPRESS FOR OLTP;