다른 명령
오라클 DBA 작업 명령어
시퀀스
시퀀스 : 순차번호 생성
CREATE SEQUENCE
시퀀스 생성
CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value;
예시:
CREATE SEQUENCE supplier_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20;
ALTER SEQUENCE
시퀀스 수정:
ALTER SEQUENCE <sequence_name> INCREMENT BY <integer>; ALTER SEQUENCE seq_inc_by_ten INCREMENT BY 10;
시퀀스 최대값 변경:
ALTER SEQUENCE <sequence_name> MAXVALUE <integer>; ALTER SEQUENCE seq_maxval MAXVALUE 10;
시퀀스 순환/비순환 변경 :
ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>; ALTER SEQUENCE seq_cycle NOCYCLE;
시퀀스 캐시/비캐시 변경:
ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE; ALTER SEQUENCE seq_cache NOCACHE;
RAC에서 두서버간 정렬순 시퀀스 채번
ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>; ALTER SEQUENCE seq_order NOORDER; ALTER SEQUENCE seq_order;
테이블 작업
INSERT
INSERT 문
INSERT ~ VALUES
-- 컬럼 생략 INSERT INTO table_name VALUES ('Value1', 'Value2', ... ); INSERT INTO table_name( Column1, Column2, ... ) VALUES ( 'Value1', 'Value2', ... );
INSERT ~ SELECT
INSERT INTO table_name( SELECT Value1, Value2, ... from table_name ); -- INSERT INTO table_name ( Column1, Column2, ... ) ( SELECT Value1, Value2, ... from table_name );
INSERT ALL INTO ~ VALUES
- 조건에 따라서 여러개의 테이블에 INSERT 할 경우 사용
INSERT ALL WHEN sal >= 3000 THEN INTO emp_sal1 VALUES (empno, name, sal) WHEN sal >= 2000 THEN INTO emp_sal2 VALUES (empno, name, sal) ELSE INTO emp_sal3 VALUES (empno, name, sal) SELECT a.empno , a.name , a.sal FROM emp a WHERE a.empno = 11
INSERT FIRST
- 조건에 만족하는 첫 번째 WHEN~THEN의 테이블에만 INSERT
INSERT FIRST WHEN sal >= 3000 THEN INTO table1 VALUES (empno, ename, sal) WHEN sal >= 2000 THEN INTO table2 VALUES (empno, ename, sal) ELSE INTO table3 VALUES (empno, ename, sal) SELECT a.empno , a.ename , a.sal FROM emp a WHERE a.empno = 7788
UPDATE
다중 UPDATE
UPDATE TB_MIG_SEQ_MGR A SET A.TABLE_HAN_NAME = ( SELECT COMMENTS FROM DBA_TAB_COMMENTS B WHERE B.TABLE_NAME = A.TABLE_NAME AND B.OWNER = A.OWNER ) ;
MERGE INTO 절
attach_file - 주의사항
- ON(조인) 조건절에 사용한 컬럼은 UPDATE 불가
- 트리거 발생되지 않음
MERGE 문법
MERGE [ hint ] -- 1.MERGE 대상 테이블 INTO [ schema. ] { table | view } [ t_alias ] -- 2.비교 대상 조회 USING { [ schema. ] { table | view } | subquery } [ t_alias ] -- 3.조인 조건 ON ( condition ) -- 4.데이터 존재시 -- WHEN MATCHED THEN UPDATE SET column = { expr | DEFAULT } [, column = { expr | DEFAULT } ]... [ DELETE where_clause ] -- 4.데이터 미존재시 -- WHEN NOT MATCHED THEN INSERT [ (column [, column ]...) ] VALUES ({ expr [, expr ]... | DEFAULT }) ;
- - INTO : DATA가 UPDATE되거나 INSERT 될 테이블 또는 뷰를 지정.
- - USING : 비교할 SOURCE 테이블 또는 뷰나 서브쿼리를 지정, INTO절의 테이블과 동일하거나 다를 수 있다.
- - ON : UPDATE나 INSERT를 하게 될 조건으로, 해당 조건을 만족하는 DATA가 있으면 WHEN MATCHED 절을 실행하게 되고, 없으면 WHEN NOT MATCHED 이하를 실행하게 된다.
- - WHEN MATCHED : ON 조건절이 TRUE인 ROW에 수행 할 내용 (UPDATE, DELETE포함 될 수 있음)
- - WHEN NOT MATCHED : ON 조건절에 맞는 ROW가 없을 때 수행할 내용 (INSERT)
샘플1
MERGE INTO emp_merge_test m -------------------------------- USING emp e -------------------------------- ON (m.empno = e.empno) -------------------------------- WHEN MATCHED THEN UPDATE SET m.sal = ROUND(m.sal*1.1) DELETE WHERE (m.deptno = 20) -- 부서번호 20의 사원정보는 삭제. -------------------------------- WHEN NOT MATCHED THEN INSERT (m.empno, m.deptno, m.sal) VALUES (e.empno, e.deptno, ROUND(e.sal*1.2)) ; -------------------------------- COMMIT;
샘플2
MERGE INTO TB_TUNE_SQL_STATS T USING (SELECT 1 SNAP_ID , SQL_TEXT, SQL_FULLTEXT, SQL_ID, LAST_ACTIVE_TIME, LAST_ACTIVE_CHILD_ADDRESS , PLAN_HASH_VALUE, PARSE_CALLS, DISK_READS, DIRECT_WRITES, BUFFER_GETS , ROWS_PROCESSED, SERIALIZABLE_ABORTS, FETCHES, EXECUTIONS, END_OF_FETCH_COUNT , LOADS, VERSION_COUNT, INVALIDATIONS, CPU_TIME , ELAPSED_TIME, AVG_HARD_PARSE_TIME, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME , USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, SORTS, SHARABLE_MEM, TOTAL_SHARABLE_MEM , TYPECHECK_MEM, IO_INTERCONNECT_BYTES, PHYSICAL_READ_REQUESTS, PHYSICAL_READ_BYTES , PHYSICAL_WRITE_REQUESTS, PHYSICAL_WRITE_BYTES FROM V$SQLSTATS WHERE LAST_ACTIVE_TIME BETWEEN SYSDATE-1/24/60*10 AND SYSDATE -- 10분전 ([TODO] SNAPID 시간으로 변경) ) S ON (T.SQL_ID = S.SQL_ID ) WHEN MATCHED THEN -- SQL_ID가 같으면 업데이트 UPDATE SET T.SQL_TEXT = S.SQL_TEXT , T.SQL_FULLTEXT = S.SQL_FULLTEXT , T.LAST_ACTIVE_TIME = S.LAST_ACTIVE_TIME , T.LAST_ACTIVE_CHILD_ADDRESS = S.LAST_ACTIVE_CHILD_ADDRESS , T.PLAN_HASH_VALUE = S.PLAN_HASH_VALUE , T.PARSE_CALLS = S.PARSE_CALLS , T.DISK_READS = S.DISK_READS , T.DIRECT_WRITES = S.DIRECT_WRITES , T.BUFFER_GETS = S.BUFFER_GETS , T.ROWS_PROCESSED = S.ROWS_PROCESSED , T.SERIALIZABLE_ABORTS = S.SERIALIZABLE_ABORTS , T.FETCHES = S.FETCHES , T.EXECUTIONS = S.EXECUTIONS , T.END_OF_FETCH_COUNT = S.END_OF_FETCH_COUNT , T.LOADS = S.LOADS , T.VERSION_COUNT = S.VERSION_COUNT , T.INVALIDATIONS = S.INVALIDATIONS , T.CPU_TIME = S.CPU_TIME , T.ELAPSED_TIME = S.ELAPSED_TIME , T.APPLICATION_WAIT_TIME = S.APPLICATION_WAIT_TIME , T.CONCURRENCY_WAIT_TIME = S.CONCURRENCY_WAIT_TIME , T.USER_IO_WAIT_TIME = S.USER_IO_WAIT_TIME , T.PLSQL_EXEC_TIME = S.PLSQL_EXEC_TIME , T.JAVA_EXEC_TIME = S.JAVA_EXEC_TIME , T.SORTS = S.SORTS WHEN NOT MATCHED THEN -- SQL_ID가 없으면 인서트 INSERT (SNAP_ID,SQL_TEXT ,SQL_FULLTEXT ,SQL_ID ,LAST_ACTIVE_TIME ,LAST_ACTIVE_CHILD_ADDRESS ,PLAN_HASH_VALUE ,PARSE_CALLS ,DISK_READS ,DIRECT_WRITES ,BUFFER_GETS ,ROWS_PROCESSED ,SERIALIZABLE_ABORTS ,FETCHES ,EXECUTIONS ,END_OF_FETCH_COUNT ,LOADS ,VERSION_COUNT ,INVALIDATIONS ,CPU_TIME ,ELAPSED_TIME ,APPLICATION_WAIT_TIME ,CONCURRENCY_WAIT_TIME ,USER_IO_WAIT_TIME ,PLSQL_EXEC_TIME ,JAVA_EXEC_TIME ,SORTS) VALUES (S.SNAP_ID,S.SQL_TEXT ,S.SQL_FULLTEXT ,S.SQL_ID ,S.LAST_ACTIVE_TIME ,S.LAST_ACTIVE_CHILD_ADDRESS ,S.PLAN_HASH_VALUE ,S.PARSE_CALLS ,S.DISK_READS ,S.DIRECT_WRITES ,S.BUFFER_GETS ,S.ROWS_PROCESSED ,S.SERIALIZABLE_ABORTS ,S.FETCHES ,S.EXECUTIONS ,S.END_OF_FETCH_COUNT ,S.LOADS ,S.VERSION_COUNT ,S.INVALIDATIONS ,S.CPU_TIME ,S.ELAPSED_TIME ,S.APPLICATION_WAIT_TIME ,S.CONCURRENCY_WAIT_TIME ,S.USER_IO_WAIT_TIME ,S.PLSQL_EXEC_TIME ,S.JAVA_EXEC_TIME ,S.SORTS) ;
ORA-30926 에러
ORA-30926 : unable to get a stable set of rows in the source tables (원본 테이블의 고정 행 집합을 가져올 수 없습니다.)
- INSERT 또는 UPDATE 할 때 1개의 레코드를 대상으로 작업이 가능
- 하지만 SELECT의 결과가 2개 이상이 리턴되었기 때문에 오류 발생
- INSERT 구문에서 중복이 발생하거나 UPDATE 에 MULTI ROW가 UPDATE되는 경우 오류
- ON 구문에서 UPDATE되는 ROW가 1개 이상일 경우 오류
- (해결방법) ON 절의 Join 조건에 USING() 에서 좋회하는 결과가 중복된 값이 없으면 된다.
ORA-00600 에러 발생(12c)
A SQL MERGE statement fails with next error on sqlplus and in the alert log: ORA-00600: internal error code, arguments: [upsRowVec4]
해결 방안
- 패치
- alter session 명령
alter session set "_optimizer_eliminate_filtering_join"=false;
or
alter session set optimizer_features_enable='11.1.0.7';
오라클 테이블 생성 , CREATE TABLE
The syntax to create a table is:
CREATE TABLE [table name] ( [column name] [datatype], ... ) TABLESPACE TS_XXX PCTUSED 40 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE (...) LOGGING MONITORING ;
예시:
CREATE TABLE "SYS"."TB_BIG" ( "COLA" VARCHAR2(20 BYTE), "COLB" NUMBER, "COLC" NUMBER, "COLD" VARCHAR2(30 BYTE), "COLE" VARCHAR2(30 BYTE), "COLF" VARCHAR2(30 BYTE), "COLG" NUMBER, "COLH" VARCHAR2(30 BYTE), "COLI" VARCHAR2(30 BYTE) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) TABLESPACE USERS ;
BLOCK SIZE
- 32K
- 평균 row size가 커서 8K사용중인 테이블에서 row migration , row chaining이 자주발생할경우
- 한번에 대량 i/o가 발생 하는 테이블인 경우
PCTFREE
- 디폴트는 10 퍼센트 - 블록의 10 퍼센트를 데이터 갱신을위한 빈 영역으로 남겨둠.
- default, 기본 => 10
- DML이 발생되지 않는 테이블, 백업용 압축테이블 => 0
- Update/Delete 가 빈번하게 발생되는 테이블 => 20
- 채번 테이블 => 90
- PCTFREE 10% , PCTUSED 40%
PCTUSED
- 재사용 되기 위해 필요한 블럭의 퍼센트
- 디폴트 40 - 사용된 영역이 40% 보다 작아져야 새로운 행을 삽입 가능.
- 입력/삭제가 자주 발생하지 않으면 => 90
- 수정이 자주 발생되면 => 40
FREELIST
- FREE 블럭을 리스트 형태로 관리.
- INSERT 작업이 많이 발생하는 테이블,인덱스에 이값을 증가 시켜 빈 블럭을 할당받기 위해서 대기 하는일이 없도록 해야함.
INITRANS 와 MAXTRANS
- INITRANS
- 데이터 블록에 동시에 접근 가능한 트랜잭션의 처리 갯수를 의미(미리 확보할 ITL entry수)
- 트랜잭션이 많이 발생하는 경우 MAXTRANS 까지 늘어나며, PCTFREE 로 확보된 영역에 추가 확장 됨
- MAXTRANS
- 데이터 블록에 접근 가능한 최대 트랜잭션 수를 의미
- 접근하는 트랜잭션 수가 MAXTRNAS 값을 초과하는 경우, 앞의 트랜잭션이 COMMIT 혹은 ROLLBACK 을 해야 다음 트랜잭션이 접근 가능
- 최대 255개
INITRANS 와 MAXTRANS
- 초기값은 INITRANS의 값으로 정의되며 미리 확보할 ITL entry수를 의미한다.(테이블 생성시 initttans 기본값은 2 임)
- 최대값은 MAXTRANS 값으로 ITL Entry의 최대 수를 의미한다. (기본 255)
- 만약 모든 가용한 ITL이 사용중이고 새로운 ITL 슬롯을 동적으로 할당하기위한 PCTFREE 영역의 공간이 충분하지 않을때는 엔트리를 할당받지 못하여 TX Lock 경합이 발생하게 된다.
- - 이때는 ITL entry를 차지한 다른 트랜잭션이 커밋이나 롤백을 수행하여 ITL 슬롯을 재사용할 수 있을때까지 대기함
- - enq:TX allocate itl entry 대기 이벤트 발생
- 오라클은 row단위의 lock을 걸지만 일단 데이터의 Access시 블럭 단위로 엑세스 하므로 결국 block 단위의 lock이 필요하다.
ALTER TABLE
컬럼 추가 변경 삭제 관리
컬럼 추가
컬럼 추가 :
ALTER TABLE [table name] ADD ( [column name] [datatype], ... );
For 예시:
ALTER TABLE employee ADD (id int)
컬럼 수정
컬럼명(rename) 변경:
ALTER TABLE [table name] RENAME COLUMN [column name] TO [new column name] );
예시:
ALTER TABLE employee RENAME COLUMN col_01 TO col_02;
컬럼 속성(modify) 변경:
ALTER TABLE [table name] MODIFY ( [column name] [new datatype] );
예시:
ALTER TABLE employee MODIFY( sickHours float );
컬럼 삭제
컬럼 삭제 :
ALTER TABLE [table name] DROP COLUMN [column name];
예시:
ALTER TABLE employee DROP COLUMN vacationPay;
- 항목
Constraints 제약조건
Constraint types and codes
Type Code | Type Description | Acts On Level |
C | Check on a table | Column |
O | Read Only on a view | Object |
P | Primary Key | Object |
R | Referential AKA Foreign Key | Column |
U | Unique Key | Column |
V | Check Option on a view | Object |
제약조건(constraints) 조회
모든 제약 조건 조회 뷰:
SELECT table_name, constraint_name, constraint_type FROM dba_constraints;
FK (외래키) 제약조건
모든 외래키 제약 조건 원본과 참조 테이블/컬럼 조회 :
SELECT c_list.CONSTRAINT_NAME as NAME, c_src.TABLE_NAME as SRC_TABLE, c_src.COLUMN_NAME as SRC_COLUMN, c_dest.TABLE_NAME as DEST_TABLE, c_dest.COLUMN_NAME as DEST_COLUMN FROM ALL_CONSTRAINTS c_list, ALL_CONS_COLUMNS c_src, ALL_CONS_COLUMNS c_dest WHERE c_list.CONSTRAINT_NAME = c_src.CONSTRAINT_NAME AND c_list.R_CONSTRAINT_NAME = c_dest.CONSTRAINT_NAME AND c_list.CONSTRAINT_TYPE = 'R' GROUP BY c_list.CONSTRAINT_NAME, c_src.TABLE_NAME, c_src.COLUMN_NAME, c_dest.TABLE_NAME, c_dest.COLUMN_NAME;
테이블 제약 조건 설정
테이블 생성시 제약 조건 추가 :
CREATE TABLE table_name ( column1 datatype null/not null, column2 datatype null/not null, ... CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE] );
예시:
CREATE TABLE suppliers ( supplier_id numeric(4), supplier_name varchar2(50), CONSTRAINT check_supplier_id CHECK (supplier_id BETWEEN 100 and 9999) );
Unique 인덱스 추가
테이블 생성시 Unique 인덱스 추가 :
CREATE TABLE table_name ( column1 datatype null/not null, column2 datatype null/not null, ... CONSTRAINT constraint_name UNIQUE (column1, column2, column_n) );
For 예시:
CREATE TABLE customer ( id integer not null, name varchar2(20), CONSTRAINT customer_id_constraint UNIQUE (id) );
Unique 제약 조건 추가
ALTER TABLE [table name] ADD CONSTRAINT [constraint name] UNIQUE( [column name] ) USING INDEX [index name];
예시:
ALTER TABLE employee ADD CONSTRAINT uniqueEmployeeId UNIQUE(employeeId) USING INDEX ourcompanyIndx_tbs;
제약조건 삭제
ALTER TABLE [table name] DROP CONSTRAINT [constraint name];
For 예시:
ALTER TABLE employee DROP CONSTRAINT uniqueEmployeeId;
인덱스
기본적으로 Oracle은 B-tree 인덱스를 만듭니다.
인덱스 생성
The syntax for creating an index is:
CREATE [UNIQUE] INDEX index_name ON table_name (column1, column2, . column_n) [ COMPUTE STATISTICS ];
UNIQUE 인덱싱 된 열의 값 조합이 고유해야 함을 나타냅니다.
COMPUTE STATISTICS 인덱스 작성 중에 통계를 수집하도록 Oracle에 지시합니다. 그런 다음 명령문이 실행될 때 옵티마이저가 통계를 사용하여 최적의 실행 계획을 선택합니다.
For 예시:
CREATE INDEX customer_idx ON customer (customer_name);
예시, customer_idx라는 고객 테이블에 인덱스가 작성되었습니다. customer_name 필드로만 구성됩니다.
The following creates an index with more than one field:
CREATE INDEX customer_idx ON supplier (customer_name, country); '''The following collects statistics upon creation of the index:'''
CREATE INDEX customer_idx ON supplier (customer_name, country) COMPUTE STATISTICS;
함수 인덱스 생성
In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.
The syntax that creates a function-based index is:
CREATE [UNIQUE] INDEX index_name ON table_name (function1, function2, . function_n) [ COMPUTE STATISTICS ];
For 예시:
CREATE INDEX customer_idx ON customer (UPPER(customer_name));
An index, based on the uppercase evaluation of the customer_name field, has been created.
To assure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(customer_name) does not evaluate to a NULL value. To ensure this, add UPPER(customer_name) IS NOT NULL to your WHERE clause as follows:
SELECT customer_id, customer_name, UPPER(customer_name) FROM customer WHERE UPPER(customer_name) IS NOT NULL ORDER BY UPPER(customer_name);
인덱스명 변경
ALTER INDEX index_name RENAME TO new_index_name;
For 예시:
ALTER INDEX customer_id RENAME TO new_customer_id;
예시, customer_id renamed to new_customer_id.
인덱스 통계 정보 수집
인덱스에서 통계를 수집하는 구문은 다음과 같습니다.
ALTER INDEX index_name REBUILD COMPUTE STATISTICS;
예시:
ALTER INDEX customer_idx REBUILD COMPUTE STATISTICS;
예시, customer_idx라는 인덱스에 대한 통계가 수집됩니다.
인덱스 삭제
DROP INDEX index_name;
롤 생성 삭제 관리
ROLE 생성
CREATE ROLE RL_MANAGER;
ROLE에 권한 부여
GRANT create session, create table TO RL_MANAGER;
권한이 부여된 ROLE을 USER나 ROLE에 부여
GRANT RL_MANAGER TO scott, test;
롤에 부여된 권한 회수
REVOKE RL_MANAGER FROM 유저명
디폴트 롤 (default role, 부여된 롤에 대한 일시 정지 )
alter user 유저명 default role 롤1, 롤2; -- or -- 해당 유저에게 부여된 모든 롤을 사용하되 롤3은 제외한다. alter user 유저명 default role all except 롤3;
- 유저에게 롤1, 롤2, 롤3이 부여되어 있다고 할 때, 위 명령 실행시 유저는 롤1과 롤2만 쓰고 나머지 롤3은 일시적으로 안쓴다.
- 해당 유저에게 부여된 모든 롤을 사용한다.
alter user 유저명 default role all;
- 해당 유저에게 부여된 모든 롤을 사용하지 않는다.
SQL> alter user 유저명 default role none;
셋 롤(set role)
- 유저가 일시적으로 자기가 부여받은 롤 범위 내에서 사용과 미사용을 제어 가능.
SQL> set role 롤1; -- 해당 유저는 롤1을 사용함. set role all; -- 해당 유저는 부여받은 롤을 모두 사용함.
롤 생성 후 바로 적용이 되지 않을때
- 일단 현재 세션을 종료 후 재접속 해본다.
현재 세션에 활성화 된 롤 체크
-- Check active roles in the current session SELECT * FROM SESSION_ROLES;
롤 삭제
DROP ROLE RL_MANAGER
(예제) 롤 생성 / 테이블 권한 부여 / 확인
- DBCAFE 유저에 테이블 생성
- RL_DBCAFE_ALL 롤을 생성
- DBCAFE 유저에 생성된 테이블의 CRUD 권한을 RL_DBCAFE_ALL롤에 부여
- RL_DBCAFE_ALL롤을 CYKIM 유저에게 부여
- CYKIM 유저가 DBCAFE 유저 테이블 CRUD 처리 확인
-- 1. DBCAFE 유저에 테이블 생성 CREATE TABLE DBCAFE.TB_DBCAFE (A NUMBER); INSERT INTO DBCAFE.TB_DBCAFE VALUES (1); COMMIT; -- 2. 롤 생성 CREATE ROLE RL_DBCAFE_ALL; -- 세션 생성권한 부여 GRANT CREATE SESSION TO RL_DBCAFE_ALL; -- 3.DBCAFE.TB_DBCAFE 테이블 CRUD 권한을 RL_DBCAFE_ALL 에게 부여 GRANT SELECT,INSERT,UPDATE,DELETE ON DBCAFE.TB_DBCAFE TO RL_DBCAFE_ALL ; -- 4. 일반사용자 CYKIM 에게 RL_DBCAFE_ALL 롤 부여 GRANT RL_DBCAFE_ALL TO CYKIM; -- 5.일반사용자 CYKIM유저가 DBCAFE.TB_DBCAFE 테이블에 CRUD 되는지 확인 INSERT INTO DBCAFE.TB_DBCAFE VALUES (2); COMMIT; SELECT * FROM DBCAFE.TB_DBCAFE;
롤/시스템권한/테이블권한 조회
-- Check the roles granted to the user SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'MY_NEW_USER'; -- Check the privileges granted to the role SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'MY_NEW_ROLE'; SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'MY_NEW_ROLE';
롤 활성화
-- Set the role to be active SET ROLE my_new_role;
롤 조회 딕셔너리 뷰
딕셔너리 뷰 | 설명 |
---|---|
DBA_ROLES | 롤 목록 조회 |
ROLE_ROLE_PRIVS | 롤에게 부여된 롤 권한 조회 |
ROLE_SYS_PRIVS | 롤에게 부여된 시스템 권한 조회 |
ROLE_TAB_PRIVS | 롤에게 부여된 테이블 권한 조회 |
DBA_ROLE_PRIVS | 조회가능한 롤 목록 |
USER_TAB_PRIVS_RECD | 유저에게 부여된 오브젝트 권한 |
USER_COL_PRIVS_MADE | 유저 오브젝트 컬럼에 부여된 오브젝트 권한 |
USER_COL_PRIVS_RECD | 특정 컬럼에 대해 유저에게 부여된 오브젝트 권한
Object privileges granted to the user on specific columns |
SESSION_ROLES | 현재 세션에 활성화 된 롤 조회 |
유저에 부여된 롤 조회
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE ='유저명'
롤에 부여된 시스템 권한조회
- - ROLE_SYS_PRIVS 롤에 부여된 시스템 권한 조회
- SELECT ANY DICTIONARY ....
- CREATE SEQUENCE / ANY SYNONYM ....
- DROP ....
- ALTER SESSION .....
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN('롤이름')
롤에 부여된 테이블 권한 조회
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE IN('롤이름')
테이블 스페이스 (TABLESPACE)
TABLESPACE 목록 조회
SELECT A.TABLESPACE_NAME , FILE_NAME , BYTES / 1024 / 1024 , MAXBYTES / 1024 / 1024 , AUTOEXTENSIBLE , B.BLOCK_SIZE , INCREMENT_BY * B.BLOCK_SIZE / 1024 / 1024 FROM DBA_DATA_FILES A , DBA_TABLESPACES B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME ORDER BY B.BLOCK_SIZE, A.TABLESPACE_NAME, A.FILE_ID;
TABLESPACE 종류
현재 유저의 DEFAULT TABLESPACE 확인
SELECT * FROM USER_USERS ;-- DEFAUT TABLESPACE로 설정된 부분을 확인
유저의 DEFAULT TABLESPACE 변경
ALTER USER [유저명] DEFAULT TABLESPACE [테이블 스페이스명]
TABLESPACE 생성 구문
CREATE [BIGFILE/SMALLFILE] TABLESPACE TABLESPACE명 DATAFILE '파일경로1' SIZE integer [M/K], '파일경로2' SIZE integer [M/K] [ MINIMUM EXTENT integer [M/K]] [ BLOCKSIZE integer [K]] [ DEFAULT STORAGE ( INITIAL integer [M/K] NEXT integer [M/K] MAXEXTENTS integer MINEXTENTS integer PCTINCREASE integer)] [ ONLINE | OFFLINE ] [ PERMANENT | TEMPORARY ] [ EXTENT MANAGEMENT [ DICTIONARY | LOCAL [ AUTOALLOCATE | UNIFORM [ SIZE integer [M/K]]]]] [ SEGMENT SPACE MANAGEMENT [ MANUAL | AUTO]] ; -- AUTOEXTEND ON NEXT 10M ==> (옵션)데이타 파일 용량초과시 자동증가설정 -- MAXSIZE 100M ==> (옵션)데이타파일 최대크기지정 -- EXTENT MANAGEMENT LOCAL ==> (옵션) -- UNIFORM SIZE 1M ==> (옵션) -- DEFAULT STORAGE( : INITIAL 80K ==> 테이블 스페이스의 맨 첫번째 EXTENTS의 크기 : NEXT 80K ==> 다음 EXTENTS의 크기 : MINNEXTENTS 1 ==> 생성할 EXTENTS의 최소값 : MAXNEXTENTS 121 ==> 생성할 EXTENTS의 최대값 : PCTINCREASE 80 ==> EXTENTS의 증가율,(DEFAULT값은 50%) : )
BIGFILE 테이블스페이스
- 8K 블록 - bigfile 테이블 스페이스에는 최대 32TB
- 32K 블록 - bigfile 파일 테이블 스페이스에는 최대 128TB
BIGFILE 테이블스페이스 장점
자동 수행 뷰 목록
BIGFILE 테이블스페이스 단점
UNDO TABLESPACE 생성
CREATE UNDO TABLESPACE [테이블 스페이스명] ==> 이부분만 다름. DATAFILE '\경로\TEST_UNDO.DBF' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M -- UNDO_MANAGEMENT와 UNDO_TABLESPACE, UNDO_RETENTION PARAMETER를 제공 -- 지역적으로 관리되는 익스텐트만 사용가능 -- 시스템에 의해 관리되는 익스텐트 할당만 사용가능하다. -- (UNIFORMSIZE를 할당 할 수 없고 AUTOALLOCATE만 가능)
TABLESPACE 변경
TABLESPACE 읽기/쓰기 모드 변경
- 읽기 전용 모드
alter tablespace <TABLESPACE명> read only;
- 읽기 쓰기 모드
alter tablespace <TABLESPACE명> read ,write;
OPEN 상태에서 DATAFILE 이동
alter tablespace TABLESPACE명 offline;
offline 된 T/S에 대해 복사/이동
cp /data1/xxx.dbf /data2/xxx.dbf or mv /data1/xxx.dbf /data2/xxx.dbf
OFFLINE 상태에서 DATAFILE 이동
alter tablespace TABLESPACE명 rename datafile '파일경로1/xxx.dbf' to '파일경로2/xxx.dbf'; alter tablespace TABLESPACE명 online;
MOUNT 상태에서 DATAFILE 이동
startup mount;
해당 T/S에 대해 복사/이동 후
alter database rename file '파일경로' to '파일경로'; alter database open;
- 모든 데이타 파일은 mount상태에서 복사/이동 가능
- system 파일은 mount상태에서만 복사/이동 가능
테이블의 TABLESPACE MOVE
ALTER TABLE [테이블명] MOVE TABLESPACE [테이블 스페이스명]
- -테이블스페이스 이동시 인덱스 리빌드 필요
ALTER INDEX [인덱스명] REBUILD ;
- 인덱스 리빌드 동시 수행 시
ALTER TABLE [테이블명] MOVE TABLESPACE [테이블 스페이스명] UPDATE INDEXES;
파티션닝 테이블 TABLESPACE MOVE
ALTER TABLE [테이블명] MOVE PARTITION [파티션명] TABLESPACE [테이블 스페이스명]
- 파티셔닝 테이블 인덱스 REBUILD
ALTER INDEX [인덱스명] REBUILD PARTITION [파티션명]
운영중인 테이블 TABLESPACE ONLINE MOVE
ALTER TABLE [테이블명] MOVE TABLESPACE [테이블 스페이스명] ONLINE -- online 옵션 ;
- 인덱스 리빌드 동시 수행 시
ALTER TABLE [테이블명] MOVE TABLESPACE [테이블 스페이스명] UPDATE INDEXES ONLINE -- online 옵션 ;
운영중인 파티션닝 테이블 TABLESPACE ONLINE MOVE
ALTER TABLE [테이블명] MOVE PARTITION [파티션명] TABLESPACE [테이블 스페이스명] ONLINE
TABLESPACE 사이즈 변경(RESIZE)
ALTER TABLESPACE ts_txxxx RESIZE 500G;
TABLESPACE 이름 변경
ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE]
인덱스의 TABLESPACE 변경
ALTER INDEX 인덱스명 REBUILD TABLESPACE [테이블 스페이스명] [ PARALLEL parallel_num ] [ LOGGING or NOLOGGING ] ;
인덱스의 TABLESPACE 변경 스크립트
SELECT 'ALTER INDEX '||index_name||' REBUILD TABLESPACE TS_OO_'||substr(a.index_name,4,2)||'_I;' FROM user_indexes a WHERE a.index_name NOT IN (SELECT index_name from user_part_indexes) AND substr(a.index_name,4,2) IN (SELECT SUBJECT_CD FROM TB_DBA_SUBJECT_CD);
인덱스/테이블 TABLESPACE 변경 스크립트
SELECT DECODE (segment_type, 'TABLE', segment_name, table_name) order_col1 , DECODE (segment_type, 'TABLE', 1, 2) order_col2 , 'alter ' || segment_type || ' ' || segment_name || DECODE (segment_type, 'TABLE', ' MOVE ', ' REBUILD ') || CHR (10) || ' TS_오너_01 ' || CHR (10) || ' storage ( initial ' || initial_extent || ' next ' || next_extent || CHR (10) || ' minextents ' || min_extents || ' maxextents ' || max_extents || CHR (10) || ' pctincrease ' || pct_increase || ' freelists ' || freelists || ');' FROM user_segments, (SELECT table_name, index_name FROM user_indexes) WHERE segment_type IN ('TABLE', 'INDEX') AND segment_name = index_name(+) ORDER BY 1, 2;
TABLESPACE 자동증가/최대 사이즈 변경
ALTER TABLESPACE ts_txxxx AUTOEXTEND ON NEXT 100M MAXSIZE 500G;-- 최대사이즈 , UNLIMITED => 무제한 증가
- 최대 TABLESPACE 사이즈 변경
ALTER TABLESPACE [테이블 스페이스명] ADD DATAFILE 'C:\경로\TEST2.DBF' SIZE 10M AUTOEXTEND ON NEXT 100M MAXSIZE 100G; ==> 10M씩 자동증가
데이터파일 사이즈 변경
ALTER DATABASE DATAFILE 'C:\경로\TEST1.DBF' RESIZE 10M;
데이터파일 추가
ALTER TABLESPACE [테이블 스페이스명] ADD DATAFILE 'C:\경로\TEST2.DBF' SIZE 10M;
데이터/템프파일 삭제
ALTER TABLESPACE [테이블 스페이스명] DROP DATAFILE 'data파일'; -- 템프TS DROP TEMPFILE 'data파일';
데이터 파일 삭제 후 OS 디스크 사이즈가 줄지 않을경우
attach_file 1) 테이블스페이스 사이즈를 줄인 (shrink) 후 데이터파일 삭제
2) DB 리부팅 하면 공간 확보 됨(DBWR 이 데이터파일을 사용중이기 때문에 공간이 줄지 않음)
TABLESPACE 삭제
TABLESPACE에 포함된 모든 세그먼트 삭제
DROP TABLESPACE [테이블 스페이스명] INCLUDING CONTENTS;-- TABLESPACE의 모든 세그먼트를 삭제한다. -- 단, 데이타가 있는 TABLESPACE는 삭제할수 없다.
TABLESPACE에 포함된 테이블의 참조/제약 조건 삭제
DROP TABLESPACE [테이블 스페이스명] CASCADE CONSTRAINTS; -- 삭제된 TABLESPACE 내의 테이블의 기본키와 유일키를 참조하는 -- 다른 TABLESPACE의 테이블로부터 참조무결성 제약 조건을 삭제한다.
TABLESPACE의 데이터파일 삭제
DROP TABLESPACE [테이블 스페이스명] INCLUDING CONTENTS AND DATAFILES; -- 물리적파일까지 삭제한다.
TABLESPACE 수정이나 삭제시 ONLINE/OFFLINE 설정
ALTER TABLESPACE [테이블 스페이스명] ONLINE ALTER TABLESPACE [테이블 스페이스명] OFFLINE
템프 TABLESPACE
TEMPORARY TABLESPACE 정보
SELECT d.TABLESPACE_NAME , d.FILE_NAME , d.BYTES / 1024 / 1024 SIZE_MB , d.AUTOEXTENSIBLE , d.MAXBYTES / 1024 / 1024 MAXSIZE_MB , d.INCREMENT_BY * (v.BLOCK_SIZE / 1024) / 1024 INCREMENT_BY_MB FROM dba_temp_files d, v$tempfile v WHERE d.FILE_ID = v.FILE# ORDER BY d.TABLESPACE_NAME, d.FILE_NAME
템프테이블 사이즈 조정
ALTER DATABASE TEMPFILE 'D:\APP\ORADATA\ORCL\TEMP01.DBF' RESIZE 304M;
TEMPORARY TABLESPACE 생성
- . 신규생성만 가능.
: 사용중인 TEMP TABLESPACE 는 삭제가 안되므로 신규로 템프 TABLESPACE를 생성
CREATE TEMPORARY TABLESPACE [테이블 스페이스명] TEMPFILE '\경로\TEST_TEMP.DBF' SIZE 10M AUTOEXTEND ON NEXT 100M MAXSIZE 300G EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K -- LOCALY MANAGED TABLESPACE UNIFORM SIZE만 생성가능하다. -- (주의)AUTOALLOCATE, EXTENT MANAGEMENT DICIONARY OPTION을 사용하면 ORA-25319 ERROR 발생한다. -- RENAME 이 불가능하다.
TEMPORARY TABLESPACE를 DEFAULT TABLESPACE로 변경
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE [테이블 스페이스명];
TEMPORARY TABLESPACE 사이즈 증가
ALTER TABLESPACE TEMP AUTOEXTEND ON NEXT 100M MAXSIZE 500G;
TEMPORARY TABLESPACE 사이즈 추가
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE 32767M;
TEMPORARY TABLESPACE 삭제
DROP TABLESPACE TEMP2;
TEMPORARY TABLESPACE DATA FILE 삭제
ALTER TABLESPACE TEMP DROP TEMPFILE '+DATA/temp.368.1013282149';
- +DATA/temp.368.1013282149 은 ASM 사용시
TEMPORARY TABLESPACE 사용율 조회 쿼리
/* TEMP 사용 쿼리 확인 */ select a.username, a.sid, a.serial#, b.blocks, a.program, a.module, a.action, a.machine, a.status, a.event, d.sql_Text , b.blocks*8192/1024/1024 mb from v$session a, v$sort_usage b, v$process c, v$sqlarea d where a.saddr = b.session_addr and a.paddr = c.addr and a.sql_hash_value= d.hash_value and b.tablespace like 'TEMP%' --and a.username ='MIG_ADM' ORDER BY A.MACHINE, SQL_TEXT
템프테이블 TABLESPACE sort 사용 현황
SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total
TABLESPACE 오브젝트별 정보
TABLESPACE/파일 확인(딕셔너리)
SELECT * FROM DBA_DATA_FILES ; SELECT * FROM DBA_TABLESPACES ; SELECT * FROM DBA_SEGMENTS;
TABLESPACE별 정보
SELECT A.TABLESPACE_NAME AS "TABLESPACE" , A.INITIAL_EXTENT / 1024 AS "INIT(K)" , A.NEXT_EXTENT / 1024 AS "NEXT(K)" , A.MIN_EXTENTS AS "MIN" , A.MAX_EXTENTS AS "MAX" , A.PCT_INCREASE AS "PCT_INC(%)" , B.FILE_NAME AS "FILE_NAME" , B.BLOCKS * C.VALUE / 1024 / 1024 AS "SIZE(M)" , B.STATUS AS "STATUS" FROM DBA_TABLESPACES A , DBA_DATA_FILES B , V$PARAMETER C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND C.NAME = 'db_block_size' ORDER BY 1, 2;
TABLESPACE별 파일 목록
SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS "TABLESPACE" , SUBSTRB(FILE_NAME, 1, 50) AS "파일명" , TO_CHAR(BLOCKS, '999,999,990') AS "블럭수" , TO_CHAR(BYTES, '99,999,999') AS "크기" FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME, FILE_NAME;
TABLESPACE 사이즈 정보
TABLESPACE별 전체 오브젝트(테이블/인덱스/LOB) 사이즈
-- OBJECT별 테이블 사이즈 (UNDO,TEMP T/S 제외)
-- SELECT TABLESPACE_NAME -- , SUM(SIZE_MB) -- FROM ( SELECT A.TABLESPACE_NAME , A.SEGMENT_NAME , A.SEGMENT_TYPE , ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB" FROM DBA_SEGMENTS A , DBA_TABLES B WHERE A.SEGMENT_NAME = B.TABLE_NAME AND A.SEGMENT_TYPE IN ('TABLE','TABLE PARTITION') -- AND A.OWNER = '유저아이디' GROUP BY A.TABLESPACE_NAME , A.SEGMENT_NAME , A.SEGMENT_TYPE UNION ALL -- INDEX SIZE SELECT A.TABLESPACE_NAME , A.SEGMENT_NAME , A.SEGMENT_TYPE , ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB" FROM DBA_SEGMENTS A , DBA_INDEXES B WHERE A.SEGMENT_NAME = B.INDEX_NAME AND A.SEGMENT_TYPE IN ('INDEX','INDEX PARTITION') -- AND A.OWNER = '유저아이디' GROUP BY A.TABLESPACE_NAME , A.SEGMENT_NAME , A.SEGMENT_TYPE -- ORDER BY 2 DESC; UNION ALL -- LOB SELECT A.TABLESPACE_NAME , A.SEGMENT_NAME , A.SEGMENT_TYPE , ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB" FROM DBA_SEGMENTS A , DBA_LOBS B WHERE A.segment_name = B.segment_name AND A.SEGMENT_TYPE LIKE 'LOB%' GROUP BY A.TABLESPACE_NAME , A.SEGMENT_NAME , A.SEGMENT_TYPE ORDER BY SEGMENT_TYPE,TABLESPACE_NAME DESC -- ) -- GROUP BY TABLESPACE_NAME ;
TABLESPACE별 사이즈
SELECT Substr(df.tablespace_name,1,20) "Tablespace Name", Substr(df.file_name,1,80) "File Name", Round(df.bytes/1024/1024,0) "Size (M)", decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)", decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)", decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used" FROM DBA_DATA_FILES DF, (SELECT file_id, sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E, (SELECT sum(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f WHERE e.file_id (+) = df.file_id AND df.file_id = f.file_id (+) ORDER BY df.tablespace_name, df.file_name
테이블 TABLESPACE 사이즈
-- 테이블 사이즈
SELECT A.SEGMENT_NAME , A.SEGMENT_TYPE , ROUND(SUM(A.BYTES)/1024/1024/1024) "SIZE_GB" FROM DBA_SEGMENTS A , DBA_TABLES B WHERE A.SEGMENT_NAME = B.TABLE_NAME AND A.SEGMENT_TYPE IN ('TABLE','TABLE PARTITION') -- AND A.OWNER = '유저아이디' GROUP BY A.SEGMENT_NAME , A.SEGMENT_TYPE
인덱스 TABLESPACE 사이즈
-- INDEX SIZE
SELECT A.SEGMENT_NAME , A.SEGMENT_TYPE , ROUND(SUM(A.BYTES)/1024/1024/1024) "SIZE_GB" FROM DBA_SEGMENTS A , DBA_INDEXES B WHERE A.SEGMENT_NAME = B.INDEX_NAME AND A.SEGMENT_TYPE IN ('INDEX','INDEX PARTITION') -- AND A.OWNER = '유저아이디' GROUP BY A.SEGMENT_NAME , A.SEGMENT_TYPE ORDER BY 2 DESC;
LOB TABLESPACE 사이즈
-- LOB 사이즈
SELECT TABLE_NAME , sum(bytes) FROM (SELECT B.table_name AS table_name , A.bytes FROM DBA_SEGMENTS A , DBA_LOBS B WHERE A.segment_name = B.segment_name ) group by table_name;
데이터파일 별 테이블스페이스 사이즈 조회
SELECT A.TABLESPACE_NAME "테이블스페이스명", A.FILE_NAME "파일경로", (A.BYTES - B.FREE) "사용공간", B.FREE "여유 공간", A.BYTES "총크기", TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간" FROM ( SELECT FILE_ID, TABLESPACE_NAME, FILE_NAME, SUBSTR(FILE_NAME,1,200) FILE_NM, SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200) ) A, ( SELECT TABLESPACE_NAME, FILE_ID, SUM(NVL(BYTES,0)) FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME,FILE_ID ) B WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.FILE_ID = B.FILE_ID;
TABLESPACE별 사용하는 파일의 크기 합
SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS TABLESPACE , TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES , TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME UNION ALL SELECT '총계', TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES, TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS FROM DBA_DATA_FILES;
TABLESPACE별 디스크 사용량
SELECT A.TABLESPACE_NAME AS "TABLESPACE" , A.INIT AS "INIT(K)" , A.NEXT AS "NEXT(K)" , A.MIN AS "MIN" , A.MAX AS "MAX" , A.PCT_INC AS "PCT_INC(%)" , TO_CHAR(B.TOTAL, '999,999,999,990') AS "총량(바이트)" , TO_CHAR(C.FREE, '999,999,999,990') AS "남은량(바이트)" , TO_CHAR(B.BLOCKS, '9,999,990') AS "총블럭" , TO_CHAR(D.BLOCKS, '9,999,990') AS "사용블럭" , TO_CHAR(100 * NVL(D.BLOCKS, 0) / B.BLOCKS, '999.99') AS "사용율%" FROM (SELECT TABLESPACE_NAME , INITIAL_EXTENT / 1024 AS INIT , NEXT_EXTENT / 1024 AS NEXT , MIN_EXTENTS AS MIN , MAX_EXTENTS AS MAX , PCT_INCREASE AS PCT_INC FROM DBA_TABLESPACES) A , (SELECT TABLESPACE_NAME, SUM(BYTES) AS TOTAL, SUM(BLOCKS) AS BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B , (SELECT TABLESPACE_NAME, SUM(BYTES) AS FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) C , (SELECT TABLESPACE_NAME, SUM(BLOCKS) AS BLOCKS FROM DBA_EXTENTS GROUP BY TABLESPACE_NAME) D WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+) AND A.TABLESPACE_NAME = D.TABLESPACE_NAME(+) ORDER BY A.TABLESPACE_NAME;
공간의 90% 이상을 사용하고 있는 TABLESPACE
SELECT X.TABLESPACE_NAME , TOTAL_SIZE / 1024 / 1024 TOTAL_SIZE , USED_SIZE / 1024 / 1024 USED_SIZE , (ROUND(USED_SIZE / TOTAL_SIZE, 2)) * 100 USED_RATIO FROM (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL_SIZE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) X , (SELECT TABLESPACE_NAME, SUM(BYTES) USED_SIZE FROM DBA_EXTENTS GROUP BY TABLESPACE_NAME) Y WHERE X.TABLESPACE_NAME = Y.TABLESPACE_NAME(+) AND Y.USED_SIZE > .9 * X.TOTAL_SIZE;
TABLESPACE에 포함된 테이블 명 보기
SELECT TABLESPACE_NAME , TABLE_NAME FROM DBA_TABLES WHERE TABLESPACE_NAME = UPPER('&TABLESPACE명') ORDER BY TABLESPACE_NAME , TABLE_NAME;
오브젝트별 TABLESPACE 및 데이터파일
SELECT DISTINCT E.SEGMENT_NAME, E.TABLESPACE_NAME, F.FILE_NAME FROM DBA_EXTENTS E , DBA_DATA_FILES F WHERE E.FILE_ID = F.FILE_ID AND E.SEGMENT_TYPE = 'TABLE' AND E.TABLESPACE_NAME NOT IN ('SYSTEM', 'TOOLS');
TABLESPACE별 Table, Index 개수
SELECT OWNER , TABLESPACE_NAME , SUM(DECODE(SEGMENT_TYPE, 'TABLE', 1, 0)) TAB , SUM(DECODE(SEGMENT_TYPE, 'INDEX', 1, 0)) IDX , SUM(DECODE(SEGMENT_TYPE, 'LOBINDEX', 1, 0)) LOB_IDX , SUM(DECODE(SEGMENT_TYPE, 'LOBSEGMENT', 1, 0)) LOB_SEG FROM DBA_SEGMENTS WHERE SEGMENT_TYPE IN ('TABLE', 'INDEX', 'LOBINDEX','LOBSEGMENT') GROUP BY OWNER, TABLESPACE_NAME;
파일위치별 TABLESPACE 아는 방법
SELECT SUBSTRB(A.FILE_NAME, 1, 40) AS FILE_NAME , A.FILE_ID , B.FREE_BYTES / 1024 AS FREE_BYTES , B.MAX_BYTES / 1024 AS MAX_BYTES FROM DBA_DATA_FILES A , (SELECT FILE_ID, SUM(BYTES) AS FREE_BYTES, MAX(BYTES) AS MAX_BYTES FROM DBA_FREE_SPACE GROUP BY FILE_ID) B WHERE A.FILE_ID = B.FILE_ID AND A.TABLESPACE_NAME = UPPER('&TABLESPACE명') ORDER BY A.FILE_NAME;
현재 Extension 횟수가 MaxExtents의 80% 이상인 경우
SELECT TABLESPACE_NAME , OWNER , SEGMENT_NAME , SEGMENT_TYPE , EXTENTS , MAX_EXTENTS FROM SYS.DBA_SEGMENTS S WHERE EXTENTS / MAX_EXTENTS > .8 AND MAX_EXTENTS > 0 ORDER BY TABLESPACE_NAME, OWNER, SEGMENT_NAME;
테이블의 익스텐트 정보 조회
SELECT B.SEGMENT_NAME , B.MAX_EXTENTS , MAX(C.EXTENT_ID) AS EXTENT_ID , B.MAX_EXTENTS - MAX(C.EXTENT_ID) AS DIFF FROM USER_TABLESPACES A , USER_SEGMENTS B , USER_EXTENTS C WHERE A.EXTENT_MANAGEMENT = 'DICTIONARY' AND B.TABLESPACE_NAME = A.TABLESPACE_NAME AND C.SEGMENT_NAME = B.SEGMENT_NAME GROUP BY B.SEGMENT_NAME, B.MAX_EXTENTS HAVING B.MAX_EXTENTS - MAX(C.EXTENT_ID) <= 50 ORDER BY B.MAX_EXTENTS - MAX(C.EXTENT_ID);
UNDO TABLESPACE
- UNDO Segment를 저장하고 있는 TABLESPACE , 관리자가 생성/관리 가능
- Instance당 여러 개가 동시에 존재할 수 있지만 사용은 한번에 1개만
현재 UNDO 상태 확인
SELECT A.TABLESPACE_NAME,A.STATUS, B.TOTAL_MB, A.USE_MB, ROUND(RATIO_TO_REPORT(A.USE_MB) OVER(PARTITION BY A.TABLESPACE_NAME) * 100)||'%' AS PCT FROM (SELECT TABLESPACE_NAME, STATUS, ROUND(SUM(BYTES/1024/1024)) USE_MB FROM DBA_UNDO_EXTENTS GROUP BY TABLESPACE_NAME,STATUS )A, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024) TOTAL_MB FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE 'UNDO%' GROUP BY TABLESPACE_NAME )B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME ORDER BY 1,2
UNDO 테이블 스페이스 삭제
DROP TABLESPACE UNDO_T1 INCLUDING CONTENTS AND DATAFILES;
ROLLBACK SEGMENT의 사용상황 보기
--: EXTENTS = 현재 할당된 EXTENT의 수 --: EXTENDS = 마지막 트랜잭션에 의해 할당된 EXTENT의 수 SELECT SUBSTRB(A.SEGMENT_NAME, 1, 10) AS SEGMENT_NAME , SUBSTRB(A.TABLESPACE_NAME, 1, 10) AS TABLESPACE_NAME , TO_CHAR(A.SEGMENT_ID, '99,999') AS SEG_ID , TO_CHAR(A.MAX_EXTENTS, '999,999') AS MAX_EXT , TO_CHAR(B.EXTENTS, '999,999') AS EXTENTS , TO_CHAR(B.EXTENDS, '999,999') AS EXTENDS , TO_CHAR((A.INITIAL_EXTENT + (B.EXTENTS - 1) * A.NEXT_EXTENT) / 1000000, '9,999.999') AS "ALLOC(MB)" , TO_CHAR(XACTS, '9,999') AS XACTS FROM DBA_ROLLBACK_SEGS A , V$ROLLSTAT B WHERE A.SEGMENT_ID = B.USN(+) ORDER BY 1;
TABLESPACE 장애 처리
TABLESPACE 용량 부족으로 에러 발생시
- 방법 1) Data file을 크게 늘려줌(자동 증가/수동증가)
- 방법 2) Data file을 하나 더 추가
Tablespace Offline
- 사용자가 더 이상 해당 Tablespace에 접근하지 못한다는 의미
- 데이터파일의 위치를 이동하거나 특정 Tablespace가 장애가 나서 복구해야 할 때 사용
TABLESPACE 를 Offline하는 방법 3가지
1) Normal Mode
SQL> alter TABLESPACE TS_TEST offline ;
2) Temporary Mode
Normal이 수행되지 못할 때(Tablespace의 Data file 이상) 사용하는 방법
3) Immediate Mode
- 반드시 Archive Log Mode일 경우에만 사용. - Data file에 장애가 나서 데이터를 내려쓰지 못하는 상황에서 TABLESPACE 를 offline해야 할 경우 사용
TABLESPACE 이동하기
1) Offline되는 Tablespace의 Data file 이동하기
- 해당 Tablespace Offline 하기
ALTER TABLESPACE TS_TEST OFFLINE;
- Data file을 대상 위치로 복사
- Control file 내의 해당 Data file 위치 변경
- 해당 Tablespace Online
ALTER TABLESPACE TS_TEST ONLINE;
2) Offline 안 되는 TABLESPACE 의 Data file 이동하기
- DB 종료
- Mount 상태로 시작
- Data file 복사
- Control file의 내용 변경
- DB Open
LOB포함된 TABLESPACE 용량 축소/REORG
해당 테이블스페이스를 사용중인 테이블/인덱스/LOB 조회
SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME ='TS_TEST_D01';
테이블에서 사용중인 테이블스페이스 신규테이블스페이스로 이동
SELECT OWNER , TABLE_NAME , TABLESPACE_NAME , 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE TABLESPACE TS_TEST_D11;' -- 임시로 생성된 T/S로 이동 , 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE TABLESPACE '||TABLESPACE_NAME||';' MV_TS -- 다시 원래 테이블스페이스로 이동 FROM DBA_TABLES WHERE TABLESPACE_NAME ='TS_TEST_D01'; -- TS_TEST_D01을 사용하는 테이블
인덱스 T/S 이동(INDEX REBUILD)
SELECT OWNER , TABLE_NAME , INDEX_NAME , TABLESPACE_NAME , 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD TABLESPACE TS_TEST_I11;' RBD_TS FROM DBA_INDEXES WHERE TABLESPACE_NAME ='TS_TEST_I01'; -- TS_TEST_I01을 사용하는 인덱스
LOB T/S 이동
-- LOB 테이블 이동하기
select owner,table_name , 'alter table '||owner||'.'||table_name||' move lob('||col||') store as (tablespace TS_TEST_D01);' mv_lob_ts from ( select a.owner,a.table_name , listagg(a.column_name,',') within group (order by 1) col from dba_lobs a where table_name in ( SELECT DISTINCT TABLE_NAME FROM DBA_TAB_COLUMNS WHERE OWNER = 'TEST' AND DATA_TYPE LIKE '%LOB' ) group by a.owner,a.table_name ) --order by 1,2 ;
원래 T/S명으로 변경
ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE]
DATAPUMP 데이터펌프
- 권한 생성
- 디렉토리 권한 부여
- 디비링크 생성 후 디비링크를 통한 데이터 펌프 실행
IMPDP (Import Datapump) 사용법
IMPORT 사용 예시
DBLINK로 IMPORT PUMP 처리 방법
1.DB 링크 생성
2.IMPDP 파라미터 파일로 실행
IMPORT 파라미터 샘플
테이블만 IMPORT(테이블 존재시 TRUNCATE)
Package, Function, Procedure 만 import 하기
IMPORT DP 파라미터
통계정보를 제외
스키마 선택
IMPORT DP 상세 파라미터
IMPORT API
IMPORT 스키마/테이블 API
IMPORT 개별 TABLE (복구시)
EXPDP (Export Datapump) 사용법
EXPORT 파라미터 작성
병렬처리 parallel 파라미터
tables 파라미터
QUERY 파라미터
sqlfile 파라미터
Package, Function, Procedure 만 EXPORT 하기
EXPORT DP 파라미터
2개 디렉토리 export 하기(1개의 디스크로 충분하지 않을때)
ASM환경에서 EXPDP 실행 방법
데이터펌프 작업 관리 및 모니터링
ExportDP JOB 확인
터미널에서 JOB Attach 모드
expdp 아이디/비밀번호@db명 attach=JOB이름 expdp>status
SQL로 확인
- dba_datapump_jobs
SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;
- sys.v_$sqlarea
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name , rows_processed , round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes , trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min from sys.v_$sqlarea where sql_text like 'INSERT %INTO "%' and command_type = 2 and open_versions > 0;
Datapump 작업 진행사항
SELECT X.JOB_NAME , B.STATE , B.JOB_MODE , B.DEGREE , X.OWNER_NAME , Z.SQL_TEXT , P.MESSAGE , P.TOTALWORK , P.SOFAR , ROUND ( (P.SOFAR / P.TOTALWORK) * 100, 2) DONE , P.TIME_REMAINING FROM DBA_DATAPUMP_JOBS B LEFT JOIN DBA_DATAPUMP_SESSIONS X ON (X.JOB_NAME = B.JOB_NAME) LEFT JOIN V$SESSION Y ON (Y.SADDR = X.SADDR) LEFT JOIN V$SQL Z ON (Y.SQL_ID = Z.SQL_ID) LEFT JOIN V$SESSION_LONGOPS P ON (P.SQL_ID = Y.SQL_ID) -- WHERE Y.MODULE = 'Data Pump Worker' AND P.TIME_REMAINING > 0 ;
데이터펌프(DATAPUMP) JOB 중지
SELECT * FROM DBA_DATAPUMP_JOBS;
IMPDP ATTACH=JOB 접속 후
$>IMPDP SCOTT/TIGER@DB ATTACH=JOB명 KILL_JOB STOP_JOB #STOP_JOB = IMMEDIATE 즉시 종료
API 이용 정지
- 주의) 즉시 정지 하지 않음
DECLARE h1 NUMBER; BEGIN h1:=DBMS_DATAPUMP.ATTACH(JOB_NAME => '"MIG_CHANGED_TABLE"', JOB_OWNER => 'RTIS_MIG'); DBMS_DATAPUMP.STOP_JOB(h1,1,0); END;
EXPORT API
스키마 EXPORT
DECLARE hndl NUMBER; TAG_NAME VARCHAR2(30) := 'EXP_MIG_TEST01'; BEGIN hndl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT' , job_mode => 'SCHEMA' -- FULL, SCHEMA, TABLE, TABLESPACE, TRANSPORTABLE , job_name=>'JOB_'||TAG_NAME --, remote_link => 'DBLINK_NAME', version => 'LATEST' ); DBMS_DATAPUMP.ADD_FILE( handle => hndl, filename => TAG_NAME||'.dmp', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE, reusefile=>1); DBMS_DATAPUMP.ADD_FILE( handle => hndl, filename => TAG_NAME||'.log', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); -- DBMS_DATAPUMP.SET_PARAMETER(handle=> hndl, name=> 'INCLUDE_METADATA', value=> 1); -- META 포함여부 DBMS_DATAPUMP.DATA_FILTER(handle=> hndl, name=> 'INCLUDE_ROWS', value=> 0); -- DATA 포함 여부 0, DBMS_DATAPUMP.METADATA_FILTER(handle=> hndl, name=> 'SCHEMA_EXPR', value=>'IN (''FED40'',''TTT'')'); -- DBMS_DATAPUMP.METADATA_FILTER(handle=> hndl, name=> 'NAME_EXPR', value=>'IN (''FED40'', ''TEST'')'); -- DBMS_DATAPUMP.METADATA_FILTER(handle=> hndl, name=> 'NAME_LIST', value=>'''EMP'',''DEPT'''); DBMS_DATAPUMP.START_JOB(hndl); END;
테이블 EXPORT API
-- 1.테이블 DUMP EXPORT 스크립트
DECLARE hdnl NUMBER; BEGIN hdnl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT', job_mode => 'SCHEMA', job_name=>'JOB_EXP_TB_RC_JEJU_14'); DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'INIT_BK_TB_RC_JEJU_14_20190910.EXP', directory => 'DATAPUMP2', filetype => dbms_datapump.ku$_file_type_dump_file ,reusefile=>1); DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'INIT_BK_TB_RC_JEJU_14_20190910.LOG', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file ,reusefile=>1); -- 스키마 정보 DBMS_DATAPUMP.METADATA_FILTER(handle => hdnl, name => 'SCHEMA_EXPR', value => ' IN (''유저명'')' ); -- 테이블 정보 DBMS_DATAPUMP.METADATA_FILTER(handle => hdnl ,name => 'NAME_EXPR' ,value => ' IN (''테이블명'')' ,object_type => 'TABLE' ); DBMS_DATAPUMP.START_JOB(hdnl); END;
데이터펌프 로그파일 읽기 API
DECLARE V1 VARCHAR2(200); --32767 F1 UTL_FILE.FILE_TYPE; BEGIN F1 := UTL_FILE.FOPEN('DATA_PUMP_DIR','INIT_BK_TB_RC_JEJU_12_20190910.LOG','R'); Loop BEGIN UTL_FILE.GET_LINE(F1,V1); dbms_output.put_line(V1); EXCEPTION WHEN No_Data_Found THEN EXIT; END; end loop; IF UTL_FILE.IS_OPEN(F1) THEN dbms_output.put_line('File is Open'); end if; UTL_FILE.FCLOSE(F1); END;
파일 복사(ASM등)
attach_file OS상의 파일복사 명령를 DB에서 직접 수행이 가능.
- DBMS_FILE_TRANSFER.COPY_FILE 패키지/함수
- 소스 디렉토리에서 파일을 읽고 대상 디렉토리에 복사
- ASM 과 로컬 디스크간 복사
- 소스 및 대상 디렉토리는 로컬 파일 시스템에 있거나 ASM (Automatic Storage Management) 디스크 그룹에 있거나
로컬 파일 시스템과 ASM간에 어느 방향 으로든 복사 할 수 있음.
BEGIN DBMS_FILE_TRANSFER.COPY_FILE( source_directory_object => 'SOURCEDIR' -- 소스 디렉토리 , source_file_name => 't_xdbtmp.f' -- 소스 파일 , destination_directory_object => 'DGROUP' -- 타켓 디렉토리 , destination_file_name =>'t_xdbtmp.f' -- 타겟 파일 ); END; /
DB LINK를 이용한 파일 복사(밀어 넣기)
attach_file DBMS_FILE_TRANSFER.PUT_FILE 패키지/함수
- 소스 에서 프로시져 실행 해야 함.
- 소스 => 타겟으로 복사
- 로컬 파일 또는 ASM을 읽고 원격 데이터베이스에 접속하여 원격 파일 시스템에 파일 사본을 작성합니다.
- DB링크 간의 DUMP파일 복사가 가능(.log파일은 복사 안됨,12c)
BEGIN DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => 'DATA_PUMP_DIR', -- 패키지 실행하는 DB source_file_name => 'sample.dmp', -- 패키지 실행하는 DIRECTORY의 덤프파일명 destination_directory_object => 'DATA_PUMP_DIR2', -- DB링크 원격지 디렉토리 destination_file_name => 'sample_copied.dmp',-- DB링크 원격지에 저장될 파일명 destination_database => '디비링크명' ); END; /
파일 복사(가져오기)
attach_file DBMS_FILE_TRANSFER.GET_FILE
- 원격 데이터베이스에 접속하여 원격 파일을 로컬 파일 시스템 또는 ASM에 파일 복사.
- 절차가 성공적으로 완료 될 때까지 대상 파일이 닫히지 않음.
BEGIN DBMS_FILE_TRANSFER.GET_FILE( source_directory_object => 'DATA_PUMP_DIR', source_file_name => 'sample.dmp', source_database => '디비링크명', destination_directory_object => 'DATA_PUMP_DIR2', destination_file_name => 'sample_copied.dmp' ); END; /
DATAPUMP API 상세 정보
https://www.morganslibrary.org/reference/pkgs/dbms_datapump.html
DB LINK를 이용한 파일 복사
BEGIN DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => 'DATA_PUMP_DIR', source_file_name => 'sample.dmp', destination_directory_object => 'DATA_PUMP_DIR', destination_file_name => 'sample_copied.dmp', destination_database => 'to_rds' ); END; /
PL/SQL
기본 프로시져
DECLARE --변수,상수 선언 BEGIN --실행 가능 SQL문,PL/SQL문 EXCEPTION --에러처리 END; /* PL/SQL UPDATE예제(UPDATE,DELETE는 다중행처리 가능) */ Declare v_sale number := 2000; begin update test set a = v_sale; delete from test where a = v_sale; commit; end; /* Procedure에서 Procedure를 호출하는 방법 */ A프로시져에서 "B프로시져명(변수, 변수2);" PROCEDURE 리턴 여러개 FUNCTION 리턴 한개 SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='PROCEDURE'; /* 프로시저나 함수 조회 */ SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='FUNCTION'; /* PROCEDURE & FUNCTION 삭제하기 */ DROP FUNCTION lee2; DROP PROCEDURE lee2;
프로시져 실행 옵션
- DBA권한이 있는데도 ORA-00942 에러 발생
AUTHID DEFINER 실행 시 컴파일 할 때의 유저 사용(DEFAULT) AUTHID CURRENT_USER 실행 시 현재 접속하고 있는 유저 사용.
- PL/SQL내에서 EXECUTE IMEDIATE 실행 시 권한없음 에러가 발생하면 AUTHID CURRENT_USER 추가
- EXECUTE IMEDIATE DDL명령 실행시 DDL명령에 선언된 OWNER(예를들어 SCOTT.XXX) 가 프로시져를 실행한 유저(예를 들어 DBADM)가 아닐때 ORA-00942에러 발생
- EXCUTE IMMEDIATE 'SQL구문'; 실행시 'SQL구문' 내부에 세미콜론은 ';' 없어야 수행됨. (중요-삽질의 시작..)
커서 활용 샘플
CREATE OR REPLACE PROCEDURE RTIS_DBA.SP_OBJ_INVALID_TO_VALID /* * */ ( IN_DATE IN VARCHAR2 DEFAULT TO_CHAR(SYSDATE,'YYYYMMDD') ) AUTHID CURRENT_USER IS CURSOR INVALID_OBJECT IS SELECT A.GRANTEE, A.OWNER, A.TABLE_NAME, A.GRANTOR, A.PRIVILEGE,A.OBJECT_TYPE FROM TB_MGR_GRANT A ; -- V_SQL VARCHAR2(200); V_G_SQL VARCHAR2(200); -- V_L_SQL VARCHAR2(2000); V_GRANTEE VARCHAR2(100); V_OWNER VARCHAR2(100); V_TABLE_NAME VARCHAR2(100); V_GRANTOR VARCHAR2(100); V_PRIVILEGE VARCHAR2(100); V_OBJECT_TYPE VARCHAR2(100); V_GRANT_REVOKE_GBN VARCHAR2(100); V_MSG long; BEGIN DBMS_OUTPUT.ENABLE; FOR V_ROW IN INVALID_OBJECT LOOP -- 블록 에러 발생시에도 계속 실행토록 BEGIN V_GRANTEE:= V_ROW.GRANTEE; V_OWNER:= V_ROW.OWNER; V_TABLE_NAME:= V_ROW.TABLE_NAME; V_GRANTOR:= V_ROW.GRANTOR; V_PRIVILEGE:= V_ROW.PRIVILEGE; V_OBJECT_TYPE:= V_ROW.OBJECT_TYPE; V_GRANT_REVOKE_GBN := 'GRANT TO USER'; -- 권한 추가 V_G_SQL := 'GRANT '||V_ROW.PRIVILEGE||' ON '||V_ROW.OWNER||'.'||V_ROW.TABLE_NAME||' TO '||V_ROW.GRANTEE; EXECUTE IMMEDIATE V_G_SQL; -- LOG 기록 INSERT INTO TB_MGR_GRANT_LOG (GRANTEE , OWNER , TABLE_NAME , GRANTOR, PRIVILEGE,OBJECT_TYPE,GRANT_REVOKE_GBN) VALUES (V_ROW.GRANTEE,V_ROW.OWNER,V_ROW.TABLE_NAME,V_ROW.GRANTOR,V_ROW.PRIVILEGE,V_ROW.OBJECT_TYPE,V_GRANT_REVOKE_GBN); COMMIT; EXCEPTION WHEN OTHERS THEN V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); INSERT INTO TB_MGR_GRANT_LOG (GRANTEE , OWNER , TABLE_NAME , GRANTOR , PRIVILEGE ,OBJECT_TYPE ,GRANT_REVOKE_GBN,GRANT_REVOKE_SQL) VALUES (V_GRANTEE,V_OWNER,V_TABLE_NAME,V_GRANTOR,V_PRIVILEGE,V_OBJECT_TYPE,V_GRANT_REVOKE_GBN,V_MSG); COMMIT; END; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM); END; /
명령어
산술 명령어
error
- 더하기: +
- 빼기: -
- 곱하기: *
- 나누기: /
- Power (PL/SQL only): **
예시
UPDATE employee SET salary = salary * 1.05 WHERE customer_id = 5;
SELECT wage - tax FROM employee;
비교 연산자
* 보다 크다 : > * 크거나 같다 : >= * 보다 작다: < * 작거나 같다: <= * 같다: = * 같지 않다: != ^= <> ¬= (depends on platform)
예시
SELECT name, salary, email FROM employees WHERE salary > 40000; SELECT name FROM customers WHERE customer_id < 6;
문자 명령어
- 문자열 합치기(Concatenate): ||
create or replace procedure addtest( a in varchar2(100), b in varchar2(100), c out varchar2(200) ) IS begin C:=concat(a,'-',b);
날짜 명령어
- 더하기: +
- 빼기: -
타입(Types)
PL/SQL 기본 타입
스칼라타입(Scalar type) (패키지 안에 선언STANDARD): NUMBER, CHAR, VARCHAR2, BOOLEAN, BINARY_INTEGER, LONG\LONG RAW, DATE, TIMESTAM)
콤포지트(결합) 타입(Composite types) (사용자 선언 타입 ):
TABLE, RECORD, NESTED TABLE and VARRAY
LOB 타입(LOB datatypes) : 구조화되지 않은 대량의 데이터를 저장하는 데 사용
%TYPE - 고정형 변수 선언(anchored type variable declaration)
고정형 변수 선언 Syntax
<var_name> <obj>%type [not null][:= <init-val>];
For 예시
name Books.title%type; / * name은 테이블 Books의 'title' 컬럼과 동일한 유형으로 정의됩니다. * / commission number(5,2) := 12.5; x commission%type; / * x는 변수 'commission'과 동일한 유형으로 정의됩니다. * /
Note:
- 고정 변수를 사용하면 유형이 변경 될 때 고정 변수 유형을 유형과 자동으로 동기화 할 수 있습니다.
- 고정 된 유형은 컴파일시 평가되므로 앵커 된 변수에서 유형의 변경 사항을 반영하도록 프로그램을 다시 컴파일하십시오.
콜렉션(Collections)
컬렉션은 정렬 된 요소그룹이며 모두 같은 유형입니다.
목록, 배열 및 기타 친숙한 데이터 유형을 포함하는 일반적인 개념입니다.
각 요소에는 컬렉션에서 해당 위치를 결정하는 고유한 첨자가 있습니다.
--Define a PL/SQL record type representing a book: TYPE book_rec IS RECORD (title book.title%TYPE, author book.author_last_name%TYPE, year_published book.published_date%TYPE); --define a PL/SQL table containing entries of type book_rec: Type book_rec_tab IS TABLE OF book_rec INDEX BY BINARY_INTEGER; my_book_rec book_rec%TYPE; my_book_rec_tab book_rec_tab%TYPE; ... my_book_rec := my_book_rec_tab(5); find_authors_books(my_book_rec.author); ...
컬렉션을 사용해야하는 많은 이유가 있습니다.
- 새로운 최적화 컴파일러, 더 나은 통합 네이티브 컴파일 및 숫자 처리 응용 프로그램에 도움이되는 새로운 데이터 유형을 포함하여 투명한 성능 향상 덕분에 훨씬 빠른 실행 속도.
- FORALL 문은 더욱 유연하고 유용합니다.
예를 들어, FORALL은 이제 비 연속 인덱스를 지원합니다.
- 정규 표현식은 PL/SQL에서 세 가지 새로운 함수 (REGEXP_INSTR, REGEXP_REPLACE 및 REGEXP_SUBSTR)와 REGEXP_LIKE 연산자를 사용하여 비교할 수 있습니다
<ref>이 문제에 대한 자세한 내용은 Jonathan Gennick의 "첫 번째 표현식"</ ref> .
- 컬렉션, 동등성을위한 컬렉션 비교 및 중첩 테이블에 대한 집합 연산 지원 등을 포함하도록 개선되었습니다.
묵시적 커서 와 커서 속성
INSERT, UPDATE, DELETE문을 수행했을 때 몇 건의 데이터가 영향을 받았는지, 즉 몇 건의 데이터가 새로 입력되고 갱신되고 삭제되었는지 알 수 있다.
예를 들어, 사원 테이블에서 80번 부서에 속한 사원은 총 34명인데, 이를 UPDATE 하는 경우
DECLARE vn_department_id employees.department_id%TYPE := 80; BEGIN -- 80번 부서의 사원이름을 자신의 이름으로 갱신 UPDATE employees SET emp_name = emp_name WHERE department_id = vn_department_id; -- 몇 건의 데이터가 갱신됐는지 출력 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); COMMIT; END; -- 결과 : 34
정확히 34건이 갱신됐음을 알 수 있다.
SQL%ROWCOUNT 커서 속성을 사용해서 해당 SQL문으로 인해 실제 처리된 결과 로우 수를 참조한 것. SQL%ROWCOUNT에서 %ROWCOUNT가 커서 속성이며 앞에 붙은 SQL은 커서 이름. 묵시적 커서의 정보를 참조할 때는 SQL로 시작되는 속성명을 사용해 참조할 수 있어 묵시적 커서를 SQL 커서라고도 한다.
- 묵시적 커서 정보 참조용 커서 속성
SQL%FOUND 결과 집합의 패치 로우 수가 1개 이상이면 TRUE, 아니면 FALSE를 반환 SQL%NOTFOUND 결과 집합의 패치 로우 수가 0이면 TRUE, 아니면 FALSE를 반환 SQL%ROWCOUNT 영향 받은 결과 집합의 로우 수 반환, 없으면 0을 반환 SQL%ISOPEN 묵시적 커서는 항상 FALSE를 반환(이 속성으로 참조할 때는 이미 해당 묵시적 커서는 닫힌 상태 이후이기 때문)
참고 레퍼런스
함수/프로시져/익명블럭..
Functions
함수는 호출자에게 값을 반환해야합니다.
Syntax
CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ] RETURN [return_datatype] IS [declaration_section] BEGIN executable_section return [return_value] [EXCEPTION exception_section] END [function_name];
예시:
CREATE OR REPLACE FUNCTION to_date_check_null(dateString IN VARCHAR2, dateFormat IN VARCHAR2) RETURN DATE IS BEGIN IF dateString IS NULL THEN return NULL; ELSE return to_date(dateString, dateFormat); END IF; END;
Procedures
프로시저는 호출자에게 값을 리턴 안된다는 점에서 함수와 다릅니다.
Syntax:
CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ] IS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [procedure_name];
프로시저 또는 함수를 작성할 때 파라미터를 정의 할 수 있습니다.
선언 할 수있는 세 가지 유형의 매개 변수가 있습니다.
파라미터는 프로시저 나 함수에 의해 참조 될 수 있습니다.
프로시저 나 함수는 파라미터 값을 겹쳐 쓸 수 없습니다.
DEFAULT 값 선언 가능
CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [IN|OUT|IN OUT] [DEFAULT ''value''] [,parameter]) ]
The following is a simple 예시 of a procedure:
/* purpose: 목적 : courseId로 지정된 학습과정의 학생들을 보여줍니다. */ CREATE OR REPLACE Procedure GetNumberOfStudents ( courseId IN number, numberOfStudents OUT number ) IS /* 학생 수를 계산하는 더 좋은 방법이 있지만 이것은 커서를 실제로 보여줄 수있는 좋은 기회입니다 */ cursor student_cur is select studentId, studentName from course where course.courseId = courseId; student_rec student_cur%ROWTYPE; BEGIN OPEN student_cur; LOOP FETCH student_cur INTO student_rec; EXIT WHEN student_cur%NOTFOUND; numberOfStudents := numberOfStudents + 1; END LOOP; CLOSE student_cur; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END GetNumberOfStudents;
anonymous block
DECLARE x NUMBER(4) := 0; BEGIN x := 1000; BEGIN x := x + 100; EXCEPTION WHEN OTHERS THEN x := x + 2; END; x := x + 10; dbms_output.put_line(x); EXCEPTION WHEN OTHERS THEN x := x + 3; END;
프로시져에 파라미터 지정 방법
스토어드 프로시저에서 파라미터를 전달하기위한 세 가지 기본 구문인
- 위치 표기법,
- 명명 표기법,
- 혼합 표기법
이 있습니다.
다음 예는 매개 변수 전달에 대한 각 기본 구문에 대한 프로 시저를 호출합니다.
CREATE OR REPLACE PROCEDURE create_customer( p_name IN varchar2, p_id IN number, p_address IN varchar2, p_phone IN varchar2 ) IS BEGIN INSERT INTO customer ( name, id, address, phone ) VALUES ( p_name, p_id, p_address, p_phone ); END create_customer;
위치 표기법
프로시저에서 선언 된 것과 동일한 순서로 동일한 매개 변수 지정.
간결하지만 매개 변수 (특히 리터럴)를 잘못된 순서로 지정하면 버그를 감지하기 어려울 수 있습니다.
프로 시저의 매개 변수 목록이 변경되면 코드를 변경해야합니다.
begin create_customer('James Whitfield' , 33 , '301 Anystreet' , '251-222-3154'); end;
명명 표기법
각 파라미터의 이름과 해당 값을 지정하십시오.
화살표 (=>)는 연결 연산자로 사용됩니다. 파라미터의 순서는 중요하지 않습니다. 이 표기법은 좀 더 장황하지만 코드를보다 쉽게 읽고 관리 할 수 있습니다.
프로 시저의 파라미터 목록이 변경되면 매개 변수가 재정렬되거나 새로운 선택적 파라미터가 추가되는 경우 코드 변경을 피할 수 있습니다.
명명 된 표기법은 다른 사람의 API를 호출하거나 다른 사람이 사용할 API를 정의하는 코드에 사용하는 것이 좋습니다.
begin create_customer(p_address => '301 Anystreet' , p_id => 33 , p_name => 'James Whitfield' , p_phone => '251-222-3154'); end;
혼합 표기법
위치 표기법으로 첫 번째 매개 변수를 지정한 다음 마지막 매개 변수에 대해 명명 표기법으로 전환하십시오.
이 표기법을 사용하여 일부 필수 매개 변수가있는 프로 시저를 호출하고 일부 선택적 매개 변수를 호출 할 수 있습니다.
begin create_customer(v_name -- 위치 지정법 , v_id -- 상동 , p_address=> '301 Anystreet' -- 명명 표기법 , p_phone => '251-222-3154' -- 상동 ); end;
Table functions
CREATE TYPE object_row_type as OBJECT ( object_type VARCHAR(18), object_name VARCHAR(30) ); CREATE TYPE object_table_type as TABLE OF object_row_type; CREATE OR REPLACE FUNCTION get_all_objects RETURN object_table_type PIPELINED AS BEGIN FOR cur IN (SELECT * FROM all_objects) LOOP PIPE ROW(object_row_type(cur.object_type, cur.object_name)); END LOOP; RETURN; END; SELECT * FROM TABLE(get_all_objects);
흐름제어 관리
Conditional Operators
- and: AND
- or: OR
- not: NOT
예시
IF salary > 40000 AND salary <= 70000 THEN() ELSE IF salary>70000 AND salary<=100000 THEN() ELSE()
If/then/else
IF [condition] THEN [statements] ELSEIF [condition] THEN [statements} ELSEIF [condition] THEN [statements} ELSEIF [condition] THEN [statements} ELSEIF [condition] THEN [statements} ELSEIF [condition] THEN [statements} ELSEIF [condition] THEN [statements} ELSEIF [condition] THEN [statements} ELSE [statements} END IF;
Arrays
Associative arrays
인-메모리 테이블로 유용한 강력한 Type의 배열
예시
- 매우 간단한 예, 인덱스는 배열에 액세스하는 KEY이므로 배열의 모든 행에서 데이터를 사용하지 않는 한 전체 테이블을 반복 할 필요가 없습니다.
- 인덱스는 숫자 값일 수도 있습니다.
DECLARE -- Associative array indexed by string: -- Associative array type TYPE population IS TABLE OF NUMBER INDEX BY VARCHAR2(64); -- Associative array variable city_population population; i VARCHAR2(64); BEGIN -- Add new elements to associative array: city_population('Smallville') := 2000; city_population('Midland') := 750000; city_population('Megalopolis') := 1000000; -- Change value associated with key 'Smallville': city_population('Smallville') := 2001; -- Print associative array by looping through it: i := city_population.FIRST; WHILE i IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE ('Population of ' || i || ' is ' || TO_CHAR(city_population(i))); i := city_population.NEXT(i); END LOOP; -- Print selected value from a associative array: DBMS_OUTPUT.PUT_LINE('Selected value'); DBMS_OUTPUT.PUT_LINE('Population of'); END; / -- Printed results: Population of Megalopolis is 1000000 Population of Midland is 750000 Population of Smallville is 2001
- 좀더 복잡한 예시, using a record
DECLARE -- Record type TYPE apollo_rec IS RECORD ( commander VARCHAR2(100), launch DATE ); -- Associative array type TYPE apollo_type_arr IS TABLE OF apollo_rec INDEX BY VARCHAR2(100); -- Associative array variable apollo_arr apollo_type_arr; BEGIN apollo_arr('Apollo 11').commander := 'Neil Armstrong'; apollo_arr('Apollo 11').launch := TO_DATE('July 16, 1969','Month dd, yyyy'); apollo_arr('Apollo 12').commander := 'Pete Conrad'; apollo_arr('Apollo 12').launch := TO_DATE('November 14, 1969','Month dd, yyyy'); apollo_arr('Apollo 13').commander := 'James Lovell'; apollo_arr('Apollo 13').launch := TO_DATE('April 11, 1970','Month dd, yyyy'); apollo_arr('Apollo 14').commander := 'Alan Shepard'; apollo_arr('Apollo 14').launch := TO_DATE('January 31, 1971','Month dd, yyyy'); DBMS_OUTPUT.PUT_LINE(apollo_arr('Apollo 11').commander); DBMS_OUTPUT.PUT_LINE(apollo_arr('Apollo 11').launch); end; / -- Printed results: Neil Armstrong 16-JUL-69