RE: value from index block or table block

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <grzegorzof@xxxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>
  • Date: Thu, 24 Nov 2011 04:44:47 -0500

This is a good case for
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12';
drop table <table_name>;

to see what is actually going on. Since a drop is actually a long list of
sql's and proc's, something like

--+ gather_plan_statistics

followed by an xplan last query will just throw an error.

Doing separate session traces for a pair of tables (one that you either
create for the purpose of dropping or otherwise don't care about and the
other the "won't drop" table) should give you a nicely different pair of
trace files to look at to see where the wheels fall off. You'll want the
test drop table to be of a similar table type, for example a regular heap
table, a partitioned table, or an iot so the pieces of the drop litany that
are invoked and used are similar.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Grzegorz Goryszewski
Sent: Wednesday, November 23, 2011 1:45 PM
To: jonathan@xxxxxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: value from index block or table block

On 2011-11-23 19:10, Jonathan Lewis wrote:
> It's difficult to say for certain (and may be version dependent), but 
> it's probably getting columns from the index whenever possible.
> Two arguments in favour:
Thanks, that might explain strange behavior of our obj$ dictionary table,
because we are unable to drop one user table .
The reason is (I think)  when index access is used table name is 'corrupted'
(different name) than that in obj$ data block :).
So we are getting object does not exists but when You force full scan on
obj$ , displayed name is proper .



Other related posts: