본문 바로가기
RDBMS

Oracle SQL

디마스터 2021. 12. 28. 16:22

테이블 목록 조회

더보기

 

SELECT
	A.OWNER,
	A.TABLE_NAME,
	A.TABLESPACE_NAME,
	A.STATUS,
	A.LAST_ANALYZED,
	A.PARTITIONED,
	C.COLUMNS_CNT,
	A.NUM_ROWS,
	B.MB
FROM ALL_TABLES A,
	(
	SELECT
		SEGMENT_NAME,
		SUM(BYTES)/ 1024 / 1024 AS MB
	FROM user_segments
	WHERE 1 = 1
		AND SEGMENT_TYPE IN('TABLE', 'INDEX')
	GROUP BY
		SEGMENT_NAME,
		SEGMENT_TYPE )B,
	(
	SELECT
		TABLE_NAME,
		COUNT(TABLE_NAME)AS COLUMNS_CNT
	FROM ALL_COL_COMMENTS
	GROUP BY TABLE_NAME ) C
WHERE
	A.OWNER = '계정'
	AND A.TABLE_NAME = B.SEGMENT_NAME
	AND A.TABLE_NAME = C.TABLE_NAME
ORDER BY OWNER, TABLE_NAME ;

테이블 상세 조회

더보기

 

SELECT
	CN.*,
CASE
		WHEN PK.CONSTRAINT_TYPE = 'P' THEN 'PK'
		ELSE PK.CONSTRAINT_TYPE
	END PK
FROM
	(
	SELECT
		A.OWNER,
		A.TABLE_NAME,
		A.COLUMN_NAME,
		B.COMMENTS,
		A.COLUMN_ID,
		A.DATA_TYPE || (
			CASE WHEN DATA_TYPE LIKE '%CHAR%' THEN '(' || DATA_LENGTH || ')'
			WHEN DATA_TYPE = 'NUMBER'
			AND DATA_PRECISION > 0
			AND DATA_SCALE > 0 THEN '(' || DATA_PRECISION || ',' || DATA_SCALE || ')'
			WHEN DATA_TYPE = 'NUMBER'
			AND DATA_PRECISION > 0 THEN '(' || DATA_PRECISION || ')'
		END) DATA_TYPE,
		A.NULLABLE
	FROM
		ALL_TAB_COLUMNS A ,
		ALL_COL_COMMENTS B
	WHERE
		A.OWNER = 'NTIC'
		AND A.TABLE_NAME = B.TABLE_NAME
		AND A.COLUMN_NAME = B.COLUMN_NAME ) CN
LEFT OUTER JOIN (
	SELECT
		A.TABLE_NAME ,
		B.COLUMN_NAME ,
		A.CONSTRAINT_TYPE
	FROM
		ALL_CONSTRAINTS A ,
		ALL_CONS_COLUMNS B
	WHERE
		A.CONSTRAINT_TYPE = 'P'
		AND A.OWNER = B.OWNER
		AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME ) PK ON
	CN.TABLE_NAME = PK.TABLE_NAME
	AND CN.COLUMN_NAME = PK.COLUMN_NAME
ORDER BY
	CN.TABLE_NAME,
	CN.COLUMN_ID ;

 

파티션 별 인덱스 unusable 및 rebuild 변환

더보기

 

CREATE OR REPLACE PROCEDURE 계정.partition_index_unusable (
    p_table_name   IN   VARCHAR2,
    p_start        IN   VARCHAR2,
    p_end          IN   VARCHAR2
) IS

    v_day          VARCHAR2(10);
    v_table_name   VARCHAR2(100);
    v_sql          VARCHAR2(4000);
    v_start        VARCHAR2(20);
    v_end          VARCHAR2(20);
    v_err_msg      VARCHAR2(100);
    v_code         NUMBER;
    v_errm         VARCHAR2(64);
BEGIN
    dbms_output.enable;
    v_table_name := substr(p_table_name, 3, 100);
    v_day := p_start;
    v_day := to_char(to_date(v_day, 'yyyymmdd') + 2, 'yyyymmdd'); -- 다른 테이블과 중복되는 일자는 데이터가 겹치지 않도록 인덱스 유지해야 함. 시작일자와 1일 뒤는 인덱스 유지. 
    WHILE ( to_char(to_date(v_day, 'yyyymmdd') + 2, 'yyyymmdd') <= p_end ) -- 지정한 기간 마지막과 마지막 1일 전 파티션은 변경하지 않음. 
     LOOP BEGIN
        v_sql := 'ALTER INDEX 계정.IU01_'|| v_table_name|| ' MODIFY PARTITION P'|| v_day|| ' UNUSABLE';
        dbms_output.put_line(v_sql);
        --EXECUTE IMMEDIATE v_sql;
        v_day := to_char(to_date(v_day, 'yyyymmdd') + 1, 'yyyymmdd'); 
        --dbms_output.put_line(v_day);
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            v_code := sqlcode;
            v_errm := substr(sqlerrm, 1, 64);
            v_err_msg := v_code|| '-'|| v_errm;
    END;
    END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE 계정.partition_index_rebuild (
    p_table_name   IN   VARCHAR2,
    p_start        IN   VARCHAR2,
    p_end          IN   VARCHAR2
) IS

    v_day          VARCHAR2(10);
    v_table_name   VARCHAR2(100);
    v_sql          VARCHAR2(4000);
    v_start        VARCHAR2(20);
    v_end          VARCHAR2(20);
    v_err_msg      VARCHAR2(100);
    v_code         NUMBER;
    v_errm         VARCHAR2(64);
BEGIN
    dbms_output.enable;
    v_table_name := substr(p_table_name, 3, 100);
    v_day := p_start;
    v_day := to_char(to_date(v_day, 'yyyymmdd') + 2, 'yyyymmdd'); -- 다른 테이블과 중복되는 일자는 데이터가 겹치지 않도록 인덱스 유지해야 함. 시작일자와 1일 뒤는 인덱스 유지. 
    WHILE ( to_char(to_date(v_day, 'yyyymmdd') + 2, 'yyyymmdd') <= p_end ) -- 지정한 기간 마지막과 마지막 1일 전 파티션은 변경하지 않음. 
     LOOP BEGIN
        v_sql := 'ALTER INDEX 계정.IU01_'
                 || v_table_name
                 || ' REBUILD PARTITION P'
                 || v_day
                 || ' ONLINE';
        dbms_output.put_line(v_sql);
        --EXECUTE IMMEDIATE v_sql;
        v_day := to_char(to_date(v_day, 'yyyymmdd') + 1, 'yyyymmdd'); 
        --dbms_output.put_line(v_day);
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            v_code := sqlcode;
            v_errm := substr(sqlerrm, 1, 64);
            v_err_msg := v_code|| '-'|| v_errm;
    END;
    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        v_code := sqlcode;
        v_errm := substr(sqlerrm, 1, 64);
        v_err_msg := v_code|| '-'|| v_errm;
END;
/

 

실행된 SQL 중 1분이 넘는 수행시간인 SQL 조회

더보기

 

SELECT
	--*
 sql_fulltext,
	SQL_text,
	FIRST_LOAD_TIME,
	last_load_time,
	executions,
	round(ELAPSED_TIME / 1000000 / executions / 60) "average min" ,
	sql_id,
	parsing_schema_name ,
	ELAPSED_TIME / 1000 sec
FROM
	v$sql
WHERE
	1 = 1
	--AND upper(sql_fulltext) LIKE '%T_CNE_VILL_FRCST_M%' AND PARSING_SCHEMA_NAME = '계정'
	AND sql_text NOT LIKE '%v$sql%'
	AND sql_text NOT LIKE 'EXPLAIN%'
	AND PARSING_SCHEMA_NAME <> 'SYS'
	AND executions > 0
	AND round(ELAPSED_TIME / 1000000 / executions / 60) > 1
	--AND sql_text LIKE 'LOCK %'
	ORDER BY last_load_time DESC;

 

TBS 데이터파일 자동 추가

더보기

 

