All Days 무한도전

테이블에 걸려있는 제약 조건의 확인

  - USER_CONS_COLUMNS : 컬럼에 할당된 제약 조건을 볼 수 있다.

  - USER_CONSTRAINTS　: 유저가 소유한 모든 제약 조건을 불 수 있다.

SQL> SELECT SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,
            DECODE(B.CONSTRAINT_TYPE, 
                  'P','PRIMARY KEY',
                  'U','UNIQUE KEY',
                  'C','CHECK OR NOT NULL',
                  'R','FOREIGN KEY') CONSTRAINT_TYPE,
            A.CONSTRAINT_NAME CONSTRAINT_NAME
     FROM　USER_CONS_COLUMNS A,　 USER_CONSTRAINTS B　　 
     WHERE A.TABLE_NAME = UPPER('&table_name')　　 
       AND A.TABLE_NAME = B.TABLE_NAME　　 
       AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME　　 
     ORDER BY 1;　　 

-- 테이블 명을 입력 하면 된다. 
table_name의 값을 입력하십시오: emp2

테이블의 특정 컬럼에 걸려있는 제약 조건의 확인

USER_CONS_COLUMNS : 컬럼에 할당된 제약 조건을 볼 수 있다.

 
SQL> SET LINESIZE 300

SQL> SELECT SUBSTR(TABLE_NAME,1,15) TABLE_NAMES,　　 
            SUBSTR(COLUMN_NAME,1,15)　COLUMN_NAME,　　 
            SUBSTR(CONSTRAINT_NAME,1,25) CONSTRAINT_NAME　　 
     FROM USER_CONS_COLUMNS　　 
     WHERE　TABLE_NAME = UPPER('&table_name')　　 
       AND COLUMN_NAME = UPPER('&column_name');　　 

