Export import Oracle Table statistics

 
Export import Oracle Table statistics 
 
--SOURCE DB

SQL> select owner,table_name,last_analyzed from dba_tables where owner='SCOTT' and table_name='EMPLOYEE';
 
OWNER                          TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ --------
SCOTT                          EMPLOYEE                       23.04.19
 
--SOURCE DB

SQL> BEGIN
     DBMS_STATS.CREATE_STAT_TABLE (ownname => 'SCOTT',stattab => 'STATTAB_EMPLOYEE');
     END;
     /  2    3    4
 
PL/SQL procedure successfully completed. 

--SOURCE DB

SQL> BEGIN  dbms_stats.export_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE',stattab=>'STATTAB_EMPLOYEE',cascade=>true);
END;
/  2    3    4
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> --SOURCE DB

SQL> select owner,table_name from dba_tables where table_name='STATTAB_EMPLOYEE';
 
OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCOTT                         STATTAB_EMPLOYEE

 --SOURCE DB
SQL> select count(1) from SCOTT.STATTAB_EMPLOYEE;
 
  COUNT(1)
----------
        99
 
--SOURCE DB : Export statistics of table EMPLOYEE
[oracle@rac02 ~]$ expdp dumpfile=STATTAB_EMPLOYEE_28SEPT2021.dmp logfile=STATTAB_EMPLOYEE_28SEPT2021.log tables=SCOTT.STATTAB_EMPLOYEE directory=SRC_EMPLOYEE_EXPORT_DIR
 
Export: Release 10.2.0.3.0 - Production on Monday, 27 September, 2021 23:52:28
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Username: / as sysdba
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA dumpfile=STATTAB_EMPLOYEE_28SEPT2021.dmp logfile=STATTAB_EMPLOYEE_28SEPT2021.log tables=SCOTT.STATTAB_EMPLOYEE directory=SRC_SCOTT_EXPORT_DIR
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "SCOTT"."STATTAB_EMPLOYEE"               30.07 KB      99 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u03/EXPORT_BACKUP/STATTAB_EMPLOYEE_28SEPT2021.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 23:52:35
  
SQL> set pages 200 lines 192
SQL> col DIRECTORY_NAME format a30
SQL> col DIRECTORY_PATH format a40
select directory_name,directory_path from dba_directories;
 
DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ----------------------------------------
TRINITYDUMP                    /backup/expdp
XML_DIR                        /home/oracle/xml
SRC_SCOTT_EXPORT_DIR           /u03/EXPORT_BACKUP
WORK_DIR                       /opt/oracle/product/10.2.0/db_1/work
ADMIN_DIR                      /opt/oracle/product/10.2.0/db_1/md/admin
DATA_PUMP_DIR                  /opt/oracle/product/10.2.0/db_1/rdbms/log
 
 
7 rows selected.
 
SQL> !ls -lrt /u03/EXPORT_BACKUP
total 112
-rw-r--r-- 1 oracle oinstall   1132 Sep 27 23:52 STATTAB_EMPLOYEE_28SEPT2021.log
-rw-r----- 1 oracle oinstall 106496 Sep 27 23:52 STATTAB_EMPLOYEE_28SEPT2021.dmp
 
--SCP  STATTAB_EMPLOYEE_28SEPT2021.dmp to target DB Server
--TARGET DB 
--import STATTABB_EMPLOYEE_28SEPT2021.dmp in the target DB

--impdp dumpfile=STATTAB_EMPLOYEE_28SEPT2021.dmp logfile=impdp_STATTAB_EMPLOYEE_28SEPT2021.log tables=SCOTT.STATTAB_EMPLOYEE directory=TGT_EMPLOYEE_IMPORT_DIR

--SOURCE DB 

SQL> select owner,table_name,last_analyzed from dba_tables where owner='SCOTT' and table_name='EMPLOYEE';
 
OWNER                          TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ --------
SCOTT                          EMPLOYEE                       23.04.19
 
--TARGET DB 

SQL> select owner,table_name,last_analyzed from dba_tables where owner='SCOTT' and table_name='EMPLOYEE';
 
OWNER                          TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ --------
SCOTT                          EMPLOYEE                       28.09.21
--TARGET DB import the  
SQL> BEGIN  dbms_stats.import_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE',stattab=>'STATTAB_EMPLOYEE',cascade=>true);
END;
/  
 
PL/SQL procedure successfully completed.
--TARGET DB  
SQL> select owner,table_name,last_analyzed from dba_tables where owner='SCOTT' and table_name='EMPLOYEE';
 
OWNER                          TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ --------
SCOTT                           EMPLOYEE                      23.04.19   -- statistics imported successfully