CREATE OR REPLACE PROCEDURE 계정.tbs_datafile_add IS

    v_sql       VARCHAR2(4000);
    v_err_msg   VARCHAR2(100);
    v_code      NUMBER;
    v_errm      VARCHAR2(64);

-- 데이터 파일 자동 추가 v1.1 SQL
    CURSOR c_list IS
--select 'alter tablespace ' || df.tablespace_name || ' add datafile ''' ||
    SELECT
        'alter tablespace '|| df.tablespace_name|| ' add datafile ''F'||
--       substr(df.file_name, 1, length(df.file_name) - 7) ||
         substr(df.file_name, 2, length(df.file_name) - 8)|| substr('000'|| to_char(fno.f_cnt + 1), length('000'|| to_char(fno.f_cnt + 1)) - 2, 3)
        || '.dbf'' size 1M AUTOEXTEND ON NEXT 50M MAXSIZE unlimited' AS add_datafile
--      , df.TABLESPACE_NAME
--      , df.FILE_NAME
--  ,round(df.BYTES / 1024 / 1024 / 1024, 0) as GB
    FROM
        dba_data_files df
        JOIN (
            SELECT
                tablespace_name,
                COUNT(*) AS f_cnt,
                MAX(file_id) m_file_id
            FROM
                dba_data_files t
            WHERE
                tablespace_name NOT IN ('??','??'
                )
                AND ( t.tablespace_name LIKE '??_%' OR t.tablespace_name LIKE 'PT_%' )
            GROUP BY
                tablespace_name
        ) fno ON df.file_id = fno.m_file_id
    WHERE
        1 = 1
        AND df.tablespace_name NOT IN ('??','??'
        )
--  and round(df.BYTES / 1024 / 1024 / 1024, 0) > 24
        AND df.bytes / df.maxbytes * 100 > 85
        AND df.autoextensible = 'YES';

BEGIN
    dbms_output.enable;
    OPEN c_list;
    LOOP BEGIN
        FETCH c_list INTO v_sql;
        EXIT WHEN c_list%notfound;
-- 조회 구문 생성
--            dbms_output.put_line(v_sql);
        INSERT INTO 계정.tbs_job_log (
            job_name,
            start_dt,
            dml_script
        ) VALUES (
            'tbs_datafile_add',
            sysdate,
            v_sql
        );

        EXECUTE IMMEDIATE v_sql;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            v_code := sqlcode;
            v_errm := substr(sqlerrm, 1, 64);v_err_msg := v_code|| '-'|| v_errm;
    END;
    END LOOP;

    CLOSE c_list;
END;

 

아래는 자동 생성 수행 내역 로깅 테이블

 (PK나 인덱스 설정 빠짐)

CREATE TABLE "계정"."TBS_JOB_LOG" 
   (	"JOB_NAME" VARCHAR2(300), 
	"START_DT" DATE, 
	"DML_SCRIPT" VARCHAR2(4000)
   ) SEGMENT CREATION IMMEDIATE 
  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 "??" ;

 

각 시간의 분을 5분 단위로 맞추고 싶을 때 

더보기

 

SELECT to_date('0013','hh24mi')
, to_date(floor(to_char(to_date('0013','hh24mi'), 'sssss')/300)*300,'sssss')
, to_char(to_date(floor(to_char(to_date(creat_hm, 'hh24mi'), 'sssss')/300)*300,'sssss'),'hh24mi')
FROM dual;

 

현재 Lock 상태인 오브젝트 확인

더보기

 

SELECT DISTINCT
    t1.session_id,
    t2.serial#,
    t4.object_name,
    t2.machine,
    t2.terminal,
    t2.program,
    t3.address,
    t3.piece,
    t3.sql_text
FROM
    v$locked_object   t1,
    v$session         t2,
    v$sqltext         t3,
    dba_objects       t4
WHERE
    1 = 1
    AND t1.session_id = t2.sid
    AND t1.object_id = t4.object_id
    AND t2.sql_address = t3.address
ORDER BY
    t3.address,
    t3.piece;

 

테이블 통계정보 갱신

더보기

 

EXEC DBMS_STATS.GATHER_TABLE_STATS('계정', '테이블');

 

unsable 상태인 파티션 인덱스 찾기

더보기

 

select index_name name,'No Partition' partition,'No Subpartition' Subpartition,status from dba_indexes where status not in('VALID','USABLE','N/A')
union
select index_name name,partition_name partition,'No Subpartition' Subpartition,status from dba_ind_partitions where status not in('VALID','USABLE','N/A')
union
select index_name name,partition_name partition,subpartition_name Subpartition,status from dba_ind_subpartitions where status not in('VALID','USABLE','N/A');

 

계정별 이벤트 및 대기시간

더보기

 

SELECT
	/*+ ordered / distinct * 속도를 위해 v$sql을 조인할 경우 중복되는 레코드 제거 */
	s.sid SID,
	s.username,
	s.program,
	p.spid "OS-Pid",
	w.seconds_in_wait AS "W_time(Sec)",
	decode(w.wait_time, 0, 'Wai-ting', 'Waited') Status,
	w.ename event,
	--—              p1text || ':' || decode(event,'latch free',p1raw, to_char(p1)) ||','||
	--—              p2text || ':' || to_char(p2) ||','|| p3text || ':' || to_char(p3) "Additional Info",
 q.sql_text
FROM
	(
	SELECT
		a.*,
		decode(a.event, 'latch free', 'latch free (' || b.name || ')', 'row cache lock', 'row cache lock (' || c.parameter || ')', 'enqueue', 'enqueue (' || chr(bitand(p1, -16777216)/ 16777215)|| chr(bitand(p1, 16711680)/ 65535)|| ':' || decode(bitand(p1, 65535), 1, 'N', 2, 'SS', 3, 'SX', 4, 'S', 5, 'SSX', 6, 'X') || ')', a.event ) ename
	FROM
		v$session_wait a,
		v$latchname b,
		v$rowcache c
	WHERE
		a.p2 = b.latch#(+)
		AND a.p1 = c.cache#(+)
		AND c.type(+) = 'PARENT'
		AND a.event NOT IN ('rdbms ipc message', 'smon timer', 'pmon timer', 'slave wait', 'pipe get', 'null event', 'SQL*Net message from client', 'SQL*Net message to client', 'PX Idle Wait', 'PX Deq: Execution Msg', 'KXFQ: kxfqdeq – normal deqeue', 'ges remote message', 'wakeup time manager', /* idle event 적절히 수정 */
		'lock manager wait for remote message', 'single-task message') ) w,
	v$session s,
	v$process p,
	v$sql q
WHERE
	w.sid = s.sid
	AND s.paddr = p.addr
	AND s.sql_hash_value = q.hash_value(+)
	AND s.sql_address = q.address(+)
ORDER BY w.ename;

DB에서 alert.log 확인하기

더보기

 

SELECT	*
FROM
	sys.V_$DIAG_ALERT_EXT
	--WHERE ORIGINATING_TIMESTAMP 
	--BETWEEN to_date ('2021-12-08 22:04:55','yyyy-mm-dd hh24:mi:ss') AND to_date('2021-12-08 22:04:56', 'yyyy-mm-dd hh24:mi:ss')
	--22:04:55.294000 +09:00

	ORDER BY ORIGINATING_TIMESTAMP DESC;

 

특정 테이블의 인덱스 및 TBS 조회

더보기

 

SELECT
	I.INDEX_NAME,
	PARTITION_NAME,
CASE
		WHEN IP.PARTITION_NAME IS NULL THEN I.STATUS
		ELSE IP.STATUS
	END,
	NVL(I.TABLESPACE_NAME, IP.TABLESPACE_NAME)
FROM
	DBA_INDEXES I
LEFT JOIN DBA_IND_PARTITIONS IP ON
	I.INDEX_NAME = IP.INDEX_NAME
WHERE
	I.TABLE_NAME IN ('CHJ_TEST', 'CHJ_TEST_ORI');

Online index rebuild 실패 시

더보기

 

