Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как изменить тип столбца если в нём есть данные?  [new]
_MOD_IF_Y_
Guest
Доброго всем времени суток!
Возможно ли изменить тип столбца, с NUMBER на VARCHAR2, если столбец не пустой?

Делаю:
ALTER TABLE TABLE_NAME
MODIFY(DOC_NO VARCHAR2(250));
Пишет:
ORA-01439: модифицируемый столбец при смене типа данных должен быть пуст
Как можно это обойти, и возможно ли вообще?

Спасибо!
2 июн 09, 09:02    [7254159]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
Alexey181
Member

Откуда: default city
Сообщений: 907
как вариант
create table tab1 as select * from tab where 0=1;
ALTER TABLE tab1
MODIFY(col1 VARCHAR2(250));
insert into tab1 select * from tab;
commit;
2 июн 09, 09:18    [7254193]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
Elic
Member

Откуда:
Сообщений: 30234
_MOD_IF_Y_
ORA-01439: модифицируемый столбец при смене типа данных должен быть пуст
Как можно это обойти, и возможно ли вообще?
Ведь по-русски же написано.
Сохраняешь, обнуляешь, модифицируешь столбец, восстанавливаешь. Либо переименовываешь, добавляешь, переносишь, удаляешь старый солбец.
2 июн 09, 09:20    [7254197]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
_MOD_IF_Y_
Guest
Alexey181
как вариант
create table tab1 as select * from tab where 0=1;
ALTER TABLE tab1
MODIFY(col1 VARCHAR2(250));
insert into tab1 select * from tab;
commit;


Да, а потом ещё
drop table tab;
и
rename tab1 to tab;
Это конечно хороший варриант, но таблиц слишком много, хотелось бы как-нибудь по проще сделать, если такое конечно возможно...
2 июн 09, 09:28    [7254230]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
_MOD_IF_Y_
Guest
Elic
Сохраняешь, обнуляешь, модифицируешь столбец, восстанавливаешь. Либо переименовываешь, добавляешь, переносишь, удаляешь старый солбец.

Хотелось без лишних заморочек сделать...
2 июн 09, 09:30    [7254240]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
Alexey181
Member

Откуда: default city
Сообщений: 907
_MOD_IF_Y_
Elic
Сохраняешь, обнуляешь, модифицируешь столбец, восстанавливаешь. Либо переименовываешь, добавляешь, переносишь, удаляешь старый солбец.

Хотелось без лишних заморочек сделать...

exp/imp в помощь
2 июн 09, 09:36    [7254264]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
Elic
Member

Откуда:
Сообщений: 30234
_MOD_IF_Y_
Хотелось без лишних заморочек сделать...
"Поздно пить боржоми, когда..." :)
Думать надо было во время дизайна.
2 июн 09, 09:40    [7254280]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
Истребитель клопов
Member

Откуда:
Сообщений: 87
_MOD_IF_Y_

Хотелось без лишних заморочек сделать...

"сим салабим" ептить скажи только громче и несколько раз.

если кто-то изначально не думает, то потом сам дурак.

автор
exp/imp в помощь

думаю у товарища красненького только добавится
2 июн 09, 09:42    [7254291]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
_MOD_IF_Y_
Guest
Elic
"Поздно пить боржоми, когда..." :)
Думать надо было во время дизайна.

Дизайном не я занимался, мне просто сказали: "А номер документа может быть не только нумбер, так что займись ка ты сменой типов..."
2 июн 09, 09:48    [7254317]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
Andrey.L
Member

Откуда: Харьков
Сообщений: 1546
_MOD_IF_Y_
Elic
"Поздно пить боржоми, когда..." :)
Думать надо было во время дизайна.

Дизайном не я занимался, мне просто сказали: "А номер документа может быть не только нумбер, так что займись ка ты сменой типов..."

Естественный ключ?
2 июн 09, 10:30    [7254495]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
_MOD_IF_Y_
Guest
Andrey.L
Естественный ключ?

Извиняюсь, но я не совсем понял вопрос...
2 июн 09, 10:33    [7254514]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
Andrey.L
Member

Откуда: Харьков
Сообщений: 1546
_MOD_IF_Y_
Andrey.L
Естественный ключ?

Извиняюсь, но я не совсем понял вопрос...
Ссылочная целостность обеспечивается по номеру документа.
2 июн 09, 10:35    [7254524]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
_MOD_IF_Y_
Guest
Andrey.L
Ссылочная целостность обеспечивается по номеру документа.

