Die Speicherung von Daten im LOB-Datentyp (wie z.B. BLOB, CLOB) ist eine wichtige Grundlage, um unstrukturierte Daten wie Dokumente und Bilder in der Datenbank abzuspeichern. Mit Oracle Database 11g steht ein neuer Datentyp für die Speicherung von Large Objects in allen Editionen der Datenbank zur Verfügung, die sogenannten SECUREFILEs. Im Gegensatz zu der "alten" LOB-Technologie, die ab 11g mit dem neuen Schlüsselwort BASICFILE angesprochen werden kann, bieten SECUREFILEs verbesserte Performance, vereinfachtes Management und unter Nutzung von zusätzlichen Datenbank-Optionen wie Advanced Compression oder Advanced Security zusätzliche Eigenschaften wie Verschlüsselung und Komprimierung bzw. "Deduplizierung" an.
Verwendet man SECUREFILEs, so ändern sich die Programmierschnittstellen Java, .NET, PHP nicht - alle API-Aufrufe bleiben gleich. Natürlich bleiben auch Datenbank Abfragen, die Oracle Text, XML-Path oder das Paket DBMS_LOB nutzen, gleich und müssen nicht angepasst werden.

Im Artikel wird die Verwendung und das Monitoring von SECUREFILEs an Beispielen demonstriert. Das Erzeugen von SECUREFILEs, die Migration, eine Anwendung von zusätzlichen Eigenschaften wie Komprimierung und der Vergleich von "alter" und neuer Technologie sind ebenfalls Thema des Tipps. Möchten Sie Informationen zur Komprimierung von strukturierten Daten in 11g erhalten, so finden Sie z.B. die Informationen dazu im Tipp Advanced Compression mit Oracle 11g in der Praxis.

In folgendem Beispiel wird als Ausgangstabelle die Tabelle BASIC_TABLE1 verwendet, die einerseits strukturierte Felder wie DOK_ID usw. und andererseits eine Text-Spalte als "out-of-line" CLOB Speicherung beinhaltet. Die Tabelle besteht aus ca 200000 Zeilen und belegt ca 3000 MB Platz unter der Verwendung von 8K Blockgröße. Folgender Ausschnitt gibt Auskunft über den verwendeten Speicherplatz im Tabellen- und Lob-Segment.

SELECT bytes/(1024*1024) "Groesse in MB" FROM user_segments
WHERE segment_name='BASIC_TABLE1'
UNION
SELECT bytes/(1024*1024) "Groesse in MB" FROM user_segments
WHERE segment_name IN
      (SELECT segment_name FROM user_lobs WHERE table_name='BASIC_TABLE1');

 Groesse in MB
--------------
            14
          3008

set long 10000
set heading off
spool lobddl.sql

SELECT dbms_metadata.get_ddl('TABLE','BASIC_TABLE1') FROM dual;

