8 Replies Latest reply: Jan 10, 2013 11:23 PM by jeneesh RSS

    modify data size

    957714
      Hi ,
      I have created a table:
      create table test(salary number(10));

      Now inserted some in it
      insert into test values (60000)
      insert into test values (70000)
      insert into test values (80000)

      Now i want to reduce the data size of salary column 10 to 7.

      Can any one help me how can I do that? Is it possible to reduce the size of the column if it is containing the data?
        • 1. Re: modify data size
          Chanchal Wankhade
          Hi,

          You can reduce the size of column after droping the column and recreating it or delete all the data from the column and then you can modify that column.
          You cannot make number (10) to number(7) while data is present in column.
          what if there is a data exceeding number(7)?
          SQL> desc test1;
           Name                                                              Null?    Type
           ----------------------------------------------------------------- -------- -----------------
           EMPNO                                                                      NUMBER
           EMPTYPE                                                                    VARCHAR2(10)
           EMP_ADDRESS                                                                VARCHAR2(20)
           SALARY                                                                     NUMBER
           DEPTNO                                                                     NUMBER
          
          SQL> alter table test1 modify empno number(1);
          alter table test1 modify empno number(1)
                                   *
          ERROR at line 1:
          ORA-01440: column to be modified must be empty to decrease precision or scale
          
          
          SQL>
          • 2. Re: modify data size
            Purvesh K
            954711 wrote:
            Hi ,
            I have created a table:
            create table test(salary number(10));

            Now inserted some in it
            insert into test values (60000)
            insert into test values (70000)
            insert into test values (80000)

            Now i want to reduce the data size of salary column 10 to 7.

            Can any one help me how can I do that? Is it possible to reduce the size of the column if it is containing the data?
            One way of doing it:
            create table test (salary number(10));
            
            insert into test values (12345);
            insert into test values (12345);
            insert into test values (12345);
            insert into test values (12345);
            insert into test values (12345);
            
            
            alter table test add (salary_temp number(7));
            update test set salary_temp = salary, salary = null;
            alter table test modify salary number(7);
            alter table test drop column salary_temp;
            • 3. Re: modify data size
              982249
              if you modify to decrease column size 10 to 7, you don't have data up 7.

              example) DATA
              1) 1234567890 (x)
              2) 123456(o)

              1) exceed size 7. you can't modify column size.
              2) size 6. you can modify column size.
              • 4. Re: modify data size
                957714
                Thanks ,It is working but I think I have to update salary column with salary_temp table before droping it.


                alter table test add (salary_temp number(7));
                update test set salary_temp = salary, salary = null;
                alter table test modify salary number(7);
                alter table test drop column salary_temp;
                • 5. Re: modify data size
                  Purvesh K
                  Ahhh!!!! Yes. I did miss the step. Thanks for noticing it. It is indeed required.
                  • 6. Re: modify data size
                    957714
                    Hi,
                    I have one question in this solution that if i am working with millions of records will this solution worthy or it will reduce the performance?
                    • 7. Re: modify data size
                      Purvesh K
                      954711 wrote:
                      Hi,
                      I have one question in this solution that if i am working with millions of records will this solution worthy or it will reduce the performance?
                      I do not think it will impact performance, since it is just a plain update.

                      Why not try it for a few records?
                      • 8. Re: modify data size
                        jeneesh
                        You could try one more option..
                        create table your_table_temp as select * from your_table;
                        truncate table your_table;
                        alter the column;
                        insert /*+ append */ into your_table
                        select * from your_table_temp;
                        commit;
                        drop temp table;