REFRESH SCHEMA from the dump taken from ORACLE 9i DB to Oracle 11g DB

AT SOURCE DATABASE:

Export the desired dump using exp utility from the source database(Oracle 9i) and transferred to Target Database using scp.

AT TARGET DATABASE:

Fetch the below information from target database:

[oracle@Test]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 31 16:12:18 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select username, account_status, created from dba_users where username='SCOTT';

USERNAME                       ACCOUNT_STATUS                   CREATED

------------------------------ -------------------------------- ---------

SCOTT                      OPEN                             26-JUL-17

SQL> select owner, sum(bytes)/1024/1024  "MB"  from dba_segments where owner='SCOTT' group by owner;

OWNER                                  MB

------------------------------ ----------

SCOTT                         2085.25

SQL> select object_type, count(*) from dba_objects where owner='SCOTT' group by object_type;

OBJECT_TYPE           COUNT(*)

------------------- ----------

INDEX                       98

TABLE                      100

---DROPING INDEX, FUNCTION ETC

Set feedback off

Set long 9999999

spool drop_idx.log

SELECT 'DROP '||object_type||' '||owner||'.'||OBJECT_NAME||';' FROM DBA_OBJECTS WHERE OWNER IN('SCOTT');

spool off;

spool drop_table.log

---droping table

SELECT 'DROP TABLE '||owner||'.'||TABLE_NAME||' CASCADE CONSTRAINT PURGE;' FROM DBA_TABLES WHERE OWNER IN('SCOTT');

spool off;

For safer side get the ddl of roles and permission of the schema.

spool table_privs.log

--Table Privilege granted

SELECT 'GRANT '||PRIVILEGE|| ' ON ' ||GRANTOR|| '.' ||TABLE_NAME|| ' TO '  ||GRANTEE|| ';' from dba_tab_privs where grantor in('SCOTT');

spool off;

spool role.log

--ROLE GRANTED

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT') from dual;

spool off;

SQL> !ls -lrth

total 16K

-rw-r--r-- 1 oracle oinstall 193 Jul 31 16:56 table_privs.log

-rw-r--r-- 1 oracle oinstall 926 Jul 31 16:56 role.log

-rw-r--r-- 1 oracle oinstall 130 Jul 31 16:56 drop_idx.sql

-rw-r--r-- 1 oracle oinstall 169 Jul 31 16:56 drop_table.sql

Open the drop_idx.sql and drop_table.sql and remove unnecessary headers and footers and execute both scripts

SQL> @drop_idx.sql

Table dropped.

DROP INDEX SCOTT.ATBKET_P_KEY

.

.

.

SQL> @drop_table.sql

DROP TABLE SCOTT.ATBKET CASCADE CONSTRAINT PURGE

.

.

.

Import the dump using below command:

imp file=/u01/app/oracle/admin/oracle/dpdump/SCOTT.dmp log=/home/oracle/SCOTT.log fromuser=scott touser=scott ignore=y

Import: Release 11.2.0.1.0 - Production on Mon Jul 31 19:14:01 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: scott

Password: tiger

Than press Enter