DBA Hub

📋Steps in this guide1/5

Export and Import schema statistics from one database to another

Export and Import schema statistics from one database to another On Source 0. Environment 1. Create table to store statistics 2. Store schema stats to table STATS_TABLE 3. Export the table STATS_TABLE using datapump 4. Transfer the dump to target server On Target 5. Delete the stats before import on target server 6. Import using … Continue reading Export/Import schema statistics →

oracle clusteringintermediate
by OracleDba
15 views
1

Overview

On Source On Target Environment:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Source:
OS:
Oracle Linux 5.7
Database version :
11.2.0.3.0
Database Name:
orcl
Schema Name:
SH
Host name:
rac1.rajasekhar.com
Target:
OS:
Oracle Linux 5.7
Database version :
11.2.0.3.0
Database Name:
cat
Schema Name:
SH
Host name:
rac2.rajasekhar.com
2

Section 2

Source: Step 1: Create table to store statistics Step 2: Export schema stats – will be stored in the ‘STATS_TABLE’ Step 3: Export the table STATS_TABLE using datapump

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SQL> show user
USER is "SYS"
SQL>
EXEC DBMS_STATS.CREATE_STAT_TABLE('SH', 'STATS_TABLE');
PL/SQL procedure successfully completed.

SQL> select OWNER, OBJECT_NAME,object_type, CREATED from dba_objects where OBJECT_NAME='STATS_TABLE';

OWNER    OBJECT_NAME          OBJECT_TYPE         CREATED
-------- -------------------- ------------------- ---------
SH       STATS_TABLE          TABLE               31-AUG-16
SH       STATS_TABLE          INDEX               31-AUG-16

SQL>

SQL> select count(*) from sh.STATS_TABLE;

  COUNT(*)
----------
         0

SQL>

SQL>
exec dbms_stats.export_schema_stats(ownname => 'SH',stattab => 'STATS_TABLE');
PL/SQL procedure successfully completed.

SQL> select count(*) from sh.STATS_TABLE;

  COUNT(*)
----------
      3966

SQL>
3

Section 3

Step 4: Transfer the dump to target server On Target =========== Step 5: Delete the stats before import on target server Step 6: Importing STATS_TABLE table in scott schema

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [orcl] ?
[oracle@rac1 ~]$
expdp directory=DATA_PUMP_DIR dumpfile=stats.dmp logfile=stats.log tables=SH.STATS_TABLE
Export: Release 11.2.0.3.0 - Production on Wed Aug 31 16:59:34 2016

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=stats.dmp logfile=stats.log tables=SH.STATS_TABLE
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 640 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "SH"."STATS_TABLE"                          425.6 KB    3966 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/orcl/dpdump/stats.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 16:59:58

[oracle@rac1 ~]$

[oracle@rac1 ~]$ cd /u01/app/oracle/admin/orcl/dpdump/
[oracle@rac1 dpdump]$
scp stats.dmp [email protected]:/u01/app/oracle/admin/cat/dpdump/
stats.dmp 						 100%  532KB 532.0KB/s   00:00
[oracle@rac1 dpdump]$

-- before delete stats, please have backup but i am not taking here because it is test machine.
EXEC DBMS_STATS.CREATE_STAT_TABLE('SH', 'STATS');
exec dbms_stats.export_schema_stats(ownname => 'SH',stattab => 'STATS');
SQL>
EXEC DBMS_STATS.delete_schema_stats('SH');
PL/SQL procedure successfully completed.

SQL>
4

Section 4

. . imported "SH"."STATS_TABLE" 425.6 KB 3966 Step 7: Importing into same schema(SH – SH), then ignore step 8 Step 8: Importing into different schema( USER A – USER B), then skip step 7

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [cat] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$
impdp directory=DATA_PUMP_DIR dumpfile=stats.dmp logfile=impstats.log TABLES=SH.STATS_TABLE
Import: Release 11.2.0.3.0 - Production on Wed Aug 31 17:08:15 2016

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=stats.dmp logfile=impstats.log TABLES=SH.STATS_TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SH"."STATS_TABLE"                          425.6 KB    3966
rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01"
successfully completed at 17:08:20
[oracle@rac2 ~]$


SQL> select OWNER, OBJECT_NAME,object_type, CREATED from dba_objects where OBJECT_NAME='STATS_TABLE';

OWNER   OBJECT_NAME          OBJECT_TYPE         CREATED
------- -------------------- ------------------- ---------
SH      STATS_TABLE          INDEX               31-AUG-16
SH      STATS_TABLE          TABLE               31-AUG-16

SQL> select count(*) from sh.STATS_TABLE;

  COUNT(*)
----------
      3966

SQL>

SQL> exec dbms_stats.import_schema_stats(OWNNAME=>'SH', STATTAB=>'STATS_TABLE');

PL/SQL procedure successfully completed.

SQL>
5

Section 5

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using. Reference: How to Transfer Statistics from one Database to Another (Doc ID 333742.1) How to Use DBMS_STATS to Move Statistics to a Different Database (Doc ID 117203.1) Reference:

Code/Command (click line numbers to comment):

1
2
3
update newschema.STATS_TABLE set c5='NEW_SCHEMA_NAME';
commit;
dbms_stats.import_schema_stats(OWNNAME=>'NEW_SCHEMA_NAME', STATTAB=>'STATS');

Comments (0)

Please to add comments

No comments yet. Be the first to comment!