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
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,