따봉 구걸남's 블로그

A 유저가 B 유저의  한 테이블을 truncate 할수 있게 만들어 주려면 어떤권한이 필요할까?

grant truncate on B."테이블명" to A 

이런 구문이 가능하다면 문제는 해결되겠지만 ... truncate 권한만을 따로 줄수가 없다.

grant drop any table to A 

이렇게 하면 A 유저가 B 유저의 테이블을 truncate 할수 있다. 

하지만 한 테이블의 truncate 를 가능하게 하기 위해서 너무나 큰 권한을 주었다. 

프로시저를 활용해서 drop any table 권한을 주지 않고도 

A 유저가 B 유저의 테이블을 truncate 하게 만들수 있다. 

create user u_obj identified by uobj;      -- table owner user

grant create session to u_obj;

grant resource to u_obj;

 

create table u_obj.t_trc ( a int);             -- truncate 대상 테이블

 

create user u_trc identified by utrc;       -- truncate 하려는 user

grant create session to u_trc;

grant select, insert on u_obj.t_trc to u_trc;

 

-- truncate 해줄 프로시져 작성 

create or replace procedure u_obj.p_trc( p_tname in varchar2 ) 

as 

begin 

execute immediate 'truncate table ' || p_tname; 

end; 

/

이 상태에서 u_trc 는 t_trc 테이블을 truncate 할수 없지만 프로시저를 

실행함으로써 가능하게 할수 있다. 

리눅스 환경에서 설치한 오라클 삭제 하기

 cd /etc

 rm ora*

 cd /usr/local/bin

 rm oraenv

userdel oracle

groupdel dba

groupdel oinstall

구글 클라우드 플랫폼에 오라클 12c 를 설치하고 접속해보자 

앞선 포스팅에서 구글 클라우드 플랫폼(이하 GCP) 에 MSSQL 을 설치하고 

접속하는 것까지 테스트 해보았다.  

GCP 를 활용하여 테스트서버를 구축하는 것의 장점은 인터넷만 가능하다면

어디든 접속이 가능하다는 것이다. 

GCP 에 오라클을 설치하고 접속하는 것 또한 MSSQL 과 과정이 동일하다.

오라클이 사용하는 포트로만 변경하면 된다. 

GCP 계정이 없다면 이전에 작성한 내용을 참고해서 만들어보자. 간단하다 

http://riorio.tistory.com/107

오라클닷컴 에서 oracle 12c 를 다운로드 한다. 

 윈도우에 설치하는 것은 간단하다. 따로 작성하진 않겠다.

GCP 에 oracle 를 설치하고 나서 바로 접속하려면 접속이 되지 않는다. 

몇가지 작업을 해줘야 한다. 

- VPC 네트워크 설정

    GCP 콘솔로 들어간 후 왼쪽의 스크롤바를 내려서 VPC 네트워크를 확인한다.  

  콘솔로 이동!

왼쪽에 길게 늘어선 메뉴 스크롤바를 내린다

방화벽 규칙을 만들자 

디폴트로 만들어진 방화벽 규칙 + MSSQL 테스트용으로 작성한 방화벽 

규칙 말고는 없다. 

이 상태에서는 외부에서 오라클 DB 서버로 접속이 안된다. 

방화벽 규칙의 이름을 만들자

대상은 네트워크의 모든 인스턴스로 선택

테스트 서버로 접속을 허용할 ip 또는 ip 대역을 입력한다. 

나는 내 pc 로만 접속할거라 내 pc 의 ip 를 입력했다. 

0.0.0.0 을 입력하면 모든 ip 가 허용된다.

ORACLE 접속을 테스트할 예정이니 ORACLE 이 

사용할 포트 (default 1521) 를 규칙에 맞게 써준다. 

만들기 클릭

시간이 좀 지나면 방화벽 규칙이 만들어진걸 확인 할 수가 있다. 

- 테스트서버 방화벽

테스트 서버의 윈도우즈 방화벽에 oracle 전용 inbound rule 을 추가하자.

제어판 -> 시스템 및 보안 -> 윈도우즈 방화벽 -> 고급설정 

Inbound Rules 에서 New Rule 추가 

oracle 포트를 입력 하자 

새로운 방화벽 규칙에 이름을 지어주자. 

여기까지 설정했으면 외부서버에서 테스트 서버의 oracle 에 접속이 되는지

테스트 해보자.

- DB server side 

우선 oracle 서버에 접속 테스트용 계정을 만들자. 

- Client side

 내pc 에서 접속을 하려면 oracle client 가 설치 되어 있어야 한다. 

 tnsnames.ora 파일 수정 