Select OWNER,OBJECT_NAME,OBJECT_ID, OBJECT_TYPE 
	from dba_objects 
    WHERE OWNER = '계정'
		AND object_name like '%_JOURNAL%';
-- 위 SQL 을 통해 오류가 있는 저널 오브젝트 ID를 확인한다. 

-- 아래처럼 해당 오브젝트를 지우면 복구 처리가 어려워진다. 
-- DROP TABLE NTIC_HSM.SYS_JOURNAL_???????;

-- 확인된 오브젝트ID를 넣어서 아래와 같이 수행한다. 
exec DBMS_REPAIR.ONLINE_INDEX_CLEAN ([확인된 object_id], DBMS_REPAIR.LOCK_WAIT);

-- 또는 아래와 같이 수행한다. 
declare
  result boolean := false;
begin
  result := dbms_repair.online_index_clean([확인된 object_id], DBMS_REPAIR.LOCK_WAIT);
  if result then
    dbms_output.put_line('result= True');
  else
    dbms_output.put_line('result= False');
  end if;
end;
/

Oracle scheduler 등록

더보기 
BEGIN
	DBMS_SCHEDULER.CREATE_JOB(
        JOB_NAME => 'HOURLY_TBS_DISKUSAGE',
        JOB_TYPE => 'STORED_PROCEDURE',
        JOB_ACTION => 'NTIC_DBA.DBA_CHK_TBS_DISK_USAGE',
        NUMBER_OF_ARGUMENTS => 0,
        START_DATE => SYSDATE,
        -- START_DATE => TO_DATE('20220203 10:00','YYYYMMDD HH24:MI'),
        --REPEAT_INTERVAL => 'FREQ=HOURLY;INTERVAL=1',
        REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=1',
        ENABLED => TRUE,
        COMMENTS => 'Every 1 hour. Check TBS and disk usage. Print out on alert.log'
	);
END;
/

 

 

AWS RDS 디스크 사용 내역

더보기

 

select
	'===========================================================' || chr(10) ||
	'Total Database Physical Size = ' || round(redolog_size_gb+dbfiles_size_gb+tempfiles_size_gb+ctlfiles_size_gb,2) || ' GB' || chr(10) ||
	'===========================================================' || chr(10) ||
	' Redo Logs Size : ' || round(redolog_size_gb,3) || ' GB' || chr(10) ||
	' Data Files Size : ' || round(dbfiles_size_gb,3) || ' GB' || chr(10) ||
	' Temp Files Size : ' || round(tempfiles_size_gb,3) || ' GB' || chr(10) ||
	' Archive Log Size - Approx only : ' || round(archlog_size_gb,3) || ' GB' || chr(10) ||
	' Control Files Size : ' || round(ctlfiles_size_gb,3) || ' GB' || chr(10) ||
	'===========================================================' || chr(10) ||
	' Used Database Size : ' || used_db_size_gb || ' GB' || chr(10) ||
	' Free Database Size : ' || free_db_size_gb || ' GB' ||chr(10) ||
	' Data Pump Directory Size : ' || dpump_db_size_gb || ' GB' || chr(10) ||
	' BDUMP Directory Size : ' || bdump_db_size_gb || ' GB' || chr(10) ||
	' ADUMP Directory Size : ' || adump_db_size_gb || ' GB' || chr(10) ||
	'===========================================================' || chr(10) ||
	'Total Size (including Dump and Log Files) = ' || round(round(redolog_size_gb,2) +round(dbfiles_size_gb,2)+round(tempfiles_size_gb,2)+round(ctlfiles_size_gb,2) +round(adump_db_size_gb,2) +round(dpump_db_size_gb,2)+round(bdump_db_size_gb,2),2) || ' GB' || chr(10) ||
	'===========================================================' as summary
FROM 
(
	SELECT 
		sys_context('USERENV', 'DB_NAME') db_name,
		(SELECT SUM(bytes) / 1024 / 1024 / 1024 redo_size FROM v$log) redolog_size_gb,
		(SELECT SUM(bytes) / 1024 / 1024 / 1024 data_size FROM dba_data_files) dbfiles_size_gb,
		(SELECT nvl(SUM(bytes), 0) / 1024 / 1024 / 1024 temp_size FROM dba_temp_files) tempfiles_size_gb,
		(SELECT SUM(blocks * block_size / 1024 / 1024 / 1024) size_gb FROM v$archived_log
			WHERE first_time >= SYSDATE - 
			((SELECT value FROM rdsadmin.rds_configuration WHERE name = 'archivelog retention hours') / 24 )) archlog_size_gb,
		(SELECT SUM(block_size * file_size_blks) / 1024 / 1024 / 1024 controlfile_size FROM v$controlfile) ctlfiles_size_gb,
		round(SUM(used.bytes) / 1024 / 1024 / 1024, 3) db_size_gb,
		round(SUM(used.bytes) / 1024 / 1024 / 1024, 3) - round(free.f / 1024 / 1024 / 1024) used_db_size_gb, 
		round(free.f / 1024 / 1024 / 1024, 3) free_db_size_gb,
		(SELECT round(SUM(filesize) / 1024 / 1024 / 1024, 3) FROM TABLE(rdsadmin.rds_file_util.listdir('BDUMP'))) bdump_db_size_gb,
		(SELECT round(SUM(filesize) / 1024 / 1024 / 1024, 3) FROM TABLE(rdsadmin.rds_file_util.listdir('ADUMP'))) adump_db_size_gb,
		(SELECT round(SUM(filesize) / 1024 / 1024 / 1024, 3) FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR'))) dpump_db_size_gb
	FROM (SELECT bytes FROM v$datafile 
			UNION ALL
			SELECT bytes FROM v$tempfile) used, 
		(SELECT SUM(bytes) AS f FROM dba_free_space) FREE GROUP BY free.f
);

AWS RDS Dump 파일 확인 후 삭제하기

더보기

 

SELECT * FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) ORDER by mtime;

-- 위에서 찾은 파일을 특정하여 아래 구문으로 삭제
EXEC utl_file.fremove('DATA_PUMP_DIR','[file name]');

RDS trace 파일 제거

더보기

 

-- 파일 조회
SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir('BDUMP'));
SELECT * FROM rdsadmin.tracefile_listing;

-- trace 파일 직접 읽기
SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP','alert_dbname.log.date'));

-- replica 접속을 한 경우라면 '_' 뒤에 있는 글씨를 BDUMP_ 뒤에 붙이면 경로가 된다. 
SELECT 'BDUMP' || (SELECT regexp_replace(DB_UNIQUE_NAME,'.*(_[A-Z])', '\1') FROM V$DATABASE) AS BDUMP_VARIABLE FROM DUAL;


/* 결과 예시 
BDUMP_VARIABLE
--------------
BDUMP_B

이 경우 아래와 같이 경로는 'BDUMP_B' 가 된다. */ 

SELECT TEXT FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP_B','alert_DATABASE.log.2020-06-23'));

-- 아래의 명령어로 TRACE가 가능하다. 
EXEC rdsadmin.manage_tracefiles.hanganalyze;
EXEC rdsadmin.manage_tracefiles.dump_systemstate;

-- TRACE 파일 삭제
-- 아래는 보관 주기 (분)
EXEC rdsadmin.rdsadmin_util.show_configuration;
NAME:tracefile retention

-- 보관 주기 변경
EXEC rdsadmin.rdsadmin_util.set_configuration('tracefile retention',1440);

-- 5분 전까지의 파일 삭제
EXEC rdsadmin.manage_tracefiles.purge_tracefiles(5);

-- 특정 파일 삭제
EXEC rdsadmin.manage_tracefiles.purge_tracefiles('SCHPOC1_ora_5935');

'RDBMS' 카테고리의 다른 글

Tibero SQL  (0) 2022.01.09
PostgreSQL SQL  (0) 2022.01.09
MSSQL SQL  (0) 2022.01.09
Maria&Mysql SQL  (0) 2022.01.09

'RDBMS' Related Articles

티스토리툴바