SYMPTOM
In some environments where the data warehouse is in Oracle, database administrators may prefer to use Global Temporary Tables for Strategy intermediate tables. (By default, the Strategy SQL Generation Engine creates standard tables with the NOLOGGING option.) The use of Global Temporary Tables can reduce administrative overhead on the database side.
However, in Strategy SQL Generation Engine versions prior to 9.0.0, reports using Oracle's create Global Temporary Table syntax would fail at the end of report execution with the following Oracle error:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
CAUSE
This error occurs because Oracle does not allow Global Temporary Tables to be dropped unless they are empty.
Strategy SQL Generation Engine 8.x does not have an option to issue "truncate table" statements prior to the "drop table" statements issued at the end of Strategy report execution. In some limited cases where the number of intermediate tables is small, it was possible to work around this issue by adding truncate table statements into the Report Post Statement VLDB Properties. This approach is difficult for general use because it is not always possible to predict the number of intermediate tables in a report.
ACTION
Strategy SQL Generation Engine 9.x introduces a third option to the VLDB Property "Drop Temp Table Method." In Strategy SQL Generation Engine 8.x, the only options were to drop tables or do nothing. In the latter case, intermediate tables would be left on the database; administrators could remove them using a database-side scheduled task.
The new option in Strategy SQL Generation Engine 9.x issues two statements at the end of the report for each intermediate table: TRUNCATE TABLE, then DROP TABLE.
Using the "Drop after final pass" option, a report with two intermediate tables would issue the following statements after retrieving the report results:
drop table ZZMD00
drop table ZZMD01
Using the "Truncate table, then drop after final pass" option, the same report issues four statements at the end:
truncate table ZZMD00
drop table ZZMD00
truncate table ZZMD01
drop table ZZMD01
With this alteration, Oracle Global Temporary Tables will be empty when the drop table statement is issued and the ORA-14452 error should no longer be received.
For details on VLDB Property settings to use Oracle "Create Global Temporary Table" syntax, consult the following Strategy Knowledgebase article.
