Thanks for the piece of code, it works. However, I am running into
another problem. My original goal was to be able to tranfer data from
one table to another using this type of naming. For example
I tried extending your code by adding " select as * from another_table."
It doesn't work. Technically you can say that I simply populate the
table after it has been created. However, this is not a solution because
I am not able to do the following:
You have the added bonus that with CTAS some constraints (i.e. null constraints) are maintained. And, especially, that the table is populated using 'direct write' mode, which is very fast since Oracle will insert the blocks directly into the datafile, without using the buffer cache (very good for latches too).
Since it seems that you are making this copy for backup, you may also consider packing your rows to the most, using a pctfree 1:
And you may also add a storage clause to set pctincrease to zero, the size of the initial and next extent ... you could also make the copy and/or the select in parallel if correct to do so (i.e. there's not much activity on your system).
By the way: your insert works, you have only made a mistake, try changing the statement by including the column names, and correct some typos: