통계 export/import

■dbms_stats 프로시저 사용한다.

 

1. create_stat_table 로 통계테이블을 만든다.

PROCEDURE CREATE_STAT_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 STATTAB                        VARCHAR2                IN
 TBLSPACE                       VARCHAR2                IN     DEFAULT
 GLOBAL_TEMPORARY               BOOLEAN                 IN     DEFAULT

 

$ sqlplus hr/hr

 

orcl@HR> exec dbms_stats.create_stat_table('hr','stat');

PL/SQL procedure successfully completed.

 

2. export_*_stats 를 통해 통계 테이블로 저장

* 위치에 들어갈 수 있는 값
database, column, dictionary, fixed_object, index, pending, schema, system, table

 

3. 다른 db로 통계 테이블 이동(datapump등 사용)

 

4. import_*_stats 를 통해 통계를 딕셔너리에 입력

* 위치에 들어갈 수 있는 값
database, column, dictionary, fixed_object, index, pending, schema, system, table

 

■LAB

1. export_table_stats를 통해 통계를 테이블에 저장

 

PROCEDURE EXPORT_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN     DEFAULT
 STATTAB                        VARCHAR2                IN
 STATID                         VARCHAR2                IN     DEFAULT
 CASCADE                        BOOLEAN                 IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT

 

$ sqlplus hr/hr

 

orcl@HR> exec dbms_stats.export_table_stats('hr','employees',null,'stat');

PL/SQL procedure successfully completed.

 

※stat은 위 1단계에서 만든 통계 저장용 테이블이다.

 

orcl@SYS>  exec print_table('select * from hr.stat where rownum=1');
STATID                        :
TYPE                          : C
VERSION                       : 5
FLAGS                         : 2
C1                            : EMPLOYEES
C2                            :
C3                            :
C4                            : LAST_NAME
C5                            : HR
N1                            : 103
N2                            : .00462962962962963
N3                            : 103
N4                            : 108
N5                            : 0
N6                            : 339495007414717000000000000000000000
N7                            : 469506047861463000000000000000000000
N8                            : 8
N9                            : 1
N10                           : 21
N11                           : 355127267452184000000000000000000000
N12                           :
D1                            : 23-nov-2015 11:00:08
R1                            : 4162656C
R2                            : 5A6C6F746B6579
CH1                           :
CL1                           :

 

2. import_table_stats를 통해 통계를 dictionary에 저장. 만약 다른 DB라면 export/import or empdp/impdp를 통해 테이블을 넣었을 것이다.

 

PROCEDURE IMPORT_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN     DEFAULT
 STATTAB                        VARCHAR2                IN
 STATID                         VARCHAR2                IN     DEFAULT
 CASCADE                        BOOLEAN                 IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT

 

orcl@HR> exec dbms_stats.import_table_stats('hr', 'employees', null, 'stat');                                                

PL/SQL procedure successfully completed.