<div>
There are probably more reasons but Tom Kyte hit on the main one I was thinking:<br />
Data Integrity.<br />
<br />
If you will only ever store 2 characters, why let the developers possibly store 4000?<br />
<br />
Here is the complete article that also talks about index issues:<br />
Reasons Not to Use Varchar2(255-4000) if Actual Length is Shorter<br />
<a href="https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3014576500346676264" rel="ugc">https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3014576500346676264</a>
</div>


<div>
OK, suppose If i declare a column size as 4000. and I have stored only 40 bytes per row.<br />
so till this point, the exact storage space is no. of rows * 40.<br />
<br />
So exactly at what point of time oracle pre-allocates space based on column size..<br />
<br />
I mean at what process, an insert or update or ...
</div>


<div>
it will preallocate space when you declare a variable to be of your column type<br />
<br />
create table testtable (v varchar2(4000));<br />
<br />
Declare<br />
&nbsp; &nbsp;my_variable testtable.v%type; &nbsp; --- here you variable has been allocated space already<br />
Begin<br />
...<br />
End;<br />
<br />
This is particuarly important in 12c where it allows for 32K varchar2 columns which changes the way memory is allocated when variables get large
</div>


<div>
&gt;&gt;&gt; &nbsp;the exact storage space is no. of rows * 40.<br />
<br />
not exactly.<br />
you still have block headers.<br />
you still have pctfree<br />
the row entry within the block also comsumes space to store the value length<br />
if you have row migration or chaining you have pointers stored.<br />
etc<br />
<br />
and that doesn't even take into account allocation vs consumption
</div>


<div>
Turn the question around...<br />
<br />
Instead of trying to ask this....<br />
&gt;&gt;&gt;&nbsp;So, Why do we need to specify the size for every table creation / declaration. <br />
<br />
Why would you want to declare a value that you know is not representative of real data?
</div>


<div>
ok I understand, apart from this instance, could there be any other place<br />
where oracle allocates space with respect to column size.
</div>


<div>
&gt;&gt;&gt;&gt; where oracle allocates space with respect to column size. <br />
<br />
that's still the wrong way to look at it.<br />
<br />
Are you going to ever use the data? &nbsp;If so, then any tool you use will be preallocating space in order to hold the data being read.<br />
<br />
It looks like you're trying to find a way to justify using a bad data model.<br />
<br />
Don't do that.
</div>


<div>
if it was a good idea to have all text be unlimited, then oracle would implement everything as clobs and the other types would be constrainted subtypes of that.<br />
<br />
but they don't because it's a bad idea.<br />
<br />
Create a model that &quot;models&quot; your data.
</div>


<div>
Thanks Everyone for explaining me patiently, <br />
<br />
I really wanted to know the basics behind this logic.
</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
<br />
Just a basic question.<br />
I believe Oracle does not reserve any space for the varchar2 column, unless a value is available.<br />
<br />
So, Why do we need to specify the size for every table creation / declaration.
</div>
</div>


Hi,

Just a basic question.
I believe Oracle does not reserve any space for the varchar2 column, unless a value is available.

So, Why do we need to specify the size for every table creation / declaration.

Reason to specify the size of varchar2

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.