'레드햇' 태그의 글 목록

레드햇

admin 수업 2018.06.22

admin 수업

2018. 6. 22. 14:47

Moving Data

* dump 파일 생성하기

[oracle@localhost ~]$ exp

Export: Release 11.2.0.1.0 - Production on Fri Jun 22 14:35:53 2018

Username: hr

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Enter array fetch buffer size: 4096 >

Export file: expdat.dmp >

(2)U(sers), or (3)T(ables): (2)U > T

Export table data (yes/no): yes > y

Compress extents (yes/no): yes >

Export done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set

server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

Table(T) or Partition(T:P) to be exported: (RETURN to quit) > quit

EXP-00011: HR.QUIT does not exist

Table(T) or Partition(T:P) to be exported: (RETURN to quit) > employees

. . exporting table EMPLOYEES 20 rows exported

Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully with warnings.

[oracle@localhost ~]$

[oracle@localhost ~]$ ls

afiedt.buf Desktop labs q_dbf.sql q_tbs.sql

backup expdat.dmp oradiag_oracle q_log.sql

export/import 실습)

[oracle@localhost ~]$ ls

afiedt.buf Desktop labs q_dbf.sql q_tbs.sql

backup expdat.dmp oradiag_oracle q_log.sql

[oracle@localhost ~]$ cd backup

[oracle@localhost backup]$ ls

arch1 rman uman

[oracle@localhost backup]$ mkdir exp

[oracle@localhost backup]$ cd

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 22 15:18:49 2018

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS> create user expuser identified by expuser;

User created.

SYS> grant connect, resource to expuser;

Grant succeeded.

SYS> conn expuser/expuser

Connected.

EXPUSER> select * from tab;

no rows selected

EXPUSER> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost ~]$ exp hr/hr file=/home/oracle/backup/exp/hrtab.dmp tables=employees,departments

Export: Release 11.2.0.1.0 - Production on Fri Jun 22 15:22:33 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set

server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table EMPLOYEES 20 rows exported

. . exporting table DEPARTMENTS 8 rows exported

Export terminated successfully without warnings.

[oracle@localhost ~]$

[oracle@localhost ~]$ cd backup/exp/

[oracle@localhost exp]$ ls

hrtab.dmp

[oracle@localhost ~]$ imp system/oracle file=/home/oracle/backup/exp/hrtab.dmp tables=employees,departments fromuser=hr touser=expuser

Import: Release 11.2.0.1.0 - Production on Fri Jun 22 15:26:21 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by HR, not by you

import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set

import server uses WE8MSWIN1252 character set (possible charset conversion)

. importing HR's objects into EXPUSER

. . importing table "EMPLOYEES" 20 rows imported

. . importing table "DEPARTMENTS" 8 rows imported

IMP-00017: following statement failed with ORACLE error 942:

"ALTER TABLE "EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") "

"REFERENCES "JOBS" ("JOB_ID") ENABLE NOVALIDATE"

IMP-00003: ORACLE error 942 encountered

ORA-00942: table or view does not exist

IMP-00017: following statement failed with ORACLE error 942:

"ALTER TABLE "DEPARTMENTS" ADD CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATI"

"ON_ID") REFERENCES "LOCATIONS" ("LOCATION_ID") ENABLE NOVALIDATE"

IMP-00003: ORACLE error 942 encountered

ORA-00942: table or view does not exist

About to enable constraints...

IMP-00017: following statement failed with ORACLE error 2430:

"ALTER TABLE "EMPLOYEES" ENABLE CONSTRAINT "EMP_JOB_FK""

IMP-00017: following statement failed with ORACLE error 2430:

"ALTER TABLE "DEPARTMENTS" ENABLE CONSTRAINT "DEPT_LOC_FK""

Import terminated successfully with warnings.

[oracle@localhost ~]$

[oracle@localhost ~]$ sqlplus expuser/expuser

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 22 15:27:35 2018

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

EXPUSER> select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPARTMENTS TABLE

EMPLOYEES TABLE

EXPUSER>

EXPUSER> conn / as sysdba

Connected.

SYS> col object_name for a20

SYS> SELECT object_name, owner FROM dba_objects

2 WHERE object_name IN ('EMPLOYEES','DEPARTMENTS');

OBJECT_NAME OWNER

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

DEPARTMENTS HR

EMPLOYEES HR

EMPLOYEES EXPUSER

DEPARTMENTS EXPUSER

SYS>

full export/import 실습)

[oracle@localhost ~]$ exp system/oracle file=/home/oracle/backup/exp/fulldb.dmp full=y

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

...........

Export terminated successfully with warnings.

[oracle@localhost ~]$ cd backup/exp

[oracle@localhost exp]$ ls -al

total 96852

drwxr-xr-x 2 oracle oinstall 4096 Jun 22 15:34 .

drwxr-xr-x 6 oracle oinstall 4096 Jun 22 15:18 ..

-rw-r--r-- 1 oracle oinstall 99041280 Jun 22 15:35 fulldb.dmp

-rw-r--r-- 1 oracle oinstall 24576 Jun 22 15:22 hrtab.dmp

[oracle@localhost exp]$

[oracle@localhost exp]$ imp system/oracle file=/home/oracle/backup/exp/fulldb.dmp tables=bigemp fromuser=hr touser=expuser

Import: Release 11.2.0.1.0 - Production on Fri Jun 22 15:40:41 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set

import server uses WE8MSWIN1252 character set (possible charset conversion)