table_name의 값을 입력하십시오: emp2
column_name의 값을 입력하십시오: empno

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, R_CONSTRAINT_NAME, SEARCH_CONDITION
FROM USER_CONSTRAINTS;
-----------------------------------
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, VALIDATED, DEFERRABLE, DEFERRED
FROM USER_CONSTRAINTS;
-----------------------------------
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM USER_CONS_COLUMNS;
-----------------------------------
SELECT A.TABLE_NAME, B.COLUMN_NAME, A.CONSTRAINT_NAME,
A.CONSTRAINT_TYPE, A.R_CONSTRAINT_NAME, A.SEARCH_CONDITION
FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME;
-----------------------------------
SELECT T.TABLE_NAME, T.CONSTRAINT_NAME, C.COLUMN_NAME,
T.DEFERRABLE, T.DEFERRED, T.VALIDATED
FROM DBA_CONSTRAINTS T, DBA_CONS_COLUMNS C
WHERE T.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND T.OWNER LIKE '%&OWNER%'
AND T.TABLE_NAME LIKE '%&TABLE_NAME%';
-----------------------------------
SELECT OBJECT_NAME, OBJECT_TYPE, OBJECT_ID, DATA_OBJECT_ID, CREATED, TIMESTAMP, LAST_DDL_TIME
FROM USER_OBJECTS
ORDER BY OBJECT_TYPE;
----------------------------------
SELECT VIEW_NAME, TEXT
FROM USER_VIEWS;
-----------------------------------
SELECT *
FROM USER_SEQUENCES;
-----------------------------------
SELECT SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM USER_SYNONYMS;
-----------------------------------
SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,
PCT_INCREASE, MIN_EXTLEN, STATUS, CONTENTS, ALLOCATION_TYPE
FROM DBA_TABLESPACES;
----------------------------------
롤백세그먼트의 일반적인 정보
SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME,
INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, STATUS
FROM DBA_ROLLBACK_SEGS;
----------------------------------
현재 인스턴트가 사용하고 있는 롤백세그먼트에 대한 통계 검색
SELECT N.NAME, S.EXTENTS, S.RSSIZE, S.OPTSIZE, S.HWMSIZE, S.XACTS, S.STATUS
FROM V$ROLLNAME N, V$ROLLSTAT S
WHERE N.USN = S.USN;
----------------------------------
롤백세그먼트에 대한 경합
SELECT N.NAME, ROUND(100*S.WAITS/S.GETS)
FROM V$ROLLNAME N, V$ROLLSTAT S
WHERE N.USN = S.USN;
----------------------------------
블록킹 트랜잭션
SELECT S.SID, S.SERIAL#, T.START_TIME, T.XIDUSN, S.USERNAME
FROM V$SESSION S, V$TRANSACTION T, V$ROLLSTAT R
WHERE S.SADDR = T.SES_ADDR
AND T.XIDUSN = R.USN
AND ((R.CUREXT = T.START_UEXT-1)
OR ((R.CUREXT = R.EXTENTS-1) AND T.START_UEXT = 0));
----------------------------------
인덱스에 대한 유효성 확인
SELECT BLOCKS, BTREE_SPACE, USED_SPACE, PCT_USED, LF_ROWS, DEL_LF_ROWS
FROM INDEX_STATS;
----------------------------------
SELECT USERNAME, USER_ID, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, PROFILE
FROM DBA_USERS;
----------------------------------
SELECT TABLESPACE_NAME, BYTES, MAX_BYTES, BLOCKS, MAX_BLOCKS
FROM USER_TS_QUOTAS;
-----------------------------------
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES,
AUTOEXTENSIBLE, INCREMENT_BY, MAXBYTES
FROM DBA_DATA_FILES;
-----------------------------------
SELECT FILE#, STATUS, RFILE#, BYTES, BLOCKS, CREATE_BYTES, BLOCK_SIZE, NAME
FROM V$DATAFILE;
-----------------------------------
SELECT FILE#, STATUS
FROM V$BACKUP;
-----------------------------------
SELECT *
FROM DBA_FREE_SPACE
ORDER BY BLOCK_ID;
-----------------------------------
SELECT A.TABLESPACE_NAME, A.BYTES, A.STATUS, B.STATUS, B.ENABLED, B.NAME
FROM DBA_DATA_FILES A, V$DATAFILE B
WHERE A.FILE_ID = B.FILE# AND A.TABLESPACE_NAME LIKE '%&TABLESPACE_NAME%';
-----------------------------------
SELECT TABLESPACE_NAME, EXTENTS_COALESCED, TOTAL_BYTES, BYTES_COALESCED
FROM DBA_FREE_SPACE_COALESCED;
-----------------------------------
SELECT A.TABLESPACE_NAME "TABLESPACE",
B.FILE_NAME "FILE",
B.BYTES "TOTAL SIZE",
C.BYTES "SIZE LEFT"
FROM DBA_TABLESPACES A, DBA_DATA_FILES B, DBA_FREE_SPACE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND
A.TABLESPACE_NAME = C.TABLESPACE_NAME AND
A.TABLESPACE_NAME LIKE '%&TABLESPACE_NAME%';
-----------------------------------
SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT
FROM USER_SEGMENTS;
-----------------------------------
SELECT INDEX_NAME, TABLE_NAME, UNIQUENESS, INDEX_TYPE, TABLESPACE_NAME
FROM USER_INDEXES;
-----------------------------------
SELECT LINE, POSITION, TEXT
FROM USER_ERRORS
ORDER BY SEQUENCE;
-----------------------------------
SELECT *
FROM USER_SOURCE;
-----------------------------------
SELECT SEGMENT_NAME, PARTITION_NAME, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS
FROM USER_EXTENTS
ORDER BY SEGMENT_NAME, EXTENT_ID;
-----------------------------------
SELECT TABLESPACE_NAME, SEGMENT_NAME, FILE_ID, EXTENT_ID, BLOCK_ID, BLOCKS
FROM DBA_EXTENTS
ORDER BY EXTENT_ID;
-----------------------------------
SELECT OPERATION, OPTIONS, OBJECT_NAME, ID, PARENT_ID, POSITION
FROM PLAN_TABLE
ORDER BY ID;
-----------------------------------
SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN
FROM USER_TABLES;
-----------------------------------
SELECT COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY
FROM USER_TAB_COLUMNS;
-----------------------------------
SELECT TABLE_NAME, TABLE_TYPE_NAME, PARENT_TABLE_NAME, PARENT_TABLE_COLUMN
FROM USER_NESTED_TABLES;
-----------------------------------
SELECT *
FROM DBA_PROFILES;
-----------------------------------
SELECT *
FROM USER_RESOURCE_LIMITS;
-----------------------------------
SELECT *
FROM USER_PASSWORD_LIMITS;
-----------------------------------
SELECT *
FROM V$OPTION;
-----------------------------------
SELECT *
FROM V$PARAMETER;
-----------------------------------
SELECT DISTINCT OBJECT_TYPE
FROM DBA_OBJECTS;
-----------------------------------
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE, CREATED
FROM DBA_OBJECTS
WHERE OWNER LIKE '%&OWNER%'
AND OBJECT_TYPE LIKE '%&OBJECT_TYPE%'
ORDER BY OBJECT_NAME;
-----------------------------------
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME LIKE '%&TABLE_NAME%'
ORDER BY OWNER, TABLE_NAME;
-----------------------------------
SELECT VIEW_NAME, TEXT
FROM DBA_VIEWS
WHERE VIEW_NAME LIKE '%&VIEW_NAME%';
-----------------------------------
SELECT *
FROM DBA_SYS_PRIVS
WHERE GRANTEE LIKE '%&ROLE%';
-----------------------------------
SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM DBA_SYNONYMS
ORDER BY 2, 1, 3;
-----------------------------------
<클러스터>
클러스터의 일반적인 정보(블록파라미터..)
SELECT * FROM DBA_CLUSTERS
WHERE OWNER LIKE '%&OWNER%';
클러스터 테이블 및 클러스터 키 조회
SELECT OWNER, CLUSTER_NAME, CLU_COLUMN_NAME, TABLE_NAME
FROM DBA_CLU_COLUMNS
WHERE OWNER LIKE '%&OWNER%';
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
FROM DBA_TAB_COLUMNS
WHERE OWNER LIKE '%&OWNER%';
-----------------------------------
IOT 조회
SELECT T.TABLE_NAME AS "IOT", O.TABLE_NAME AS "OVERFLOW",
I.INDEX_NAME AS "INDEX", O.TABLESPACE_NAME AS "OVERFLOW TBS",
I.TABLESPACE_NAME AS "INDEX TBS", I.PCT_THRESHOLD
FROM DBA_TABLES T, DBA_TABLES O, DBA_INDEXES I
WHERE T.OWNER = O.OWNER
AND T.TABLE_NAME = O.IOT_NAME
AND T.OWNER = I.OWNER
AND T.TABLE_NAME = I.TABLE_NAME
AND T.OWNER LIKE '%&OWNER%';
-----------------------------------
SELECT USERNAME, TIMESTAMP, ACTION_NAME
FROM DBA_AUDIT_TRAIL;
-----------------------------------
SELECT USERID, OBJ$NAME, SES$ACTIONS, TIMESTAMP#
FROM SYS.AUD$
WHERE OBJ$NAME LIKE '%&OBJECT_NAME%';
-----------------------------------
SELECT SESSIONID, STATEMENT, TIMESTAMP#, USERID, TERMINAL, ACTION#,
OBJ$CREATOR, OBJ$NAME, SES$ACTIONS, COMMENT$TEXT, SPARE1
FROM SYS.AUD$;