Поле нот нулл, но ссылочной целостности нет.
Сейчас делаю так:
create table tab1 as (select * from tab);

ALTER TABLE tab
MODIFY("No"  NULL);

update tab set "No" = null;

ALTER TABLE tab
MODIFY("No" VARCHAR2(250));

update tab set "No" = (SELECT "No" from tab1 where tab1."ObjectGUID" = tab."ObjectGUID"); 

drop table tab1;

ALTER TABLE tab
MODIFY("No"  NOT NULL);
2 июн 09, 10:41    [7254562]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
SQLap
Member [заблокирован]

Откуда:
Сообщений: 34063
_MOD_IF_Y_
но ссылочной целостности нет.


Это хорошо, что нет)
2 июн 09, 10:46    [7254601]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
Andrey.L
Member

Откуда: Харьков
Сообщений: 1546
_MOD_IF_Y_
Andrey.L
Ссылочная целостность обеспечивается по номеру документа.

Поле нот нулл, но ссылочной целостности нет.
Сейчас делаю так:
create table tab1 as (select * from tab);

ALTER TABLE tab
MODIFY("No"  NULL);

update tab set "No" = null;

ALTER TABLE tab
MODIFY("No" VARCHAR2(250));

update tab set "No" = (SELECT "No" from tab1 where tab1."ObjectGUID" = tab."ObjectGUID"); 

drop table tab1;

ALTER TABLE tab
MODIFY("No"  NOT NULL);
По-моему сильно много телодвижений...
Упрости...
2 июн 09, 10:48    [7254614]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
_MOD_IF_Y_
Guest
Всем спасибо!
Таблиц оказалось не так уж и много(23)... сделал так как описал выше.
2 июн 09, 10:56    [7254647]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
ZVV
Member

Откуда: Минск
Сообщений: 744
_MOD_IF_Y_,

Это у вас таблички маленькие и работа пользователей по боку... :)

А вообще есть более спортивные варианты:

SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jun 2 10:17:16 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> Create Table "неверная"
  2  ("столбец" Number)
  3  /

Table created.

SQL> Insert Into "неверная"
  2     Select     rownum / 2
  3           From dual
  4     Connect By Level < 11
  5  /

10 rows created.

SQL> Select *
  2    From "неверная"
  3  /

   столбец                                                                      
----------                                                                      
        .5                                                                      
         1                                                                      
       1.5                                                                      
         2                                                                      
       2.5                                                                      
         3                                                                      
       3.5                                                                      
         4                                                                      
       4.5                                                                      
         5                                                                      

10 rows selected.

SQL> Drop Table "верная"
  2  /
Drop Table "верная"
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> Create Table "верная"
  2  ("столбец" Varchar(10))
  3  /

Table created.

SQL> 
SQL> Begin
  2     dbms_redefinition.Can_redef_table ( Uname                         => User
  3                                        ,Tname                         => '"неверная"'
  4                                        ,Options_flag                  => dbms_redefinition.Cons_use_rowid
  5                                       );
  6  End;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> Begin
  2     dbms_redefinition.Start_redef_table ( Uname                         => User
  3                                          ,Orig_table                    => '"неверная"'
  4                                          ,Int_table                     => '"верная"'
  5                                          ,Options_flag                  => dbms_redefinition.Cons_use_rowid
  6                                          ,Col_mapping                   => 'to_char("столбец") "столбец"'
  7                                         );
  8  End;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> Rem COPY_TABLE_DEPENDENTS при необходимости...
SQL> 
SQL> Begin
  2     dbms_redefinition.Finish_redef_table ( Uname                         => User,
  3                                            Orig_table                    => '"неверная"'
  4                                           ,Int_table                     => '"верная"' );
  5  End;
  6  /

PL/SQL procedure successfully completed.

SQL> Drop Table "верная"
  2  /

Table dropped.

SQL> desc "неверная";
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 столбец                                            VARCHAR2(10)

SQL> Select *
  2    From "неверная"
  3  /

столбец                                                                         
----------                                                                      
.5                                                                              
1                                                                               
1.5                                                                             
2                                                                               
2.5                                                                             
3                                                                               
3.5                                                                             
4                                                                               
4.5                                                                             
5                                                                               

10 rows selected.
2 июн 09, 11:21    [7254829]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
_MOD_IF_Y_
Guest
ZVV
А вообще есть более спортивные варианты:

Спасибо за решение!
Попробовал ваш варриант с вашими таблицами, всё проходит хорошо, когда пытаюсь сделать на своих после прогона
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jun 2 10:17:16 2009
SQL> Begin
  2     dbms_redefinition.Start_redef_table ( Uname                         => User
  3                                          ,Orig_table                    => 'tab'
  4                                          ,Int_table                     => 'test_tab'
  5                                          ,Options_flag                  => dbms_redefinition.Cons_use_rowid
  6                                          ,Col_mapping                   => 'to_char("No") "No"'
  7                                         );
  8  End;
  9  /
пишет:
ORA-12091: невозможно интерактивно переопределить таблицу tab с материализованными представлениями
ORA-06512: на  "SYS.DBMS_REDEFINITION", line 8
ORA-06512: на  "SYS.DBMS_REDEFINITION", line 146
ORA-06512: на  line 1

Begin dbms_redefinition.Start_redef_table ( Uname                         => User,
                                            Orig_table   
test_tab создаю следующим образом:
create table test_tab as (select * from tab where 0=1);
Затем делаю:
ALTER TABLE test_tab
MODIFY("No"  VARCHAR2(250 char));
Почему выходит ошибка?
Спасибо!
2 июн 09, 13:57    [7255872]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
_MOD_IF_Y_,
автор
ORA-12091: невозможно интерактивно переопределить таблицу tab с материализованными представлениями

Что в ошибке из написанного русским языком непонятно? Нельзя переопределить таблицу, если на ней основываются материализованные представления. Если всё равно хоцца - грохаем матвью и матлоги, переопределяем, создаём матвью и матлоги заново.
2 июн 09, 15:28    [7256503]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
_MOD_IF_Y_
Guest
suPPLer
Что в ошибке из написанного русским языком непонятно?

Не понятно то, что нет никаких матвью-шек привязанных к этой таблице.
4 июн 09, 10:48    [7263808]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
_MOD_IF_Y_,

как определяем?
4 июн 09, 11:09    [7263941]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
DВА
Member

Откуда:
Сообщений: 5439
и логов тоже нет?
4 июн 09, 11:18    [7264011]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Как изменить тип столбца если в нём есть данные?  [new]
VIVIM
Member

Откуда:
Сообщений: 1
Приветствую всех.
Столкнулся со схожей проблемой: нужно изменить number на varchar2 и добавить длину поля на 4 разряда, поле используется как естественный ключ. По постановке только последние 3 разряда могут принимать буквенные значения. В основной таблице, порядка 200 тысяч записей, в таблице где используется поле как ключ порядка 20 миллионов записей (накопили за 12 лет).
Про "думать заранее" тут не подходит, системе 20 лет и всё работало как часы, а тут заставляют "синхронизировать" справочники с новой системой, аргументы что "лучше у них там переделать" не работают - бизнесу удобнее как там.
Приведённый выше код скорее всего сработает, но я опасаюсь, что время исполнения может оказаться неприемлемо долгим, а мы можем остановить систему максимум на 2 дня в выходные.
Есть идеи как обойтись без конвертации или ускорить процесс?
Подумываю сделать отдельную таблицу для маппинга буквенных кодов в цифровые и в интерфейсе подменять, но основная идея минимально переписывать GUI.

Сообщение было отредактировано: 18 ноя 21, 22:20
18 ноя 21, 22:11    [22397717]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10337
VIVIM

Столкнулся со схожей проблемой: нужно изменить number на varchar2 и добавить длину поля на 4 разряда, поле используется как естественный ключ.


Использование естественных ключей раньше или позже аукается. Я бы просто добавил поле в родительскую таблицу и навесил на нее UNIQUE INDEX и продолжал использовать существующее поле.

SY.
18 ноя 21, 22:35    [22397727]     Ответить | Цитировать Сообщить модератору
 Re: Как изменить тип столбца если в нём есть данные?  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
VIVIM
порядка 20 миллионов записей (накопили за 12 лет).
...
мы можем остановить систему максимум на 2 дня в выходные.

Даже париться не стоит.
Ладно бы 20 ярдов записей - еще было бы о чем говорить, но два дня на 20 лямов - овердофига.
Даже dbms_redefinition привлекать смысла особого нет.
Табличка секционированная или нет?
Свободное место в БД на копию есть?
19 ноя 21, 16:15    [22398026]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить