tip) as is(현재 시스템) -> to be(차세대 시스템)
legacy database
1. tablespace 생성한 후 확인 하세요.
tablespace 이름 : hrm_tbs
datafile 위치 및 이름 : /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
datafile 사이즈 : 10m
datafile 자동 확장 활성화
extent 관리 : local uniform size 1m
segment space management: auto
SQL> CREATE TABLESPACE hrm_tbs
DATAFILE '/u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf' SIZE 10M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m
SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
2. user 생성하세요.
user 이름 : hrm
user 비밀번호 : oracle
default tablespace : hrm_tbs
temporary tablespace(기존꺼) : temp
default tablespace quota : unlimited
SQL> CREATE USER hrm
IDENTIFIED BY oracle
DEFAULT TABLESPACE hrm_tbs
TEMPORARY TABLESPACE temp
QUOTA unlimited ON hrm_tbs;
User created.
SYS@XE> CREATE TABLESPACE hrm_tbs
2 DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\hrm_tbs01.dbf' SIZE 10M
3 AUTOEXTEND ON
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m
5 SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SYS@XE> CREATE USER hrm IDENTIFIED BY oracle DEFAULT TABLESPACE hrm_tbs TEMPORARY TABLESPACE temp QUOTA unlimited ON hrm_tbs;
User created.
3. hrm 유저에게 시스템 권한 부여한 후 확인 하세요.
시스템 권한 : create session, create table, create view, create procedure
SQL> grant create session, create table, create view, create procedure to hrm;
Grant succeeded.
SQL> select * from dba_sys_privs where grantee = 'HRM';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
HRM CREATE TABLE NO
HRM CREATE PROCEDURE NO
HRM CREATE SESSION NO
HRM CREATE VIEW NO
4. /home/oracle1/hr_pump 물리적인 디렉토리를 생성 한 후 dump file이 생성될 수 있는
물지적인 home/oracle1/hr_pump 위치에 따른 논리적인 디렉토리는 hr_dir 이름으로 생성한 후 확인 하세요.
1) 디렉터리 생성
[oracle1@oracle ~]$ pwd
/home/oracle1
[oracle1@oracle ~]$ mkdir hr_pump
[oracle1@oracle ~]$ cd hr_pump/
[oracle1@oracle hr_pump]$ pwd
/home/oracle1/hr_pump
2) 논리적인 디렉터리 생성
SQL> create directory hr_dir as '/home/oracle1/hr_pump';
Directory created.
3) 논리적인 디렉터리 생성 확인
col owner format a10
col directory_name format a20
col DIRECTORY_PATH format a50
SQL> SELECT * FROM dba_directories WHERE directory_name = 'HR_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- -------------------- --------------------------------------------------
SYS HR_DIR /home/oracle1/hr_pump
5. hr_dir 논리적인 디렉토리에 대한 read, write 권한은 hr에게 부여 한 후 확인해주세요.
SQL> grant read, write on directory hr_dir to hr;
Grant succeeded.
SQL> select * from dba_tab_privs where grantee ='HR';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- ---------- ------------------------------ -------------------- -------------------- --- ---
HR SYS DBMS_STATS SYS EXECUTE NO NO
HR SYS PUMP_DIR SYS WRITE NO NO
HR SYS PUMP_DIR SYS READ NO NO
HR SYS HR_DIR SYS WRITE NO NO
HR SYS HR_DIR SYS READ NO NO
6. data pump를 이용해서 hr유저가 소유한 모든 object를 hr_dir 디렉토리에 hr_object.dump 이름으로 생성해 주세요.
SQL> !
[oracle1@oracle ~]$ expdp system/oracle directory=hr_dir dumpfile=hr_object.dmp schemas=hr
Export: Release 11.2.0.4.0 - Production on Tue Jan 23 16:48:17 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=hr_dir dumpfile=hr_object.dmp schemas=hr
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 640 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."EMP_20" 9.382 KB 2 rows
. . exported "HR"."EMP_50_STMAN" 9.593 KB 5 rows
. . exported "HR"."INSA_EMP" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.992 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.476 KB 4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************/
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle1/hr_pump/hr_object.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jan 23 16:48:28 2024 elapsed 0 00:00:10
7. hr_object.dump에 있는 employees 테이블에 대해서 hrm유저에게 import에 해주세요.
단, trigger, grant, constraint, ref_constraint, index는 제외 시켜 주세요.
1) import 하기
[oracle1@oracle ~]$ impdp system/oracle directory=hr_dir dumpfile=hr_object.dmp remap_schema=hr:hrm remap_tablespace='example':'hrm_tbs' tables=hr.employees exclude=trigger,grant,constraint,ref_constraint,index
Import: Release 11.2.0.4.0 - Production on Tue Jan 23 17:20:34 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=hr_dir dumpfile=hr_object.dmp remap_schema=hr:hrm remap_tablespace=example:hrm_tbs tables=hr.employees exclude=trigger,grant,constraint,ref_constraint,index
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HRM"."EMPLOYEES" 16.80 KB 107 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Jan 23 17:20:36 2024 elapsed 0 00:00:02
2) 확인
SQL> conn hrm/oracle
Connected.
HRM@ora11g> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMPLOYEES TABLE
HRM@ora11g> select count(*) from hrm.employees;
COUNT(*)
----------
107
8. hr_object.dump에 있는 departments, locations 테이블에 대해서 hrm유저에게 import에 해주세요.
단 trigger, grant, constraint, ref_constraint, index는 제외 시켜 주세요.
[oracle1@oracle ~]$ impdp system/oracle directory=hr_dir dumpfile=hr_object.dmp tables=hr.departments,hr.locations remap_schema='HR':'HRM' remap_tablespace='EXAMPLE':'HRM_TBS' exclude=trigger,grant,constraint,ref_constraint,index
Import: Release 11.2.0.4.0 - Production on Tue Jan 23 17:30:32 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=hr_dir dumpfile=hr_object.dmp tables=hr.departments,hr.locations remap_schema=HR:HRM remap_tablespace=EXAMPLE:HRM_TBS exclude=trigger,grant,constraint,ref_constraint,index
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HRM"."DEPARTMENTS" 7.007 KB 27 rows
. . imported "HRM"."LOCATIONS" 8.273 KB 23 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Jan 23 17:30:33 2024 elapsed 0 00:00:01
[oracle1@oracle ~]$ exit
exit
HRM@ora11g> select count(*) from departments;
COUNT(*)
----------
27
HRM@ora11g> select count(*) from locations;
COUNT(*)
----------
23
9. hrm.employees employee_id 컬럼에 primary key 생성하세요. 제약조건 이름은 hrm_emp_id_pk로 생성하세요.
1) primary key 생성
HRM@ora11g> ALTER TABLE hrm.employees ADD CONSTRAINT hrm_emp_id_pk PRIMARY KEY (employee_id);
Table altered.
2) primary key 생성 확인
HRM@ora11g> SELECT constraint_name, constraint_type, search_condition, r_constraint_name, status, validated, index_name
FROM user_constraints
WHERE table_name = 'EMPLOYEES'; 2 3
CONSTRAINT_NAME C SEARCH_CONDITION R_CONSTRAINT_NAME STATUS VALIDATED INDEX_NAME
------------------------------ - ------------------------------ ------------------------------ -------- ------------- ------------------------------
HRM_EMP_ID_PK P ENABLED VALIDATED HRM_EMP_ID_PK
EMP_LAST_NAME_NN C "LAST_NAME" IS NOT NULL ENABLED VALIDATED
EMP_EMAIL_NN C "EMAIL" IS NOT NULL ENABLED VALIDATED
EMP_HIRE_DATE_NN C "HIRE_DATE" IS NOT NULL ENABLED VALIDATED
EMP_JOB_NN C "JOB_ID" IS NOT NULL ENABLED VALIDATED
10. hrm.departments department_id 컬럼에 primary key 생성하세요. 제약조건 이름은 hrm_dept_id_pk로 생성하세요.
1) primary key 생성
HRM@ora11g> ALTER TABLE hrm.departments ADD CONSTRAINT hrm_dept_id_pk PRIMARY KEY (department_id);
Table altered.
2) 생성 확인
HRM@ora11g> SELECT constraint_name, constraint_type, search_condition, r_constraint_name, status, validated, index_name
FROM user_constraints
WHERE table_name = 'DEPARTMENTS'; 2 3
CONSTRAINT_NAME C SEARCH_CONDITION R_CONSTRAINT_NAME STATUS VALIDATED INDEX_NAME
------------------------------ - ------------------------------ ------------------------------ -------- ------------- ------------------------------
DEPT_NAME_NN C "DEPARTMENT_NAME" IS NOT NULL ENABLED VALIDATED
HRM_DEPT_ID_PK P ENABLED VALIDATED HRM_DEPT_ID_PK
11. hrm.employees department_id에 foreign key를 생성하세요. references는 departments department_id를 참조하세요. 제약조건 이름은 emp_dept_id_fk로 생성하세요.
1) foreign key 생성
HRM@ora11g> alter table hrm.employees add constraint emp_dept_id_fk foreign key(department_id) references departments(department_id);
Table altered.
2) 생성 확인
HRM@ora11g> SELECT constraint_name, constraint_type, search_condition, r_constraint_name, status, validated, index_name
FROM user_constraints
WHERE table_name = 'EMPLOYEES'; 2 3
CONSTRAINT_NAME C SEARCH_CONDITION R_CONSTRAINT_NAME STATUS VALIDATED INDEX_NAME
------------------------------ - ------------------------------ ------------------------------ -------- ------------- ------------------------------
HRM_EMP_ID_PK P ENABLED VALIDATED HRM_EMP_ID_PK
EMP_LAST_NAME_NN C "LAST_NAME" IS NOT NULL ENABLED VALIDATED
EMP_EMAIL_NN C "EMAIL" IS NOT NULL ENABLED VALIDATED
EMP_HIRE_DATE_NN C "HIRE_DATE" IS NOT NULL ENABLED VALIDATED
EMP_JOB_NN C "JOB_ID" IS NOT NULL ENABLED VALIDATED
EMP_DEPT_ID_FK R HRM_DEPT_ID_PK ENABLED VALIDATED
6 rows selected.
'문제 > Backup' 카테고리의 다른 글
| 240125 Backup / RMAN _ 문제 (0) | 2024.01.25 |
|---|