I export a database dump from oracle 8i. Drop all table in the target database and importthe database dump, I found that all of the non-ASCII characters can not be displayed correctly.
The way to resolve it is before doing the export and import, try to execute this.
set NLS_LANG=american_america.UTF8 If all your character is stored with the UTF-8
Useful pl/sql
The way to delete the all the tables without drop the user.
set termout off
set feedback off
set pagesize 0
set linesize 200
col object_name format a30
col object_type format a30
col owner format a30
set trimspool on
spool dropall.sql
select 'DROP ' || object_type || ' '|| owner||'.'||object_name|| ';' from all_objects where owner='&username';
spool off
@dropall.sql