#### system 계정으로 로그인해서

# 테이블스페이스 이름 보기
select ts#,rfile#,name from v$datafile order by ts#

# user별 테이블스페이스 정보 보기
select username,password,default_tablespace,temporary_tablespace from dba_users

# 테이블스페이스 만들기
create tablespace spaceName
datafile '/oracle/oradata/data.dbf' size 200m
default storage
(initial 1m next 1m
minextents 1 maxextents unlimited pctincrease 0);

# 테이블 스페이스 자동증가 on
alter database datafile '/oracle/oradata/data.dbf' autoextend on;

# 사용자 만들기
create user userName identified by password default tablespace spaceName temporary tablespace tempSpaceName

# 권한 만들기
create role roleName;
grant create session, create table, create view, create synonym to roleName;

# 권한 부여
grant roleName to userName

# Resource 부여
grant resource to userName

# import
imp userid=system/manager file=xxx.dmp fromuser=aaa touser=aaa full=n commit=y ignore=y log=logfile.log

#### 해당 user로 로그인해서

# data space 사용량 조회
select table_name,tablespace_name from user_tables;
select index_name,table_name,tablespace_name from user_tables;
select segment_name,tablespace_name,sum(bytes) from user_extents
where segment_type='TABLE' group by segment_name,tablespace_name;
select segment_name, sum(bytes) from user_extents
where segment_type='TABLE' group by segment_name

