OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c 특정 테이블, 인덱스 용량 증가 추이 확인
오라클에서 특정 테이블이나 인덱스의 용량이 많이 증가한 경우 일별 또는 월별로 얼마나 증가했는지 확인하는 쿼리임
쿼리와 샘플 데이터를 만들어 결과까지 같이 확인해봄
참고로 dba_hist_seg_stat 뷰는 awr 라이센스가 별도로 필요하지 않음
라이센스 문서 내용
All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.
The only exception are the views:
DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT.
They can be used without the Oracle Diagnostics Pack license.
참고 : https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html
쿼리
일별 추이 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
set lines 200 pages 1000
col owner for a10
col object_name for a10
select
o.owner,
o.object_name,
to_char(sn.begin_interval_time,'yyyy-mm-dd') start_month,
round(sum(a.space_allocated_delta)/1024) increase_mb,
round(sum(a.logical_reads_delta)/1024) logical_reads_delta_mb,
round(sum(a.db_block_changes_delta)/1024) db_block_changes_delta_mb,
round(sum(a.physical_writes_delta)/1024) physical_writes_delta_mb
from dba_hist_seg_stat a,
dba_hist_snapshot sn,
dba_objects o
where sn.snap_id = a.snap_id
and o.object_id = a.obj#
and o.object_name = 'TESTTABLE'
and o.object_type in ('TABLE','LOB')
group by o.owner, o.object_name, to_char(sn.begin_interval_time,'yyyy-mm-dd')
order by 3 desc;
|
월별 추이 확인(간소)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
set lines 200 pages 1000
col owner for a10
col object_name for a10
select
o.owner,
o.object_name,
to_char(sn.begin_interval_time,'yyyy-mm-dd') start_month,
round(sum(a.space_allocated_delta)/1024) increase_mb,
round(sum(a.logical_reads_delta)/1024) logical_reads_delta_mb,
round(sum(a.db_block_changes_delta)/1024) db_block_changes_delta_mb,
round(sum(a.physical_writes_delta)/1024) physical_writes_delta_mb
from dba_hist_seg_stat a,
dba_hist_snapshot sn,
dba_objects o
where sn.snap_id = a.snap_id
and o.object_id = a.obj#
and o.object_name = 'TESTTABLE'
and o.object_type in ('TABLE','LOB')
group by o.owner, o.object_name, to_char(sn.begin_interval_time,'yyyy-mm-dd')
order by 3 desc;
|
월별 추이 확인(상세)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
|
$ vi seggrowth.sql
REM
REM $Header: seggrowth.sql 1.0.0 2025/10/07 marcus.v.pedro $
REM
REM Copyright (c) 2025, All rights reserved.
REM
REM AUTHOR
REM Marcus Vinicius Miguel Pedro
REM Accenture Enkitec Group
REM https://www.viniciusdba.com.br/blog
REM https://www.linkedin.com/in/viniciusdba
REM
REM SCRIPT
REM seggrowth.sql
REM
REM DESCRIPTION
REM This script shows the top-20 segments that growh per month
REM
REM PRE-REQUISITES
REM 1. Be granted with SELECT_ANY_DICTIONARY privilege.
REM
REM EXECUTION
REM 1. Connect into SQL*Plus as user with access to data dictionary
REM 2. Execute script seggrowth.sql
REM
REM EXAMPLE
REM # sqlplus system
REM SQL> START seggrowth.sql
REM
REM NOTES
REM 1. This script works on 10g or higher
REM
break on rnk on owner on object_name
SET LINES 200 PAGES 200
COL owner FORMAT A15
COL object_name FORMAT A30
COL start_month FORMAT A7
COL current_size_gb FORMAT 999,999,999.90
COL increase_gbytes FORMAT 999,999,999.90
COL pct_increase FORMAT 999,999.90
COL rnk FORMAT 99
WITH monthly_increases AS (
SELECT
o.owner,
o.object_name,
TO_CHAR(sn.begin_interval_time,'RRRR-MM') start_month,
SUM(a.space_allocated_delta)/1024/1024/1024 increase_gbytes
FROM dba_hist_seg_stat a,
dba_hist_snapshot sn,
dba_objects o
WHERE sn.snap_id = a.snap_id
AND o.object_id = a.obj#
AND o.object_type in ('TABLE','LOB')
GROUP BY o.owner, o.object_name, TO_CHAR(sn.begin_interval_time,'RRRR-MM')
),
current_sizes AS (
SELECT owner,
segment_name object_name,
SUM(bytes)/1024/1024/1024 current_size_gb
FROM dba_segments
WHERE segment_type in ('TABLE','LOBSEGMENT')
GROUP BY owner, segment_name
),
total_increases AS (
SELECT owner, object_name,
SUM(increase_gbytes) total_increase
FROM monthly_increases
GROUP BY owner, object_name
)
SELECT *
FROM (
SELECT
DENSE_RANK() OVER (ORDER BY ti.total_increase DESC) rnk,
mi.owner,
mi.object_name,
mi.start_month,
mi.increase_gbytes,
cs.current_size_gb - NVL(SUM(mi.increase_gbytes) OVER (
PARTITION BY mi.owner, mi.object_name
ORDER BY mi.start_month
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
), 0) current_size_gb,
CASE
WHEN (cs.current_size_gb - NVL(SUM(mi.increase_gbytes) OVER (
PARTITION BY mi.owner, mi.object_name
ORDER BY mi.start_month
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
), 0) - mi.increase_gbytes) > 0
THEN (mi.increase_gbytes / (cs.current_size_gb - NVL(SUM(mi.increase_gbytes) OVER (
PARTITION BY mi.owner, mi.object_name
ORDER BY mi.start_month
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
), 0) - mi.increase_gbytes)) * 100
END pct_increase
FROM monthly_increases mi,
current_sizes cs,
total_increases ti
WHERE mi.owner = cs.owner
AND mi.object_name = cs.object_name
AND mi.owner = ti.owner
AND mi.object_name = ti.object_name
--AND cs.current_size_gb > 5
AND mi.increase_gbytes > 0
)
WHERE rnk <= 20
AND current_size_gb > 0
ORDER BY rnk, owner, object_name, start_month;
|
참고로 106라인 AND cs.current_size_gb > 5 부분 주석을 해제하여 특정 크기 이상인 오브젝트만 체크할 수도 있음
테스트
현재 테스트 db에는 데이터가 없기 때문에 본문과 같이 데이터를 생성하고 os 날짜를 수동으로 변경해주고 매번 awr 스냅샷을 수동으로 찍어 awr 뷰를 확인해봄
먼저 awr 스냅샷 주기는 15분, 보관은 90일로 설정 후 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>60*24*90,interval=>15);
PL/SQL procedure successfully completed.
SQL>
set lines 200 pages 1000
col snap_interval for a30
col retention for a30
select dbid, snap_interval, retention from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION
---------- ------------------------------ ------------------------------
1166340695 +00000 00:15:00.0 +00090 00:00:00.0
|
변경됨
기존 awr 모두 삭제(선택)
|
1
2
3
4
5
6
7
8
9
10
|
--dbid 확인
SQL> select dbid from v$database;
-- 현재 스냅샷 범위 확인
SQL>
select min(snap_id) min_snap, max(snap_id) max_snap
from dba_hist_snapshot;
-- 1부터 50000까지 모두 삭제(dbid는 위에서 조회한 id 입력)
SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 1, high_snap_id => 50000, dbid => 1166340695);
|
OS 날짜 변경
|
1
|
# date -s "2028-01-01 19:00:00"
|
테이블 생성
|
1
2
3
4
5
6
7
8
9
10
|
SQL> create table imsitable as select * from dba_objects;
Table created.
SQL> insert into imsitable select * from imsitable;
/
/
/
/
(반복)
|
용량 확인
|
1
2
3
4
5
6
7
8
|
SQL>
select bytes/1024/1024 mb
from dba_segments
where segment_name ='IMSITABLE';
MB
----------
104
|
대략 100mb가 됨
위 테이블로 테스트용 테이블 생성
|
1
2
3
|
SQL> create table testtable as select * from imsitable;
Table created.
|
스냅샷 수동 생성
|
1
2
3
|
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
|
위와 같이 날짜 변경하고 데이터 삽입 후 스냅샷 생성을 90번 반복해 날짜별 용량 증가량을 볼 예정
자동 스크립트 생성
awr_ins_snap.sql 생성
|
1
2
3
4
5
6
7
|
# su - oracle
$ vi /home/oracle/awr_ins_snap.sql
insert into testtable select * from dba_objects;
commit;
exec dbms_lock.sleep(1);
exec dbms_workload_repository.create_snapshot;
exit
|
shell 파일 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
$ su -
# vi awr_90days.sh
#!/bin/bash
BASE_DATETIME="2027-01-01 19:00:00"
DAYS=90
BASE_EPOCH=$(date -d "$BASE_DATETIME" +%s)
for i in $(seq 1 $DAYS)
do
TARGET_EPOCH=$(( BASE_EPOCH + i*86400 ))
TARGET_DATETIME=$(date -d "@$TARGET_EPOCH" "+%Y-%m-%d %H:%M:%S")
echo "===> 시스템 시간 설정: $TARGET_DATETIME"
date -s "$TARGET_DATETIME"
echo "현재 시스템 시간:"
date "+%Y-%m-%d %H:%M:%S"
echo "===> Oracle 접속 후 testtable insert + AWR 스냅샷 생성"
su - oracle -c "sqlplus -s / as sysdba @/home/oracle/awr_ins_snap.sql"
echo "===> 완료 (day offset: $i)"
echo
done
|
shell 스크립트 권한 부여 및 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
# chmod +x awr_90days.sh
# sh awr_90days.sh
===> 시스템 시간 설정: 2028-01-02 19:00:00
Sun Jan 2 19:00:00 KST 2028
현재 시스템 시간:
2028-01-02 19:00:00
===> Oracle 접속 후 imsitable insert + AWR 스냅샷 생성
63160 rows created.
Commit complete.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
===> 완료 (day offset: 1)
===> 시스템 시간 설정: 2028-01-03 19:00:00
Mon Jan 3 19:00:00 KST 2028
현재 시스템 시간:
2028-01-03 19:00:00
===> Oracle 접속 후 testtable insert + AWR 스냅샷 생성
63160 rows created.
Commit complete.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
===> 완료 (day offset: 2)
..
===> 시스템 시간 설정: 2028-03-31 19:00:00
Fri Mar 31 19:00:00 KST 2028
현재 시스템 시간:
2028-03-31 19:00:00
===> Oracle 접속 후 testtable insert + AWR 스냅샷 생성
63160 rows created.
Commit complete.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
===> 완료 (day offset: 90)
|
완료됨
awr데이터 확인
일별 추이 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
|
SQL>
set lines 200 pages 1000
col owner for a10
col object_name for a10
select
o.owner,
o.object_name,
to_char(sn.begin_interval_time,'yyyy-mm-dd') start_day,
round(sum(a.space_allocated_delta)/1024) increase_mb,
round(sum(a.logical_reads_delta)/1024) logical_reads_delta_mb,
round(sum(a.db_block_changes_delta)/1024) db_block_changes_delta_mb,
round(sum(a.physical_writes_delta)/1024) physical_writes_delta_mb
from dba_hist_seg_stat a,
dba_hist_snapshot sn,
dba_objects o
where sn.snap_id = a.snap_id
and o.object_id = a.obj#
and o.object_name = 'TESTTABLE'
and o.object_type in ('TABLE','LOB')
group by o.owner, o.object_name, to_char(sn.begin_interval_time,'yyyy-mm-dd')
order by 3 desc;
OWNER OBJECT_NAM START_DAY INCREASE_MB LOGICAL_READS_DELTA_MB DB_BLOCK_CHANGES_DELTA_MB PHYSICAL_WRITES_DELTA_MB
---------- ---------- ---------- ----------- ---------------------- ------------------------- ------------------------
SYS TESTTABLE 2028-03-30 16384 9 7 1
SYS TESTTABLE 2028-03-29 8192 8 7 1
SYS TESTTABLE 2028-03-28 8192 8 7 2
SYS TESTTABLE 2028-03-27 8192 8 7 1
SYS TESTTABLE 2028-03-26 8192 8 7 1
SYS TESTTABLE 2028-03-25 8192 8 7 0
SYS TESTTABLE 2028-03-24 8192 8 7 0
SYS TESTTABLE 2028-03-23 8192 8 7 1
SYS TESTTABLE 2028-03-22 8192 8 7 1
SYS TESTTABLE 2028-03-21 16384 8 7 1
SYS TESTTABLE 2028-03-20 8192 8 7 1
SYS TESTTABLE 2028-03-19 8192 8 7 1
SYS TESTTABLE 2028-03-18 8192 8 7 1
SYS TESTTABLE 2028-03-17 8192 9 7 1
SYS TESTTABLE 2028-03-16 8192 8 7 1
SYS TESTTABLE 2028-03-15 8192 8 7 1
SYS TESTTABLE 2028-03-14 8192 8 7 1
SYS TESTTABLE 2028-03-13 16384 8 7 2
SYS TESTTABLE 2028-03-12 8192 9 7 1
SYS TESTTABLE 2028-03-11 8192 8 7 0
SYS TESTTABLE 2028-03-10 8192 8 7 1
SYS TESTTABLE 2028-03-09 8192 8 7 1
SYS TESTTABLE 2028-03-08 8192 8 7 1
SYS TESTTABLE 2028-03-07 8192 8 7 1
SYS TESTTABLE 2028-03-06 8192 9 7 1
SYS TESTTABLE 2028-03-05 16384 8 7 0
SYS TESTTABLE 2028-03-04 8192 9 7 0
SYS TESTTABLE 2028-03-03 8192 8 7 0
SYS TESTTABLE 2028-03-02 8192 8 7 0
SYS TESTTABLE 2028-03-01 8192 8 7 0
SYS TESTTABLE 2028-02-29 8192 9 7 0
SYS TESTTABLE 2028-02-28 8192 8 7 0
SYS TESTTABLE 2028-02-27 8192 8 7 0
SYS TESTTABLE 2028-02-26 8192 8 7 0
SYS TESTTABLE 2028-02-25 16384 8 7 0
SYS TESTTABLE 2028-02-24 8192 8 7 0
SYS TESTTABLE 2028-02-23 8192 8 7 0
SYS TESTTABLE 2028-02-22 8192 8 7 0
SYS TESTTABLE 2028-02-21 8192 9 7 0
SYS TESTTABLE 2028-02-20 8192 8 7 0
SYS TESTTABLE 2028-02-19 8192 9 7 0
SYS TESTTABLE 2028-02-18 8192 8 7 0
SYS TESTTABLE 2028-02-17 16384 8 7 0
SYS TESTTABLE 2028-02-16 8192 8 7 0
SYS TESTTABLE 2028-02-15 8192 8 7 0
SYS TESTTABLE 2028-02-14 8192 8 7 0
SYS TESTTABLE 2028-02-13 8192 9 7 0
SYS TESTTABLE 2028-02-12 8192 9 7 0
SYS TESTTABLE 2028-02-11 8192 8 7 0
SYS TESTTABLE 2028-02-10 8192 8 7 0
SYS TESTTABLE 2028-02-09 16384 9 7 0
SYS TESTTABLE 2028-02-08 8192 8 7 0
SYS TESTTABLE 2028-02-07 8192 8 7 0
SYS TESTTABLE 2028-02-06 8192 8 7 0
SYS TESTTABLE 2028-02-05 8192 8 7 0
SYS TESTTABLE 2028-02-04 8192 9 7 0
SYS TESTTABLE 2028-02-03 8192 8 7 0
SYS TESTTABLE 2028-02-02 8192 8 7 0
SYS TESTTABLE 2028-02-01 8192 8 7 0
SYS TESTTABLE 2028-01-31 16384 9 7 0
SYS TESTTABLE 2028-01-30 8192 8 7 0
SYS TESTTABLE 2028-01-29 8192 8 7 0
SYS TESTTABLE 2028-01-28 8192 9 7 0
SYS TESTTABLE 2028-01-27 8192 9 7 0
SYS TESTTABLE 2028-01-26 8192 8 7 0
SYS TESTTABLE 2028-01-25 8192 8 7 0
SYS TESTTABLE 2028-01-24 8192 8 7 0
SYS TESTTABLE 2028-01-23 16384 8 7 0
SYS TESTTABLE 2028-01-22 8192 8 7 0
SYS TESTTABLE 2028-01-21 8192 8 7 0
SYS TESTTABLE 2028-01-20 8192 8 7 0
SYS TESTTABLE 2028-01-19 8192 8 7 0
SYS TESTTABLE 2028-01-18 8192 8 7 0
SYS TESTTABLE 2028-01-17 8192 8 7 0
SYS TESTTABLE 2028-01-16 8192 8 7 0
SYS TESTTABLE 2028-01-15 8192 8 7 0
SYS TESTTABLE 2028-01-14 16384 8 7 0
SYS TESTTABLE 2028-01-13 8192 8 7 0
SYS TESTTABLE 2028-01-12 8192 8 7 0
SYS TESTTABLE 2028-01-11 8192 8 7 0
SYS TESTTABLE 2028-01-10 8192 8 7 0
SYS TESTTABLE 2028-01-09 8192 8 7 0
SYS TESTTABLE 2028-01-08 8192 9 7 0
SYS TESTTABLE 2028-01-07 8192 8 7 0
SYS TESTTABLE 2028-01-06 16384 8 7 0
SYS TESTTABLE 2028-01-05 8192 9 7 0
SYS TESTTABLE 2028-01-04 8192 8 7 0
SYS TESTTABLE 2028-01-03 8192 8 7 0
SYS TESTTABLE 2028-01-02 8192 8 7 0
SYS TESTTABLE 2028-01-01 8192 8 7 0
90 rows selected.
|
일별 데이터 증가량을 알수 있음
월별 추이 확인(간소)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
SQL>
set lines 200 pages 1000
col owner for a10
col object_name for a10
select
o.owner,
o.object_name,
to_char(sn.begin_interval_time,'yyyy-mm') start_month,
round(sum(a.space_allocated_delta)/1024) increase_mb,
round(sum(a.logical_reads_delta)/1024) logical_reads_delta_mb,
round(sum(a.db_block_changes_delta)/1024) db_block_changes_delta_mb,
round(sum(a.physical_writes_delta)/1024) physical_writes_delta_mb
from dba_hist_seg_stat a,
dba_hist_snapshot sn,
dba_objects o
where sn.snap_id = a.snap_id
and o.object_id = a.obj#
and o.object_name = 'TESTTABLE'
and o.object_type in ('TABLE','LOB')
group by o.owner, o.object_name, to_char(sn.begin_interval_time,'yyyy-mm')
order by 3 desc;
OWNER OBJECT_NAM START_M INCREASE_MB LOGICAL_READS_DELTA_MB DB_BLOCK_CHANGES_DELTA_MB PHYSICAL_WRITES_DELTA_MB
---------- ---------- ------- ----------- ---------------------- ------------------------- ------------------------
SYS TESTTABLE 2028-03 278528 249 210 29
SYS TESTTABLE 2028-02 262144 240 203 0
SYS TESTTABLE 2028-01 286720 258 216 0
|
월별 데이터 증가량을 알수 있음
월별 추이 확인(상세)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL> @seggrowth.sql
RNK OWNER OBJECT_NAME START_M INCREASE_GBYTES CURRENT_SIZE_GB PCT_INCREASE
--- --------------- ------------------------------ ------- --------------- --------------- ------------
1 SYS TESTTABLE 2028-01 .27 .38 269.23 <<--
2028-02 .25 .63 66.67 <<--
2028-03 .27 .89 42.50 <<--
2 SYS WRH$_SHARED_POOL_ADVICE 2028-01 .00 .00 11.11
2028-02 .00 .00 10.00
2028-03 .00 .00 9.09
3 SYS WRH$_MEM_DYNAMIC_COMP 2028-02 .00 .00 12.50
2028-03 .00 .00 11.11
WRH$_RESOURCE_LIMIT 2028-02 .00 .00 14.29
2028-03 .00 .00 12.50
4 SYS WRH$_IOSTAT_DETAIL 2028-03 .00 .00 14.29
WRH$_IOSTAT_FUNCTION 2028-02 .00 .00 25.00
WRH$_JAVA_POOL_ADVICE 2028-03 .00 .00 25.00
WRH$_PGASTAT 2028-03 .00 .00 25.00
WRH$_PGA_TARGET_ADVICE 2028-02 .00 .00 25.00
WRH$_PROCESS_MEMORY_SUMMARY 2028-03 .00 .00 50.00
16 rows selected.
|
월별 데이터 증가량을 알수 있음
테스트 완료 후 db 종료 및 시간 원복
|
1
2
|
# date -s "2025-11-13 19:53:00"
Thu Nov 13 19:53:00 KST 2025
|
결론 :
본문 쿼리를 이용해 테이블이나 인덱스의 크기 증가 현황을 파악할 수 있음
참고1. ORA-13559 에러
awr 스냅샷 수동 생성시, 데이터 삽입 후 바로 commit 하고 스냅샷을 생성하려 하면 아래 에러가 발생했었음
dbms_lock.sleep(1)을 넣어준뒤에는 에러가 발생하지 않았음
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
# sh awr_90days.sh
===> 시스템 시간 설정: 2027-01-02 19:00:00
Sat Jan 2 19:00:00 KST 2027
현재 시스템 시간:
2027-01-02 19:00:00
===> Oracle 접속 후 imsitable insert + AWR 스냅샷 생성
63154 rows created.
Commit complete.
BEGIN dbms_workload_repository.create_snapshot; END;
*
ERROR at line 1:
ORA-13559: CPU time or run time policy violation - AWR snapshot creation exceeded runtime limit of 1800 seconds
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 99
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 145
ORA-06512: at line 1
|
참고2. 당시 alert을 보니 이런 메세지도 남아있었음
시간을 강제로 변경하고 awr을 수집하는 등의 이상한 작업을 해서 컨트롤파일 오토백업에도 문제가 생긴듯함
|
1
2
3
4
5
6
7
8
9
10
11
12
|
$ vi /app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/alert_ORA19DBFS.log
2028-03-10T19:00:01.091167+09:00
******************** WARNING **************************
The errors during server control file autobackup are not
fatal, as it is attempted after sucessful completion of
the command. However, it is recomended to take an RMAN
control file backup as soon as possible because the
autobackup failed with the following error:
ORA-19583: conversation terminated due to error
ORA-12751: cpu time or run time policy violation
******************** END OF WARNING *******************
|
참조 :
https://positivemh.tistory.com/293
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_HIST_SEG_STAT.html
https://viniciusdba.com.br/2025/10/15/finding-top-growing-segments-joelkallmanday/
https://github.com/vinidba/dba_scripts/blob/master/seggrowth.sql
https://www.linkedin.com/posts/saxonchris_dbascriptsseggrowthsql-at-master-vinidba-activity-7394343598969794560-4T0_?utm_source=share&utm_medium=member_android&rcm=ACoAACnc0-YB637ikCAA_0oqCOEB07kHI_OAIrw
https://bae9086.tistory.com/470
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 정적 리스너 이용하여 원격지 db에 접속 및 재시작 (0) | 2025.11.22 |
|---|---|
| 오라클 19c opatch debug 방법 (0) | 2025.11.19 |
| 오라클 19c ahf 기동 중 ru 패치 테스트 (0) | 2025.11.14 |
| 오라클 19c expdp master_table 옵션 (0) | 2025.11.02 |
| 오라클 19c job의 failure_count 초기화 (0) | 2025.10.25 |