Avatar of dba1234
dba1234
 asked on

alter table is not working on a particular table

Hi, I have a table with 700000 rows on 9.2.0.8.0 database. i am trying to analyze this table
analyze table tablename estimate statistics  sample 20 percent; This is running forever, and I am not able to alter this table alter table rename, alter table move etc.
I created one temp table by selecting data from this table which is being analyzed in couple of seconds,

Please help me on it.
Oracle Database

Avatar of undefined
Last Comment
dba1234

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
dba1234

ASKER
When I try to alter and analyze it ir running forever.

I got one object locked but that is different object uder different schema.
Sean Stuber

what are the wait events for the session that is taking a long time?

how big is the table?  rows, blocks, bytes?   0 rows could still be terabytes of allocated space
dba1234

ASKER
It is 20 MB table with 700000 rows.

There is just one lock library cache lock
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
dba1234

ASKER
Thanks I was able to alter the table after killing the session who was holding library cache lock