### 로그파일 추가시

1. 먼저 Dummy 그룹 추가
alter database add logfile group 3 '/data/oradata/redoo4.log' size 50m;

2. 변경할 그룹 inactive 상태로 만들기
alter system switch logfile;

3. Inactive 상태가 됐는지 확인
select * from v$log;
select * from v$logfile;

4. 그룹 삭제
alter database drop logfile group 1;

5. 로그 그룹 추가
alter database add logfile group 1 ('/data/oradata/redo01a.log','/data/oradata/redo01b.log','/data/oradata/redo01c.log','/data/oradata/redo01d.log','/data/oradata/redo01e.log') size 524288000;
alter database add logfile group 2 ('/oracle/oradata/ESMDB/redo02a.log','/oracle/oradata/ESMDB/redo02b.log','/oracle/oradata/ESMDB/redo02c.log','/oracle/oradata/ESMDB/redo02d.log','/oracle/oradata/ESMDB/redo02e.log') size 524288000;

▶ SYSMAN 이란?
SYSMAN
By default during the installation of Oracle Enterprise Manager, one Super Administrator account is created with the user name of SYSMAN. The SYSMAN account should be used to perform infrequent system-wide, global configuration tasks such as setting up the environment. Other administrator accounts can be created for daily administration work. The SYSMAN account is:
Owner of the Management Repository schema
Default Enterprise Manager Super Administrator
User name used to log in to Enterprise Manager the first time
10g 설치시 EM이 설치되는데 EM의 최고권리자 계정으로 생성되는 계정입니다.
. Reopsitory 스키마의 관리를 하는 계정입니다.
. EM의 최고권리자입니다.
. EM 으로 가장 처음 로그인 가능한 유저입니다.

▶ DBSNMP 이란?
. grid control 에서 각 DB의 연결정보 등을 담당하는 계정입니다.
. DBSNMP를lock 시킬경우 Grid Control 및 EM 사용 못합니다.

▶ MGMT_VIEW 이란?
. SYSMAN/EM 관련계정입니다.
3가지 모두 EM과 관리된 계정들로EM을 사용하신다면 lock 하시면 안되고
EM을 사용안하신다면 보안성때문에 lock을 하는 싸이트도 있습니다.

1. 프로세스 모니터링

1) 서버 프로세스
SELECT sid, serial#, username, status "Status of Session", server "ServerType", osuser, machine, program, process FROM v$session;
2) 사용자 프로세스
SELECT sid, serial#, osuser, process, machine, program, to_char(logon_time, 'yyyy/mm/dd hh:mi:ss') as Logon_Time FROM v$session WHERE (upper(server) <> 'PSEUDO' AND UPPER(program) NOT LIKE 'oracle.exe%') and serial# <> 1;

2. SGA 모니터링

1) Data Buffer Cache 구조
SELECT id, name, block_size, buffers FROM v$buffer_pool;

2) 히트율
SELECT phy.value "Physical Read",cur.value+con.value "Buffer Cache Read", (1-((phy.value) / (cur.value+con.value)))*100 "히트율" FROM v$sysstat cur, v$sysstat con, v$sysstat phy WHERE cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads';
3) Wait Status 확인
SELECT (SELECT total_waits FROM v$system_event WHERE event = 'free buffer waits') "Free Buffer Waits", (SELECT total_waits FROM v$system_event WHERE event = 'buffer busy waits') "Buffer Busy Waits" FROM dual;
4) 메모리 사용상태(세션별)
SELECT A.process, A.username, B.block_gets, B.consistent_gets, B.physical_reads FROM v$sess_io B, v$session A WHERE B.sid = A.sid;

3. 로그 버퍼 히트율 모니터링

SELECT S1.value "Redo Alloc Retries", S2.value "Redo Entries", S1.value / S2.value * 100 "히트율" FROM v$sysstat S1, v$sysstat S2 WHERE S1.name = 'redo buffer allocation retries' AND S2.name = 'redo entries';

4. Shared Pool 모니터링

1) 히트율
SELECT Gets, Gethits, Gethitratio*100 "히트율"FROM v$Librarycache WHERE namespace = 'SQL AREA';

