Re: import problem

From: s.kapitza <skapitza_at_volcanomail.com>
Date: 5 Jan 2002 09:13:35 -0800
Message-ID: <26703915.0201050913.4073e17a@posting.google.com>

hi dmitry

i had the second error once.
Someone renamed a table with a trigger.
The Table was renamed, but it seems the Trigger was "residing" on the old tablename , all went fine
until the next export/import.
we had to extract the triggersource from the destination database , rewrite the triggersource for the new tablename and aplied it to the target database.

maybe this is the same problem with your db.

greetings

s.Kapitza

dm_at_belkam.com (Dmitry Melekhov) wrote in message news:<48f1f058.0201050627.6938fd62_at_posting.google.com>...
> Hello!
>
> I want to import full database export from Oracle 8.1.7/w2k to Oracle
> 8.1.7/Linux.
>
> But I get following errors errors of two types (sorry, some tables and
> fields names are russian):
>
>
> . importing RUTOWN's objects into RUTOWN
> IMP-00017: following statement failed with ORACLE error 1730:
> "CREATE FORCE VIEW "RUTOWN"."RUT_SEL_USER_ROLES"
> (""
> "USER_NAME","NAME_ROLE","GRANTED_ROLE","ADM_OPT","DEF_ROLE","OPER_ROLE")
> AS
> "
> "SELECT aaa.*,"
> " decode(NVL(grt.GRANTED_ROLE,'*'),'*',0,1) GRANTED_ROLE,"
> " decode(NVL(grt.ADMIN_OPTION,'*'),'*',0,'YES',1,'NO',0,1) ADM_OPT,"
> " decode(NVL(grt.DEFAULT_ROLE,'*'),'*',0,'YES',1,'NO',0,1)
> DEF_ROLE,"
> " decode(substr(aaa.NAME_ROLE, 5, 1), 'О', 1, 'Ф', 0, 0
> ) OPER_ROLE"
> "FROM"
> "("
> "SELECT usr.USERNAME USER_NAME,"
> " rol.ROLE NAME_ROLE"
> "FROM ALL_USERS usr,"
> " DBA_ROLES rol"
> "WHERE"
> " rol.ROLE LIKE 'РУТ_%'"
> ") aaa,"
> "DBA_ROLE_PRIVS grt"
> "WHERE"
> " grt.GRANTEE(+) = aaa.user_name"
> " AND grt.GRANTED_ROLE(+) = aaa.name_role"
> IMP-00003: ORACLE error 1730 encountered
> ORA-01730: invalid number of column names specified
> ORA-01031: insufficient privileges
>
>
>
> and
>
>
> IMP-00003: ORACLE error 25001 encountered
> ORA-25001: cannot create this trigger type on views
> IMP-00017: following statement failed with ORACLE error 25001:
> "CREATE TRIGGER RUTJOURNAL.AR_РПР_JN"
> " AFTER DELETE OR INSERT OR UPDATE"
> " ON РУТ_РАСХОДЫ_ЗА_ПЕРИОДЫ"
> " FOR EACH ROW"
> "-- PL/SQL Block"
> "--Запполнение
> таблицы
> журнала"
> ""
> ""
> "BEGIN"
> "IF DELETING THEN"
> " INSERT INTO JN_РАСХОДЫ_ЗА_ПЕРИОДЫ
> ("
> " jn_user, jn_date_time, jn_operation"
> " , old_КОД"
> " , old_КН_КОД"
> " , old_ОП_ДАТА"
> " , old_ИСТОЧНИК_ВВОДА"
> " , old_ЭНЕРГИЯ"
> " , old_МАКС_МОЩНОСТЬ"
> " , old_ДАТА_МАКС_МОЩНОСТИ"
> " , old_НАЧАЛЬНОЕ_ПОКАЗАНИЕ"
> " , old_ДАТА_НАЧ_ПОКАЗАНИЯ"
> " , old_КОНЕЧНОЕ_ПОКАЗАНИЕ"
> " , old_ДАТА_КОН_ПОКАЗАНИЯ"
> " , old_РАСЧЕТНЫЙ_КОЭФ"
> " , old_КОММЕНТАРИЙ"
> " , old_ВВОД_ЭНЕРГИИ"
> " , old_ВВОД_МОЩНОСТИ"
> " , old_ВВОД_НП"
> " , old_ВВОД_КП"
> " ) values (user, sysdate, 'DEL'"
> " , :old.КОД"
> " , :old.КН_КОД"
> " , :old.ОП_ДАТА"
> " , :old.ИСТОЧНИК_ВВОДА"
> " , :old.ЭНЕРГИЯ"
> " , :old.МАКС_МОЩНОСТЬ"
> " , :old.ДАТА_МАКС_МОЩНОСТИ"
> " , :old.НАЧАЛЬНОЕ_ПОКАЗАНИЕ"
> " , :old.ДАТА_НАЧ_ПОКАЗАНИЯ"
> " , :old.КОНЕЧНОЕ_ПОКАЗАНИЕ"
> " , :old.ДАТА_КОН_ПОКАЗАНИЯ"
> " , :old.РАСЧЕТНЫЙ_КОЭФ"
> " , :old.КОММЕНТАРИЙ"
> " , :old.ВВОД_ЭНЕРГИИ"
> " , :old.ВВОД_МОЩНОСТИ"
> " , :old.ВВОД_НП"
> " , :old.ВВОД_КП"
> " );"
> "ELSIF INSERTING THEN"
> " INSERT INTO JN_РАСХОДЫ_ЗА_ПЕРИОДЫ
> ("
> " jn_user, jn_date_time, jn_operation"
> " , new_КОД"
> " , new_КН_КОД"
> " , new_ОП_ДАТА"
> " , new_ИСТОЧНИК_ВВОДА"
> " , new_ЭНЕРГИЯ"
> " , new_МАКС_МОЩНОСТЬ"
> " , new_ДАТА_МАКС_МОЩНОСТИ"
> " , new_НАЧАЛЬНОЕ_ПОКАЗАНИЕ"
> " , new_ДАТА_НАЧ_ПОКАЗАНИЯ"
> " , new_КОНЕЧНОЕ_ПОКАЗАНИЕ"
> " , new_ДАТА_КОН_ПОКАЗАНИЯ"
> " , new_РАСЧЕТНЫЙ_КОЭФ"
> " , new_КОММЕНТАРИЙ"
> " , new_ВВОД_ЭНЕРГИИ"
> " , new_ВВОД_МОЩНОСТИ"
> " , new_ВВОД_НП"
> " , new_ВВОД_КП"
> " ) values (user, sysdate, 'INS'"
> " , :new.КОД"
> " , :new.КН_КОД"
> " , :new.ОП_ДАТА"
> " , :new.ИСТОЧНИК_ВВОДА"
> " , :new.ЭНЕРГИЯ"
> " , :new.МАКС_МОЩНОСТЬ"
> " , :new.ДАТА_МАКС_МОЩНОСТИ"
> " , :new.НАЧАЛЬНОЕ_ПОКАЗАНИЕ"
> " , :new.ДАТА_НАЧ_ПОКАЗАНИЯ"
> " , :new.КОНЕЧНОЕ_ПОКАЗАНИЕ"
> " , :new.ДАТА_КОН_ПОКАЗАНИЯ"
> " , :new.РАСЧЕТНЫЙ_КОЭФ"
> " , :new.КОММЕНТАРИЙ"
> " , :new.ВВОД_ЭНЕРГИИ"
> " , :new.ВВОД_МОЩНОСТИ"
> " , :new.ВВОД_НП"
> " , :new.ВВОД_КП"
> " );"
> "ELSE"
> " INSERT INTO JN_РАСХОДЫ_ЗА_ПЕРИОДЫ
> ("
> " jn_user, jn_date_time, jn_operation"
> " , new_КОД"
> " , old_КОД"
> " , new_КН_КОД"
> " , old_КН_КОД"
> " , new_ОП_ДАТА"
> " , old_ОП_ДАТА"
> " , new_ИСТОЧНИК_ВВОДА"
> " , old_ИСТОЧНИК_ВВОДА"
> " , new_ЭНЕРГИЯ"
> " , old_ЭНЕРГИЯ"
> " , new_МАКС_МОЩНОСТЬ"
> " , old_МАКС_МОЩНОСТЬ"
> " , new_ДАТА_МАКС_МОЩНОСТИ"
> " , old_ДАТА_МАКС_МОЩНОСТИ"
> " , new_НАЧАЛЬНОЕ_ПОКАЗАНИЕ"
> " , old_НАЧАЛЬНОЕ_ПОКАЗАНИЕ"
> " , new_ДАТА_НАЧ_ПОКАЗАНИЯ"
> " , old_ДАТА_НАЧ_ПОКАЗАНИЯ"
> " , new_КОНЕЧНОЕ_ПОКАЗАНИЕ"
> " , old_КОНЕЧНОЕ_ПОКАЗАНИЕ"
> " , new_ДАТА_КОН_ПОКАЗАНИЯ"
> " , old_ДАТА_КОН_ПОКАЗАНИЯ"
> " , new_РАСЧЕТНЫЙ_КОЭФ"
> " , old_РАСЧЕТНЫЙ_КОЭФ"
> " , new_КОММЕНТАРИЙ"
> " , old_КОММЕНТАРИЙ"
> " , new_ВВОД_ЭНЕРГИИ"
> " , old_ВВОД_ЭНЕРГИИ"
> " , new_ВВОД_МОЩНОСТИ"
> " , old_ВВОД_МОЩНОСТИ"
> " , new_ВВОД_НП"
> " , old_ВВОД_НП"
> " , new_ВВОД_КП"
> " , old_ВВОД_КП"
> " ) values (user, sysdate, 'UPD'"
> " , :new.КОД"
> " , :old.КОД"
> " , :new.КН_КОД"
> " , :old.КН_КОД"
> " , :new.ОП_ДАТА"
> " , :old.ОП_ДАТА"
> " , :new.ИСТОЧНИК_ВВОДА"
> " , :old.ИСТОЧНИК_ВВОДА"
> " , :new.ЭНЕРГИЯ"
> " , :old.ЭНЕРГИЯ"
> " , :new.МАКС_МОЩНОСТЬ"
> " , :old.МАКС_МОЩНОСТЬ"
> " , :new.ДАТА_МАКС_МОЩНОСТИ"
> " , :old.ДАТА_МАКС_МОЩНОСТИ"
> " , :new.НАЧАЛЬНОЕ_ПОКАЗАНИЕ"
> " , :old.НАЧАЛЬНОЕ_ПОКАЗАНИЕ"
> " , :new.ДАТА_НАЧ_ПОКАЗАНИЯ"
> " , :old.ДАТА_НАЧ_ПОКАЗАНИЯ"
> " , :new.КОНЕЧНОЕ_ПОКАЗАНИЕ"
> " , :old.КОНЕЧНОЕ_ПОКАЗАНИЕ"
> " , :new.ДАТА_КОН_ПОКАЗАНИЯ"
> " , :old.ДАТА_КОН_ПОКАЗАНИЯ"
> " , :new.РАСЧЕТНЫЙ_КОЭФ"
> " , :old.РАСЧЕТНЫЙ_КОЭФ"
> " , :new.КОММЕНТАРИЙ"
> " , :old.КОММЕНТАРИЙ"
> " , :new.ВВОД_ЭНЕРГИИ"
> " , :old.ВВОД_ЭНЕРГИИ"
> " , :new.ВВОД_МОЩНОСТИ"
> " , :old.ВВОД_МОЩНОСТИ"
> " , :new.ВВОД_НП"
> " , :old.ВВОД_НП"
> " , :new.ВВОД_КП"
> " , :old.ВВОД_КП"
> " );"
> "END IF;"
> "END;"
>
> But trigger is for table!!!
>
> I can't understand why this errors appear and how to do import :(
Received on Sat Jan 05 2002 - 11:13:35 CST