DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Import related Questions

  1. #1
    Join Date
    Jun 2003
    Location
    India
    Posts
    118

    Import related Questions

    I want to be more clear on the following.

    1. At the time of Importing into exsisting users an error message come i.e integerty constraints voilated. To solve this problem we must disable the constranits at the time of taking export. Suppose we have many tables with constraints then how to disable all the constraints in one go from all tables.? Is there some other option exsit so that this error should not occur?

    2. It is said if we import a dump file more than once then at the second time we must use ROW=N so that rows should not get duplicated in tables. Then when we import the dump file into existing table then at the time of import(first time) why rows there is no duplicacy.

    3. What parameters should be used at the time of taking Import into existing users/tables, so that no/minimum error should occur.

    4. To clear more concepts on this from where I can get information.?
    vishal sood
    OCP 8

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    1. you can use dynamic SQL to build the SQL to disable the constraints

    2. uh... dont understand what you are saying at all, if you insert into empty table how would you get error, plus constraints are enabled after rows are inserted

    3. read the doc

    4. technet.oracle.com

  3. #3
    Join Date
    Nov 2001
    Posts
    11
    please try option IGNORE=no when importing
    so it will give exact error where u r going wrong.
    if error is of duplicate key then try to
    Truncate the table where u r inserting the rows and then again try
    importing .

  4. #4
    Join Date
    Jun 2003
    Location
    India
    Posts
    118
    Yes, the errors are related to Unique constraints & Ref.*******ty Constranits.

    It is very troublesome to manually truncate the tables or manually disabling constraints if there are many.

    Suppose there is a OLTP system. The primary database is on Unix and we take export from unix database as on 17-11-2003 and then we import it on other database say on NT.

    Next day if we again take export from Unix of 18-11-2003 and when we try to import on NT database then unique key or ref. integerity constraints error occurs.

    What are the ways to solve this problem and which one will be the fastest & easiest.?
    vishal sood
    OCP 8

  5. #5
    Hi Vishal,
    Im not sure what you are doing here but - if you are getting unique constraint violations then disabling the constraints and importing will only leave you with constraints that you probably cannot re-enable again.

    IGNORE=Y makes import ignore the errors on the CREATE TABLE/INDEX statements in the import file where the table/index already exists - it doesnt have anything to do with the data.

    ROWS=N Has nothing to do with importing duplicate rows; Y= Insert all the rows, N=Dont insert rows.

    You cannot import just a portion of the rows in a export file finto a table - its all or nothing, and any rows that fail due to constraints get errors.

    -Bob

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Vishal

    The method that you have adopted is painful!!..But if that si what you want to do..you can do two things

    1)Drop all users objects befire doing the import
    something like

    select 'drop ' || object_type || ' ' || object_name || decode(object_type,
    'CLUSTER', ' including tables cascade constraints;',
    'TABLE', ' cascade constraints;',
    ';')
    from user_objects
    where object_type in ('CLUSTER', 'TABLE', 'VIEW', 'SEQUENCE', 'SYNONYM',
    'FUNCTION',
    'PROCEDURE', 'PACKAGE','PACKAGE BODY','TYPE')
    order by object_type;

    This will create a script to drop all users objects and then you can go ahead and import.Offcourse this method is not clean due to garnts etc.

    Other method is disable the constarints and import the records and then delete the records which are not matching.and again reenable the constraints

    regards
    Hrishy

  7. #7
    Join Date
    Jun 2003
    Location
    India
    Posts
    118
    Hirshy : The method that you have adopted is painful!!..
    Yes u r right. But the thing is I got a job where the enviroment is that they Have 20 Branches all over the country and they r in process to connect with head office thru citrix server(installed at head office). At Head office they have Sun Server and on this they have installed Oracle 9i database. Along with this they have a backup server on NT with same Oracle database as of Sun Server. Client r with win.98 OS.

    They have a plan like every evening they will take export from SUN Server and import on NT server. In case of SUN server goes down the NT will act as Primary.

    Because the OS is different they can not have standby database option.They r using different OS just to reduce the cost.

    Do u have any other easy method for above mentioned enviorment. As DBA what I should suggest to them.? Because I will be the new in company and everything they have already planned.


    Any suggestion please.
    vishal sood
    OCP 8

  8. #8
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    What is the size of the DB ?

    I have a similar setup for one development DB (hardly 15GB). Actual DB is on HP and the backup DB on WINNT. Every night on the DB on NT my script drops the schemas and recreates the schema owners only and then the data exported from the HP (taken during the day) is imported.

    This is so that in case we have our HP down for maintainance we simply use the isql url and application url on WINDOWS and use the DB on NT. The application is deployed on both HP and NT, all application changes are automated and in sync.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  9. #9
    Join Date
    Jun 2003
    Location
    India
    Posts
    118
    The size of the Database is not more than 10GB.

    Amar, since ur method and our method is almost same, Can u send me the script ? which u are using for export,import & droping all the users and recreating them.

    I want to take extra care because I am joining a new company and don't want to ignore anything which can cause an error.

    It will be great help for me.

    my emailid is soodkvishal@yahoo.com
    vishal sood
    OCP 8

  10. #10
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    All the following scripts are running from windows machine.

    Order of scripts execution.
    PHP Code:
    Iexportdaily.bat -- Scheduled to run every evening.
    IIcreatedb.bat -- Scheduled to run every morning around 3:00AM.
        
    acollectfiles.sql -- lists the list of existing datafilescontrolfilesredologfiles.
        
    bfiles.bat -- generated by collectfiles.sqltakes a cold backup of files collected by the above script.
        
    cdropowners.sql -- drops the existing schemas.
        
    dcreateowners.sql -- creates the existing schemas
    createdb.bat
    Code:
    set ORACLE_SID=DB_NAME
    set ORACLE_HOME=D:\9iDatabase\ora92
    %ORACLE_HOME%\bin\oradim -STARTUP -SID DB_NAME -starttype srvc,inst
    %ORACLE_HOME%\bin\sqlplus.exe "/ as sysdba" @d:\amar\sqls\collectfiles.sql
    %ORACLE_HOME%\bin\oradim -SHUTDOWN -SID DB_NAME -shuttype srvc,inst -shutmode i
    CALL D:\Amar\DB_NAMEBackup\files.bat
    %ORACLE_HOME%\bin\oradim -STARTUP -SID DB_NAME -starttype srvc,inst
    %ORACLE_HOME%\bin\sqlplus.exe "/ as sysdba" @d:\amar\sqls\dropowners.sql
    %ORACLE_HOME%\bin\sqlplus.exe "/ as sysdba" @d:\amar\sqls\createowners.sql
    %ORACLE_HOME%\bin\imp userid=impuser/impuser file=D:\exportDumps\nightexpDB_NAME.dmp log=D:\exportDumps\impnightexpDB_NAME.log commit=y buffer=1000000 ignore=y fromuser=(owner1,owner2,owner3) touser=(owner1,owner2,owner3)
    collectfiles.sql
    Code:
    set feedback off
    set pages 0
    set lines 300
    spool D:\Amar\DB_NAMEBackup\files.bat
    select 'copy /Y '||name||' D:\Amar\DB_NAMEBackup' from v$datafile;
    select 'copy /Y '||name||' D:\Amar\DB_NAMEBackup' from v$controlfile;
    select 'copy /Y '||member||' D:\Amar\DB_NAMEBackup' from v$logfile;
    spool off
    exit
    dropowners.sql
    Code:
    spool D:\Amar\SQLs\logs\dropowners.log
    drop user OWNER1 cascade;
    drop user OWNER2 cascade;
    drop user OWNER3 cascade;
    drop user OWNER4 cascade;
    drop user OWNER5 cascade;
    drop user OWNER6 cascade;
    drop user OWNER7 cascade;
    drop user OWNER8 cascade;
    spool off
    exit
    createowners.sql
    Code:
    spool D:\Amar\SQLs\logs\createowners.log
    
    PROMPT Creating owners
    
    CREATE USER owner1 IDENTIFIED BY password DEFAULT TABLESPACE t_owner1 TEMPORARY TABLESPACE t_temp;
    CREATE USER owner2 IDENTIFIED BY password DEFAULT TABLESPACE t_owner2 TEMPORARY TABLESPACE t_temp;
    CREATE USER owner3 IDENTIFIED BY password DEFAULT TABLESPACE t_owner3 TEMPORARY TABLESPACE t_temp;
    CREATE USER owner4 IDENTIFIED BY password DEFAULT TABLESPACE t_owner4 TEMPORARY TABLESPACE t_temp;
    CREATE USER owner5 IDENTIFIED BY password DEFAULT TABLESPACE t_owner5 TEMPORARY TABLESPACE t_temp;
    CREATE USER owner6 IDENTIFIED BY password DEFAULT TABLESPACE t_owner6 TEMPORARY TABLESPACE t_temp;
    CREATE USER owner7 IDENTIFIED BY password DEFAULT TABLESPACE t_owner7 TEMPORARY TABLESPACE t_temp;
    CREATE USER owner8 IDENTIFIED BY password DEFAULT TABLESPACE t_owner8 TEMPORARY TABLESPACE t_temp;
    
    ALTER USER owner1 QUOTA UNLIMITED ON t_owner1;
    ALTER USER owner2 QUOTA UNLIMITED ON t_owner2;
    ALTER USER owner3 QUOTA UNLIMITED ON t_owner3;
    ALTER USER owner4 QUOTA UNLIMITED ON t_owner4;
    ALTER USER owner5 QUOTA UNLIMITED ON t_owner5;
    ALTER USER owner6 QUOTA UNLIMITED ON t_owner6;
    ALTER USER owner7 QUOTA UNLIMITED ON t_owner7;
    ALTER USER owner8 QUOTA UNLIMITED ON t_owner8;
    
    GRANT r_owner1 TO owner1;
    GRANT r_owner2 TO owner2;
    GRANT r_owner3 TO owner3;
    GRANT r_owner4 TO owner4;
    GRANT r_owner5 TO owner5;
    GRANT r_owner6 TO owner6;
    GRANT r_owner7 TO owner7;
    GRANT r_owner8 TO owner8;
    spool off
    exit
    exportdaily.bat
    Code:
    set ORACLE_HOME=D:\9iDatabase\ora92
    %ORACLE_HOME%\bin\exp userid=expuser/expuser@REMOTE_DB file=D:\exportDumps\nightexpDB_NAME.dmp full=y consistent=y log=D:\exportDumps\nightexpDB_NAME.log statistics=none
    Where impuser and expuser are users having imp_full_database and exp_full_database grants respectively along with create session grant.

    You may need to modify the above scripts according to ur needs.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width