2) Reload 상태
SELECT SUM(pins) "Executions(PINS)", SUM(reloads) "Cache Misses(RELOADS) " , SUM(reloads) / SUM(pins)*100 "Reload" FROM v$Librarycache;
3) Reserved Pool(응답상태)
SELECT requests, request_misses, request_failures, free_space, avg_free_size, max_free_size, used_space,avg_used_size FROM v$shared_pool_reserved;
4) Parsing SQL문 상태
SELECT cpu_time, elapsed_time,executions, loads, invalidations, version_count, parse_calls, sorts, sql_text FROM v$sqlarea WHERE sql_text NOT LIKE '%$%' AND command_type IN (2,3,6,7) AND rownum <= 200 ORDER BY sql_text DESC;
5) 파싱 SQL문(메모리순)
SELECT buffer_gets, sql_text FROM v$sqlarea WHERE sql_text NOT LIKE '%$%' AND command_type IN(2,3,6,7) ORDER BY buffer_gets DESC;
6) 파싱 SQL문(I/O순)
SELECT disk_reads, sql_text FROM v$sqlarea WHERE sql_text NOT LIKE '%$%' AND command_type IN(2,3,6,7) ORDER BY disk_reads DESC;
7) Shard Pool 히트율
SELECT SUM(gets) " Gets", SUM(getmisses) "Cache Get Misses", SUM(getmisses)/SUM(gets)*100 "히트율" FROM v$rowcache;

5. Large Pool 메모리 상태

SELECT pool, name, bytes FROM v$sgastat WHERE pool = 'large pool';

6. 프로세스 상태

1) DBWR 정보
SELECT spid, program, username, serial#, terminal FROM v$process WHERE program LIKE '%DB%';
2) DBWR 대기상태
SELECT event, total_waits, total_timeouts, time_waited, average_wait FROM v$system_event WHERE event = 'free buffer waits';

3) CKPT 정보

SELECT spid, program, username, serial#, terminal FROM v$process WHERE program LIKE '%CKPT%';
4) CKPT 대기상태
SELECT event, total_waits, time_waited, average_wait FROM v$system_event WHERE event LIKE 'log file switch (checkpoint%';
5) LGWR 정보
SELECT spid, program, username, serial#, terminal FROM v$process WHERE program LIKE '%LGWR%';
6) LGWR 대기상태
SELECT sid, event, seconds_in_wait, state FROM v$session_wait WHERE event = 'log buffer space%';
7) PMON 정보
SELECT spid, program, username, serial#, terminal FROM v$process WHERE program LIKE '%PMON%';
8) PMON 대기상태
SELECT event, total_waits, total_timeouts, time_waited, average_wait FROM v$system_event WHERE event = 'pmon timer';
9) SMON 정보
SELECT spid, program, username, serial#, terminal FROM v$process WHERE program LIKE '%SMON%';
10) SMON 대기상태
SELECT event, total_waits, total_timeouts, time_waited, average_wait FROM v$system_event WHERE event = 'smon timer';

7. 데이터 파일 상태

1) Data-File 구조
SELECT B.file_id "File #", B.file_name, B.tablespace_name, B.bytes "Bytes", ((B.bytes - sum(nvl(A.bytes,0)))) "Used Space", (SUM(NVL(A.bytes,0))) "Free Space", (SUM(NVL(A.bytes,0)) / (B.bytes)) * 100 "FreeSpace Ratio" FROM sys.dba_free_space A, sys.dba_data_files B WHERE A.file_id(+) = B.file_id GROUP BY B.tablespace_name, B.file_id, B.file_name, B.bytes ORDER BY B.file_id;
2) Disk I/O 상태
select name, phyrds, phywrts FROM v$datafile DF, v$filestat FS WHERE DF.file# = FS.file#;
3) Object 종류
SELECT A.owner, A.object_id, A.object_name, B.partition_name, B.tablespace_name, B.bytes, B.blocks, B.extents, B.initial_extent, B.next_extent, B.min_extents, B.max_extents, B.pct_increase, B.freelists, B.relative_fno, B.buffer_pool, A.created, A.status FROM dba_objects A, dba_segments B WHERE A.owner = 'SCOTT' AND A.object_type = 'TABLE' ORDER BY A.object_name;
4) 롤백세그먼트 경합상태
SELECT SUM(waits) "Waits", SUM(gets) "Gets", 100 * SUM(waits)/SUM(gets) "히트율" FROM v$rollstat;
5) 롤백세그먼트 대기상태
SELECT (SELECT count FROM v$waitstat WHERE class = 'undo header') "Undo Header", (SELECT count FROM v$waitstat WHERE class = 'undo block') "Undo Block", (SELECT count FROM v$waitstat WHERE class = 'system undo header') "System Undo Header", (SELECT count FROM v$waitstat WHERE class = 'system undo block') "System Undo block" FROM dual;

