오라클] 사용자 단위 데이터 이관(exp, imp)

2017.10.23 07:00



DB user 데이터 이관




A DB에서 사용하던 사용자 계정 데이터를 B DB로 이관




- A DB SID : OLDCOM(오라클 9i, utf-8, unix)

- B DB SID : NEWCOM(오라클 11g, windows 7)

- 이관 사용자 계정 : COMUSER





1) OLDCOM 캐릭터셋 조회


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
sys@OLDCOM SQL> col value format a40
sys@OLDCOM SQL> select * from nls_database_parameters;
 
PARAMETER                                VALUE
---------------------------------------- ----------------------------------------
NLS_LANGUAGE                             AMERICAN
NLS_TERRITORY                            AMERICA
NLS_CURRENCY                             $
NLS_ISO_CURRENCY                         AMERICA
NLS_NUMERIC_CHARACTERS                   .,
NLS_CHARACTERSET                         UTF8
NLS_CALENDAR                             GREGORIAN
NLS_DATE_FORMAT                          DD-MON-RR
NLS_DATE_LANGUAGE                        AMERICAN
NLS_SORT                                 BINARY
NLS_TIME_FORMAT                          HH.MI.SSXFF AM
 
PARAMETER                                VALUE
---------------------------------------- ----------------------------------------
NLS_TIMESTAMP_FORMAT                     DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                       HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                  DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                        $
NLS_COMP                                 BINARY
NLS_LENGTH_SEMANTICS                     BYTE
NLS_NCHAR_CONV_EXCP                      FALSE
NLS_NCHAR_CHARACTERSET                   AL16UTF16
NLS_RDBMS_VERSION                        10.2.0.5.0
 
20 rows selected.
cs




2) 캐릭터셋 변경


- 데이터를 이관할 B DB(NEWCOM)의 캐릭터셋을 A DB(OLDCOM)의 캐릭터셋과 동일하게 변경



1
2
3
4
5
6
sys@NEWCOMSQL> conn /as sysdba
sys@NEWCOMSQL> update sys.props$ set value$='UTF8' where name='NLS_CHARACTERSET';
sys@NEWCOMSQL> commit;
sys@NEWCOMSQL> shutdown immediate; -- DB 재기동 필요
 
sys@NEWCOMSQL> startup
cs







3) A DB user별 object 확인


1
2
3
4
5
6
7
8
9
10
11
12
13
sys@OLDCOM SQL> select owner, object_type, count(*) from dba_objects
  2       where object_type in ('TABLE''INDEX''VIEW''PROCEDURE''FUNCTION')
  3       group by owner, object_type order by 12;
     
OWNER                          OBJECT_TYPE                      COUNT(*)
------------------------------ ------------------------------ ----------
COMUSER                        FUNCTION                                3
COMUSER                        INDEX                                 109
COMUSER                        PROCEDURE                               3
COMUSER                        TABLE                                 215
COMUSER                        VIEW                                   21
 
--- 생략 ---
cs







4) 테이블스페이스 등 데이터 사용량 조회


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
47
48
49
50
51
52
53
54
55
sys@OLDCOM SQL> sys@OLDCOM SQL> set linesize 300
sys@OLDCOM SQL> column tablespace_name heading Tablespace format a19
sys@OLDCOM SQL> column object_count heading 'Objects|(#)' format 999990
sys@OLDCOM SQL> column mb heading Mb format 9999990
sys@OLDCOM SQL> column sum(frags) heading 'Frags|(#)' format 9999
sys@OLDCOM SQL> column avail heading 'Max|(Mb)' format 999999.99
sys@OLDCOM SQL> column free heading 'Free|(%)' format 9999999.9
sys@OLDCOM SQL> column bytesize heading 'Size|(Mb)' format 9999999
sys@OLDCOM SQL> column byteused heading 'Used|(Mb)' format 9999990
sys@OLDCOM SQL> column bytefree heading 'Free|(Mb)' format 9999999
sys@OLDCOM SQL> column init_ext heading 'Initial|(K)' format 999999
sys@OLDCOM SQL> column next_ext heading 'Next|(K)' format 999999
sys@OLDCOM SQL> break on report
sys@OLDCOM SQL> compute sum of object_count bytesize byteused bytefree on report
sys@OLDCOM SQL> select tablespace_name,sum(obj_cnt) object_count,sum(ini_ext) init_ext,sum(nex_ext) next_ext,sum(byte)/1048576 bytesize, 
  2   (sum(byte)/1048576)- (sum(fbyte)/1048576) byteused,sum(fbyte)/1048576 bytefree, sum(frags), sum(largest)/1048576 avail,
  3   (sum(fbyte)/decode(sum(byte),0,1,sum(byte)) )*100 free 
  4  from (select tablespace_name,0 obj_cnt,0 ini_ext,0 nex_ext, 0 byte, sum(bytes) fbyte, count(*) frags, max(bytes) largest
  5  from dba_free_space 
  6  group by tablespace_name
  7  union
  8  select tablespace_name,000, sum(bytes), 000 from dba_data_files 
  9  group by tablespace_name
 10  union
 11  select tablespace_name, 0, initial_extent/1024 ini_ext, next_extent/1024 nex_ext, 0,0,0,0
 12  from dba_tablespaces
 13  union
 14  select tablespace_name, count(*) obj_cnt, 000000 
 15  from dba_segments
 16  group by tablespace_name)
 17  group by tablespace_name ;
