{"id":13945,"date":"2020-04-19T21:17:20","date_gmt":"2020-04-19T19:17:20","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/"},"modified":"2020-04-19T21:17:20","modified_gmt":"2020-04-19T19:17:20","slug":"segment-maintenance-online-compress","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/","title":{"rendered":"&#8220;Segment Maintenance Online Compress&#8221; feature usage"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nOn Twitter, Ludovico Caldara mentioned the #licensing #pitfall when using the Online Partition Move with Basic Compression. Those two features are available in Enterprise Edition without additional option, but when used together (moving online a compressed partition) they enable the usage of Advance Compression Option:<\/p>\n<blockquote class=\"twitter-tweet\" data-width=\"500\" data-dnt=\"true\">\n<p lang=\"en\" dir=\"ltr\">Will it be reflected in dba_feature_usage_statistics?<\/p>\n<p>&mdash; Niels Jespersen (@njesp) <a href=\"https:\/\/twitter.com\/njesp\/status\/1251961678020960257?ref_src=twsrc%5Etfw\">April 19, 2020<\/a><\/p><\/blockquote>\n<p><script async src=\"https:\/\/platform.twitter.com\/widgets.js\" charset=\"utf-8\"><\/script><br \/>\nAnd there was a qustion about detection of this feature. I&#8217;ll show how this is detected. Basically, the ALTER TABLE MOVE PARTITION sets the &#8220;fragment was compressed online&#8221; flag in TABPART$ or TABSUBPART$ when the segment was compressed during the online move.<\/p>\n<p>I create a partitioned table:<\/p>\n<pre><code>\nSQL&gt; create table SCOTT.DEMO(id,x) partition by hash(id) partitions 2 as select rownum,lpad('x',100,'x') from xmltable('1 to 1000');\n\nTable created.\n<\/code><\/pre>\n<p>I set basic compression, which does not compress anything yet but only for future direct loads:<\/p>\n<pre><code>\nSQL&gt; alter table SCOTT.DEMO modify partition for (42) compress;\n\nTable altered.\n<\/code><\/pre>\n<p>I move without the &#8216;online&#8217; keyword:<\/p>\n<pre><code>\nSQL&gt; alter table SCOTT.DEMO move partition for (42);\n\nTable altered.\n<\/code><\/pre>\n<p>This does not enable the online compression flag (which is 0x2000000):<\/p>\n<pre><code>\nSQL&gt; select obj#,dataobj#,part#,flags,to_char(flags,'FMXXXXXXXXXXXXX') from SYS.TABPART$ where obj# in ( select object_id from dba_objects where owner='SCOTT' and objec\nt_name='DEMO');\n\n      OBJ#   DATAOBJ#      PART#      FLAGS TO_CHAR(FLAGS,\n---------- ---------- ---------- ---------- --------------\n     75608      75608          1          0 0\n     75609      75610          2         18 12\n<\/code><\/pre>\n<p>The 0x12 is about the presence of statistics (the MOVE does online statistics gathering in 12c).<\/p>\n<pre><code>\nSQL&gt; exec sys.dbms_feature_usage_internal.exec_db_usage_sampling(sysdate)\n\nPL\/SQL procedure successfully completed.\n\nSQL&gt; select name,detected_usages,currently_used,feature_info from dba_feature_usage_statistics where name='Segment Maintenance Online Compress';\n\nNAME                                     DETECTED_USAGES CURRE FEATURE_INFO\n---------------------------------------- --------------- ----- --------------------------------------------------------------------------------\nSegment Maintenance Online Compress                    0 FALSE\n<\/code><\/pre>\n<h3>Online Move of compressed partition<\/h3>\n<p>Now moving online this compressed segment:<\/p>\n<pre><code>\nSQL&gt; alter table SCOTT.DEMO move partition for (42) online;\n\nTable altered.\n<\/code><\/pre>\n<p>This has enabled the 0x2000000 flag:<\/p>\n<pre><code>\nSQL&gt; select obj#,dataobj#,part#,flags,to_char(flags,'FMXXXXXXXXXXXXX') from SYS.TABPART$ where obj# in ( select object_id from dba_objects where owner='SCOTT' and objec\nt_name='DEMO');\n\n      OBJ#   DATAOBJ#      PART#      FLAGS TO_CHAR(FLAGS,\n---------- ---------- ---------- ---------- --------------\n     75608      75608          1          0 0\n     75611      75611          2   33554450 2000012\n<\/code><\/pre>\n<p>And, of course, is logged by the feature usage detection:<\/p>\n<pre><code>\nSQL&gt; exec sys.dbms_feature_usage_internal.exec_db_usage_sampling(sysdate)\n\nPL\/SQL procedure successfully completed.\n\nSQL&gt; select name,detected_usages,currently_used,feature_info from dba_feature_usage_statistics where name='Segment Maintenance Online Compress';\n\nNAME                                     DETECTED_USAGES CURRE FEATURE_INFO\n---------------------------------------- --------------- ----- --------------------------------------------------------------------------------\nSegment Maintenance Online Compress                    1 FALSE Partition Obj# list: 75611:\n<\/code><\/pre>\n<p>The FEATURE_INFO mentions the object_id for the concerned partitions (for the last detection only).<\/p>\n<h3>No Compress<\/h3>\n<p>The only way I know to disable this flag is to uncompress the partition, and this can be done online:<\/p>\n<pre><code>\nSQL&gt; alter table SCOTT.DEMO move partition for (42) nocompress online;\n\nTable altered.\n\nSQL&gt; select obj#,dataobj#,part#,flags,to_char(flags,'FMXXXXXXXXXXXXX') from SYS.TABPART$ where obj# in ( select object_id from dba_objects where owner='SCOTT' and object_name='DEMO');\n\n      OBJ#   DATAOBJ#      PART#      FLAGS TO_CHAR(FLAGS,\n---------- ---------- ---------- ---------- --------------\n     75608      75608          1          0 0\n     75618      75618          2         18 12\n<\/code><\/pre>\n<h3>DBMS_REDEFINITION<\/h3>\n<p>As a workaround, DBMS_REDEFINITION does not use the Advanced Compression Option. For example, this does not enable any flag:<\/p>\n<pre><code>\nSYS@CDB$ROOT&gt;\nSYS@CDB$ROOT&gt; alter table SCOTT.DEMO rename partition for (24) to PART1;\n\nTable altered.\n\nSYS@CDB$ROOT&gt; create table SCOTT.DEMO_X for exchange with table SCOTT.DEMO;\n\nTable created.\n\nSYS@CDB$ROOT&gt; alter table SCOTT.DEMO_X compress;\n\nTable altered.\n\nSYS@CDB$ROOT&gt; exec dbms_redefinition.start_redef_table(uname=&gt;'SCOTT',orig_table=&gt;'DEMO',int_table=&gt;'DEMO_X',part_name=&gt;'PART1',options_flag=&gt;dbms_redefinition.cons_use\n_rowid);\n\nPL\/SQL procedure successfully completed.\n\nSYS@CDB$ROOT&gt; exec dbms_redefinition.finish_redef_table(uname=&gt;'SCOTT',orig_table=&gt;'DEMO',int_table=&gt;'DEMO_X',part_name=&gt;'PART1');\n\nPL\/SQL procedure successfully completed.\n\nSYS@CDB$ROOT&gt; drop table SCOTT.DEMO_X;                                                                                                                        \nTable dropped.\n\n<\/code><\/pre>\n<p>But of course, the difference is that only the blocks that are direct-path inserted into the interim table are compressed. Not the online modifications.<\/p>\n<h3>Only for partitions?<\/h3>\n<p>As far as I know, this is detected only for partitions and subpartitions, the online partition move operation which came in 12cR1. Since 12cR2 we can also move online a non-partitioned table and this, as far as I know, is not detected by dba_feature_usage_statistics. But don&#8217;t count on this as this may be considered as a bug which may be fixed one day.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . On Twitter, Ludovico Caldara mentioned the #licensing #pitfall when using the Online Partition Move with Basic Compression. Those two features are available in Enterprise Edition without additional option, but when used together (moving online a compressed partition) they enable the usage of Advance Compression Option: Will it be reflected in dba_feature_usage_statistics? [&hellip;]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[1908,377,654,96,1909],"type_dbi":[],"class_list":["post-13945","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-compress","tag-licensing","tag-online","tag-oracle","tag-partition"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>&quot;Segment Maintenance Online Compress&quot; feature usage - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"&quot;Segment Maintenance Online Compress&quot; feature usage\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . On Twitter, Ludovico Caldara mentioned the #licensing #pitfall when using the Online Partition Move with Basic Compression. Those two features are available in Enterprise Edition without additional option, but when used together (moving online a compressed partition) they enable the usage of Advance Compression Option: Will it be reflected in dba_feature_usage_statistics? [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-04-19T19:17:20+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/segment-maintenance-online-compress\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/segment-maintenance-online-compress\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"&#8220;Segment Maintenance Online Compress&#8221; feature usage\",\"datePublished\":\"2020-04-19T19:17:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/segment-maintenance-online-compress\\\/\"},\"wordCount\":338,\"commentCount\":0,\"keywords\":[\"Compress\",\"Licensing\",\"online\",\"Oracle\",\"Partition\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/segment-maintenance-online-compress\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/segment-maintenance-online-compress\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/segment-maintenance-online-compress\\\/\",\"name\":\"\\\"Segment Maintenance Online Compress\\\" feature usage - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-04-19T19:17:20+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/segment-maintenance-online-compress\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/segment-maintenance-online-compress\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/segment-maintenance-online-compress\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"&#8220;Segment Maintenance Online Compress&#8221; feature usage\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"\"Segment Maintenance Online Compress\" feature usage - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/","og_locale":"en_US","og_type":"article","og_title":"\"Segment Maintenance Online Compress\" feature usage","og_description":"By Franck Pachot . On Twitter, Ludovico Caldara mentioned the #licensing #pitfall when using the Online Partition Move with Basic Compression. Those two features are available in Enterprise Edition without additional option, but when used together (moving online a compressed partition) they enable the usage of Advance Compression Option: Will it be reflected in dba_feature_usage_statistics? [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/","og_site_name":"dbi Blog","article_published_time":"2020-04-19T19:17:20+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"&#8220;Segment Maintenance Online Compress&#8221; feature usage","datePublished":"2020-04-19T19:17:20+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/"},"wordCount":338,"commentCount":0,"keywords":["Compress","Licensing","online","Oracle","Partition"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/","url":"https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/","name":"\"Segment Maintenance Online Compress\" feature usage - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-04-19T19:17:20+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/segment-maintenance-online-compress\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"&#8220;Segment Maintenance Online Compress&#8221; feature usage"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13945","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=13945"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13945\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=13945"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=13945"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=13945"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=13945"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}