6) Temp 세그먼트 경함상태
SELECT username, user, contents, segtype, extents, blocks FROM v$sort_usage;
7) Lock 상태(Holder & Waiter)
SELECT LPAD(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type FROM v$lock WHERE id1 IN (SELECT id1 FROM v$lock WHERE lmode = 0) ORDER BY id1, request;
8) Lock 상태(Waiters)
SELECT LPAD(' ',DECODE(C.request,0,0,1))||C.sid sess, B.username, B.program, C.type, A.sql_text SQL FROM v$sqlarea A,v$session B, v$lock C, v$access D WHERE C.id1 IN (SELECT id1 FROM v$lock WHERE lmode = 0 OR lmode = 6) AND A.address(+) = B.sql_address AND A.hash_value(+) = B.sql_hash_value AND B.sid = D.sid AND B.sid = C.sid AND D.sid = C.sid AND D.owner != 'SYS' ORDER BY C.id1, C.request;
9) DB 사용자
SELECT username, default_tablespace, temporary_tablespace, account_status, expiry_date, lock_date, profile FROM dba_users;

8. Control 파일 구조

SELECT * FROM v$controlfile;

9. 리두로그 파일확인

1) 파일 구조
SELECT A.group# col1, bytes / 1024 col2, members col3, member col4 , A.status col5, sequence# FROM v$log A, v$logfile B WHERE A.group# = B.group#;
2) 대기 상태
SELECT event, total_waits, time_waited, average_wait FROM v$system_event WHERE event LIKE 'log file switch completion%';

10. 아카이브로그 확인

1) 로그 설정 상태
SELECT value FROM v$parameter WHERE name = 'log_archive_start';

2) 아카이브 파일
SELECT name, sequence#, first_change#, first_time, next_change#, next_time, archived, status FROM v$archived_log;

11. 파라메터 확인

SELECT name, type, value, isdefault FROM v$parameter;

ALTER PROFILE "DEFAULT" LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL;

비밀번호 관리

암호관리란?

더 뛰어난 데이터베이스 보안을 위해서 관리자가 프로파일로 오라클 암호를 관리 제어 할 수 있는 기능을 말합니다.

암호관리의 기능

- 계정 잠금(Accont locking) : 계정잠금 이란 사용자가 지정된 시도 횟수 내에 시스템에 로그인하지 못한 경우 자동적으로 계정을 잠그는 기능을 말합니다.

- 암호의 노화(aging)와 기간 만료(expire) : 암호가 수명을 갖도록 하여 그 기간이 지나면 암호를 변경 하도록 설정하는 기능 입니다.

- 암호의 현재까지의 기록(Password history) : 암호를 검사하여 지정된 시간 동안이나 지정된 횟수 이상 사용되지 않도록 설정 할 수 있습니다.

- 암호의 복잡성 검증(Password complexity verification) : 추측된 암호를 사용하지 못하도록 암호를 충분히 복잡하게 설정 할 수 있는 기능 입니다.

암호관리를 사용하는 방법

- 프로파일을 사용자에게 지정하여 암호관리를 설정 합니다.

- CREATE USER나 ALTER USER 명령을 사용하여 계정을 잠그거나 잠금을 해제하고 기간을 만료 할 수 있습니다.

- 인스턴스의 RESOURCE_LIMIT가 FALSE로 설정되어 있더라도 암호제한은 항상 시행 됩니다.

- 자원 제한 설정과 비슷하게 CREATE나 ALTER USER 명령을 사용하여 암호 설정을 제한하도록 프로파일을 생성하여 사용자에게 지정할 수 있습니다.

- SESSIONS_PER_USER 같은 다른 제한들은 초기화 파라미터나 ALTER SYSTEM 명령으로 자원 제한이 enable되어 있을 때에만 시행되는 반면, 프로파일의 암호 설정은 항상 시행 됩니다.

- 암호 관리가 enable되면 사용자 계정은 CREATE USER나 ALTER USER 명령을 사용하여 잠그거나 잠금을 해제할 수 있습니다.

