Vishnu,
If you quote from the documentation it would be nice if you could supply a URL
as well. Searching for the text you supplied it seems to be the second
sentence of the following paragraph:
=======================================================================
If you need to remove all rows from a table when using DBMS_STATS, use TRUNCATE
instead of dropping and re-creating the same table. When you drop a table,
workload information used by the auto-histogram gathering feature and saved
statistics history used by the RESTORE_*_STATS procedures is lost. Without this
data, these features do not function properly.
=======================================================================
The context is about truncating a table instead of dropping and recreating it.
If you drop a table then execute a CREATE TABLE statement to create a table of
the same name in the same schema then an attempt to "flashback to before drop"
will result in Oracle error:
ERROR at line 1:
ORA-38312: original name is used by an existing object
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Vishnu Potukanuma <vishnupotukanuma@xxxxxxxxx>
Sent: 24 November 2019 16:29
To: Mohamed Houri
Cc: Oracle L
Subject: Re: optimizer uses objects in Recyclebin or not!- Bug?
Now that you mention it houri, this raises a different issue probably unless we
test.. we are not 100% sure whether the statisitics history and/or synoposis
collected will be dropped as well... as per the documentation,.
When you drop a table, workload information used by the auto-histogram
gathering feature and saved statistics history used by the RESTORE_*_STATS
procedures is lost. Without this data, these features do not function properly.
To remove all rows from a table, and to restore these statistics with
DBMS_STATS, use TRUNCATE instead of dropping and re-creating the same table.
Thanks,
Vishnu
On Sun, Nov 24, 2019 at 7:49 PM Mohamed Houri
<mohamed.houri@xxxxxxxxx<mailto:mohamed.houri@xxxxxxxxx>> wrote:
It is not only the index name which is not flashed back but a couple of other
table objects as I explained in this blog post
https://hourim.wordpress.com/2012/11/14/recycle-bin-whats-going-on/
And things become interesting in this context when you are using a SPM
baseline. Dropping and flashing back a table can preempt the CBO from using
that SPM plan if this one uses an index from that dropped & flashed back table
https://hourim.wordpress.com/2014/01/24/sql-plan-management-and-table-flashback/
Bottom line: when you drop and flashback a table, then think about the
following points
1. the foreign key constraints are not flashed back
2. the original index name, the trigger name and constraint name are not
flashed back<https://hourim.wordpress.com/?s=recycle>
3. any SQL plan baseline based on an index created on a table that has been
dropped and flashed back will not be reproducible until you give that index its
original name
But I haven't tested this in recent releases.
Best regards
Mohamed Houri
--
//www.freelists.org/webpage/oracle-l
