| Invalid package and synonym [message #607301] |
Tue, 04 February 2014 15:28  |
 |
anncao
Messages: 87
Registered: August 2013
|
Member |
|
|
I have a package that only has one stored procedure, I compiled it without errors. And then second day when I login in again, I see there is a red X next to the package body, I know it means invalid, but what is invalid, I recompile it, it compiles fine and the red X disappear, I know it will show up some later time, but don't know why.
I am using oracle sql developer.
Then I checked the underlying table the stored procedure is using, that I created a couple of days ago. The table is fine, but when I query select * from dba_objects where object_name ='mytablename', I login as schema PS
I found something like below:
owner Object_name Object_type Status
PS mytablename TABLE VALID
MGMT mytablename SYNONYM INVALID
I wonder why the SYNONYM is Invalid, how can I fix it? I assume it is because of this my package showed red X.
Thanks
|
|
|
|
|
|
| Re: Invalid package and synonym [message #607303 is a reply to message #607302] |
Tue, 04 February 2014 15:48  |
|
anncao
Messages: 87
Registered: August 2013
|
Member |
|
|
Thanks, I have my tables all uppercase. Sorry about the typing.
So after I create a table, Does the MGMT user automatically have a SYNONYM for MYTABLE, at least I see so by doing the query?
What should be the correct step I will do to let the synonyms to become valid, since I login using PS account, and I created the table, I just tried to grant select to public, it runs fine, but still by doing above query, the MGMT user still have SYNONYM invalid.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Invalid package and synonym [message #607314 is a reply to message #607313] |
Tue, 04 February 2014 18:05 |
|
anncao
Messages: 87
Registered: August 2013
|
Member |
|
|
Thanks, is there an easy way to find out the dependent objects for the package, or I have to check manually in the package what objects it used?
Also, you are right, I found out there is another table used by the package, I guess there is no compilation date for a table, but after I select * from dba_objects where object_name='MYTABLE', I found out it the created timestamp is yesterday afternoon, it was after I compiled my package,
I guess that was it, the reason why my package has a red X.
Thanks much!
|
|
|
|
|
|
| Re: Invalid package and synonym [message #607316 is a reply to message #607315] |
Tue, 04 February 2014 18:41 |
|
anncao
Messages: 87
Registered: August 2013
|
Member |
|
|
Thanks, I am using oralce sql developer, when I run select * from dbms_utility.get_dependency; it says table or view does not exist.
Also a question about last_DDL_Time, the table I mentioned above that the package is depend on, I know it is populated every night through a job,
but when I check in our production environment, I see the job was created at weekends, but the Last_DDL_time is today's date. But we just populate the table, maybe Truncate table is considered as a ddl too, but how can I know the last compile time of a table? thanks
[Updated on: Tue, 04 February 2014 18:41] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
