-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|