성능 관리와 데이터 이동
Memory 관리 방법
- ~8i
- static parameter
- 9i
- dynamic SGA
- shared_pool_size
- db_cache_size
- large_pool_size
- java_pool_size
- streams_pool_size
- 10g ASMM (Automatic Shared Memory Management)
- SGA 자동으로 관리
- shared_pool_size
- db_cache_size
- large_pool_size
- java_pool_size
- streams_pool_size
- sga_target =1G
- sga_max_target =2G
- pga_aggregate_target = 1G
- 11g AMM (Automatic Memory Management)
- Memory_target = 2G SGA + PGA
- Memory_max_target = 3G SGA + PGA
- Invalid한 index의 문제 해결
SCOTT@orcl2> select T.index_name, column_name, uniqueness, status
from user_indexes T , user_ind_columns C
where T.index_name =C.index_name
and T.table_name ='EMP';
SCOTT@orcl2> create index emp_sal on emp(sal);
Index created.
SCOTT@orcl2> select deptno, rowid
from emp
where deptno is not null
order by deptno;
DEPTNO ROWID
---------- ------------------
10 AAAR3dAAEAAAACXAAG
10 AAAR3dAAEAAAACXAAI
10 AAAR3dAAEAAAACXAAN
20 AAAR3dAAEAAAACXAAD
20 AAAR3dAAEAAAACXAAM
20 AAAR3dAAEAAAACXAAK
20 AAAR3dAAEAAAACXAAA
20 AAAR3dAAEAAAACXAAH
30 AAAR3dAAEAAAACXAAC
30 AAAR3dAAEAAAACXAAJ
30 AAAR3dAAEAAAACXAAB
30 AAAR3dAAEAAAACXAAL
30 AAAR3dAAEAAAACXAAF
30 AAAR3dAAEAAAACXAAE
14 rows selected.
SCOTT@orcl2> create index emp_deptno on emp(deptno);
Index created.
SCOTT@orcl2> select deptno, rowid
from emp
where deptno is not null
order by deptno;
DEPTNO ROWID
---------- ------------------
10 AAAR3dAAEAAAACXAAG
10 AAAR3dAAEAAAACXAAI
10 AAAR3dAAEAAAACXAAN
20 AAAR3dAAEAAAACXAAA
20 AAAR3dAAEAAAACXAAD
20 AAAR3dAAEAAAACXAAH
20 AAAR3dAAEAAAACXAAK
20 AAAR3dAAEAAAACXAAM
30 AAAR3dAAEAAAACXAAB
30 AAAR3dAAEAAAACXAAC
30 AAAR3dAAEAAAACXAAE
30 AAAR3dAAEAAAACXAAF
30 AAAR3dAAEAAAACXAAJ
30 AAAR3dAAEAAAACXAAL
14 rows selected.
SCOTT@orcl2> select table_name, index_name, uniqueness, status from user_indexes;
TABLE_NAME INDEX_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------
EMP2 EMP2_PK UNIQUE VALID
EMP PK_EMP UNIQUE VALID
EMP EMP_SAL NONUNIQUE VALID
EMP EMP_DEPTNO NONUNIQUE VALID
DEPT PK_DEPT UNIQUE VALID
SCOTT@orcl2> alter index pk_emp rebuild online nologging;
SCOTT@orcl2> alter index emp_deptno rebuild;
unusable_ind_list.sql
set pages 0
set head off
set feedback off
spool alter_index.sql
select 'alter index '||index_name||' rebuild online nologging;'
from user_indexes
where status <> 'VALID'
/
spool off
set feedback on
set head on
set pages 100
SCOTT@orcl2> set autot on exp
SCOTT@orcl2> select * from emp where sal = 5000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 5000 20
7566 JONES MANAGER 7839 02-APR-81 5000 20
7876 ADAMS CLERK 7788 23-MAY-87 5000 20
7902 FORD ANALYST 7566 03-DEC-81 5000 20
4 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3507486672
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 348 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 4 | 348 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_SAL | 2 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(CASE "DEPTNO" WHEN 10 THEN
SYS_AUDIT('SCOTT','EMP','CHK_SCOTT_EMP',3) ELSE NULL END IS NULL)
3 - access("SAL"=5000)
SCOTT@orcl2> set autot off
데이터 이동
- data.zip을 share폴더에 넣고 압축풀기
[root@edydr1p0 ~]# cd /media/sf_share/
[root@edydr1p0 sf_share]# cp -r data /home/oracle
[root@edydr1p0 oracle]# chown -Rh oracle data
[root@edydr1p0 oracle]# chgrp -Rh oinstall data
[orcl2:~]$ cd data
디렉토리 객체 생성 방법
SYS@orcl2> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------------ -------------------------------------------------------
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
SYS DATA_PUMP_DIR /u01/app/oracle/admin/orcl2/dpdump/
SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/pro
duct_media/
SYS XMLDIR /ade/b/1191423112/oracle/rdbms/xml
SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sal
es_history/
SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/
SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/ord
er_entry/
SYS SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/ord
er_entry//2002/Sep
8 rows selected.
SYS@orcl2> create directory data_dir
as '/home/oracle/data';
Directory created.
-- 디렉토리 생성
-- 에러가 안난다고 해서 제대로 만들어지는 것은 아니다
-- 디렉토리가 존재하지 않아도, create를 하면 data디렉토리가 자동으로 생성된다.
SYS@orcl2> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------------ -------------------------------------------------------
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
................................
SYS DATA_DIR /home/oracle/data
9 rows selected.
-- 생성된 것을 확인
SYS@orcl2> create directory dirpump as '/home/oracle/data';
Directory created.
SYS@orcl2> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------------ -------------------------------------------------------
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
................................
SYS DATA_DIR /home/oracle/data
SYS DIRPUMP /home/oracle/data
10 rows selected.
SYS@orcl2> grant read, write on directory dirpump to scott, hr, sh;
Grant succeeded.
SYS@orcl2> grant read, write on directory data_dir to scott, hr, sh;
Grant succeeded.
SCOTT@orcl2> select privilege, table_name from user_tab_privs;
PRIVILEGE TABLE_NAME
---------------------------------------- ------------------------------
SELECT EMP
SELECT EMP
UPDATE EMP
UPDATE EMP
SELECT EMP
READ DATA_DIR
WRITE DATA_DIR
READ DIR_PUMP
WRITE DIR_PUMP
9 rows selected.
==================================================================================================
-- 실습 : # Data Pump를 이용해서 SH 스키마의 Sales 테이블 export
# SH 유저의 SALES 테이블의 통계 수집
SYS@orcl2> exec dbms_stats.gather_table_stats('SH','SALES') ;
# export 전 필요한 공간 확인
[orcl2:~]$ cat ~/data/expdb_check.par
userid=sh/sh
directory=dirpump
job_name=datapump
estimate=statistics
estimate_only=Y
logfile=expdp_pump.log
tables=SALES
# export 에 필요한 Parameter 정의 확인
[orcl2:data]$ cat ~/data/expdb_pump.par
userid=sh/sh
directory=dirpump
job_name=datapump
logfile=expdp_pump.log
dumpfile=expdp_pump%U.dmp
filesize=100m
tables=SALES
# Data Pump를 이용한 export
[orcl2:data]$ expdp parfile=~/data/expdb_pump.par
[orcl2:data]$ expdp userid=sh/sh directory=dirpump dumpfile=expdp_dump1.dmp logfile=expdp_dump.log tables=sales
25.80M
[orcl2:data]$ cat ~/data/expdp_pump.log
==================================================================================================
# 현재 Row 개수 확인 후 Truncate
SYS@orcl2> select count(*) from sh.sales ;
SYS@orcl2> truncate table sh.sales ;
SYS@orcl2> select count(*) from sh.sales ;
SYS@orcl2> select count(*) from sh.sales ;
COUNT(*)
----------
0
-- 실습 : # Data Pump를 이용해서 SH 스키마의 Sales 테이블 import
# export 받았던 파일을 이용하여 import
[orcl2:data]$ cat ~/data/impdb_pump.par
userid=sh/sh
directory=dirpump
job_name=datapump
logfile=impdp_pump.log
dumpfile=expdp_pump%U.dmp
tables=SALES
table_exists_action=append
[orcl2:data]$ impdp userid=sh/sh directory=data_dir dumpfile=expdp_dump1.dmp logfile=impdp_pump.log tables=sales table_exists_action=append
Import: Release 11.2.0.1.0 - Production on Wed Apr 10 11:08:14 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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 "SH"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SH"."SYS_IMPORT_TABLE_01": userid=sh/******** directory=data_dir dumpfile=expdp_dump1.dmp logfile=impdp_pump.log tables=sales table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "SH"."SALES" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SH"."SALES":"SALES_Q4_2001" 2.257 MB 69749 rows
. . imported "SH"."SALES":"SALES_Q1_1999" 2.071 MB 64186 rows
. . imported "SH"."SALES":"SALES_Q3_2001" 2.130 MB 65769 rows
. . imported "SH"."SALES":"SALES_Q1_2000" 2.012 MB 62197 rows
. . imported "SH"."SALES":"SALES_Q1_2001" 1.965 MB 60608 rows
. . imported "SH"."SALES":"SALES_Q2_2001" 2.051 MB 63292 rows
. . imported "SH"."SALES":"SALES_Q3_1999" 2.166 MB 67138 rows
. . imported "SH"."SALES":"SALES_Q4_1999" 2.014 MB 62388 rows
. . imported "SH"."SALES":"SALES_Q2_2000" 1.802 MB 55515 rows
. . imported "SH"."SALES":"SALES_Q3_2000" 1.909 MB 58950 rows
. . imported "SH"."SALES":"SALES_Q4_1998" 1.581 MB 48874 rows
. . imported "SH"."SALES":"SALES_Q4_2000" 1.814 MB 55984 rows
. . imported "SH"."SALES":"SALES_Q2_1999" 1.754 MB 54233 rows
. . imported "SH"."SALES":"SALES_Q1_1998" 1.412 MB 43687 rows
. . imported "SH"."SALES":"SALES_Q3_1998" 1.633 MB 50515 rows
. . imported "SH"."SALES":"SALES_Q2_1998" 1.160 MB 35758 rows
. . imported "SH"."SALES":"SALES_1995" 0 KB 0 rows
. . imported "SH"."SALES":"SALES_1996" 0 KB 0 rows
. . imported "SH"."SALES":"SALES_H1_1997" 0 KB 0 rows
. . imported "SH"."SALES":"SALES_H2_1997" 0 KB 0 rows
. . imported "SH"."SALES":"SALES_Q1_2002" 0 KB 0 rows
. . imported "SH"."SALES":"SALES_Q1_2003" 0 KB 0 rows
. . imported "SH"."SALES":"SALES_Q2_2002" 0 KB 0 rows
. . imported "SH"."SALES":"SALES_Q2_2003" 0 KB 0 rows
. . imported "SH"."SALES":"SALES_Q3_2002" 0 KB 0 rows
. . imported "SH"."SALES":"SALES_Q3_2003" 0 KB 0 rows
. . imported "SH"."SALES":"SALES_Q4_2002" 0 KB 0 rows
. . imported "SH"."SALES":"SALES_Q4_2003" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/USER_PREF_STATISTICS
Job "SH"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 11:08:28
[orcl2:data]$ cat ~/data/impdp_pump.log
-- 로그 기록 확인
SYS@orcl2> select count(*) from sh.sales ;
COUNT(*)
----------
918843
==================================================================================================
-- 실습 : # schema 단위로 export 시 SALES 테이블의 인덱스 제외하고 Export
[orcl2:data]$ cat ~/data/expdb_pump2.par
userid=sh/sh
directory=dirpump
job_name=datapump
logfile=expdp_pump2.log
dumpfile=expdp_pump2%U.dmp
filesize=100m
schemas=SH
Exclude=INDEX:"LIKE 'SALES%'"
[orcl2:data]$ expdp parfile=~/data/expdb_pump2.par
==================================================================================================
-- 실습 : # Import 시 schema 변경해서 임포트한다.
SYS@orcl2> create user smith identified by oracle ;
SYS@orcl2> grant dba to smith ;
[orcl2:data]$ cat ~/data/impdb_pump2.par
userid=system/oracle_4U
directory=dirpump
job_name=datapump
logfile=impdp_pump2.log
dumpfile=expdp_pump2%U.dmp
REMAP_SCHEMA=SH:SMITH
[orcl2:data]$ impdp parfile=~/data/impdb_pump2.par
.............................................
Job "SYSTEM"."DATAPUMP" successfully completed at 11:30:45
# 결과 확인 ( SALES 테이블의 인덱스 확인 )
SYS@orcl2> col owner for a10
SYS@orcl2> col object_name for a30
SYS@orcl2> col object_type for a20
SYS@orcl2> select owner , object_name, object_type
from dba_objects
where owner = 'SMITH' order by 3, 2;
OWNER OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ --------------------
SMITH CHANNELS_DIM DIMENSION
SMITH CUSTOMERS_DIM DIMENSION
SMITH PRODUCTS_DIM DIMENSION
.............................................
SMITH SALES TABLE PARTITION
SMITH SALES TABLE PARTITION
SMITH SALES TABLE PARTITION
SMITH PROFITS VIEW
161 rows selected.
==================================================================================================-- 실습 : # emp table 을 export하고 users tablespace 를 export 한 후 두 덤프 파일에서 emp 테이블만 import 한다.
[orcl2:data]$ expdp userid=system/oracle_4U directory=dirpump dumpfile=emp.dmp tables=scott.emp
[orcl2:data]$ expdp userid=system/oracle_4U directory=dirpump dumpfile=users_tbs.dmp tablespaces=users
# scott 사용자의 emp table 삭제 후 dump 화일 이용해서 복구한다.
SCOTT@orcl2> select * from emp;
SCOTT@orcl2> drop table emp;
[orcl2:data]$ impdp userid=scott/tiger directory=dirpump dumpfile=emp.dmp tables=scott.emp
[orcl2:data]$ impdp userid=system/oracle_4U directory=dirpump dumpfile=users_tbs.dmp tables=scott.emp
--TBS를 import하기 위해서는 DBA계정으로 진행하여야 한다.
SYS@orcl2> select tablespace_name, table_name, owner
2 from dba_tables
3 where table_name ='EMP';
TABLESPACE_NAME TABLE_NAME OWNER
------------------------------ ------------------------------ ------------------------------
USERS EMP SCOTT
INSA_TBS EMP INSA
SCOTT@orcl2> select * from emp;
==================================================================================================
마무리
SCOTT@orcl2> drop table hr.new_emp purge ;
SCOTT@orcl2> drop user smith cascade ;
[orcl2:data]$ cd /home/oracle/data
[orcl2:data]$ rm *.dmp
[orcl2:data]$rm *.log
SQL*Loader
- 외부의 data file(textfile ... )을 읽는다.
- 잘못된 데이터는 bad file에 저장된다.
- 조건에 안맞은 데이터는 discard file에 저장된다.
- 정상적인 데이터는 DB에 저장된다.
- 로그파일에 떨어트려준다.
실습에 사용할 new_emp.dat
new_emp.dat
7839,KING,PRESIDENT,,1981-11-17,5000,,10
7698,BLAKE,ABCDEFGHIJKLMN,7839,1981-05-01,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09,2450,,10
7566,JONES,MANAGER,7839,1981-04-02,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30
7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30
7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30
7900,JAMES,CLERK,7698,1981-12-03,950,,30
7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30
7902,FORD,ANALYST,7566,1981-12-03,3000,,20
7369,SMITH,CLERK,7902,1980-12-17,800,,20
7788,SCOTT,ANALYST,7566,1982-12-09,3000,,20
7876,ADAMS,CLERK,7788,1983-01-12,1100,,20
7934,MILLER,CLERK,7782,1982-01-23,1300,,10
HR@orcl2> CREATE TABLE new_emp
(empno number(4),
ename varchar2(10),
job varchar2(10),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2));
Table created.
-- # 실습 : new_emp 테이블에 데이타 로딩
sql_loader.ctl (컨트롤 파일)
LOAD DATA
INFILE 'new_emp.dat' -- 데이타파일
APPEND INTO TABLE new_emp
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(empno INTEGER EXTERNAL,
ename CHAR,
job CHAR,
mgr INTEGER EXTERNAL ,
hiredate DATE "YYYY-MM-DD",
sal INTEGER EXTERNAL,
comm INTEGER EXTERNAL ,
deptno INTEGER EXTERNAL)
[orcl2:data]$ sqlldr hr/hr control=sql_loader.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Apr 10 14:15:14 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 14
[orcl2:data]$ vi sql_loader.log
[orcl2:data]$ vi new_emp.bad
7698,BLAKE,ABCDEFGHIJKLMN,7839,1981-05-01,2850,,30
[orcl2:data]$ vi new_emp.dis
7839,KING,PRESIDENT,,1981-11-17,5000,,10
7782,CLARK,MANAGER,7839,1981-06-09,2450,,10
7566,JONES,MANAGER,7839,1981-04-02,2975,,20
7902,FORD,ANALYST,7566,1981-12-03,3000,,20
7369,SMITH,CLERK,7902,1980-12-17,800,,20
7788,SCOTT,ANALYST,7566,1982-12-09,3000,,20
7876,ADAMS,CLERK,7788,1983-01-12,1100,,20
7934,MILLER,CLERK,7782,1982-01-23,1300,,10
HR@orcl2> select * from new_emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
10 rows selected.
HR@orcl2> delete new_emp;
10 rows deleted.
HR@orcl2> commit;
Commit complete.
<<<<<<<<<<<<<TIP>>>>>>>>>>>>>
SCOTT@orcl2> show colsep
colsep " "
SCOTT@orcl2> set colsep
SCOTT@orcl2> select * from emp;
EMPNO,ENAME ,JOB , MGR,HIREDATE , SAL, COMM, DEPTNO
----------,----------,---------,----------,---------,----------,----------,----------
7369,SMITH ,CLERK , 7902,17-DEC-80, 5000, , 20
7499,ALLEN ,SALESMAN , 7698,20-FEB-81, 1584, 300, 30
7521,WARD ,SALESMAN , 7698,22-FEB-81, 1375, 500, 30
7566,JONES ,MANAGER , 7839,02-APR-81, 5000, , 20
7654,MARTIN ,SALESMAN , 7698,28-SEP-81, 1375, 1400, 30
7698,BLAKE ,MANAGER , 7839,01-MAY-81, 3135, , 30
7782,CLARK ,MANAGER , 7839,09-JUN-81, 2695, , 10
7788,SCOTT ,ANALYST , 7566,19-APR-87, 2000, , 20
7839,KING ,PRESIDENT, ,17-NOV-81, 5500, , 10
7844,TURNER ,SALESMAN , 7698,08-SEP-81, 1650, 0, 30
7876,ADAMS ,CLERK , 7788,23-MAY-87, 5000, , 20
7900,JAMES ,CLERK , 7698,03-DEC-81, 2000, , 30
7902,FORD ,ANALYST , 7566,03-DEC-81, 5000, , 20
7934,MILLER ,CLERK , 7782,23-JAN-82, 1430, , 10
14 rows selected.
SCOTT@orcl2> set colsep
SCOTT@orcl2> set lines 120
SCOTT@orcl2> set pages 0
SCOTT@orcl2> set feed off
SCOTT@orcl2> set head off
SCOTT@orcl2> set echo off
SCOTT@orcl2> spool emp.dat
SCOTT@orcl2> select empno, ename, job, mgr, to_char(hiredate, 'YYYY-MM-DD'), sal, comm, deptno
from emp;
SCOTT@orcl2>spool off
SCOTT@orcl2> set pages 120
SCOTT@orcl2> set feed on
SCOTT@orcl2> set head on
SCOTT@orcl2> set echo on
<<<<<<<<<<<<<<<<TIP : SPOOL>>>>>>>>>>>>>>>>>
SYS@orcl2> select name from v$datafile
union all
select member from v$logfile
union all
select name from v$controlfile;
NAME
------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl2/system01.dbf
/u01/app/oracle/oradata/orcl2/sysaux01.dbf
/u01/app/oracle/oradata/orcl2/undotbs01.dbf
/u01/app/oracle/oradata/orcl2/users01.dbf
/u01/app/oracle/oradata/orcl2/example01.dbf
/home/oracle/insa_tbs01.dbf
/u01/app/oracle/oradata/orcl2/redo03.log
/u01/app/oracle/oradata/orcl2/redo02.log
/u01/app/oracle/oradata/orcl2/redo01.log
/u01/app/oracle/oradata/orcl2/control01.ctl
/u01/app/oracle/orcl2/control02.ctl
11 rows selected.
-- 백업 받아야할 db 리스트
SYS@orcl2> save db_list
Created file db_list.sql
set pages 0
set head off
set feed off
set echo off
spool orcl2_backup.sh
select 'cp ' || name || ' /home/oracle/BACKUP_ORCL2'
from (select name from v$datafile
union all
select member from v$logfile
union all
select name from v$controlfile);
/
spool off
set pages 120
set head on
set feed on
set echo on
[orcl2:data]$ ls -al orcl2_backup.sh
-rw-r--r-- 1 oracle oinstall 2222 Apr 10 15:00 orcl2_backup.sh
[orcl2:data]$ chmod 755 orcl2_backup.sh
<<<<<<<<<<<<<<<<TIP : SPOOL END>>>>>>>>>>>>>>>>>
External Table
- 오라클 데이터베이스 외부의 운영 체제 파일로 저장되는 읽기 전용 테이블
SCOTT@orcl2> create table emp5 (empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4))
organization external
(type oracle_loader default directory data_dir
access parameters (records delimited by newline
fields terminated by ',')
location ('new_emp.dat')
)
reject limit unlimited;
Table created.
SH@orcl2> create table sales_old(prod_id, cust_id, time_id, amount_sold)
organization external
(type oracle_datapump default directory data_dir
location ('sales01.exp', 'sales02.exp')
)
parallel
as
select prod_id, cust_id, time_id, amount_sold
from sales
where time_id <= to_date('98/01/01','RR/MM/DD');
-- 파일의 용량이 크기때문에 병렬처리를 위해 파일을 두개로 뒀다
SH@orcl2> save sales_old
'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-기초 (0) | 2019.04.22 |
|---|---|
| [Oracle DBA]Backup & Recovery (0) | 2019.04.10 |
| [Oracle DBA]데이터베이스 유지 관리 (0) | 2019.04.08 |
| 복습문제 (0) | 2019.04.05 |
| [Oracle DBA]데이터 동시성 관리 (0) | 2019.04.03 |