ip 와 서비스 네임은 환경에 맞게 수정한다. 

접속이 잘된다. 

이상 끗

● centos 7 환경에서 oracle 12c silent mode 로 설치 

가끔 오라클을 설치할때 GUI 방식을 사용하지 못할때가 있다. 

그럴때는 silent 모드로 설치하면 되는데, 지금까지는 그냥 GUI 방식으로 

일할수 있는 환경을 만들어 달라고 요구했었다. 

이번에는 그런 환경이 여의치 않아 테스트 삼아 silent 방식으로 설치했다. 

역시 IT 의 세계가 그렇듯이 한방에 되는건 없었다. 

responseFile 에서 삽질에 삽질을 거듭하다 설치를 성공했다. 

현시점으로는 EE 버전에서 12c R2 는 안나온것 같다. 

오라클 버전은 Enterprise Edition 12.1.0.2.0 이다. 

DB 는 containerDB 를 사용하는 것으로 설정했다.

많이 참고하십시요.  공감 구걸.

1. 환경설정 (root 유저)

- yum install 

yum install -y binutils compat-libcap1 gcc gcc-c++ glibc glibc glibc-devel glibc-devel ksh compat-libstdc++-33 libaio libaio libaio-devel libaio-devel libgcc libgcc libstdc++ libstdc++ libstdc++-devel libstdc++-devel libXi libXi libXtst libXtst make sysstat xorg-x11-apps

- oracle 유저 생성

[root@localhost ~]# groupadd oinstall

[root@localhost ~]# groupadd dba

[root@localhost ~]# useradd -g oinstall -G dba oracle

[root@localhost ~]# passwd oracle

Changing password for user oracle.

New password: 

BAD PASSWORD: The password is shorter than 8 characters

Retype new password: 

passwd: all authentication tokens updated successfully.

[root@localhost ~]# 

cd / 

mkdir oracle

chown -R oracle:oinstall /oracle

vi /etc/sysctl.conf

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 1987162112

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048586

-- 설정 확인

sysctl -p

vi /etc/security/limits.conf

( 파일 아래쪽에 ) 

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

2. oracle 설정 (oracle 유저)

su - oracle

vi /home/oracle/.bash_profile

------------ bash_profile --------------------------------------------------

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export TMP=/tmp

export ORACLE_HOSTNAME=ora12c

export ORACLE_UNQNAME=orcl

export ORACLE_SID=orcl

export ORACLE_BASE=/oracle

export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1

export ORACLE_HOME_LISTNER=$ORACLE_HOME/bin/lsnrctl

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export PATH=$ORACLE_HOME/bin:$PATH

------------ bash_profile --------------------------------------------------

unzip linuxamd64_12102_database_1of2.zip

unzip linuxamd64_12102_database_2of2.zip

3. silent mode 로 Database 설치

cd /home/oracle/database

[oracle@oracdb database]$  ./runInstaller -silent \

> DECLINE_SECURITY_UPDATES=true \

> SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \

> oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.1.0 \

> oracle.install.option=INSTALL_DB_SWONLY \

> ORACLE_HOSTNAME=oracdb \

> UNIX_GROUP_NAME=dba \

> INVENTORY_LOCATION=/home/oracle/oraInventory \

> SELECTED_LANGUAGES=en \

> ORACLE_HOME=/oracle/product/12.1.0/dbhome_1 \

> ORACLE_BASE=/oracle \

> oracle.install.db.InstallEdition=EE \

> oracle.install.db.DBA_GROUP=oinstall \

> oracle.install.db.OPER_GROUP=dba \

> oracle.install.db.BACKUPDBA_GROUP=dba \

> oracle.install.db.DGDBA_GROUP=dba \

> oracle.install.db.KMDBA_GROUP=dba

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 14939 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 3071 MB    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-12-12_05-11-10PM. Please wait ...[oracle@oracdb database]$ [WARNING] [INS-13014] Target environment does not meet some optional requirements.

   CAUSE: Some of the optional prerequisites are not met. See logs for details. /tmp/OraInstall2017-12-12_05-11-10PM/installActions2017-12-12_05-11-10PM.log

   ACTION: Identify the list of failed prerequisite checks from the log: /tmp/OraInstall2017-12-12_05-11-10PM/installActions2017-12-12_05-11-10PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.

You can find the log of this install session at:

 /home/oracle/oraInventory/logs/installActions2017-12-12_05-11-10PM.log

[oracle@oracdb database]$ 

[oracle@oracdb database]$ 

[oracle@oracdb database]$ 

[oracle@oracdb database]$ The installation of Oracle Database 12c was successful.

