I have two DB servers and they originally contain identical DB. However, since people update database B and the export dump from database B is done regularly, what is the safest way to import the dump file into database A? All the objects and data are in a single user's schema.
Should I drop the user, recreate it, and then import from the dump file? Will I be missiing something if I do it this way?
Use this script to drop objects belonging to the user
select 'drop ' || object_type || ' ' || owner || '.' || object_name || ';'
where owner = upper('&1')
and object_type not in ('PACKAGE BODY','INDEX')
order by object_type, object_name
After the objects are dropped, just coalesce the tablespaces belonging to the user. Next u can import the objects with fromuser,touser option
Whatever it is, u need to run a script before u start the import-whether it is dropping the objects in a schema or dropping the user and then running the script for the user creation- it is matter of which way u like.
we dont have to run any scripts beforehand to delete the objects, IF u drop the user with CASCADE option. The objects which your scripts will delete will be deleted by this command. Or am I missing something?