loading

오라클 DB 이관(9i) 스크립트(exp, imp)

2014. 1. 21. 09:51
728x90
반응형

Oracle DB이관(Oracle 9i)



oracle 9i -> 9i (서버만 이관) 

    - SID : jdb

    - oracle : 9.2.0.8

    - 보안상 계정명은 삭제하고 일부만 예제로 남겨둠






#####################################################################

### Character Set 조회

#####################################################################


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               KO16KSC5601

NLS_CALENDAR                   GREGORIAN

NLS_DATE_FORMAT                DD-MON-RR

NLS_DATE_LANGUAGE              AMERICAN

NLS_SORT                       BINARY

NLS_TIME_FORMAT                HH.MI.SSXFF AM

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              9.2.0.8.0



#####################################################################

### Control File 조회

#####################################################################


SQL> select name from v$controlfile;


NAME

------------------------------------------------------------

/ora9208/app/oracle/oradata/jdb/control01.ctl

/ora9208/app/oracle/oradata/jdb/control02.ctl

/ora9208/app/oracle/oradata/jdb/control03.ctl



#####################################################################

### user별 object 조회

#####################################################################


SQL> select owner, object_type, count(*) from dba_objects

     where object_type in ('TABLE', 'INDEX', 'VIEW', 'PROCEDURE', 'FUNCTION')

     group by owner, object_type

     order by 1, 2;


OWNER                          OBJECT_TYPE          COUNT(*)co

------------------------------ ------------------ ----------

BASIC                          FUNCTION                    2

BASIC                          INDEX                     129

BASIC                          PROCEDURE                  46

BASIC                          TABLE                     127

BASIC                          VIEW                        2

......

ORANGE                         FUNCTION                    9

ORANGE                         INDEX                       7

ORANGE                         TABLE                       7

ORANGE                         VIEW                       39

...

SYS                            FUNCTION                   37

SYS                            INDEX                     309

SYS                            PROCEDURE                  25

SYS                            TABLE                     331

SYS                            VIEW                     2059

SYSTEM                         INDEX                     160

SYSTEM                         PROCEDURE                   1

SYSTEM                         TABLE                     130

SYSTEM                         VIEW                       10

.....


73 rows selected.




#####################################################################

### Datafile Size 조회

#####################################################################


SQL> select sum(bytes)/1024/1024 from dba_data_files;


SUM(BYTES)/1024/1024

--------------------

               20146



SQL> select sum(bytes)/1024/1024 from dba_segments;


SUM(BYTES)/1024/1024

--------------------

          9305.29688



#####################################################################

### user별 tablespace 조회

#####################################################################


SQL> select username, default_tablespace, temporary_tablespace from dba_users

     where username not in ('SYS', 'SYSTEM');




USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE

------------------------------ ------------------------------ ------------------------------

ORANGE                         TOOLS                          TEMP

......

BASIC                          SMRT_DATA01                    TEMP

......


17 rows selected.




#####################################################################

### drop user

#####################################################################


select 'DROP USER ' ||  username ||  ' CASCADE' || ';'

from dba_users

where username not in ('SYS', 'SYSTEM')


'DROPUSER'||USERNAME||'CASCADE'||';'

-------------------------------------------------


DROP USER ORANGE CASCADE;

.......

DROP USER BASIC CASCADE;

.......



#####################################################################

### user 및 user password 조회, 생성

#####################################################################


select 'CREATE USER ' || username || ' IDENTIFIED BY values' ||  password ||

' DEFAULT TABLESPACE ' || default_tablespace ||

' TEMPORARY TABLESPACE ' || temporary_tablespace ||';'

from dba_users

where username not in ('SYS','SYSTEM');


'CREATEUSER'||USERNAME||'IDENTIFIEDBY'||USERNAME||'DEFAULTTABLESPACE'||DEFAULT_TABLESPACE||'TEMPORARYTABLESPACE'||TEMPORARY_TABLESPACE||';'

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

.........


CREATE USER ORANGE IDENTIFIED BY values '3D9B7*********' DEFAULT TABLESPACE TOOLS TEMPORARY TABLESPACE TEMP;


..........


CREATE USER BASIC IDENTIFIED BY values 'F428AA2********' DEFAULT TABLESPACE SMRT_DATA01 TEMPORARY TABLESPACE TEMP;


==>>  계정 패스워드는 패스워드 해시값을 그대로 복사해서 사용



