Block Corruption
drop table hr.emp purge;
create table hr.emp tablespace users as select * from hr.employees;
select file_id, block_id
from dba_extents
where segment_name = 'EMP' and owner = 'HR';
@segfile
EMP
HR
방금 만든 테이블은 users01.dbf에 들어있음
select employee_id, rowid, dbms_rowid.rowid_block_number(rowid)
from hr.emp;
block 훼손
!dd if=/dev/zero of=/u02/oradata/orcl/users01.dbf bs=8192 seek=39828 count=2 conv=notrunc
users01.dbf 파일의 39828번 블록부터 2개 블록을 0으로 덮어써서 깨뜨리는 작업
if: input file
/dev/zero: 0만 나오는 특수 파일
of: output file
bs: block size(8kb)
seek: 파일에서 건너뛸 블록 수
count: 덮어쓸 블록 수
conv=notrunc: 파일 크기 유지(truncate 금지)
alter system flush buffer_cache;
select count(*) form hr.emp;
!dbv userid=system/oracle file=/u02/oradata/orcl/users01.dbf
select t.ts#, s.header_file, s.header_block
from v$tablespace t, dba_segments s
where t.name = s.tablespace_name
and s.segment_name = 'EMP'
and s.owner = 'HR';
!dbv userid=system/oracle segment_id=4.7.39826
rman target "'sys/oracle@t1 as sysdba'" auxiliary "'sys/oracle@n1 as sysdba'"
validate datafile 7;
list failure;
list failure 4101 detail;
advise failure;
repair failure preview;
repair failure;
yes
list failure;
select count(*) from hr.emp;
data pump
!mkdir -p /home/oracle/datapump
create directory dirpump as '/home/oracle/datapump';
col owner for a5
col directory_name for a20
col directory_path for a30
select * from dba_directories where directory_name = 'DIRPUMP';
grant read, write on directory dirpump to hr;
col grantee for a10
col table_name for a15
col grantor for a10
col privilege for a10
col type for a10
select * from dba_tab_privs where grantee = 'HR';
schema level export
!expdp system/oracle schemas=hr directory=dirpump dumpfile=hr_schema.dmp
drop table hr.emp purge;
select count(*) from hr.emp;
Table level Import
!impdp system/oracle directory=dirpump dumpfile=hr_schema.dmp tables=hr.emp
select count(*) from hr.emp;
col constraint_name for a20
col search_condition for a30
col index_name for a30
select constraint_name, constraint_type, search_condition, status, index_name
from dba_constraints
where table_name = 'EMP' and owner = 'HR';
Remap Operation(Table level) Export
!expdp system/oracle schemas=hr directory=dirpump dumpfile=hr_table.dmp include=table
@segfile
EMP
HR
select table_name, tablespace_name from dba_tables where owner = 'HR';
create user kkt identified by kkt
default tablespace users
temporary tablespace temp
quota unlimited on users;
grant connect, resource to kkt;
Remap Tablespace Import
!impdp system/oracle directory=dirpump dumpfile=hr_table.dmp tables=hr.jobs remap_schema='hr':'kkt' remap_tablespace='sysaux':'users'
conn kkt/kkt
col table_name for a10
col tablespace_name for a15
select table_name, tablespace_name from user_tables;
select count(*) from jobs;
Remap Schema
!impdp system/oracle directory=dirpump dumpfile=hr_table.dmp tables=hr.emp,hr.employees,hr.departments,hr.locations remap_schema='hr':'kkt' exclude=trigger,ref_constraint
특정 Table Export/Import
! expdp system/oracle directory=dirpump dumpfile=hr_emp_dept_job.dmp tables=hr.employees,hr.departments,hr.job_history,kkt.emp
select salary from kkt.emp where employee_id=100;
update kkt.emp set salary=5000 where employee_id=100;
select salary from kkt.emp where employee_id=100;
commit;
drop table kkt.emp purge;
!impdp system/oracle directory=dirpump dumpfile=hr_emp_dept_job.dmp tables=kkt.emp
select salary from kkt.emp where employee_id=100;
Data만 Import
truncate table hr.job_history;
select count(*) from hr.job_history;
!impdp system/oracle directory=dirpump dumpfile=hr_emp_dept_job.dmp tables=hr.job_history content=data_only
select count(*) from hr.job_history;
Table구조만 Import
drop table hr.job_history purge;
select count(*) from hr.job_history;
!impdp system/oracle directory=dirpump dumpfile=hr_emp_dept_job.dmp tables=hr.job_history content=metadata_only
select count(*) from hr.job_history;
!impdp system/oracle directory=dirpump dumpfile=hr_emp_dept_job.dmp tables=hr.job_history content=data_only
select count(*) from hr.job_history;
!impdp system/oracle directory=dirpump dumpfile=hr_emp_dept_job.dmp tables=hr.job_history table_exists_action=skip
동일 Table은 skip
!impdp system/oracle directory=dirpump dumpfile=hr_emp_dept_job.dmp tables=hr.job_history table_exists_action=append
동일한 Table이 있는 경우 데이터 추가
에러 발생
!impdp system/oracle directory=dirpump dumpfile=hr_emp_dept_job.dmp tables=hr.job_history table_exists_action=truncate
데이터 삭제 후 추가
!impdp system/oracle directory=dirpump dumpfile=hr_emp_dept_job.dmp tables=hr.job_history table_exists_action=replace
동일한 Table이 있는 경우 삭제한 후 새로 생성하여 import
- 조건절에 해당하는 데이터만 export
!expdp system/oracle directory=dirpump dumpfile=hr_50_stman.dmp tables=hr.employees query=hr.employees:\"where department_id\=50 and job_id\=\'ST_MAN\'\"
create table emp1 tablespace users
as select * from employees
where 1=2;
select * from emp1;
!impdp system/oracle directory=dirpump dumpfile=hr_50_stman.dmp remap_table=employees:emp1 content=data_only
select count(*) from emp1;
conn sys/oracle@c1 as sysdba
create user itwill identified by itwill
default tablespace users
quota unlimited on users;
grant connect, resource to itwill;
conn itwill/itwill@c1
create table t1(id number);
insert into t1 values(1);
commit;
create public database link prod_lk
connect to system identified by oracle
using 'c1';
alter tablespace temp
add tempfile '/u02/oradata/orcl/temp01.dbf'
reuse
autoextend on
next 10m
maxsize unlimited;
expdp system/oracle directory=dirpump network_link=prod_lk schemas=itwill dumpfile=itwill.dmp
impdp system/oracle directory=dirpump schemas=itwill dumpfile=itwill.dmp remap_schema=itwill:hr
select * from t1;
