DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Import data

  1. #1
    Join Date
    Mar 2003
    Location
    Gujarat
    Posts
    19

    Import data

    i hv two database
    prod & test
    i am trying to load export backup from prod db to test but some data is already avalable in test so its giviing error key violation ora-00001.
    can i remove all data and overright in test through import option please advice.
    regs
    ramji

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

    There is script here to drop all users objects..and then start importing...

    with import utility you cannot drop already existing objects

    #!/bin/sh
    #Script Created on 12-03-2001 By Hrishikesh
    drpobjects()
    {
    cat > ${spoolfile}.sql << !EOF
    set pause off
    set head off
    set pagesize 0
    set linesize 80
    set feedback off
    spool ${spoolfile}.sql
    select 'set echo on feedback on termout on' from dual;
    select 'spool drop_user_objects' from dual;
    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')
    order by object_type;
    select 'spool off' from dual;
    spool off
    spool ${spoolfile}.err
    @${spoolfile}.sql
    set pause on
    set head on
    set pagesize 30
    set feedback on
    exit
    !EOF
    }
    echo "Enter userid whose objects needs to be dropped : \c"
    read uid
    echo "Enter password for ${uid} [No echo]: \c"
    stty -echo
    read pswd
    stty echo
    #echo "\nEnter Connect String for ${1} : \c"
    #read strng

    pswd=$pswd

    spoolfile="./drpobjects"

    drpobjects
    sqlplus -s ${uid}/${pswd} @${spoolfile}.sql
    rm ./drpobjects.*

    you can schedule this through corn job and drop the schemas during night and refresh them later with dev databases.

    You can alterbnatively think of replication

    regards
    Hrishy

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