Re: Can ARSystem database - invalid views be deleted?
Yes, I went around and around with support on that, because it does something nasty in SQL Server, which prevents a database from being 'migrated' from 2000 to 2005 using the 'migrate database wiard', we had to use 'dump/restore' :( to move the database. It all involved the SySDEPENDS, in where as you can imagine, you have something like this: Table-1 Table-2 View-of-Form-1 -- depends upon Table-1 View-of-Form-2 -- depends upon Table-2 Join of Table-1 Table-2 results in: Joined-View-of-1-2 -- Depends upon View-of-Form-1 View-of-Form-2 Well, all is fine, as long as you built them in sequence... Now you UPDATE Table-1 or Table-2, and the Depends (Joined-View-of-1-2) is now corrupted in SYSDEPENDS, because of the object_reference_identifier for 'View-of-Form-(1)2' got changed because of the 'drop view X;commit;create view X;commit;' sequence. The answer was not all databases support the Create or Replace View X(values) (values)' statement... needless to say, I'm not thrilled with this operation, but we get what we get at times :( HTH Robert On Feb 12, 2008 10:05 AM, Grooms, Frederick W [EMAIL PROTECTED] wrote: ** Because Remedy drops then creates views (instead of using Create or Replace) when modifying a form, Join form views will sometimes show as Invalid. When you try to access data from those views Oracle will automatically compile them (at least it always has for me). Since I use TOAD to access my Oacle DB I use the menu item of Recompile all invalid objects to clean up these. An Invalid view can mean that something as simple as a View or Table that the join is based on has changed. The change may not have affected the view data. Robert's select top 1 * from view should work or so should select * from view where rownum = 1 Fred -- *From:* Action Request System discussion list(ARSList) [mailto: [EMAIL PROTECTED] *On Behalf Of *Robert Molenda *Sent:* Tuesday, February 12, 2008 9:35 AM *To:* arslist@ARSLIST.ORG *Subject:* Re: Can ARSystem database - invalid views be deleted? ** Maybe - maybe Not! First, does the form still exist in ARS? (CFG_SERVICE_CATALOG_LOOKUP for example), if so, open the form in admin tool, ensure there are no error messages coming up, and 're-save' the form. This will drop (yes drop) and Rebuild the view. Then it should be OK. If you attempt to 'select top 1 * from view' what type of error does Oracle vice back? HTH Robert Molenda On Feb 11, 2008 10:40 PM, Prashant Patil [EMAIL PROTECTED] wrote: ** Hi All, Solaris 5.10 Oracle 10.2 ARSystem 7.1 ServiceDesk 7 In the ARSystem database I can see some 100 views with status INVALID. The Created and Last_ddl_time have exactly the same date/time. Is it ok to delete these invalid views? Is it possible that some remedy workflow has not been activated and later may be required? There is an audit being carried out and we have been informed that invalid views should not exist in the db! Please advise. Example entries are: OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS ARADMIN CFG_SERVICE_CATALOG_LOOKUP VIEW 01/10/2008 14:26:20 01/10/2008 14:26:20 INVALID ARADMIN B672 VIEW 01/10/2008 14:26:20 01/10/2008 14:26:20 INVALID ARADMIN BMC_CORE_BMC_RACK VIEW 11/29/2007 10:16:20 11/29/2007 10:16:20 INVALID Thanks, Prashant __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Can ARSystem database - invalid views be deleted?
Because Remedy drops then creates views (instead of using Create or Replace) when modifying a form, Join form views will sometimes show as Invalid. When you try to access data from those views Oracle will automatically compile them (at least it always has for me). Since I use TOAD to access my Oacle DB I use the menu item of Recompile all invalid objects to clean up these. An Invalid view can mean that something as simple as a View or Table that the join is based on has changed. The change may not have affected the view data. Robert's select top 1 * from view should work or so should select * from view where rownum = 1 Fred From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Robert Molenda Sent: Tuesday, February 12, 2008 9:35 AM To: arslist@ARSLIST.ORG Subject: Re: Can ARSystem database - invalid views be deleted? ** Maybe - maybe Not! First, does the form still exist in ARS? (CFG_SERVICE_CATALOG_LOOKUP for example), if so, open the form in admin tool, ensure there are no error messages coming up, and 're-save' the form. This will drop (yes drop) and Rebuild the view. Then it should be OK. If you attempt to 'select top 1 * from view' what type of error does Oracle vice back? HTH Robert Molenda On Feb 11, 2008 10:40 PM, Prashant Patil [EMAIL PROTECTED] wrote: ** Hi All, Solaris 5.10 Oracle 10.2 ARSystem 7.1 ServiceDesk 7 In the ARSystem database I can see some 100 views with status INVALID. The Created and Last_ddl_time have exactly the same date/time. Is it ok to delete these invalid views? Is it possible that some remedy workflow has not been activated and later may be required? There is an audit being carried out and we have been informed that invalid views should not exist in the db! Please advise. Example entries are: OWNEROBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS ARADMIN CFG_SERVICE_CATALOG_LOOKUP VIEW01/10/2008 14:26:20 01/10/2008 14:26:20 INVALID ARADMIN B672VIEW01/10/2008 14:26:20 01/10/2008 14:26:20 INVALID ARADMIN BMC_CORE_BMC_RACK VIEW11/29/2007 10:16:20 11/29/2007 10:16:20 INVALID Thanks, Prashant ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Fw: Can ARSystem database - invalid views be deleted?
I send that too fast. dbms_utility.compile_schema will compile triggers, functions, packages and procedures -- but not views. You can use 'alter view view name compile;' to compile invalid views. Google it, there are some good scripts that will find walk the dependencies,find invalid views and compile them. A good one is by Solomon Yakobson. -- Tony Worthington Sr. Technical Analyst Kohl's Department Stores [EMAIL PROTECTED] 262-703-5911 - Forwarded by Tony Worthington/Corp/Kohls on 02/12/2008 08:34 AM - Tony Worthington/Corp/Kohls 02/12/2008 08:22 AM To arslist@ARSLIST.ORG cc Subject Re: Can ARSystem database - invalid views be deleted? Have you tried recompiling those invalid views? I would not recommend dropping them. SQL exec dbms_utility.compile_schema(schema = 'schema_name_goes_here'); PL/SQL procedure successfully completed. SQL -- Tony Worthington Sr. Technical Analyst Kohl's Department Stores [EMAIL PROTECTED] 262-703-5911 Prashant Patil [EMAIL PROTECTED] Sent by: Action Request System discussion list(ARSList) arslist@ARSLIST.ORG 02/12/2008 12:40 AM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Can ARSystem database - invalid views be deleted? ** Hi All, Solaris 5.10 Oracle 10.2 ARSystem 7.1 ServiceDesk 7 In the ARSystem database I can see some 100 views with status INVALID. The Created and Last_ddl_time have exactly the same date/time. Is it ok to delete these invalid views? Is it possible that some remedy workflow has not been activated and later may be required? There is an audit being carried out and we have been informed that invalid views should not exist in the db! Please advise. Example entries are: OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS ARADMIN CFG_SERVICE_CATALOG_LOOKUP VIEW 01/10/2008 14:26:20 01/10/2008 14:26:20 INVALID ARADMIN B672 VIEW 01/10/2008 14:26:20 01/10/2008 14:26:20 INVALID ARADMIN BMC_CORE_BMC_RACK VIEW 11/29/2007 10:16:20 11/29/2007 10:16:20 INVALID Thanks, Prashant __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ CONFIDENTIALITY NOTICE: This is a transmission from Kohl's Department Stores, Inc. and may contain information which is confidential and proprietary. If you are not the addressee, any disclosure, copying or distribution or use of the contents of this message is expressly prohibited. If you have received this transmission in error, please destroy it and notify us immediately at 262-703-7000. CAUTION: Internet and e-mail communications are Kohl's property and Kohl's reserves the right to retrieve and read any message created, sent and received. Kohl's reserves the right to monitor messages by authorized Kohl's Associates at any time without any further consent. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Can ARSystem database - invalid views be deleted?
Have you tried recompiling those invalid views? I would not recommend dropping them. SQL exec dbms_utility.compile_schema(schema = 'schema_name_goes_here'); PL/SQL procedure successfully completed. SQL -- Tony Worthington Sr. Technical Analyst Kohl's Department Stores [EMAIL PROTECTED] 262-703-5911 Prashant Patil [EMAIL PROTECTED] Sent by: Action Request System discussion list(ARSList) arslist@ARSLIST.ORG 02/12/2008 12:40 AM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Can ARSystem database - invalid views be deleted? ** Hi All, Solaris 5.10 Oracle 10.2 ARSystem 7.1 ServiceDesk 7 In the ARSystem database I can see some 100 views with status INVALID. The Created and Last_ddl_time have exactly the same date/time. Is it ok to delete these invalid views? Is it possible that some remedy workflow has not been activated and later may be required? There is an audit being carried out and we have been informed that invalid views should not exist in the db! Please advise. Example entries are: OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS ARADMIN CFG_SERVICE_CATALOG_LOOKUP VIEW 01/10/2008 14:26:20 01/10/2008 14:26:20 INVALID ARADMIN B672 VIEW 01/10/2008 14:26:20 01/10/2008 14:26:20 INVALID ARADMIN BMC_CORE_BMC_RACK VIEW 11/29/2007 10:16:20 11/29/2007 10:16:20 INVALID Thanks, Prashant __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ CONFIDENTIALITY NOTICE: This is a transmission from Kohl's Department Stores, Inc. and may contain information which is confidential and proprietary. If you are not the addressee, any disclosure, copying or distribution or use of the contents of this message is expressly prohibited. If you have received this transmission in error, please destroy it and notify us immediately at 262-703-7000. CAUTION: Internet and e-mail communications are Kohl's property and Kohl's reserves the right to retrieve and read any message created, sent and received. Kohl's reserves the right to monitor messages by authorized Kohl's Associates at any time without any further consent. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Can ARSystem database - invalid views be deleted?
Maybe - maybe Not! First, does the form still exist in ARS? (CFG_SERVICE_CATALOG_LOOKUP for example), if so, open the form in admin tool, ensure there are no error messages coming up, and 're-save' the form. This will drop (yes drop) and Rebuild the view. Then it should be OK. If you attempt to 'select top 1 * from view' what type of error does Oracle vice back? HTH Robert Molenda On Feb 11, 2008 10:40 PM, Prashant Patil [EMAIL PROTECTED] wrote: ** Hi All, Solaris 5.10 Oracle 10.2 ARSystem 7.1 ServiceDesk 7 In the ARSystem database I can see some 100 views with status INVALID. The Created and Last_ddl_time have exactly the same date/time. Is it ok to delete these invalid views? Is it possible that some remedy workflow has not been activated and later may be required? There is an audit being carried out and we have been informed that invalid views should not exist in the db! Please advise. Example entries are: OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS ARADMIN CFG_SERVICE_CATALOG_LOOKUP VIEW 01/10/2008 14:26:20 01/10/2008 14:26:20 INVALID ARADMIN B672 VIEW 01/10/2008 14:26:20 01/10/2008 14:26:20 INVALID ARADMIN BMC_CORE_BMC_RACK VIEW 11/29/2007 10:16:20 11/29/2007 10:16:20 INVALID Thanks, Prashant __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Can ARSystem database - invalid views be deleted?
Prashant, Is it possible that there there are missing fields in the underlying table that the view belongs to in their select statement?? Might be worth checking.. Where these created during the install of something? What was it? Joe -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Prashant Patil Sent: Tuesday, February 12, 2008 1:41 AM To: arslist@ARSLIST.ORG Subject: Can ARSystem database - invalid views be deleted? ** Hi All, Solaris 5.10 Oracle 10.2 ARSystem 7.1 ServiceDesk 7 In the ARSystem database I can see some 100 views with status INVALID. The Created and Last_ddl_time have exactly the same date/time. Is it ok to delete these invalid views? Is it possible that some remedy workflow has not been activated and later may be required? There is an audit being carried out and we have been informed that invalid views should not exist in the db! Please advise. Example entries are: OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS ARADMIN CFG_SERVICE_CATALOG_LOOKUP VIEW 01/10/2008 14:26:20 01/10/2008 14:26:20 INVALID ARADMIN B672 VIEW 01/10/2008 14:26:20 01/10/2008 14:26:20 INVALID ARADMIN BMC_CORE_BMC_RACK VIEW 11/29/2007 10:16:20 11/29/2007 10:16:20 INVALID Thanks, Prashant No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.516 / Virus Database: 269.20.2/1272 - Release Date: 2/11/2008 5:28 PM ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Can ARSystem database - invalid views be deleted?
Hi All, Solaris 5.10 Oracle 10.2 ARSystem 7.1 ServiceDesk 7 In the ARSystem database I can see some 100 views with status INVALID. The Created and Last_ddl_time have exactly the same date/time. Is it ok to delete these invalid views? Is it possible that some remedy workflow has not been activated and later may be required? There is an audit being carried out and we have been informed that invalid views should not exist in the db! Please advise. Example entries are: OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS ARADMIN CFG_SERVICE_CATALOG_LOOKUP VIEW 01/10/2008 14:26:20 01/10/2008 14:26:20 INVALID ARADMIN B672 VIEW 01/10/2008 14:26:20 01/10/2008 14:26:20 INVALID ARADMIN BMC_CORE_BMC_RACK VIEW 11/29/2007 10:16:20 11/29/2007 10:16:20 INVALID Thanks, Prashant ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