. importing HR's objects into EXPUSER

. . importing table "BIGEMP" 655360 rows imported

Import terminated successfully without warnings.

[oracle@localhost exp]$

[oracle@localhost exp]$ sqlplus expuser/expuser

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 22 15:42:04 2018

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

EXPUSER> select * from tab;

TNAME TABTYPE CLUSTERID

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

BIGEMP TABLE

DEPARTMENTS TABLE

EMPLOYEES TABLE

EXPUSER> select count(*) from bigemp;

COUNT(*)

----------

655360

EXPUSER>

DATA PUMP 실습)

[oracle@localhost ~]$ cd backup

[oracle@localhost backup]$ ls

arch1 exp rman uman

[oracle@localhost backup]$ mkdir dptest

[oracle@localhost backup]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 22 16:05:04 2018

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS> create directory dp_dir as '/home/oracle/backup/dptest';

Directory created.

SYS> grant read, write on directory dp_dir to hr;

Grant succeeded.

SYS> SELECT directory_path FROM dba_directories

2 WHERE directory_name = 'DP_DIR';

DIRECTORY_PATH

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

/home/oracle/backup/dptest

SYS>

SYS> create user dp identified by dp;

User created.

SYS> grant connect, resource to dp;

Grant succeeded.

SYS> grant read, write on directory dp_dir to dp;

Grant succeeded.

SYS> conn dp/dp

Connected.

DP> select * from tab;

no rows selected

DP> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost backup]$ expdp hr/hr tables=employees,departments directory=dp_dir dumpfile=hr.dmp

Export: Release 11.2.0.1.0 - Production on Fri Jun 22 16:35:22 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "HR"."SYS_EXPORT_TABLE_01": hr/******** tables=employees,departments directory=dp_dir dumpfile=hr.dmp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

. . exported "HR"."DEPARTMENTS" 6.554 KB 8 rows

. . exported "HR"."EMPLOYEES" 10.64 KB 20 rows

Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for HR.SYS_EXPORT_TABLE_01 is:

/home/oracle/backup/dptest/hr.dmp

Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 16:35:32

[oracle@localhost backup]$

[oracle@localhost backup]$ impdp system/oracle directory=dp_dir dumpfile=hr.dmp remap_schema=hr:dp

Import: Release 11.2.0.1.0 - Production on Fri Jun 22 16:37:46 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dp_dir dumpfile=hr.dmp remap_schema=hr:dp

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "DP"."DEPARTMENTS" 6.554 KB 8 rows

. . imported "DP"."EMPLOYEES" 10.64 KB 20 rows

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

ORA-39083: Object type REF_CONSTRAINT failed to create with error:

ORA-00942: table or view does not exist

Failing sql is:

ALTER TABLE "DP"."DEPARTMENTS" ADD CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID") REFERENCES "DP"."LOCATIONS" ("LOCATION_ID") ENABLE

ORA-39083: Object type REF_CONSTRAINT failed to create with error:

ORA-00942: table or view does not exist

Failing sql is:

ALTER TABLE "DP"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "DP"."JOBS" ("JOB_ID") ENABLE

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 16:37:51

[oracle@localhost backup]$

[oracle@localhost backup]$ sqlplus dp/dp

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 22 16:39:55 2018

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

DP> select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPARTMENTS TABLE

EMPLOYEES TABLE

(이렇게 해 두고, 다른 세션을 하나 더 연다.)

[oracle@localhost ~]$ sqlplus system/oracle

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 22 16:43:56 2018

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYSTEM> col owner_name for a10

SYSTEM> col job_name for a10

SYSTEM> col job_mode for a10

SYSTEM> col stat for a15

SYSTEM> col operation for a15

SYSTEM> set line 100

SYSTEM> select * from dba_datapump_jobs;

no rows selected

(아까 세션으로 돌아와서 아래 명령을 실행한다.)

[oracle@localhost ~]$ expdp system/oracle directory=dp_dir job_name=dptest dumpfile=fulldb.dmp full=y

(그리고 명령을 실행하자마자 실행 중인 창을 닫는다.

다시 SYSTEM 계정으로 접속한 세션으로 가서 확인해 보면,)

SYSTEM> /

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE

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

ATTACHED_SESSIONS DATAPUMP_SESSIONS

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

SYSTEM DPTEST EXPORT FULL EXECUTING 1

1 3

SYSTEM> /

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE

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

ATTACHED_SESSIONS DATAPUMP_SESSIONS

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

SYSTEM DPTEST EXPORT FULL EXECUTING 1

0 2

SYSTEM>

(DATA PUMP가 서버에서 계속 수행 중임을 확인할 수 있다. 이것이 EXPORT와의 차이점이다.

EXPORT는 명령을 실행하고 창을 닫으면 EXPORT 작업이 중단된다.)

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

'스마트웹&콘텐츠 개발자 과정 > Oracle' 카테고리의 다른 글

Workshop II 남은 부분 수업 (0)	2018.07.24
admin 수업 마지막 (0)	2018.07.03
Admin 수업 - 백업 실습 (0)	2018.06.21
admin (0)	2018.06.20
Admin (0)	2018.06.14

PREV 1 NEXT

+ Recent posts

Rss Feed and Twitter, Facebook, Youtube, Google+

레도널스의 개발자 되어가기

레드햇

admin 수업

'스마트웹&콘텐츠 개발자 과정 > Oracle' 카테고리의 다른 글

+ Recent posts

티스토리툴바