We help IT Professionals succeed at work.

Maximum Size of column in Dual table in Oracle database

Hi Team,

I would like to know the maximum size of a column in DUAL table in oracle database.
Watch Question

johnsoneSenior Oracle DBA

You shouldn't be putting anything into the DUAL table.

DUAL is a table that has one column (DUMMY) defined as VARCHAR2(1) and contains one row of data ('X').

As you are never really selecting anything from the DUAL table, the limitation is a SQL limitation, not a limitation of the table.
Database Administrator
This question doesn’t make sense: “the maximum size of a column in DUAL table in oracle database”.  You shouldn’t ever change the definition of that table.  And the definition of that table does *NOT* limit the size of the value that you can select from that table.  That table exists in every Oracle database just to give us a reliable way to always select exactly one value with a SQL query.  But the size of the value you return from a query of DUAL is *NOT* limited by the definition of the DUAL table.

For example, you can use queries like these:
Select rpad(1,100,’0’) “Hundred_zeroes” from dual;
Select rpad(1,1000,’0’) “Thousand_zeroes” from dual;
Select rpad(1,1000000,’0’) “Million_zeroes” from dual;

And you can try larger values too.  Sooner or later you will likely encounter a SQL (or memory?) limit, but that has nothing to do with the DUAL table.

Please explain exactly what problem you are facing that you think the DUAL table may be related to.


Thanks a lot