ORACLE_OWNER=oracle
ORACLE_SID=orcl
0. 관리자 접속
sqlplus "system/manager as sysdba"
1. oracle 계정의 환경변수 셋팅
export TMP=/tmp
export TMPDIR=${TMP}
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=${ORACLE_BASE}/12.1.0
export ORACLE_HOME_LISTNER=${ORACLE_HOME}
export ORACLE_HOSTNAME=localhost.localdomain
export ORACLE_SID=orcl
export PATH=/usr/sbin:${ORACLE_HOME}/bin:${PATH}:/usr/local/bin
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${ORACLE_HOME}/lib
export LANG="ko_KR.UTF-8″
2. 스크립트 허용
$ sqlplus "system/manager as sysdba"
ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE;
3. Oracle 테이블스페이스 만들기
// 생성 (최초 2MB에서 자동확장)
CREATE TABLESPACE ts_user_name DATAFILE '/oradata/orcl/user_name.dbf' SIZE 2M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
// 삭제
DROP TABLESPACE ts_user_name INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
4. 계정 만들기
CREATE USER user_name IDENTIFIED BY "비밀번호"; // 계정 생성
ALTER USER user_name DEFAULT TABLESPACE ts_user_name; // 테이블 스페이스 할당
ALTER USER user_name TEMPORARY TABLESPACE TEMP; // temp 테이블 스페이스 할당
ALTER USER user_name IDENTIFIED BY "비밀번호"; // 비밀번호 수정
5. 계정 권한 주기
// 모든 권한 (한방에 끝)
GRANT CONNECT, DBA, RESOURCE TO user_name;
// 상세 권한
GRANT CREATE SESSION TO 유저명 // 데이터베이스에 접근할 수 있는 권한
GRANT CREATE DATABASE LINK TO 유저명
GRANT CREATE MATERIALIZED VIEW TO 유저명
GRANT CREATE PROCEDURE TO 유저명
GRANT CREATE PUBLIC SYNONYM TO 유저명
GRANT CREATE ROLE TO 유저명
GRANT CREATE SEQUENCE TO 유저명
GRANT CREATE SYNONYM TO 유저명
GRANT CREATE TABLE TO 유저명 // 테이블을 생성할 수 있는 권한
GRANT DROP ANY TABLE TO 유저명 // 테이블을 제거할 수 있는 권한
GRANT CREATE TRIGGER TO 유저명
GRANT CREATE TYPE TO 유저명
GRANT CREATE VIEW TO 유저명
6. 생성한 계정으로 sqlplus 접속하기
sqlplus user_name/mypasswd@orcl
7. 계정 삭제하기
ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE;
DROP USER user_name CASCADE;
8. 기타 유용한 쿼리들
— 테이블 스페이스 크기 확장해주는 쿼리문
ALTER DATABASE DATAFILE 'D:\oracle\oradata\XE\ADMIN.DBF' RESIZE 900M;
–테이블 스페이스 정보 보는 쿼리문
SELECT file_name, tablespace_name, bytes, status FROM DBA_DATA_FILES;
–테이블 명시적 인덱스 생성
–1번째 방법
CREATE INDEX MSID_IDX1 ON TEST(MSID)
–2번째 방법
create index test1_test on test1(test)
tablespace users
storage
(
initial 10k
next 10k
pctincrease 0)
pctfree 10
–테이블 정보 보는 쿼리
SELECT * FROM USER_CONSTRAINTS– WHERE TABLE_NAME = UPPER('TEST1');
–ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다. 해결 방법
select a.sid, a.serial#
from v$session a, v$lock b, dba_objects c
where a.sid=b.sid and
b.id1=c.object_id and
b.type='TM' and
c.object_name='CAR_INFO';
alter system kill session '12, 27846′;
— CAR_INFO에는 있는 값을 DASH_BOARD에 넣기
INSERT INTO DASH_BOARD(CAR_LICEN_NUM)
SELECT CAR_LICEN_NUM FROM CAR_INFO
MINUS
SELECT CAR_LICEN_NUM FROM DASH_BOARD
SYSTEM 계정 패스워드 변경
사용자계정 : /as sysdba
ALTER USER SYSTEM IDENTIFIED BY "암호";
일반 스트링은 관계없지만 특수문자가 있을경우 반드시 "" 따옴표로 감싸준다.
9. 테이블스페이스 조회
SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,40) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)",
Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
FROM DBA_DATA_FILES DF, (SELECT file_id,
Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(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;
10. 계정 조회 및 Lock 풀기
-계정 조회
SELECT USERNAME FROM DBA_USERS;
-Lock 걸린 계정 조회
SELECT USERNAME, ACCOUNT_STATUS, TO_CHAR(LOCK_DATE, 'YYYY.MM.DD HH24:MI') ROCK_DATE FROM DBA_USERS;
-Lock 풀기
ALTER USER 사용자 ACCOUNT UNLOCK;
- 비밀번호 잠금 및 유효기간 해제
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER USER user_name ACCOUNT UNLOCK;
ALTER USER user_name IDENTIFIED BY "<PASSWORD>";
11. 데이터베이스 목록 조회
SELECT * FROM V$DATABASE;
or
SELECT ORA_DATABASE_NAME FROM DUAL;
12. 패스워드 사용기간 만료 비활성화 (기본적으로 180일 후 만료되고 접속 불가됨)
ALTER PROFILE default LIMIT FAILED_LOGIN_ATTEMPTS 999 PASSWORD_LIFE_TIME UNLIMITED PASSWORD_GRACE_TIME 10;