Please check '/home/oracle/oraInventory/logs/silentInstall2017-12-12_05-11-10PM.log' for more details.

As a root user, execute the following script(s):

        1. /home/oracle/oraInventory/orainstRoot.sh

        2. /oracle/product/12.1.0/dbhome_1/root.sh

Successfully Setup Software.

** root 로 접속해서 스크립트 실행 

[root@oracdb ~]# sh /home/oracle/oraInventory/orainstRoot.sh

Changing permissions of /home/oracle/oraInventory.

Adding read,write permissions for group.

Removing read,write,execute permissions for world.

Changing groupname of /home/oracle/oraInventory to dba.

The execution of the script is complete.

[root@oracdb ~]# sh /oracle/product/12.1.0/dbhome_1/root.sh

Check /oracle/product/12.1.0/dbhome_1/install/root_oracdb_2017-12-12_17-16-25.log for the output of root script

[root@oracdb ~]# 

[oracle@oracdb response]$ dbca -silent -responseFile /home/oracle/database/response/dbca_fs.rsp

Enter PDBADMIN User Password: 

Copying database files

1% complete

2% complete

8% complete

13% complete

19% complete

27% complete

Creating and starting Oracle instance

29% complete

32% complete

33% complete

34% complete

38% complete

42% complete

43% complete

45% complete

Completing Database Creation

48% complete

51% complete

53% complete

62% complete

70% complete

72% complete

Creating Pluggable Databases

78% complete

100% complete

Look at the log file "/oracle/cfgtoollogs/dbca/cdbtest/cdbtest.log" for further details.

--------------- dbca.rsp ------------------------------

[GENERAL]

RESPONSEFILE_VERSION = "12.1.0"

OPERATION_TYPE = "createDatabase"

[CREATEDATABASE]

GDBNAME = "cdbtest"

DATABASECONFTYPE  = "SI"

SID = "cdbtest"

CREATEASCONTAINERDATABASE = true

NUMBEROFPDBS = 1

PDBNAME = pdb01

TEMPLATENAME = "General_Purpose.dbc"

SYSPASSWORD = "oracle"

SYSTEMPASSWORD = "oracle"

DATAFILEDESTINATION = /DATA01/oracle

RECOVERYAREADESTINATION= /DATA01/oracle/reco

STORAGETYPE=FS

CHARACTERSET = "US7ASCII"

NATIONALCHARACTERSET= "UTF8"

SAMPLESCHEMA=FALSE

MEMORYPERCENTAGE = "40"

DATABASETYPE = "MULTIPURPOSE"

AUTOMATICMEMORYMANAGEMENT = "TRUE"

TOTALMEMORY = "2048"

--------------- dbca.rsp ------------------------------

** 나는 12c 의 new feature 인 CDB, PDB 를 테스트 할 목적이라 

CDB, PDB 를 생성하는 것으로 설정을 했다. 참고해서 자신의 용도에 맞게

설정을 수정하시길...

[oracle@oracdb response]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 13 14:21:19 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name, cdb from v$database;

NAME      CDB

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

CDBTEST   YES

SQL> 

이상 끗

스키마모드 datapump 테스트 

● EXPDP

- exclude 에 like 사용하기

expdp test/test  parfile=like.par

-------------- like.par -------------------------- 

directory=datapump

logfile=schema_exp.log

dumpfile=schema.dmp

filesize =100G

schemas=A,B,C,testS

exclude=table:"like '%EX_TAB'"

content=ALL

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

- exclude 에 in 사용하기

expdp test/test  parfile=in.par

-------------- in.par --------------------------

directory=datapump

logfile=schema_exp.log

dumpfile=schema.dmp

filesize =100G

schemas=A,B,C,testS

exclude=table:"IN('EX_TAB')"

content=ALL

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

** parameter 파일을 사용하지 않고 직접 커먼드에 쓸때는

모든 특수문자 앞에 '\' 를 넣어줘야 한다. 

ex) expdp test/test full=y EXCLUDE=TABLE:\"IN \(\'TEMP\',\'TEMP1\'\)\"

● IMPDP

full dump 에서 testS 스키마만 imptest 스키마에 이관하기

1. Index,TRIGGER,statistics,constraint 는 제외하고 import 하는 경우

impdp  test/test parfile=imp_ddl.par

---------imp_ddl.par ------------------------- 

directory=datapump 

logfile=full_data.log 

dumpfile=full_data.dmp 

schemas=testS

remap_schema=testS:imptest

content=metadata_only

exclude=index,TRIGGER,statistics,constraint

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

