{"id":235,"date":"2018-12-11T11:27:49","date_gmt":"2018-12-11T10:27:49","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/?p=235"},"modified":"2018-12-11T15:59:23","modified_gmt":"2018-12-11T14:59:23","slug":"find-nosegment-indexes-that-you-forgot-to-delete","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2018\/12\/11\/find-nosegment-indexes-that-you-forgot-to-delete\/","title":{"rendered":"Find NOSEGMENT indexes that you forgot to delete"},"content":{"rendered":"<p>Err is human and I, as human, I do forget sometimes to clean-up some tests.<\/p>\n<p>Recently this happen with NOSEGMENT indexes. Yesterday I received an email from a colleague, reporting some errors he received with Datapump:<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nORA-08114: can not alter a fake index\r\nFailing sql is:\r\nALTER INDEX &quot;SUPERAPP&quot;.&quot;IDX_POSITION_TEST&quot; NOPARALLEL\r\nORA-39083: Object type INDEX failed to create with error:\r\nORA-08114: can not alter a fake index\r\n<\/pre>\n<p>The indexes created with NOSEGMENT do not appear in DBA_INDEXES (don&#8217;t get fool by the SEGMENT_CREATED column).<br \/>\nThey do appear on DBA_OBJECTS, but there they look like any other index.<\/p>\n<p>The solution is to match the two tables. So to find the NOSEGMENT indexes that you forgot to delete you can use the following query:<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nSELECT 'DROP INDEX '||owner||'.'||object_name||';' cmd, created FROM dba_objects \r\nWHERE object_type='INDEX' \r\nAND object_name NOT IN (SELECT index_name FROM  dba_indexes);\r\n<\/pre>\n<p>If you wonder what are NOSEGMENT indexes, a good source is <a href=\"https:\/\/oracle-base.com\/articles\/misc\/virtual-indexes\" target=\"_blank\" rel=\"noopener\">Oracle-Base<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Err is human and I, as human, I do forget sometimes to clean-up some tests. Recently this happen with NOSEGMENT indexes. Yesterday I received an email from a colleague, reporting some errors he received with Datapump: ORA-08114: can not alter a fake index Failing sql is: ALTER INDEX &quot;SUPERAPP&quot;.&quot;IDX_POSITION_TEST&quot; NOPARALLEL ORA-39083: Object type INDEX failed [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":{"0":"post-235","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-oracle","7":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/235","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=235"}],"version-history":[{"count":4,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/235\/revisions"}],"predecessor-version":[{"id":245,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/235\/revisions\/245"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=235"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=235"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=235"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}