Oracle partition export and import using datapump

Oracle partition  export and import using datapump via parfile ....

SQL> create user user1 identified by user1;



SQL> create user user2 identified by user2;



grant connect, resource to user1, user2;


2.Create datapump directory.

CREATE OR REPLACE DIRECTORY EXPDP_DIR AS '/dumps/expdp';
GRANT READ, WRITE ON DIRECTORY EXPDP_DIR to user1,user2;



3.Create partition table SALES.


alter session set current_schema=user1;

or

 conn user1/user1




Create table 


CREATE TABLE sales
( name varchar2(5),
time_id DATE)
PARTITION BY RANGE (time_id)
(PARTITION sales_AUG VALUES LESS THAN (TO_DATE('01-AUG-2014','dd-MON-yyyy'))
TABLESPACE users,
PARTITION sales_SEP VALUES LESS THAN (TO_DATE('01-SEP-2014','dd-MON-yyyy'))
TABLESPACE users,
PARTITION sales_OCT VALUES LESS THAN (TO_DATE('01-OCT-2014','dd-MON-yyyy'))
TABLESPACE users,
PARTITION sales_NOV VALUES LESS THAN (TO_DATE('01-NOV-2014','dd-MON-yyyy'))
TABLESPACE users,
PARTITION sales_DEV VALUES LESS THAN (TO_DATE('01-DEC-2014','dd-MON-yyyy'))
TABLESPACE users );


Table created.




  
insert into USER1.sales values('prod1','01-AUG-2014');
insert into USER1.sales values('prod2','01-SEP-2014');
insert into USER1.sales values('prod3','01-OCT-2014');




 SELECT partition_name FROM dba_tab_partitions WHERE table_name = 'SALES' and TABLE_OWNER='USER1';

PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
SALES_AUG
SALES_DEV
SALES_NOV
SALES_OCT
SALES_SEP




  
col owner for a20
 SELECT owner,partitioned FROM dba_tables WHERE table_name = 'SALES';


OWNER                PAR
-------------------- ---
USER1                YES





 SELECT partition_name FROM dba_tab_partitions WHERE table_name = 'SALES';

PARTITION_NAME
------------------------------
SALES_AUG
SALES_DEV
SALES_NOV
SALES_OCT
SALES_SEP






to create par file 

set linesize 400
col OBJECT_NAME for a20 
col SUBOBJECT_NAME for a20
col created for a25
col owner for a20
col list for a40
col list for a40
select 'tables='||owner||'.'|| object_name||':'||subobject_name list
--||',' ||'--'||created list
from dba_objects 
where object_type='TABLE PARTITION' 
and owner='USER1'
--and trunc(created) = trunc(sysdate)
order by CREATED
;



tables=USER1.SALES:SALES_DEV
tables=USER1.SALES:SALES_NOV
tables=USER1.SALES:SALES_AUG
tables=USER1.SALES:SALES_SEP
tables=USER1.SALES:SALES_OCT



userid="/ as sysdba"
directory=EXPDP_DIR
logfile=sales_table_partition.log
dumpfile=sales_table_partition.dmp
tables=USER1.SALES:SALES_DEV
tables=USER1.SALES:SALES_NOV
tables=USER1.SALES:SALES_AUG
tables=USER1.SALES:SALES_SEP
tables=USER1.SALES:SALES_OCT
 


expdp  parfile=expdp_table.par


 expdp  parfile=expdp_table.par

Export: Release 12.2.0.1.0 - Production on Wed Jun 5 01:55:56 2024

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA parfile=expdp_table.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "USER1"."SALES":"SALES_NOV"                 5.492 KB       1 rows
. . exported "USER1"."SALES":"SALES_OCT"                 5.492 KB       1 rows
. . exported "USER1"."SALES":"SALES_SEP"                 5.492 KB       1 rows
. . exported "USER1"."SALES":"SALES_AUG"                     0 KB       0 rows
. . exported "USER1"."SALES":"SALES_DEV"                     0 KB       0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /dumps/expdp/sales_table_partition.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jun 5 01:58:35 2024 elapsed 0 00:02:38




for imp 



cat impdp_table.par
userid="/ as sysdba"
directory=EXPDP_DIR
remap_schema=USER1:USER2
dumpfile=sales_table_partition.dmp
tables=USER1.SALES:SALES_DEV
tables=USER1.SALES:SALES_NOV
tables=USER1.SALES:SALES_AUG
tables=USER1.SALES:SALES_SEP
tables=USER1.SALES:SALES_OCT
 

impdp  parfile=impdp_table.par

Import: Release 12.2.0.1.0 - Production on Wed Jun 5 02:06:57 2024

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA parfile=impdp_table.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER2"."SALES":"SALES_NOV"                 5.492 KB       1 rows
. . imported "USER2"."SALES":"SALES_OCT"                 5.492 KB       1 rows
. . imported "USER2"."SALES":"SALES_SEP"                 5.492 KB       1 rows
. . imported "USER2"."SALES":"SALES_AUG"                     0 KB       0 rows
. . imported "USER2"."SALES":"SALES_DEV"                     0 KB       0 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jun 5 02:07:07 2024 elapsed 0 00:00:08


===========================================================


to check row count !!!


define TABLE_OWNER='USER2'
define TABLE_NAME='SALES'

set serverout on size 1000000  verify off

declare

sql_stmt varchar2(1024);

row_count number;
cursor get_tab is
select table_name,partition_name from dba_tab_partitions where table_owner=upper('&TABLE_OWNER') and table_name='&TABLE_NAME'
--and rownum<20
;


begin
dbms_output.put_line('Record Counts for table_name.....  ');
dbms_output.put_line('Log file to numrows_part_&TABLE_OWNER.lst ....');
dbms_output.put_line('....');

for get_tab_rec in get_tab loop


BEGIN

sql_stmt := 'select count(*) from &TABLE_OWNER..'||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';

EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table '||rpad(get_tab_rec.table_name||'('||get_tab_rec.partition_name||')',50)||' '||TO_CHAR(row_count)||' rows.');
exception when others then dbms_output.put_line ('Error counting rows for table '||get_tab_rec.table_name);
END;
end loop;
end;
/

Table SALES(SALES_AUG)                                   0 rows.
Table SALES(SALES_DEV)                                   0 rows.
Table SALES(SALES_NOV)                                   1 rows.
Table SALES(SALES_OCT)                                   1 rows.
Table SALES(SALES_SEP)                                   1 rows.

PL/SQL procedure successfully completed.



or



 
define 1 ='USER1';
 
set serveroutput on

declare 

p_count number;

begin
for i in (select * from DBA_tab_partitions where TABLE_OWNER='&1') loop 
 execute immediate 'select count(*) from '||i.TABLE_OWNER||'.'||i.TABLE_NAME|| ' partition('||i.partition_name||')' into p_count;
 dbms_output.put_line(i.partition_name||' '||to_char(p_count));
 end loop;
 end ;
 


SALES_AUG 0
SALES_SEP 1
SALES_OCT 1
SALES_NOV 1
SALES_DEV 0