아래 에러가 난다면 ..

ORA-39168: Object path TRIGGER was not found. 

- 뷰를 확인해서 valid value 인지 확인

* exclude, include 에 사용가능한 object type 확인은

 full mode =>  DATABASE_EXPORT_OBJECTS

 schema mode => SCHEMA_EXPORT_OBJECTS

 table/tablespace mode => TABLE_EXPORT_OBJECTS

뷰에서 확인 가능.

select *

from SCHEMA_EXPORT_OBJECTS

where object_path like '%TRIG%'

- export 에 trigger 가 없는 경우

* LOB 타입은 exclude  안되

2. data 만 import 하는 경우

impdp test/test parfile=imp_data.par

-----------imp_data.par  ----------------- 

directory=datapump 

logfile=imp_data.log 

dumpfile=full_data.dmp 

schemas=testS

remap_schema=testS:imptest

content=data_only

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

3. Index 등 기타 object 만 import 하는 경우

impdp test/test parfile=imp_idx.par

------------imp_idx.par -------------------- 

directory=datapump 

logfile=imp_idx.log 

dumpfile=full_data.dmp 

schemas=testS

remap_schema=testS:imptest

content=metadata_only

include=index,TRIGGER,statistics,constraint

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

datapump 로 이관하는 테스트를 정리해봤다. 

parameter 파일을 쓰면 좀 더 편하지만 잘못 작성할경우 에러메세지가 

안나오기도 하더란..

필요하신분은 참고하시길..

여러개의 반복되는 쿼리 수동으로 만들기 귀찮을 때... 

비싼 오라클에게 일을 맡기자.

예를 들어 AAA 테이블의 data 를 select 해서 AAA2 로 insert 하는 경우

insert into AAA2 select * from AAA

이렇게 하면 된다. 

하지만 AAA 의 데이터량이 많아서 운영중에 대량 insert 하기 부담스러운 상황일 경우..

마침 AAA 테이블의 key_date 컬럼이 있다고 치고 key_date 의 값에 따라 쪼개서 insert 를 할수 있다. 

i.e) insert into AAA2 select * from AAA 

where key_date between TO_DATE('20170915') +9/24 and TO_DATE('20170915') +10/24 

and key_date < TO_DATE('20170915') +10/24;

 -- 1.  파티션 테이블 생성

create table PT_TEST(
  NO NUMBER NOT NULL,
  NAME VARCHAR2(10) NULL
)
PARTITION BY RANGE(no)
(
     PARTITION PT_0 VALUES LESS THAN (0)
);

 -- 2. 파티션 테이블 추가

alter table PT_TEST ADD PARTITION PT_1 VALUES LESS THAN (5);
alter table PT_TEST ADD PARTITION PT_2 VALUES LESS THAN (10);

alter table PT_TEST ADD PARTITION PT_3 VALUES LESS THAN (15);

 -- 3. 테스트 데이터 INSERT
INSERT INTO PT_TEST VALUES (1 , 'A');
INSERT INTO PT_TEST VALUES (2 , 'B');
INSERT INTO PT_TEST VALUES (3 , 'C');
INSERT INTO PT_TEST VALUES (4 , 'D');
INSERT INTO PT_TEST VALUES (5 , 'E');
INSERT INTO PT_TEST VALUES (6 , 'F');
INSERT INTO PT_TEST VALUES (7 , 'G');
INSERT INTO PT_TEST VALUES (8 , 'H');
INSERT INTO PT_TEST VALUES (9 , 'I');
INSERT INTO PT_TEST VALUES (10 , 'J');
INSERT INTO PT_TEST VALUES (11 , 'K');

 -- 확인

SELECT * FROM PT_TEST
SELECT * FROM PT_TEST PARTITION (PT_1);
SELECT * FROM PT_TEST PARTITION (PT_2);

 -- 3. 파티션 삭제

ALTER TABLE  PT_TEST DROP PARTITION pt_1;

 -- 4. 파티션 이름 변경

ALTER TABLE PT_TEST RENAME PARTITION PT_1 TO PT_111;

-- 5.  파티션 TRUNCATE

ALTER TABLE pt_test TRUNCATE PARTITION PT_2;

-- 6. 파티션 exchange 

ALTER TABLE PT_TEST EXCHANGE PARTITION PT_2  WITH TABLE TEST  WITHOUT VALIDATION;

-- 7. 파티션 테이블 통계정보 

exec dbms_stats.gather_table_stats(cascade=>true,ownname=>'A',tabname=>'PT_TEST',partname=>'PT_2',

estimate_percent=>10,degree=>5,granularity=>'PARTITION');