SQL> select ' grant connect, resource to ' || username ||';'

     from dba_users

     where username not in ('SYS', 'SYSTEM');


 

'GRANTCONNECT,RESOURCETO'||USERNAME||';'

-----------------------------------------------------------


 grant connect, resource to ORANGE;

 grant connect, resource to BASIC;

.........



17 rows selected.



#####################################################################

### Tablespace Size 조회

#####################################################################


SQL> select tablespace_name, sum(bytes)/1024/1024  || 'M' as MBYTES

     from dba_data_files

     group by tablespace_name

     order by 1;



TABLESPACE_NAME                MBYTES

------------------------------ -----------------------------------------

INDX                           25M

...............

SYSTEM                         500M

TOOLS                          200M

UNDOTBS1                       1000M

USERS                          25M


13 rows selected.



#####################################################################

### datafile이 두개 이상인 Tablespace 조회

#####################################################################


SQL> select tablespace_name , count(*)

     from dba_data_files

     having count(*)>1

     group by tablespace_name

     order by 1;



TABLESPACE_NAME                  COUNT(*)

------------------------------ ----------

RBS                                     3

TS_KRB01                                3





#####################################################################

### Create Tablespace SQL Query Statement 작성

#####################################################################


SQL> set lines 200

SQL> set pages 200

SQL> col file_name for a50

SQL> col tablespace_name for a20

SQL> col Mbytes for a20

SQL> spool create_tablespace.sql

SQL> set echo off;

SQL> set heading off;

SQL> select ' create tablespace ' || tablespace_name ||

     ' datafile ''' || file_name || ''' size ' || bytes/1024/1024 || 'M autoextend off; '

     from dba_data_files

     where tablespace_name not in ('SYSTEM', 'USER', 'RBS', 'TOOLS', 'TEMP')

     and tablespace_name not like '%TMP%'

     and tablespace_name not like '%TEMP%';


 

'CREATETABLESPACE'||TABLESPACE_NAME||'DATAFILE'''||FILE_NAME||'''SIZE'||BYTES/1024/1024||'MAUTOEXTENDOFF;'

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 create tablespace UNDOTBS1 datafile '/ora9208/app/oracle/oradata/jdb/undotbs01.dbf' size 1000M autoextend off;

 create tablespace INDX datafile '/ora9208/app/oracle/oradata/jdb/indx01.dbf' size 25M autoextend off;

 create tablespace USERS datafile '/ora9208/app/oracle/oradata/jdb/users01.dbf' size 25M autoextend off;

 ......................




#####################################################################

###  Create Temporary Tablespace SQL Query Statement 작성

#####################################################################


SQL> set pages 200

SQL> set lines 200

SQL> col file_name for a50

SQL> col tablespace_name for a20

SQL> col Mbytes for a20

SQL> spool create_temp_tablespace.sql

SQL> set echo off;

SQL> set heading off;

SQL> select ' create temporary tablespace ' || tablespace_name ||

     ' tempfile ''' || file_name || ''' size ' || bytes/1024/1024 || 'M autoextensible off; '

     from dba_data_files

     where tablespace_name like '%TMP%'

     or tablespace_name like '%TEMP%';


 create temporary tablespace TEMP tempfile '/oracle/app/oracle/oradata/WWW/temp01.dbf' size 500M autoextensible off;

 create temporary tablespace TS_NOTTEMP01 tempfile '/WWW/Data/ts_not01.dbf' size 1500M autoextensible off;

 create temporary tablespace TS_FRITEMP01 tempfile '/WWW/Data/ts_fri01.dbf' size 1000M autoextensible off;

 create temporary tablespace TS_ORSTEMP01 tempfile '/WWW/Data/ts_orstemp01.dbf' size 500M autoextensible off;

 create temporary tablespace TS_EEXTEMP01 tempfile '/WWW/Data/ts_eex01.dbf' size 500M autoextensible off;

 create temporary tablespace TS_EEXTEMP02 tempfile '/WWW/Data/ts_eex03.dbf' size 500M autoextensible off;



6 rows selected.



#####################################################################

### Source data backup

#####################################################################


exp system/password file=jdb_full.dmp log=jdb_full.log  full=y



#####################################################################

### Target data Import 

#####################################################################


 imp system/password file=jdb_full.dmp log=imp.log full=y ignore=y

  






728x90

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