암호 설정 방법

아래와 같이 프로파일을 생성 하고 그 프로파일을 암호설정을 적용할 유저에게 부여하면 됩니다.

SQL> CREATE PROFILE scott_password

LIMIT FAILED_LOGIN_ATTEMPTS 3 ---> 로그인 실패 횟수

PASSWORD_LIFE_TIME 30 ---> 기간만료일

PASSWORD_REUSE_TIME 30 ---> 암호가 재사용 될 때까지의 날 수

PASSWORD_VERIFY_FUNCTION verify_function ---> 복합성 검사를 수행할 PL/SQL

PASSWORD_GRACE_TIME 5 ; ---> 유예기간

암호 설정 관련 파라미터

암호 노화(Aging) 기간 만료

- PASSWORD_LIFE_TIME 파라미터는 패스워드의 수명을 설정 합니다. 이 기간이 지나면 암호를 변경해야 합니다.

- 데이터베이스 관리자는 유예 기간(PASSWORD_GRACE_TIME)을 지정할 수 있습니다. 이 기간은 암호가 기간 만료된 후 데이터베이스에 처음 접속한 때부터 시작 됩니다.

- 유예 기간이 끝날 때까지는 사용자가 매번 접속하려 할 때마다 경고 메시지가 발생 하며, 사용자는 유예 기간 내에 암호를 변경해야 합니다. 암호가 변경되지 않으면 계정은 잠깁니다.

- 암호를 기간 만료된 것으로 명시적으로 설정하면 사용자의 계정 상태는 EXPIRED로 변경됩니다. 이렇게 되면, 사용자가 로그인 할 때 계정은 유예 기간으로 넘어 갑니다.

암호의 현재까지의 기록

- 암호의 현재까지의 기록은 사용자가 지정된 기간 동안 암호를 재사용하지 못하게 합니다. 아래의 한 가지 방법으로 구현할 수 있습니다.

- PASSWORD_REUSE_TIME : 주어진 날 수 동안 암호를 재사용할 수 없도록 지정.

- PASSWORD_REUSE_MAX : 예전에 사용한 암호와 동일한 것을 사용하지 못하도록 시행.

Password Verification Function VERIFY_FUNCTION

- 사용자에게 암호를 할당하기 전에 암호의 유효성을 검증 할 수 있습니다.

- 암호의 최소길이는 네문자 입니다.

- 암호는 사용자 이름과 같아서는 안된다.

- 암호는 최소한 하나의 알파벳,하나의 수자,그리고 하나의 특수 문자를 가져야한다.

- 암호는 예전에 사용했던 암호와 최소한 세문자는 달라야한다.

- utlpwdmg.sql 스크립트에 의해 VERIFY_FUNCTION이라는 디폴트 PL/SQL 함수 형태로 제공되며 반드시 SYS 스키마에서 실행되어야 합니다.

- 디폴트 profile의 PASSWORD_VERIFY_FUNCTION 기능을 사용하지 않으려면 아래의 명령어를 실행하면 됩니다.

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;

계정 잠금과 암호 제어(Controlling Account Lock and Password)

ALTER USER 명령을 사용하여 암호를 변경하고 계정을 잠글 수 있습니다. 이와 같은 작업이 필요한 경우는 다음과 같습니다.

• - 사용자가 암호를 잃어버려서 암호를 재설정하고자 할 때
• - 시스템에 의해 잠겨진 사용자 계정을 풀 때
• - 계정을 명시적으로 잠글 때
• - 수동으로 암호를 만료되게 할 때, 사용자 암호를 재설정할 때 유용합니다.

암호 만료후 재설정 문법

ALTER USER user

[ IDENTIFIED {BY password | EXTERNALLY } ]

[ PASSWORD EXPIRE ]

[ ACCOUNT { LOCK | UNLOCK } ] ;

암호 만료후 재설정 예제

SQL> ALTER USER scott

IDENTIFIED BY puhaha

PASSWORD EXPIRE ;

사용자가 로그인한 상태라면 암호 변경, 만료, 잠금은 현재의 세션에는 영향을 주지 않습니다. 사용자 계정이 잠겨져 있는데 접속하려 하면 아래와 같은 메세지가 출력 됩니다

ERROR:

ORA-28000: the account is locked

Warning: You are no longer connected to ORACL