drop table test3
/
create table test3 as select * from dba_objects where rownum<1
/
drop table test4
/
create table test4 as select * from dba_objects where rownum<1
/
delete from plan_table
where statement_id='test1'
/
commit
/
EXPLAIN PLAN SET STATEMENT_ID='test1' FOR
INSERT /*+ APPEND test1 */
INTO test3(
OWNER                  
,OBJECT_NAME            
,SUBOBJECT_NAME         
,OBJECT_ID              
,DATA_OBJECT_ID         
,OBJECT_TYPE            
,CREATED                
,LAST_DDL_TIME          
,TIMESTAMP              
,STATUS                 
,TEMPORARY              
,GENERATED              
,SECONDARY              
)
select
OWNER                  
,OBJECT_NAME            
,SUBOBJECT_NAME         
,OBJECT_ID              
,DATA_OBJECT_ID         
,OBJECT_TYPE            
,CREATED                
,LAST_DDL_TIME          
,TIMESTAMP              
,STATUS                 
,TEMPORARY              
,GENERATED              
,SECONDARY              
FROM test4
/
set scan on
set define on
set linesize 300
set pagesize 10000
set wrap off
select plan_table_output from table(dbms_xplan.display)
/
INSERT /*+ APPEND test1 */
INTO test3(
OWNER                  
,OBJECT_NAME            
,SUBOBJECT_NAME         
,OBJECT_ID              
,DATA_OBJECT_ID         
,OBJECT_TYPE            
,CREATED                
,LAST_DDL_TIME          
,TIMESTAMP              
,STATUS                 
,TEMPORARY              
,GENERATED              
,SECONDARY              
)
select
OWNER                  
,OBJECT_NAME            
,SUBOBJECT_NAME         
,OBJECT_ID              
,DATA_OBJECT_ID         
,OBJECT_TYPE            
,CREATED                
,LAST_DDL_TIME          
,TIMESTAMP              
,STATUS                 
,TEMPORARY              
,GENERATED              
,SECONDARY              
FROM test4
/
select sp.sql_id
,      sp.position
,      sp.object_owner
,      sp.object_name
,      sp.operation
,      sp.options
,      sp.object#
from   v$sql s
,      v$sql_plan sp
where  s.sql_text like 'INSERT%APPEND test1%test3%test4%'
and    s.sql_id = sp.sql_id
order by sp.id
/
COMMIT
/