Varchar2 size issues in OEM

[oaklanders]

I keep running into space issues when creating and/or modifying field varchar2 size in OEM for my Oracle 9i Schema. If I lower one field size then I can increase another field size etc...It seems like my Schema has a limit on total sizes of all my varchar2 fields.

Is the space or limit due to how much space the DBA set up in my Schema?
Basically if I ask the DBA to increase my Schema size I will be able to increase more of my fields varchar2 size?

Table example that has all varchar2 datatypes:

fieldOne   varchar2(1500)  
fieldTwo   varchar2(1500)
fieldThree varchar2(2000)
etc..

 

[SantaMufasa]

Absolutely not an issue, Oaklanders. When you increase the size of a column, there is no effect on the data itself...the only change that is occurring is in the definition of the table, not in the contents of the table. That means that the only thing that changes is a couple of bytes in the part of the data dictionary where the definition of tables resides.

I propose that you make the column-width adjustment(s) from the SQL*Plus prompt (in case there is something flakey about the OEM interface), then see if the change(s) appears in OEM. The SQL command would be:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR2(<new, larger length>);

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[karluk]

It would be very helpful if you would tell us the error you are getting. Otherwise we are just guessing as to the cause of your problem. However, a plausible guess is that you have one or more indexes defined on the varchar2 columns that are showing size dependencies. There is a limit for the total length of an index key, so you can't add arbitrarily many lengthy varchar2 columns to the same index.

For one of my 9i test databases, the limit on the index size is 6398 bytes. If I create an index that is just at the limit and then try to increase the size of one of the varchar2 columns in the index, I get the error

ORA-01404: ALTER COLUMN will make an index too large

If I really need to increase the length of the column, I would either have to drop the index or first reduce the maximum length of one of the other columns in the index.

 

[LKBrwnDBA]

Don't know if this still holds true, but the maximum size for an index key used to be the block size minus whatever overhead was required.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb