Curious Developing

* CTL FIle Format(reference by http://technet.tmaxsoft.com) LOAD DATA [CHARACTERSET ASCII|EUCKR|MSWIN949|UTF8|SJIS] [INFILE data_file_name] [LOGFILE log_file_name] [BADFILE bad_file_name] [APPEND|REPLACE|TRUNCATE|MERGE(column_name, .....)] [PRESERVE BLANKS] INTO TABLE table_name [MULTI INSERT INDEXES|FAST BUILD INDEXES] [FIELDS [TERMINATED BY field_terminator] [OPTIONALLY ENCLOSED BY enclosed_by..

select a.tablespace_name, round(a.bytes_alloc / 1024 / 1024, 2) as current_size ,round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) as free_size ,round(a.bytes_alloc - nvl(b.bytes_free, 0) / 1024 / 1024, 2) as used_si ,round((nvl(b.bytes_free, 0) / a.bytes_alloc)*100, 2) as free_rate ,100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc)*100, 2) as used_rate -- ,round(a.MAXBYTES/ 1024 / 1024, 2) as curre..

select * from dba_users; -- 계정확인 select * from dba_role_privs -- 롤 확인 where 1=1 ; select * from dba_role_privs where grantee = 'BBB'; -- 사용자에게 부여된 롤 확인 select * from dba_tab_privs -- 객체 권한이 주어졌는지 확인 where 1=1 and grantee = 'ROLE_TTT' and owner = 'AAA'; SELECT * FROM dba_sys_privs; -- sys 권한 확인 SELECT * FROM dba_tab_privs WHERE table_name = 'TTTTT' -- 테이블 권한 ; -- role 부여 / 회수 GRANT ROLE_TTT TO PF..

SELECT * FROM dba_ind_partitions -- 그냥 index면 dba_indexes WHERE index_owner = 'AAA' AND status = 'UNUSABLE' --ALTER INDEX AA_IDX REBUILD PARTITION SYS_P313859; --ALTER INDEX AA_IDX2 REBUILD PARTITION P2019_07 ONLINE PARALLEL 32 NOLOGGING SELECT 'ALTER INDEX ' || index_name || ' REBUILD PARTITION '|| PARTITION_NAME || ';' AS ALTER_STMT FROM dba_ind_partitions -- 그냥 index면 dba_indexes WHERE index_..

* 순서 : listener 종료->DB종료->클러스터종료->클러스터기동->DB기동->listener기동 * 명령어 srvctl status listener srvctl stop listener -L {리스너이름} -n {rac} srvctl stop database -d {DB이름} -o immediate srvctl stop instance -d {DB이름} -i {instance이름} -o immediate srvctl stop asm -n {노드이름} -i {ASM instance이름} -o immediate

■ TEMP Tablespace 를 사용하는 경우 #PGA 내에서 처리 불가할 경우 사용 - sort based operation (order by, group by rollup, window function) - hash join - merge join - bitmap merge - bitmap create #항상 사용 - 쿼리내 meterialize view - dual table ■ TEMP Tablespace 사용 쿼리 모니터링 #AWR SNAP 별 TMEP 사용량 확인 select begin_time, end_time, instance_number, round(maxval/1024/1024) temp_mb from dba_hist_sysmetric_summary where metric_name ..

■ UNDO tablespace 크기 설정 #UNDO tablespace 크기를 설정하기 윈해서는 다음 3가지 값이 필요합니다. 1) UNDO_RETENTION 2) DB_BLOCK_SIZE 3) 초당 생성되는 undo block 수 #위 3가지 값을 이용하여 Optimal 한 값을 확인할 수 있습니다. select round(((ur*(ups*dbs))+(dbs*24))/1024/1024,1) as mb from (select value as ur from v$parameter where name = 'undo_retention') ,(select value as dbs from v$parameter where name = 'db_block_size') ,(select (sum(undoblks)/sum..

오래전 인터넷 돌아다니다가 발견했고, 그 이후 추가 추가 했던 것들. --====================================================================================== --#. 01 테이블스페이스별 파일 목록을 보기 --====================================================================================== SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS "테이블스페이스" ,SUBSTRB(FILE_NAME, 1, 50) AS "파일명" ,TO_CHAR(BLOCKS, '999,999,990') AS "블럭수" ,TO_CHAR(BYTES, '99,999,9..

- 매일 적재량 select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 size_MB from v$ARCHIVED_LOG group by trunc(completion_time); - 로그파일 사이즈 select a.group#, a.thread#, a.sequence#, a.archived, a.status, b.member as relog_file_name, a.bytes/1024/1024 as size_mb from v$log a inner join v$logfile b on a.group#=b.group# order by a.group# asc; - 파일사이즈 조정 alter system checkpoint; alter dat..

SELECT NAME, value$ FROM sys.props$ WHERE NAME LIKE '%CHARACTERSET%' ; --type 1의 charsetform - 1: varchar2, 2: nvarchar2 --type 2의 charsetform - 1: varchar 2: nchar varying --type 96의 charsetform - 1: char 2: nchar --type 112의 charsetform - 1: clob 2: nclob select distinct(nls_charset_name(charsetid)) CHARACTERSET, type#, charsetform from sys.col$ where charsetform in (1,2) and type# in (1,9,96,..