{"id":9787,"date":"2019-02-24T10:45:37","date_gmt":"2019-02-24T10:45:37","guid":{"rendered":"https:\/\/dbtut.com\/?p=9787"},"modified":"2019-02-24T10:45:37","modified_gmt":"2019-02-24T10:45:37","slug":"how-to-resize-datafiles-in-oracle","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/","title":{"rendered":"How To Resize Datafiles in Oracle"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p>We ensure that unused space in the datafiles are reclaimed by the ASM disk group with the resize process. This ensures that unused areas are reused.<\/p>\n<p><strong>You can find the script below for the resize process:<\/strong><\/p>\n<pre class=\"lang:default decode:true\">select * from \r\n(select file_name, \r\nceil( (nvl(hwm,1)*&amp;&amp;blksize)\/1024\/1024 ) smallest, \r\nceil( blocks*&amp;&amp;blksize\/1024\/1024) currsize, \r\nceil( blocks*&amp;&amp;blksize\/1024\/1024) - \r\nceil( (nvl(hwm,1)*&amp;&amp;blksize)\/1024\/1024 ) savings, \r\n'alter database datafile ''' || file_name || ''' resize ' || ceil( (nvl(hwm,1)*&amp;&amp;blksize)\/1024\/1024 ) || 'M;' sql \r\nfrom dba_data_files a, \r\n( select file_id, max(block_id+blocks-1) hwm \r\nfrom dba_extentsgroup by file_id ) b \r\nwhere a.file_id = b.file_id(+)\r\n) \r\nwhere savings &gt; 0 \r\norder by savings desc;<\/pre>\n<p>You need to set the &amp;&amp; blksize variable in the query according to the db_block_size parameter of your database.<\/p>\n<p>As a result of the query, the savings column shows the amount of space to be earned. The sql column also specifies the query required for the resize operation.<\/p>\n\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_9787\" class=\"pvc_stats all  \" data-element-id=\"9787\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; We ensure that unused space in the datafiles are reclaimed by the ASM disk group with the resize process. This ensures that unused areas are reused. You can find the script below for the resize process: select * from (select file_name, ceil( (nvl(hwm,1)*&amp;&amp;blksize)\/1024\/1024 ) smallest, ceil( blocks*&amp;&amp;blksize\/1024\/1024) currsize, ceil( blocks*&amp;&amp;blksize\/1024\/1024) &#8211; ceil( (nvl(hwm,1)*&amp;&amp;blksize)\/1024\/1024 ) &hellip;<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_9787\" class=\"pvc_stats all  \" data-element-id=\"9787\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[4],"tags":[2134,2135],"class_list":["post-9787","post","type-post","status-publish","format-standard","","category-oracle","tag-reclaim-unused-space-in-oracle","tag-resize-datafiles-in-oracle"],"aioseo_notices":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.9 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How To Resize Datafiles in Oracle - Database Tutorials<\/title>\n<meta name=\"description\" content=\"How To Resize Datafiles in Oracle\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Resize Datafiles in Oracle - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"How To Resize Datafiles in Oracle\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-02-24T10:45:37+00:00\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\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:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"How To Resize Datafiles in Oracle\",\"datePublished\":\"2019-02-24T10:45:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/\"},\"wordCount\":97,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"keywords\":[\"Reclaim unused space in Oracle\",\"resize datafiles in oracle\"],\"articleSection\":[\"ORACLE\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/\",\"name\":\"How To Resize Datafiles in Oracle - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"datePublished\":\"2019-02-24T10:45:37+00:00\",\"description\":\"How To Resize Datafiles in Oracle\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Resize Datafiles in Oracle\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Resize Datafiles in Oracle - Database Tutorials","description":"How To Resize Datafiles in Oracle","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:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/","og_locale":"en_US","og_type":"article","og_title":"How To Resize Datafiles in Oracle - Database Tutorials","og_description":"How To Resize Datafiles in Oracle","og_url":"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/","og_site_name":"Database Tutorials","article_published_time":"2019-02-24T10:45:37+00:00","author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"How To Resize Datafiles in Oracle","datePublished":"2019-02-24T10:45:37+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/"},"wordCount":97,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"keywords":["Reclaim unused space in Oracle","resize datafiles in oracle"],"articleSection":["ORACLE"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/","url":"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/","name":"How To Resize Datafiles in Oracle - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"datePublished":"2019-02-24T10:45:37+00:00","description":"How To Resize Datafiles in Oracle","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/24\/how-to-resize-datafiles-in-oracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"How To Resize Datafiles in Oracle"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/9787","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=9787"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/9787\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=9787"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=9787"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=9787"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}