The Oracle Database verifies whether the imported metadata of IMP overwrites the historical table data.

The Oracle Database verifies whether the imported metadata of IMP overwrites the historical table data.

Scenario: When imp imports data, the final trigger reports an error and exits without importing stored procedures, triggers, and functions.
Now exp exports the metadata separately, and imp imports the metadata to verify whether the imported table data is affected.

Test environment: CentOS 6.7 + Oracle 11.2.0.4
Construct the experiment environment:

• 1. Export scott's tables and data
• 2. scott user creation process, functions, triggers
• 3. Export scott metadata
• 4. Delete scott users
• 5. Import scott tables and data
• 6. Import Scott metadata

1. Export scott's tables and data

To export the table and data of scott, there are no triggers, procedures, and functions:

 exp scott/tiger OWNER=scott BUFFER=10240000 STATISTICS=none RESUMABLE=y FILE=scott_exp.dmp LOG=scott_exp.log 

2. scott user creation process, functions, triggers

Scott user creation process:

create or replace procedure pro_insert_dept isbegin  insert into dept values (99, 'xx_dept_name', 'Beijing');end;/

Scott user creates a function:

create or replace function sp_fun1(spName varchar2) return number isyearSal number(7, 2);beginselect sal * 12 + nvl(comm, 0) * 12 into yearSal from emp where ename = spName; return yearSal;end;/

Scott user trigger:

-- Create sequence seq_del_id; -- create table emp_del_info (autoid number primary key, deptno number, empno number, ename varchar2 (20), del_rq date ); -- create trigger create or replace trigger trg_del_emp_info before delete on emp for each row declare -- local variables here begin insert into emp_del_info (autoid, deptno, empno, ename, del_rq) VALUES ,: OLD. deptno,: OLD. empno,: OLD. ename, sysdate); END ;/

3. Export scott metadata

Export scott metadata:

 exp scott/tiger OWNER=scott ROWS=n BUFFER=10240000 STATISTICS=none RESUMABLE=y FILE=scott_metadata_exp.dmp LOG=scott_metadata_exp.log 

At this time, the exported metadata includes triggers, procedures, and functions.

4. Delete scott users

Confirm that no session is logged on by scott:

 

select 'alter system kill session '''||sid||','||serial#||''''||';' from v$session where username='SCOTT';

If the preceding query has results, copy and execute the results to kill the session logged on to scott.

Delete scott User:

SQL>  drop user scott cascade;User dropped.

5. Import scott tables and data

Create a user and grant permissions:

SQL> create user scott identified by tiger default tablespace users;User created.SQL> grant connect, resource to scott;Grant succeeded.

Import tables and data:

imp scott/tiger BUFFER=10240000 RESUMABLE=y FILE=scott_exp.dmp LOG=imp_scott_exp.log IGNORE=y FULL=y

At this time, only the table and table data are imported, and there are no triggers, procedures, and functions.

6. Import Scott metadata

 

imp scott/tiger BUFFER=10240000 RESUMABLE=y FILE=scott_metadata_exp.dmp LOG=imp_scott_metadata_exp.log IGNORE=y FULL=y

At this time, only the table structure, trigger, process, function, and other objects are imported,
Finally, verify whether to overwrite the imported table data in the previous step?
The final conclusion is that the imported table data is not overwritten, and the processes, functions, and triggers that have not been imported have been successfully imported.