exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'STAT_BACKUP',tblspace=>'USERS');
1. Create STAT_BACKUP table
exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'STAT_BACKUP',tblspace=>'USERS');
PL/SQL procedure successfully completed.
SQL> desc scott.STAT_BACKUP
Name Null? Type
----------------------------------------- -------- ----------------------------
STATID VARCHAR2(128)
TYPE CHAR(1)
VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(128)
C2 VARCHAR2(128)
C3 VARCHAR2(128)
C4 VARCHAR2(128)
C5 VARCHAR2(128)
C6 VARCHAR2(128)
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
N7 NUMBER
N8 NUMBER
N9 NUMBER
N10 NUMBER
N11 NUMBER
N12 NUMBER
N13 NUMBER
D1 DATE
T1 TIMESTAMP(6) WITH TIME ZONE
R1 RAW(1000)
R2 RAW(1000)
R3 RAW(1000)
CH1 VARCHAR2(1000)
CL1 CLOB
BL1 BLOB
2.
-- if required !!
SQL> truncate table scott.STAT_BACKUP;
Table truncated.
3.
exec dbms_stats.export_table_stats(ownname=>'SCOTT', tabname=>'EMP', stattab=>'STAT_BACKUP', cascade=>true);
SQL> exec dbms_stats.export_table_stats(ownname=>'SCOTT', tabname=>'EMP', stattab=>'STAT_BACKUP', cascade=>true);
PL/SQL procedure successfully completed.
SQL> select count(*) from scott.STAT_BACKUP ;
COUNT(*)
----------
21
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set lines 200 pagesize 200
col table_name for a12
col owner for a12
select owner,table_name,last_analyzed from dba_tables where table_name='EMP' and owner='SCOTT';
OWNER TABLE_NAME LAST_ANALYZED
------------ ------------ ----------------
SCOTT EMP 27-03-2024 22:00
4.
expdp STAT_BACKUP table !!!!
expdp scott/vihaan123 tables=STAT_BACKUP dumpfile=STAT_BACKUP.dmp logfile=STAT_BACKUP.log
===
$expdp scott/vihaan123 tables=STAT_BACKUP dumpfile=STAT_BACKUP.dmp logfile=STAT_BACKUP.log DIRECTORY=ORACLE_BASE
Export: Release 12.2.0.1.0 - Production on Wed Jul 16 09:27:54 2025
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 "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** tables=STAT_BACKUP dumpfile=STAT_BACKUP.dmp logfile=STAT_BACKUP.log DIRECTORY=ORACLE_BASE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "SCOTT"."STAT_BACKUP" 19.58 KB 21 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/STAT_BACKUP.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jul 16 09:32:32 2025 elapsed 0 00:04:33
expdp \"/ as sysdba\" tables=scott.STAT_BACKUP dumpfile=STAT_BACKUP1.dmp logfile=STAT_BACKUP1.log DIRECTORY=ORACLE_BASE
Export: Release 12.2.0.1.0 - Production on Wed Jul 16 10:15:05 2025
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" tables=scott.STAT_BACKUP dumpfile=STAT_BACKUP1.dmp logfile=STAT_BACKUP1.log DIRECTORY=ORACLE_BASE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "SCOTT"."STAT_BACKUP" 19.58 KB 21 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/STAT_BACKUP1.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jul 16 10:19:36 2025 elapsed 0 00:04:26
====================
5. STAT_BACKUP table to anuj from scott !!!
$impdp \"/ as sysdba\" tables=scott.STAT_BACKUP DIRECTORY=ORACLE_BASE DUMPFILE=STAT_BACKUP1.dmp remap_schema=scott:anuj TABLE_EXISTS_ACTION=replace LOGFILE=STAT_BACKUP1.log
Import: Release 12.2.0.1.0 - Production on Wed Jul 16 10:25:03 2025
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" tables=scott.STAT_BACKUP DIRECTORY=ORACLE_BASE DUMPFILE=STAT_BACKUP1.dmp remap_schema=scott:anuj TABLE_EXISTS_ACTION=replace LOGFILE=STAT_BACKUP1.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ANUJ"."STAT_BACKUP" 19.58 KB 21 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
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 Jul 16 10:25:42 2025 elapsed 0 00:00:36
SQL> select count(*) from anuj.STAT_BACKUP ;
COUNT(*)
----------
21
6.
import stats from STAT_BACKUP table to anuj.emp table
exec dbms_stats.IMPORT_table_stats(ownname=>'ANUJ', tabname=>'EMP', stattab=>'STAT_BACKUP');
SQL> exec dbms_stats.IMPORT_table_stats(ownname=>'ANUJ', tabname=>'EMP', stattab=>'STAT_BACKUP');
PL/SQL procedure successfully completed.
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set lines 200 pagesize 200
col table_name for a12
col owner for a12
select owner,table_name,last_analyzed from dba_tables where table_name='EMP' and owner='ANUJ';
Wednesday, 16 July 2025
Table statistics Export import in Oracle
Subscribe to:
Comments (Atom)
