Oracle DBA 명령어
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
오라클 DBA 작업 명령어
목차
- 1 INSERT
- 2 UPDATE
- 3 SEQUENCES
- 4 === 기본 프로시져
- 5 프로시져 실행 옵션
- 6 커서 활용 샘플
- 7 DDL SQL
- 7.1 Tables
- 7.2 INDEXES
- 8 DBA 작업
- 9 테이블 스페이스 (TABLESPACE)
- 9.1 TABLESPACE 목록 조회
- 9.2 TABLESPACE 종류
- 9.3 현재 유저의 DEFAULT TABLESPACE 확인
- 9.4 TABLESPACE 생성 구문
- 9.5 UNDO TABLESPACE 생성
- 9.6 TABLESPACE 변경
- 9.7 테이블의 TABLESPACE MOVE
- 9.8 운영중인 테이블 TABLESPACE ONLINE MOVE
- 9.9 TABLESPACE 사이즈 변경(RESIZE)
- 9.10 TABLESPACE 이름 변경
- 9.11 인덱스의 TABLESPACE 변경
- 9.12 인덱스의 TABLESPACE 변경 스크립트
- 9.13 인덱스/테이블 TABLESPACE 변경 스크립트
- 9.14 TABLESPACE 자동증가/최대 사이즈 변경
- 9.15 데이터파일 사이즈 변경
- 9.16 데이터파일 추가
- 9.17 데이터/템프파일 삭제
- 9.18 데이터 파일 삭제 후 OS 디스크 사이즈가 줄지 않을경우
- 9.19 TABLESPACE 삭제
- 9.20 TABLESPACE 수정이나 삭제시 ONLINE/OFFLINE 설정
- 9.21 템프 TABLESPACE
- 9.21.1 TEMPORARY TABLESPACE 정보
- 9.21.2 TEMPORARY TABLESPACE 생성
- 9.21.3 TEMPORARY TABLESPACE를 DEFAULT TABLESPACE로 변경
- 9.21.4 TEMPORARY TABLESPACE 사이즈 증가
- 9.21.5 TEMPORARY TABLESPACE 사이즈 추가
- 9.21.6 TEMPORARY TABLESPACE 삭제
- 9.21.7 TEMPORARY TABLESPACE DATA FILE 삭제
- 9.21.8 TEMPORARY TABLESPACE 사용율 조회 쿼리
- 9.21.9 템프테이블 TABLESPACE sort 사용 현황
- 10 TABLESPACE 오브젝트별 정보
- 11 UNDO TABLESPACE
- 12 TABLESPACE 장애 처리
- 13 LOB포함된 TABLESPACE 용량 축소/REORG
- 14 PL/SQL
- 15 References
- 16 APEX
- 17 References
1 INSERT[편집]
틀:Merge 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 );2 UPDATE[편집]
UPDATE ~ SELECT
UPDATE customer SET state='CA';
UPDATE table1
SET column1 = (SELECT expression1
FROM table2
WHERE conditions)
[WHERE conditions];2.1 다중 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
)
;3 SEQUENCES[편집]
시퀀스 : 순차번호 생성
3.1 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;3.2 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;4 === 기본 프로시져
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;5 프로시져 실행 옵션[편집]
AUTHID DEFINER 실행 시 컴파일 할 때의 유저 사용(DEFAULT)
AUTHID CURRENT_USER 실행 시 현재 접속하고 있는 유저 사용.- PL/SQL내에서 EXECUTE IMEDIATE 실행 시 권한없음 에러가 발생하면 AUTHID CURRENT_USER 추가
6 커서 활용 샘플[편집]
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;
/===
6.1 문자열 함수[편집]
6.1.1 Length[편집]
Length returns an integer representing the length of a given string. It can be referred to as: lengthb, lengthc, length2, and length4.
length( string1 );
SELECT length('hello world') FROM dual;
'''this returns 11, since the argument is made up of 11 characters including the space'''
SELECT lengthb('hello world') FROM dual;
SELECT lengthc('hello world') FROM dual;
SELECT length2('hello world') FROM dual;
SELECT length4('hello world') FROM dual;
'''these also return 11, since the functions called are equivalent'''6.1.2 Instr[편집]
Instr (in string) returns an integer that specifies the location of a sub-string within a string. The programmer can specify which appearance of the string they want to detect, as well as a starting position. An unsuccessful search returns 0.
instr( string1, string2, [ start_position ], [ nth_appearance ] )
instr( 'oracle pl/sql cheatsheet', '/');
'''this returns 10, since the first occurrence of "/" is the tenth character'''
instr( 'oracle pl/sql cheatsheet', 'e', 1, 2);
'''this returns 17, since the second occurrence of "e" is the seventeenth character'''
instr( 'oracle pl/sql cheatsheet', '/', 12, 1);
'''this returns 0, since the first occurrence of "/" is before the starting point, which is the 12th character'''6.1.3 Replace[편집]
Replace looks through a string, replacing one string with another. If no other string is specified, it removes the string specified in the replacement string parameter.
replace( string1, string_to_replace, [ replacement_string ] );
replace('i am here','am','am not');
'''this returns "i am not here"'''6.1.4 Substr[편집]
Substr (substring) returns a portion of the given string. The "start_position" is 1-based, not 0-based. If "start_position" is negative, substr counts from the end of the string. If "length" is not given, substr defaults to the remaining length of the string.
substr( ''string'', start_position [, length])SELECT substr( 'oracle pl/sql cheatsheet', 8, 6) FROM dual;- returns "pl/sql" since the "p" in "pl/sql" is in the 8th position in the string (counting from 1 at the "o" in "oracle")
SELECT substr( 'oracle pl/sql cheatsheet', 15) FROM dual;- returns "cheatsheet" since "c" is in the 15th position in the string and "t" is the last character in the string.
SELECT substr('oracle pl/sql cheatsheet', -10, 5) FROM dual;- returns "cheat" since "c" is the 10th character in the string, counting from the end of the string with "t" as position 1.
6.1.5 Trim[편집]
These functions can be used to filter unwanted characters from strings. By default they remove spaces, but a character set can be specified for removal as well.
trim ( [ leading | trailing | both ] [ trim-char ] from string-to-be-trimmed );
trim (' removing spaces at both sides ');
'''this returns "removing spaces at both sides"'''
ltrim ( string-to-be-trimmed [, trimming-char-set ] );
ltrim (' removing spaces at the left side ');
'''this returns "removing spaces at the left side "'''
rtrim ( string-to-be-trimmed [, trimming-char-set ] );
rtrim (' removing spaces at the right side ');
'''this returns " removing spaces at the right side"'''7 DDL SQL[편집]
7.1 Tables[편집]
7.1.1 Create table[편집]
The syntax to create a table is:
CREATE TABLE [table name]
( [column name] [datatype], ... );For example:
CREATE TABLE employee
(id int, name varchar(20));
7.1.2 Alter TAble[편집]
7.1.2.1 Add column[편집]
The syntax to add a column is:
ALTER TABLE [table name]
ADD ( [column name] [datatype], ... );For example:
ALTER TABLE employee
ADD (id int)7.1.2.2 Modify column[편집]
The syntax to modify a column is:
ALTER TABLE [table name]
MODIFY ( [column name] [new datatype] );ALTER table syntax and examples:
For example:
ALTER TABLE employee
MODIFY( sickHours s float );7.1.2.3 Drop column[편집]
The syntax to drop a column is:
ALTER TABLE [table name]
DROP COLUMN [column name];For example:
ALTER TABLE employee
DROP COLUMN vacationPay;7.1.2.4 Constraints[편집]
7.1.2.5 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 |
7.1.2.5.1 Displaying constraints[편집]
The following statement shows all constraints in the system:
SELECT
table_name,
constraint_name,
constraint_type
FROM user_constraints;7.1.2.5.2 Selecting referential constraints[편집]
The following statement shows all referential constraints (foreign keys) with both source and destination table/column couples:
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;7.1.2.6 Setting constraints on a table[편집]
The syntax for creating a check constraint using a CREATE TABLE statement is:
CREATE TABLE table_name
(
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);
'''For example:'''
CREATE TABLE suppliers
(
supplier_id numeric(4),
supplier_name varchar2(50),
CONSTRAINT check_supplier_id
CHECK (supplier_id BETWEEN 100 and 9999)
);7.1.2.7 Unique Index on a table[편집]
The syntax for creating a unique constraint using a CREATE TABLE statement is:
CREATE TABLE table_name
(
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name UNIQUE (column1, column2, column_n)
);For example:
CREATE TABLE customer
(
id integer not null,
name varchar2(20),
CONSTRAINT customer_id_constraint UNIQUE (id)
);7.1.2.8 Adding unique constraints[편집]
The syntax for a unique constraint is:
ALTER TABLE [table name]
ADD CONSTRAINT [constraint name] UNIQUE( [column name] ) USING INDEX [index name];For example:
ALTER TABLE employee
ADD CONSTRAINT uniqueEmployeeId UNIQUE(employeeId) USING INDEX ourcompanyIndx_tbs;7.2 INDEXES[편집]
An index is a method that retrieves records with greater efficiency. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.
7.2.1 Create an index[편집]
The syntax for creating an index is:
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];UNIQUE indicates that the combination of values in the indexed columns must be unique.
COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose an optimal execution plan when the statements are executed.
For example:
CREATE INDEX customer_idx
ON customer (customer_name);In this example, an index has been created on the customer table called customer_idx. It consists of only of the customer_name field.
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;7.2.2 Create a function-based index[편집]
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 example:
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);7.2.3 Rename an Index[편집]
The syntax for renaming an index is:
ALTER INDEX index_name
RENAME TO new_index_name;For example:
ALTER INDEX customer_id
RENAME TO new_customer_id;In this example, customer_id is renamed to new_customer_id.
7.2.4 Collect statistics on an index[편집]
If you need to collect statistics on the index after it is first created or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics. You collect statistics so that oracle can use the indexes in an effective manner. This recalcultes the table size, number of rows, blocks, segments and update the dictionary tables so that oracle can use the data effectively while choosing the execution plan.
The syntax for collecting statistics on an index is:
ALTER INDEX index_name
REBUILD COMPUTE STATISTICS;For example:
ALTER INDEX customer_idx
REBUILD COMPUTE STATISTICS;In this example, statistics are collected for the index called customer_idx.
7.2.5 Drop an index[편집]
The syntax for dropping an index is:
DROP INDEX index_name;For example:
DROP INDEX customer_idx;In this example, the customer_idx is dropped.
8 DBA 작업[편집]
8.1 User 관리[편집]
8.1.1 user 생성[편집]
The syntax for creating a user is:
CREATE USER username IDENTIFIED BY password;For example:
CREATE USER brian IDENTIFIED BY brianpass;8.1.2 권한 부여[편집]
The syntax for granting privileges is:
GRANT privilege TO user;For example:
GRANT dba TO brian;8.1.3 비밀번호 변경[편집]
The syntax for changing user password is:
ALTER USER username IDENTIFIED BY password;For example:
ALTER USER brian IDENTIFIED BY brianpassword;
9 테이블 스페이스 (TABLESPACE)[편집]
9.1 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;9.2 TABLESPACE 종류[편집]
9.3 현재 유저의 DEFAULT TABLESPACE 확인[편집]
SELECT * FROM USER_USERS ;-- DEFAUT TABLESPACE로 설정된 부분을 확인9.3.1 유저의 DEFAULT TABLESPACE 변경[편집]
ALTER USER [유저명] DEFAULT TABLESPACE [테이블 스페이스명]9.4 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%)
: )9.4.1 BIGFILE 테이블스페이스[편집]
- 8K 블록 - bigfile 테이블 스페이스에는 최대 32TB
- 32K 블록 - bigfile 파일 테이블 스페이스에는 최대 128TB
9.4.1.1 BIGFILE 테이블스페이스 장점[편집]
9.4.1.2 자동 수행 뷰 목록[편집]
android 장점
- CREATE DATABASE 및 CREATE CONTROLFILE 문의 DB_FILES 초기화 매개 변수와 MAXDATAFILES 매개 변수를 조정하여 데이터 파일 정보에 필요한 SGA 공간의 양과 제어 파일의 크기를 줄일 수 있다.
- Bigfile 테이블 스페이스는 데이터 파일 투명성을 제공하여 데이터베이스 관리를 단순화합니다.
- ALTER TABLESPACE 문의 SQL 구문을 사용하면 기본 개별 데이터 파일이 아닌 테이블 스페이스에서 작업을 수행 할 수 있다.
- 빅파일 테이블 스페이스는 자동 세그먼트 공간 관리가있는 로컬 관리 테이블 스페이스에 대해서만 가능함. (언두,템프,시스템 테이블스페이스는 지원 안함)
9.4.1.3 BIGFILE 테이블스페이스 단점[편집]
android BIGFILE 테이블스페이스 단점
- 병렬 쿼리 실행 및 RMAN 백업 병렬화에 부정적인 영향을 미치므로 스트라이핑을 지원하지 않는 시스템에서 빅 파일 테이블 스페이스를 생성하지 마십시오.
- 큰 파일 크기를 지원하지 않는 OS 플랫폼에서 bigfile 테이블 스페이스를 사용하는 것은 권장되지 않으며 테이블 스페이스 용량을 제한 할 수 있습니다.
- Bigfile 테이블 스페이스는 ASM (Automatic Storage Management) 또는 스트라이핑 또는 RAID를 지원하는 기타 논리 볼륨 관리자 및 동적으로 확장 가능한 논리 볼륨과 함께 사용하기위한 것입니다.
9.5 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만 가능)9.6 TABLESPACE 변경[편집]
9.6.1 TABLESPACE 읽기/쓰기 모드 변경[편집]
- 읽기 전용 모드
alter tablespace <TABLESPACE명> read only;- 읽기 쓰기 모드
alter tablespace <TABLESPACE명> read ,write;9.6.2 OPEN 상태에서 DATAFILE 이동[편집]
alter tablespace TABLESPACE명 offline;9.6.2.1 offline 된 T/S에 대해 복사/이동[편집]
cp /data1/xxx.dbf /data2/xxx.dbf
or
mv /data1/xxx.dbf /data2/xxx.dbf9.6.2.2 OFFLINE 상태에서 DATAFILE 이동[편집]
alter tablespace TABLESPACE명 rename datafile '파일경로1/xxx.dbf' to '파일경로2/xxx.dbf';
alter tablespace TABLESPACE명 online;9.7 테이블의 TABLESPACE MOVE[편집]
ALTER TABLE [테이블명]
MOVE TABLESPACE [테이블 스페이스명]- -테이블스페이스 이동시 인덱스 리빌드 필요
ALTER INDEX [인덱스명] REBUILD ;- 인덱스 리빌드 동시 수행 시
ALTER TABLE [테이블명]
MOVE TABLESPACE [테이블 스페이스명]
UPDATE INDEXES;9.7.1 파티션닝 테이블 TABLESPACE MOVE[편집]
ALTER TABLE [테이블명]
MOVE PARTITION [파티션명] TABLESPACE [테이블 스페이스명]- 파티셔닝 테이블 인덱스 REBUILD
ALTER INDEX [인덱스명]
REBUILD PARTITION [파티션명]9.8 운영중인 테이블 TABLESPACE ONLINE MOVE[편집]
ALTER TABLE [테이블명]
MOVE TABLESPACE [테이블 스페이스명]
ONLINE -- online 옵션
;
- 인덱스 리빌드 동시 수행 시
ALTER TABLE [테이블명]
MOVE TABLESPACE [테이블 스페이스명]
UPDATE INDEXES
ONLINE -- online 옵션
;9.8.1 운영중인 파티션닝 테이블 TABLESPACE ONLINE MOVE[편집]
ALTER TABLE [테이블명]
MOVE PARTITION [파티션명] TABLESPACE [테이블 스페이스명]
ONLINE
assignment 온라인 Move 작업시 장단점
- Move 작업 중 다른 세션의 DML 작업이 가능함
- 파티션의 로컬인덱스는 USABLE 상태로 바로 사용가능함(unusable 되지 않음)
- 도메인 인덱스가 포함된 테이블은 사용불가
- 온라인 Move 작업시 parallel DML , Direct Path Insert 가 안됨(즉,성능은 좋지않다)
9.9 TABLESPACE 사이즈 변경(RESIZE)[편집]
ALTER TABLESPACE ts_txxxx
RESIZE 500G;9.10 TABLESPACE 이름 변경[편집]
ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE]9.11 인덱스의 TABLESPACE 변경[편집]
ALTER INDEX 인덱스명 REBUILD TABLESPACE [테이블 스페이스명]
[ PARALLEL parallel_num ]
[ LOGGING or NOLOGGING ]
;9.12 인덱스의 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);9.13 인덱스/테이블 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;
9.14 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씩 자동증가9.15 데이터파일 사이즈 변경[편집]
ALTER DATABASE DATAFILE 'C:\경로\TEST1.DBF'
RESIZE 10M;9.16 데이터파일 추가[편집]
ALTER TABLESPACE [테이블 스페이스명]
ADD DATAFILE 'C:\경로\TEST2.DBF' SIZE 10M;9.17 데이터/템프파일 삭제[편집]
ALTER TABLESPACE [테이블 스페이스명]
DROP DATAFILE 'data파일';
-- 템프TS DROP TEMPFILE 'data파일';9.18 데이터 파일 삭제 후 OS 디스크 사이즈가 줄지 않을경우[편집]
attach_file 1) 테이블스페이스 사이즈를 줄인 (shrink) 후 데이터파일 삭제
2) DB 리부팅 하면 공간 확보 됨(DBWR 이 데이터파일을 사용중이기 때문에 공간이 줄지 않음)
9.19 TABLESPACE 삭제[편집]
9.19.1 TABLESPACE에 포함된 모든 세그먼트 삭제[편집]
DROP TABLESPACE [테이블 스페이스명]
INCLUDING CONTENTS;-- TABLESPACE의 모든 세그먼트를 삭제한다.
-- 단, 데이타가 있는 TABLESPACE는 삭제할수 없다.9.19.2 TABLESPACE에 포함된 테이블의 참조/제약 조건 삭제[편집]
DROP TABLESPACE [테이블 스페이스명]
CASCADE CONSTRAINTS; -- 삭제된 TABLESPACE 내의 테이블의 기본키와 유일키를 참조하는
-- 다른 TABLESPACE의 테이블로부터 참조무결성 제약 조건을 삭제한다.9.19.3 TABLESPACE의 데이터파일 삭제[편집]
DROP TABLESPACE [테이블 스페이스명]
INCLUDING CONTENTS AND DATAFILES; -- 물리적파일까지 삭제한다.
9.20 TABLESPACE 수정이나 삭제시 ONLINE/OFFLINE 설정[편집]
ALTER TABLESPACE [테이블 스페이스명] ONLINE
ALTER TABLESPACE [테이블 스페이스명] OFFLINE9.21 템프 TABLESPACE[편집]
9.21.1 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_NAME9.21.1.1 템프테이블 사이즈 조정[편집]
ALTER DATABASE TEMPFILE 'D:\APP\ORADATA\ORCL\TEMP01.DBF'
RESIZE 304M;9.21.2 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 NUIFORM SIZE만 생성가능하다.
-- (주의)AUTOALLOCATE, EXTENT MANAGEMENT DICIONARY OPTION을 사용하면 ORA-25319 ERROR 발생한다.
-- RENAME 이 불가능하다.9.21.3 TEMPORARY TABLESPACE를 DEFAULT TABLESPACE로 변경[편집]
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE [테이블 스페이스명];9.21.4 TEMPORARY TABLESPACE 사이즈 증가[편집]
ALTER TABLESPACE TEMP
AUTOEXTEND ON
NEXT 100M
MAXSIZE 500G;9.21.5 TEMPORARY TABLESPACE 사이즈 추가[편집]
ALTER TABLESPACE TEMP
ADD TEMPFILE '+DATA' SIZE 10G
AUTOEXTEND ON
NEXT 100M
MAXSIZE 32767M;9.21.6 TEMPORARY TABLESPACE 삭제[편집]
DROP TABLESPACE TEMP2;9.21.7 TEMPORARY TABLESPACE DATA FILE 삭제[편집]
ALTER TABLESPACE TEMP DROP TEMPFILE '+DATA/temp.368.1013282149';- +DATA/temp.368.1013282149 은 ASM 사용시
9.21.8 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_TEXT9.21.9 템프테이블 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_total10 TABLESPACE 오브젝트별 정보[편집]
10.1 TABLESPACE/파일 확인(딕셔너리)[편집]
SELECT * FROM DBA_DATA_FILES ;
SELECT * FROM DBA_TABLESPACES ;
SELECT * FROM DBA_SEGMENTS;10.2 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;10.3 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;10.4 TABLESPACE 사이즈 정보[편집]
10.4.1 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
;10.4.2 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_name10.4.3 테이블 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_TYPE10.4.4 인덱스 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;10.4.5 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;10.4.6 데이터파일 별 테이블스페이스 사이즈 조회[편집]
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;
10.4.7 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;10.4.8 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;10.4.9 공간의 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;10.5 TABLESPACE에 포함된 테이블 명 보기[편집]
SELECT TABLESPACE_NAME
, TABLE_NAME
FROM DBA_TABLES
WHERE TABLESPACE_NAME = UPPER('&TABLESPACE명')
ORDER BY TABLESPACE_NAME
, TABLE_NAME;10.6 오브젝트별 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');10.7 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;10.8 파일위치별 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;10.9 현재 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;10.10 테이블의 익스텐트 정보 조회[편집]
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);11 UNDO TABLESPACE[편집]
- UNDO Segment를 저장하고 있는 TABLESPACE , 관리자가 생성/관리 가능
- Instance당 여러 개가 동시에 존재할 수 있지만 사용은 한번에 1개만
11.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,211.2 UNDO 테이블 스페이스 삭제[편집]
DROP TABLESPACE UNDO_T1 INCLUDING CONTENTS AND DATAFILES;
11.3 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;12 TABLESPACE 장애 처리[편집]
12.1 TABLESPACE 용량 부족으로 에러 발생시[편집]
- 방법 1) Data file을 크게 늘려줌(자동 증가/수동증가)
- 방법 2) Data file을 하나 더 추가
12.1.1 Tablespace Offline[편집]
- 사용자가 더 이상 해당 Tablespace에 접근하지 못한다는 의미
- 데이터파일의 위치를 이동하거나 특정 Tablespace가 장애가 나서 복구해야 할 때 사용
12.1.2 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해야 할 경우 사용
12.1.3 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
13 LOB포함된 TABLESPACE 용량 축소/REORG[편집]
13.1 해당 테이블스페이스를 사용중인 테이블/인덱스/LOB 조회[편집]
SELECT *
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME ='TS_TEST_D01';13.2 테이블에서 사용중인 테이블스페이스 신규테이블스페이스로 이동[편집]
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을 사용하는 테이블13.3 인덱스 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을 사용하는 인덱스13.4 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
;13.5 원래 T/S명으로 변경[편집]
ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE]13.6 Importing and exporting[편집]
13.6.1 Import a dump file using IMP[편집]
This command is used to import Oracle tables and table data from a *.dmp file created by the 'exp' tool. Remember that this a command that is executed from the command line through $ORACLE_HOME/bin and not within SQL*Plus.
The syntax for importing a dump file is:
imp KEYWORD=valueThere are number of parameters you can use for keywords.
To view all the keywords:
imp HELP=yesAn example:
imp brian/brianpassword FILE=mydump.dmp FULL=yes14 PL/SQL[편집]
14.1 Operators[편집]
14.1.1 Arithmetic operators[편집]
- Addition: +
- Subtraction: -
- Multiplication: *
- Division: /
- Power (PL/SQL only): **
14.1.1.1 Examples[편집]
gives all employees from customer id 5 a 5% raise
UPDATE employee SET salary = salary * 1.05
WHERE customer_id = 5;determines the after tax wage for all employees
SELECT wage - tax FROM employee;14.1.2 Comparison operators[편집]
* Greater Than: >
* Greater Than or Equal To: >=
* Less Than: <
* Less Than or Equal to: <=
* Equivalence: =
* Inequality: != ^= <> ¬= (depends on platform)
=====Examples=====
<source lang=sql>
SELECT name, salary, email FROM employees WHERE salary > 40000;
SELECT name FROM customers WHERE customer_id < 6;14.1.3 String operators[편집]
- Concatenate: ||
create or replace procedure addtest(
a in varchar2(100),
b in varchar2(100), c out varchar2(200)
)
IS
begin
C:=concat(a,'-',b);14.1.4 Date operators[편집]
- Addition: +
- Subtraction: -
14.2 Types[편집]
14.2.1 Basic PL/SQL Types[편집]
Scalar type (defined in package STANDARD): NUMBER, CHAR, VARCHAR2, BOOLEAN, BINARY_INTEGER, LONG\LONG RAW, DATE, TIMESTAMP and its family including intervals)
Composite types (user-defined types): TABLE, RECORD, NESTED TABLE and VARRAY
LOB datatypes : used to store an unstructured large amount of data
14.2.2 %TYPE - anchored type variable declaration[편집]
The syntax for anchored type declarations is
<var_name> <obj>%type [not null][:= <init-val>];For example
name Books.title%type; /* name is defined as the same type as column 'title' of table Books */
commission number(5,2) := 12.5;
x commission%type; /* x is defined as the same type as variable 'commission' */Note:
- Anchored variables allow for the automatic synchronization of the type of anchored variable with the type of <obj> when there is a change to the <obj> type.
- Anchored types are evaluated at compile time, so recompile the program to reflect the change of <obj> type in the anchored variable.
14.2.3 Collections[편집]
A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.
--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);
...There are many good reasons to use collections.
- Dramatically faster execution speed, thanks to transparent performance boosts including a new optimizing compiler, better integrated native compilation, and new datatypes that help out with number-crunching applications.
- The FORALL statement, made even more flexible and useful. For example, FORALL now supports nonconsecutive indexes.
- Regular expressions are available in PL/SQL in the form of three new functions (REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR) and the REGEXP_LIKE operator for comparisons[2].
- Collections, improved to include such things as collection comparison for equality and support for set operations on nested tables.
15 References[편집]
15.1 Stored logic[편집]
15.1.1 Functions[편집]
A function must return a value to the caller.
The syntax for a function is
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];For example:
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;15.1.2 Procedures[편집]
A procedure differs from a function in that it must not return a value to the caller.
The syntax for a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:
- IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
- OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
- IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
Also you can declare a DEFAULT value;
CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [IN|OUT|IN OUT] [DEFAULT ''value''] [,parameter]) ]The following is a simple example of a procedure:
/* purpose: shows the students in the course specified by courseId */
CREATE OR REPLACE Procedure GetNumberOfStudents
( courseId IN number, numberOfStudents OUT number )
IS
/* although there are better ways to compute the number of students,
this is a good opportunity to show a cursor in action */
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;15.1.3 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;15.1.4 Passing parameters to stored logic[편집]
There are three basic syntaxes for passing parameters to a stored procedure: positional notation, named notation and mixed notation.
The following examples call this procedure for each of the basic syntaxes for parameter passing:
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;15.1.4.1 Positional notation[편집]
Specify the same parameters in the same order as they are declared in the procedure. This notation is compact, but if you specify the parameters (especially literals) in the wrong order, the bug can be hard to detect. You must change your code if the procedure's parameter list changes.
create_customer('James Whitfield', 33, '301 Anystreet', '251-222-3154');15.1.4.2 Named notation[편집]
Specify the name of each parameter along with its value. An arrow (=>) serves as the association operator. The order of the parameters is not significant. This notation is more verbose, but makes your code easier to read and maintain. You can sometimes avoid changing code if the procedure's parameter list changes, for example if the parameters are reordered or a new optional parameter is added. Named notation is a good practice to use for any code that calls someone else's API, or defines an API for someone else to use.
create_customer(p_address => '301 Anystreet', p_id => 33, p_name => 'James Whitfield', p_phone => '251-222-3154');15.1.4.3 Mixed notation[편집]
Specify the first parameters with positional notation, then switch to named notation for the last parameters. You can use this notation to call procedures that have some required parameters, followed by some optional parameters.
create_customer(v_name, v_id, p_address=> '301 Anystreet', p_phone => '251-222-3154');15.1.5 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);15.2 Flow control[편집]
15.2.1 Conditional Operators[편집]
- and: AND
- or: OR
- not: NOT
15.2.2 Example[편집]
IF salary > 40000 AND salary <= 70000
THEN()
ELSE IF salary>70000 AND salary<=100000
THEN()
ELSE()15.2.3 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;15.3 Arrays[편집]
15.3.1 Associative arrays[편집]
- Strongly typed arrays, useful as in-memory tables
15.3.2 Example[편집]
- Very simple example, the index is the key to accessing the array so there is no need to loop through the whole table unless you intend to use data from every line of the array.
- The index can also be a numeric value.
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- More complex example, 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-6916 APEX[편집]
틀:W aka APEX, is a web-based software development environment that runs on an Oracle database.
16.1 String substitution[편집]
- In SQL: :VARIABLE
- In PL/SQL: V('VARIABLE') or NV('VARIABLE')
- In text: &VARIABLE.
17 References[편집]
- ↑ http://www.psoug.org/reference/constraints.html
- ↑ "First Expressions" by Jonathan Gennick for more information in this issue