Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Reg: Index - Gathering statistics v/s Rebuild -

915396Jun 29 2015 — edited Jul 13 2015

Hi Experts,

I have a conceptual doubt on Index statistics - when to gather the stats, and when to rebuild it?

Scenario -

We have say 10 (big) tables which we archive it to our archive tables, in a period of every 4-5 months. We archive 70% of the data keeping only latest (dated) 30% in it.

The technique we use is using - use CTAS for temp tables, truncate the actual table, reinsert the data back and drop the temp table.

After this I am doing gathering the table statistics.

Now, the doubt is, shall I gather the index stats (manually), or, rebuild the index?

Could you please give me some pointer to understand when the index rebuild should be performed? What will be the pro/con ?

p.s. I've never done index rebuild before.

Thanks,

-Ranit

(on Oracle 10.2.0.4.0)

Comments

DK2010

Hi,

It means  some session accessing that table. you can Query from v$access

Find that sid and kill that sid  and drop the table

HTH

unknown-698157
Answer

For sure there are locks, as the table is in use.

Drop table wants an exclusive lock on the corresponding dictionary record(s).

Whenever the table is in use there will be a row-share lock on that record in the dictionary.

Did you consult gv$access ?

Please do so now.

-------------

Sybrand Bakker

Senior Oracle DBA

Marked as Answer by User_KWXZ9 · Sep 27 2020
1 person found this helpful
Niket Kumar

http://abeytom.blogspot.in/2012/08/finding-and-fixing-ora-00054-resource.html

User_KWXZ9

Sybrand ,

Thanks alot for your help . gv$access resolved the issue .

Regards

DBApps

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 10 2015
Added on Jun 29 2015
13 comments
15,634 views