CREATE TABLE "US"."BASIC_TABLE1"
   (    "DOK_ID" NUMBER,
        "ORT" VARCHAR2(80),
        "KAT" VARCHAR2(3),
        "DATUM" NUMBER,
        "JAHR" NUMBER,
        "MONAT" NUMBER,
        "TAG" NUMBER,
        "TEXT" CLOB
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 LOB ("TEXT") STORE AS BASICFILE (
  TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
spool off

ALTER SYSTEM SET db_securefile=force;

start lobddl.sql

SELECT min(dbms_lob.getlength(text))"min",max(dbms_lob.getlength(text))"max",avg(dbms_lob.getlength(text))"avg" 
FROM basic_table1;

       min        max        avg
---------- ---------- ----------
        48     230448 11438.4022

   
CREATE TABLE "US"."COMPRESS_TABLE"
   (    "DOK_ID" NUMBER,
        "ORT" VARCHAR2(80),
        "KAT" VARCHAR2(3),
        "DATUM" NUMBER,
        "JAHR" NUMBER,
        "MONAT" NUMBER,
        "TAG" NUMBER,
        "TEXT" CLOB
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 LOB ("TEXT") STORE AS SECUREFILE (
  TABLESPACE "TAB_2K" DISABLE STORAGE IN ROW CHUNK 2048
  NOCACHE LOGGING  COMPRESS HIGH  KEEP_DUPLICATES
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 BUFFER_POOL DEFAULT))

INSERT /*+ append nologging */ ALL 
INTO secure_table 
INTO compress_table
SELECT * FROM basic_table1;
COMMIT;

SELECT table_name, encrypt, deduplication, compression, in_row, tablespace_name 
FROM user_lobs 
WHERE securefile='YES';

TABLE_NAME           ENCR DEDUPLICATION   COMPRE IN_ TABLESPACE_NAME
-------------------- ---- --------------- ------ --- --------------------
SECURE_TABLE         NO   NO              NO     NO  USERS
DEDUP_TABLE          NO   LOB             HIGH   NO  TAB_2K
COMPRESS_TABLE       NO   NO              HIGH   NO  TAB_2K 

SELECT bytes/(1024*1024) groesse FROM user_segments
WHERE segment_name='SECURE_TABLE'
UNION
SELECT bytes/(1024*1024) FROM user_segments 
WHERE segment_name IN
(SELECT segment_name FROM user_lobs WHERE table_name='SECURE_TABLE')

   GROESSE
----------
        34
      3463

SELECT bytes/(1024*1024) groesse FROM user_segments
WHERE segment_name='COMPRESS_TABLE'
UNION
SELECT bytes/(1024*1024) FROM user_segments 
WHERE segment_name IN
(SELECT segment_name FROM user_lobs WHERE table_name='COMPRESS_TABLE');

   GROESSE
----------
        19
    472.25

set serveroutput on
declare
     l_segment_name          varchar2(30);
     l_segment_size_blocks   number;
     l_segment_size_bytes    number;
     l_used_blocks           number;
     l_used_bytes            number;
     l_expired_blocks        number;
     l_expired_bytes         number;
     l_unexpired_blocks      number;
     l_unexpired_bytes       number;
begin
     select segment_name
     into l_segment_name
     from dba_lobs
     where table_name = '&table_name' and owner='US';
         dbms_output.put_line('Segment Name=' || l_segment_name);

     dbms_space.space_usage(
         segment_owner           => 'US',
         segment_name            => l_segment_name,
         segment_type            => 'LOB',
         partition_name          => NULL,
         segment_size_blocks     => l_segment_size_blocks,
         segment_size_bytes      => l_segment_size_bytes,
         used_blocks             => l_used_blocks,
         used_bytes              => l_used_bytes,
         expired_blocks          => l_expired_blocks,
         expired_bytes           => l_expired_bytes,
         unexpired_blocks        => l_unexpired_blocks,
         unexpired_bytes         => l_unexpired_bytes);

dbms_output.put_line('segment_size_blocks      => '||  l_segment_size_blocks);
dbms_output.put_line('segment_size_bytes in MB => '||  l_segment_size_bytes/(1024*1024));
dbms_output.put_line('used_bytes  in MB        => '||  l_used_bytes/(1024*1024));
dbms_output.put_line('expired_bytes  in MB     => '||  l_expired_bytes/(1024*1024));
dbms_output.put_line('unexpired_bytes in MB    => '||  l_unexpired_bytes/(1024*1024));
end;
/
Enter value for table_name: COMPRESS_TABLE
old  15:      where table_name = '&table_name' and owner='US';
new  15:      where table_name = 'COMPRESS_TABLE' and owner='US';
Segment Name=SYS_LOB0001731125C00008$$
segment_size_blocks      => 241792
segment_size_bytes in MB => 472.25
used_bytes  in MB        => 420.76171875
expired_bytes  in MB     => 46.759765625
unexpired_bytes in MB    => 0

PL/SQL procedure successfully completed.

Zurück zur Community-Seite