01-01-07, 10:34 #1Registered User
- Join Date
- May 2006
Unanswered: column length consistency in tables
I am having schema where I want to alter some of the table structure as some of the fields in my tables like
I want to make all ADDRESSNO to VARCHAR2(5) in table2 and all street in table1 to VARCHAR2(30).
select * from all_tab_columns where COLUMN_NAME='STREET1' AND OWNER='schema_name';
output of this query shows different length for same column name ,
So the different tables meant for storing same information but there lengths is varying .
I want to maintain structural consistency in my column lengths.For doing these kind of changes I have to first make a copy of table in some temp table . then truncate the table alter the length
alter table table2 modify street2 varchar2(30);
and then again insert the data .
I have to do many of these kind of structure changes .
any suggestions. Any tools for seeing this kind of descripencies in my database and to do correction.
thanks in advance
01-01-07, 11:29 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
>Any tools for seeing this kind of descripencies in my database
Yes, it is called SQL
>and to do correction.
Yes, it is called SQL.You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.
01-02-07, 09:11 #3Registered User
- Join Date
- Jun 2004
- Liverpool, NY USA
Instead of decreasing the length of the columns, which could run into length problems, why not increase them to the largest size. While the application that uses these tables have to support what ever length you are using, you will get into problems with the application no matter if you increase or decrease the existing columns and increasing them is the easiest and doesn't cause any problems with existing data. And speaking as a programmer of 30 years, bigger is almost always better when you are setting up columns to store data. never plan the the smallest size, plan for the biggest and then add some growth space.Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.