Stupid issue in accessing tables !!

Stupid me.. !! And I guess thats how I learn new things... !!

I got a request from our performance team that we have to move tables from default tablespace to new tablespace to improve performance.

So I did following

1. Created a duplicate tables
2. Preserved data by "insert into temp select * from main"
3. generated DDL with grants,aliases etc.
4. change the tablespace name in DDL
5. Drop main tables
6. Created new tablew with modified DDL containing new tablespace
7. Insert from temp to new table
8. runstats

Now , apps people came into pictures. Complain was that they can not access the table...!!

hmmm??? I was confused and could not find any visible issue with any of the grants !!

And finaly after 1 hour of excersice I figured out that when these tables were created by a content management product using application userid so that ID was the definer of those tables and did not need any grants.

I recreated these tables with dbadmin ID and so definer was dbadmin, not that application user.

So, what I did was just to recreate those tables using app userid and BINGOOO !!

Apps people were happy and I am happy....!!

Now I should go and eat something !!

C u all later,

Views: 51


You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Comment by Bilal Abdullah on October 20, 2011 at 4:14am
Nice experience Darshan.. plz keep on posting with your findings.
Comment by fuangwith on August 5, 2009 at 10:31pm

Featured Downloads

Try BLU Acceleration on Cloud

© 2020   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service