SQL> create table scott.t
2 ( last_name varchar2( 30 )
3 , encrypted_name varchar2( 30 ) encrypt
4 )
5 ;
테이블이 생성되었습니다.
SQL> insert into scott.t( last_name )
2 select object_name from scott.stage;
84190개의 행이 생성되었습니다.
SQL> create or replace
2 procedure show_space
3 ( p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default 'TABLE',
6 p_partition in varchar2 default NULL )
7 authid current_user
8 as
9 l_free_blks number;
10
11 l_total_blocks number;
12 l_total_bytes number;
13 l_unused_blocks number;
14 l_unused_bytes number;
15 l_LastUsedExtFileId number;
16 l_LastUsedExtBlockId number;
17 l_LAST_USED_BLOCK number;
18
19 l_UNFORMATTED_BLOCKS number;
20 l_UNFORMATTED_BYTES number;
21
22 l_FS1_BLOCKS number;
23 l_FS1_BYTES number;
24 l_FS2_BLOCKS number;
25 l_FS2_BYTES number;
26 l_FS3_BLOCKS number;
27 l_FS3_BYTES number;
28 l_FS4_BLOCKS number;
29 l_FS4_BYTES number;
30 l_FULL_BLOCKS number;
31 l_FULL_BYTES number;
32
33 procedure p( p_label in varchar2, p_num in number )
34 is
35 begin
36 dbms_output.put_line( rpad(p_label,40,'.') ||
37 p_num );
38 end;
39 begin
40 for x in ( select tablespace_name
41 from dba_tablespaces
42 where tablespace_name = ( select tablespace_name
43 from dba_segments
44 where segment_type = p_type
45 and segment_name = p_segname
46 and SEGMENT_SPACE_MANAGEMENT <> 'AUTO' )
47 )
48 loop
49 dbms_space.free_blocks
50 ( segment_owner => p_owner,
51 segment_name => p_segname,
52 segment_type => p_type,
53 partition_name => p_partition,
54 freelist_group_id => 0,
55 free_blks => l_free_blks
56 --,
57 --UNFORMATTED_BLOCKS => l_UNFORMATTED_BLOCKS
58 );
59 end loop;
60
61 dbms_space.space_usage
62 (segment_owner => p_owner,
63 segment_name => p_segname,
64 segment_type => p_type,
65 UNFORMATTED_BLOCKS => l_UNFORMATTED_BLOCKS,
66 UNFORMATTED_BYTES => l_UNFORMATTED_BYTES,
67 FS1_BLOCKS => l_FS1_BLOCKS,
68 FS1_BYTES => l_FS1_BYTES,
69 FS2_BLOCKS => l_FS2_BLOCKS,
70 FS2_BYTES => l_FS2_BYTES,
71 FS3_BLOCKS => l_FS3_BLOCKS,
72 FS3_BYTES => l_FS3_BYTES,
73 FS4_BLOCKS => l_FS4_BLOCKS,
74 FS4_BYTES => l_FS4_BYTES,
75 FULL_BLOCKS => l_FULL_BLOCKS,
76 FULL_BYTES => l_FULL_BYTES
77 );
78
79 dbms_space.unused_space
80 ( segment_owner => p_owner,
81 segment_name => p_segname,
82 segment_type => p_type,
83 partition_name => p_partition,
84 total_blocks => l_total_blocks,
85 total_bytes => l_total_bytes,
86 unused_blocks => l_unused_blocks,
87 unused_bytes => l_unused_bytes,
88 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
89 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
90 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
91
92 p( 'UNFORMATTED_BLOCKS', l_UNFORMATTED_BLOCKS );
93 p( 'FS1_BLOCKS', l_FS1_BLOCKS );
94 p( 'FS2_BLOCKS', l_FS2_BLOCKS );
95 p( 'FS3_BLOCKS', l_FS3_BLOCKS );
96 p( 'FS4_BLOCKS', l_FS4_BLOCKS );
97 p( 'FULL_BLOCKS', l_FULL_BLOCKS );
98
99 -- p( 'Free Blocks', l_free_blks );
100 p( 'Total Blocks', l_total_blocks );
101 p( 'Total Bytes', l_total_bytes );
102 p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
103 p( 'Unused Blocks', l_unused_blocks );
104 p( 'Unused Bytes', l_unused_bytes );
105 p( 'Last Used Ext FileId', l_LastUsedExtFileId );
106 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
107 p( 'Last Used Block', l_LAST_USED_BLOCK );
108 end;
109 /
프로시저가 생성되었습니다.
SQL>
SQL> exec show_space( 'T','SCOTT', 'TABLE', NULL );
UNFORMATTED_BLOCKS......................0
FS1_BLOCKS..............................1
FS2_BLOCKS..............................0
FS3_BLOCKS..............................0
FS4_BLOCKS..............................24
FULL_BLOCKS.............................345
Total Blocks............................384 -- 384 블럭 사용
Total Bytes.............................3145728
Total MBytes............................3
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................896
Last Used Block.........................128
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> truncate table SCOTT.t;
테이블이 잘렸습니다.
SQL> insert into scott.t( encrypted_name )
2 select object_name from scott.stage;
84190개의 행이 생성되었습니다.
SQL>
SQL> exec show_space( 'T','SCOTT', 'TABLE', NULL );
UNFORMATTED_BLOCKS......................0
FS1_BLOCKS..............................0
FS2_BLOCKS..............................0
FS3_BLOCKS..............................0
FS4_BLOCKS..............................20
FULL_BLOCKS.............................854
Total Blocks............................896 -- 896 블럭 사용
Total Bytes.............................7340032
Total MBytes............................7
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................1408
Last Used Block.........................128
PL/SQL 처리가 정상적으로 완료되었습니다.
테이블이 잘렸습니다.
SQL> ALTER TABLE SCOTT.t MODIFY encrypted_name ENCRYPT NO SALT;
테이블이 변경되었습니다.
SQL>
SQL> insert into scott.t( encrypted_name )
2 select object_name from scott.stage;
84190개의 행이 생성되었습니다.
SQL> exec show_space( 'T','SCOTT', 'TABLE', NULL );
UNFORMATTED_BLOCKS......................62
FS1_BLOCKS..............................0
FS2_BLOCKS..............................0
FS3_BLOCKS..............................1
FS4_BLOCKS..............................20
FULL_BLOCKS.............................665
Total Blocks............................768 – 16의 배수 고정 길이 필드로 저장
Total Bytes.............................6291456
Total MBytes............................6
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................1280
Last Used Block.........................128
PL/SQL 처리가 정상적으로 완료되었습니다.
{CODE}
| 에섹스 패턴 1 | |
|---|---|
| {code:sql} create table custormer( cust_id number primary key, .. other data .. credit_card# varchar2( 50 ) encrypt ) | {code} |
| 패턴 2 - 극단적 |
|---|
| {code:sql} |
create table custormer(
cust_id number primary key,
number_of_page_views number encrypt, -- 매번 사이트의 페이지를 클릭시 업데이트 컬럼
.. other data ..
credit_card# varchar2( 50 ) encrypt
)
| {code} |
| cpu 사용량 및 redo 사이즈 영향도 |
|---|
SQL> create table scott.stage
2 as
3 select object_name
4 from all_objects;
테이블이 생성되었습니다.
SQL> create table scott.t
2 (
3 non_encrypted varchar2( 30 ),
4 encrypted varchar2( 30 ) encrypt
5 )
6 ;
테이블이 생성되었습니다.
SQL> create or replace procedure do_sql( p_sql in varchar2, p_truncate in boolean default true )
2 authid current_user -- SQL 인젝션 공격 보안 문제 회피
3 as
4 l_start_cpu number;
5 l_start_redo number;
6 l_total_redo number;
7 function get_stat_val( p_name in varchar2 ) return number
8 as
9 l_val number;
10 begin
11 select b.value
12 into l_val
13 from v$statname a, v$mystat b
14 where a.statistic# = b.statistic#
15 and a.name = p_name;
16
17 return l_val;
18 end;
19 begin
20 if( p_truncate )
21 then
22 execute immediate 'truncate table scott.t';
23 end if;
24 dbms_output.put_line( p_sql );
25
26 l_start_cpu := dbms_utility.get_cpu_time;
27
28 l_start_redo := get_stat_val( 'redo size' );
29
30 execute immediate p_sql;
31
32 commit work write batch wait;
33
34 dbms_output.put_line( (dbms_utility.get_cpu_time - l_start_cpu ) || ' cpu hsecs' );
35
36 l_total_redo := round( ( get_stat_val('redo size') - l_start_redo ) / 1024 / 1024, 1 );
37
38 dbms_output.put_line( to_char( l_total_redo, '999,999,999,9')||' mbytes redo' );
39
40 end;
41 /
프로시저가 생성되었습니다.
SQL>
SQL> begin
2 do_sql( 'insert into scott.t( non_encrypted ) '||'select object_name from scott.stage' );
3 do_sql( 'insert into scott.t( encrypted ) '||'select object_name from scott.stage' );
4 end;
5 /
insert into scott.t( non_encrypted ) select object_name from scott.stage
21 cpu hsecs
3 mbytes redo
insert into scott.t( encrypted ) select object_name from scott.stage
217 cpu hsecs
7 mbytes redo
PL/SQL 처리가 정상적으로 완료되었습니다.
| 로우 단위 영향도 |
|---|
SQL> declare
2 l_sql long := 'begin ' || 'for x in (select object_name from scott.stage) ' ||
3 'loop ' ||
4 'insert into scott.t("CNAME") ' ||
5 'VALUES ( X.OBJECT_NAME ); ' ||
6 'end loop; ' ||
7 'end; ';
8 begin
9 do_sql( replace( l_sql, '"CNAME"', 'non_encrypted') );
10 do_sql( replace( l_sql, '"CNAME"', 'encrypted' ) );
11 end;
12 /
begin for x in (select object_name from scott.stage) loop insert into
scott.t(non_encrypted) VALUES ( X.OBJECT_NAME ); end loop; end;
752 cpu hsecs
2,1 mbytes redo
begin for x in (select object_name from scott.stage) loop insert into
scott.t(encrypted) VALUES ( X.OBJECT_NAME ); end loop; end;
1658 cpu hsecs
2,5 mbytes redo
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> truncate table scott.t;
테이블이 잘렸습니다.
SQL> insert into scott.t select object_name, object_name from scott.stage;
84196개의 행이 생성되었습니다.
SQL> exec dbms_stats.gAther_table_stats( 'SCOTT', 'T' );
PL/SQL 처리가 정상적으로 완료되었습니다.
3 'loop ' ||
4 'null; ' ||
5 'end loop; ' ||
6 'end; ';
7 begin
8 do_sql( replace( l_sql, '#CNAME#', 'non_encrypted'), false );
9 do_sql( replace( l_sql, '#CNAME#', 'encrypted' ), false );
10 end;
11 /
begin for x in (select non_encrypted from scott.t) loop null; end loop; end;
11 cpu hsecs
0 mbytes redo
begin for x in (select encrypted from scott.t) loop null; end loop; end;
140 cpu hsecs
0 mbytes redo
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL>
SQL>
SQL> select * from scott.t where non_encrypted = 'ALL_OBJECTS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 184 | 342 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 2 | 184 | 342 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NON_ENCRYPTED"='ALL_OBJECTS')
SQL>
SQL> select * from scott.t where encrypted = 'ALL_OBJECTS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 842 | 77464 | 342 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 842 | 77464 | 342 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("ENCRYPTED")='ALL_OBJECTS')