{"id":17713,"date":"2024-03-15T15:33:12","date_gmt":"2024-03-15T15:33:12","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/"},"modified":"2024-03-21T11:33:51","modified_gmt":"2024-03-21T11:33:51","slug":"how-can-an-existing-table-be-partitioned-in-oracle","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/","title":{"rendered":"How can an existing table be partitioned in Oracle?"},"content":{"rendered":"<p>In Oracle, you can use the ALTER TABLE statement to create partitions for an existing table. Here is an example:<\/p>\n<pre class=\"post-pre\"><code><span class=\"hljs-comment\">-- \u521b\u5efa\u4e00\u4e2a\u65b0\u7684\u5206\u533a\u8868<\/span>\r\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> my_table (\r\n    id <span class=\"hljs-type\">INT<\/span>,\r\n    name VARCHAR2(<span class=\"hljs-number\">50<\/span>),\r\n    date_created <span class=\"hljs-type\">DATE<\/span>\r\n)\r\n<span class=\"hljs-keyword\">PARTITION<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">RANGE<\/span> (date_created)\r\n(\r\n    <span class=\"hljs-keyword\">PARTITION<\/span> p1 <span class=\"hljs-keyword\">VALUES<\/span> LESS THAN (TO_DATE(<span class=\"hljs-string\">'01-01-2022'<\/span>, <span class=\"hljs-string\">'DD-MM-YYYY'<\/span>)),\r\n    <span class=\"hljs-keyword\">PARTITION<\/span> p2 <span class=\"hljs-keyword\">VALUES<\/span> LESS THAN (TO_DATE(<span class=\"hljs-string\">'01-01-2023'<\/span>, <span class=\"hljs-string\">'DD-MM-YYYY'<\/span>)),\r\n    <span class=\"hljs-keyword\">PARTITION<\/span> p3 <span class=\"hljs-keyword\">VALUES<\/span> LESS THAN (TO_DATE(<span class=\"hljs-string\">'01-01-2024'<\/span>, <span class=\"hljs-string\">'DD-MM-YYYY'<\/span>)),\r\n    <span class=\"hljs-keyword\">PARTITION<\/span> p4 <span class=\"hljs-keyword\">VALUES<\/span> LESS THAN (MAXVALUE)\r\n);\r\n\r\n<span class=\"hljs-comment\">-- \u5c06\u5df2\u6709\u7684\u8868\u8f6c\u6362\u4e3a\u5206\u533a\u8868<\/span>\r\n<span class=\"hljs-keyword\">ALTER<\/span> <span class=\"hljs-keyword\">TABLE<\/span> existing_table <span class=\"hljs-keyword\">ADD<\/span> <span class=\"hljs-keyword\">PARTITION<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">RANGE<\/span> (date_created)\r\n(\r\n    <span class=\"hljs-keyword\">PARTITION<\/span> p1 <span class=\"hljs-keyword\">VALUES<\/span> LESS THAN (TO_DATE(<span class=\"hljs-string\">'01-01-2022'<\/span>, <span class=\"hljs-string\">'DD-MM-YYYY'<\/span>)),\r\n    <span class=\"hljs-keyword\">PARTITION<\/span> p2 <span class=\"hljs-keyword\">VALUES<\/span> LESS THAN (TO_DATE(<span class=\"hljs-string\">'01-01-2023'<\/span>, <span class=\"hljs-string\">'DD-MM-YYYY'<\/span>)),\r\n    <span class=\"hljs-keyword\">PARTITION<\/span> p3 <span class=\"hljs-keyword\">VALUES<\/span> LESS THAN (TO_DATE(<span class=\"hljs-string\">'01-01-2024'<\/span>, <span class=\"hljs-string\">'DD-MM-YYYY'<\/span>)),\r\n    <span class=\"hljs-keyword\">PARTITION<\/span> p4 <span class=\"hljs-keyword\">VALUES<\/span> LESS THAN (MAXVALUE)\r\n);\r\n<\/code><\/pre>\n<p>It is important to note that you will need to modify the partition method and values based on your specific needs. The example above uses partitioning by date range, but you can also partition based on the values of other columns.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Oracle, you can use the ALTER TABLE statement to create partitions for an existing table. Here is an example: &#8212; \u521b\u5efa\u4e00\u4e2a\u65b0\u7684\u5206\u533a\u8868 CREATE TABLE my_table ( id INT, name VARCHAR2(50), date_created DATE ) PARTITION BY RANGE (date_created) ( PARTITION p1 VALUES LESS THAN (TO_DATE(&#8217;01-01-2022&#8242;, &#8216;DD-MM-YYYY&#8217;)), PARTITION p2 VALUES LESS THAN (TO_DATE(&#8217;01-01-2023&#8242;, &#8216;DD-MM-YYYY&#8217;)), PARTITION p3 VALUES [&hellip;]<\/p>\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-17713","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How can an existing table be partitioned in Oracle? - Blog - Silicon Cloud<\/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.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How can an existing table be partitioned in Oracle?\" \/>\n<meta property=\"og:description\" content=\"In Oracle, you can use the ALTER TABLE statement to create partitions for an existing table. Here is an example: -- \u521b\u5efa\u4e00\u4e2a\u65b0\u7684\u5206\u533a\u8868 CREATE TABLE my_table ( id INT, name VARCHAR2(50), date_created DATE ) PARTITION BY RANGE (date_created) ( PARTITION p1 VALUES LESS THAN (TO_DATE(&#039;01-01-2022&#039;, &#039;DD-MM-YYYY&#039;)), PARTITION p2 VALUES LESS THAN (TO_DATE(&#039;01-01-2023&#039;, &#039;DD-MM-YYYY&#039;)), PARTITION p3 VALUES [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-15T15:33:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-21T11:33:51+00:00\" \/>\n<meta name=\"author\" content=\"William Carter\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"William Carter\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/\"},\"author\":{\"name\":\"William Carter\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0\"},\"headline\":\"How can an existing table be partitioned in Oracle?\",\"datePublished\":\"2024-03-15T15:33:12+00:00\",\"dateModified\":\"2024-03-21T11:33:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/\"},\"wordCount\":70,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/\",\"name\":\"How can an existing table be partitioned in Oracle? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T15:33:12+00:00\",\"dateModified\":\"2024-03-21T11:33:51+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How can an existing table be partitioned in Oracle?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0\",\"name\":\"William Carter\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g\",\"caption\":\"William Carter\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/williamcarter\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How can an existing table be partitioned in Oracle? - Blog - Silicon Cloud","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.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/","og_locale":"en_US","og_type":"article","og_title":"How can an existing table be partitioned in Oracle?","og_description":"In Oracle, you can use the ALTER TABLE statement to create partitions for an existing table. Here is an example: -- \u521b\u5efa\u4e00\u4e2a\u65b0\u7684\u5206\u533a\u8868 CREATE TABLE my_table ( id INT, name VARCHAR2(50), date_created DATE ) PARTITION BY RANGE (date_created) ( PARTITION p1 VALUES LESS THAN (TO_DATE('01-01-2022', 'DD-MM-YYYY')), PARTITION p2 VALUES LESS THAN (TO_DATE('01-01-2023', 'DD-MM-YYYY')), PARTITION p3 VALUES [&hellip;]","og_url":"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T15:33:12+00:00","article_modified_time":"2024-03-21T11:33:51+00:00","author":"William Carter","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"William Carter","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/"},"author":{"name":"William Carter","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0"},"headline":"How can an existing table be partitioned in Oracle?","datePublished":"2024-03-15T15:33:12+00:00","dateModified":"2024-03-21T11:33:51+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/"},"wordCount":70,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/","url":"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/","name":"How can an existing table be partitioned in Oracle? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T15:33:12+00:00","dateModified":"2024-03-21T11:33:51+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-can-an-existing-table-be-partitioned-in-oracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How can an existing table be partitioned in Oracle?"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0","name":"William Carter","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g","caption":"William Carter"},"url":"https:\/\/www.silicloud.com\/blog\/author\/williamcarter\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/17713","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=17713"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/17713\/revisions"}],"predecessor-version":[{"id":51342,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/17713\/revisions\/51342"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=17713"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=17713"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=17713"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}