tti off
 
 
                    Objects Initial    Next     Size     Used     Free Frags        Max       Free
Tablespace              (#)     (K)     (K)     (Mb)     (Mb)     (Mb)   (#)       (Mb)        (%)
------------------- ------- ------- ------- -------- -------- -------- ----- ---------- ----------
SYSTEM                 1197      64       0      998      217      781     1     780.81       78.2
USERS                     0      64       0      498        0      498     1     497.94      100.0
TEMP                      0    1024    1024        0        0        0     0        .00         .0
UNDOTBS2                 15      64       0    24990       14    24976    28    3965.81       99.9
****_INDEX                0      64       0     9998        0     9998     3    3968.00      100.0
COMUSER_DATA            710      64       0    49990     3025    46965  1151    3968.00       93.9
COMUSER_INDEX           129      64       0    19996      886    19110   258    3968.00       95.6
****_DATA                 0      64       0     9998        0     9998     3    3968.00      100.0
SYSAUX                 1144      64       0      998      361      637    32     622.94       63.8
UNDOTBS1                 10      64       0    24990    10535    14455    99    1879.00       57.8
 
                    Objects Initial    Next     Size     Used     Free Frags        Max       Free
Tablespace              (#)     (K)     (K)     (Mb)     (Mb)     (Mb)   (#)       (Mb)        (%)
------------------- ------- ------- ------- -------- -------- -------- ----- ---------- ----------
                    -------                 -------- -------- --------
sum                    3205                   142456    15039   127417
 
10 rows selected.
cs




5) user별 디폴트 테이블스페이스 확인


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
sys@OLDCOM SQL> col username format a30
sys@OLDCOM SQL> col default_tablespace format a40 
sys@OLDCOM SQL> col temporary_tablespace format a40
sys@OLDCOM SQL> select username, default_tablespace, temporary_tablespace from dba_users
  2       where username not in ('SYS''SYSTEM');
 
USERNAME                       DEFAULT_TABLESPACE                       TEMPORARY_TABLESPACE
------------------------------ ---------------------------------------- ----------------------------------------
****                           ****_DATA                                TEMP
COMUSER                        COMUSER_DATA                             TEMP
****                           COMUSER_DATA                             TEMP
TSMSYS                         USERS                                    TEMP
DIP                            USERS                                    TEMP
ORACLE_OCM                     USERS                                    TEMP
DBSNMP                         SYSAUX                                   TEMP
WMSYS                          SYSAUX                                   TEMP
OUTLN                          SYSTEM                                   TEMP
 
9 rows selected.
cs






6) 데이터 export



1
2
3
$ exp system/system_pw FILE='/orahome/comuser.dmp' 
LOG='/orahome/comuser.log' GRANTS=Y INDEXES=Y ROWS=Y CONSTRAINTS=
TRIGGERS=Y COMPRESS=N DIRECT=Y CONSISTENT=N STATISTICS=ESTIMATE OWNER=COMUSER
cs






7) B DB 테이블스페이스 생성(A DB와 동일하게)



1
2
3
4
5
6
7
8
sys@NEWCOM SQL> create tablespace COMUSER_DATA datafile 'D:\app\user\oradata\comuser_data01.dbf' 
                size 9998M autoextend off;
sys@NEWCOM SQL> alter tablespace COMUSER_DATA add datafile 'D:\app\user\oradata\comuser_data02.dbf' 
                size 9998M autoextend off;
sys@NEWCOM SQL> create tablespace COMUSER_INDEX datafile 'D:\app\user\oradata\comuser_index01.dbf' 
                size 9998M autoextend off;
sys@NEWCOM SQL> alter tablespace COMUSER_INDEX add datafile 'D:\app\user\oradata\comuser_index02.dbf' 
                size 9998M autoextend off;
cs





8) USER 생성 및 권한 부여


1
2
3
sys@NEWCOM SQL> CREATE USER COMUSER IDENTIFIED BY COMUSER DEFAULT TABLESPACE COMUSER_DATA TEMPORARY TABLESPACE TEMP;
 
sys@NEWCOM SQL>  grant connect, resource to COMUSER;
cs






9) 데이터 import



1
2
D:\> imp system/system_pw file='D:\app\comuser.dmp' 
     fromuser=COMUSER touser=COMUSER log=COMUSER.log
cs




10) import 수행 후 3), 4), 5)로 데이터 이관 확인



저작자 표시 비영리 변경 금지
신고

또루아빠 SQL(오라클,mysql) , , , , , , , ,