In this post I wanted to share some methods you can use while manipulating XML data. My XMLType Table example is based on my last posts about XMLIndex and (binary) XML Storage. So if you want some more information I suggest reading up on those as well.
Triggered by a great example from Jonathan Gennick (“Faster, Safer, and Smaller LOBs”) in the September issue of Oracle Magazine.
I used this as a base, manipulating XMLType tables and see what happens if I would do the same, but now on XML objects.
Although I had a very nice test / demonstration SQL script, I can’t post this yet, because I encountered some features that are currently dealt with by Oracle Support.
Anyway, I hereby also recommend reading Jonathan’s test documents embedded in the sample code zip-file (“domains_06.doc“). I have no idea if this article (mentioned in “domains_06”) already was posted, but as said, it is a very nice article.
Environment
Below the environment I am working with and the examples given here are based upon most of the objects. The XML schema used was registered for binary use (see former posts regarding Binary XML Storage how to realize this)
SQL> select schema_url, binary from user_xml_schemas;
SCHEMA_URL BIN
-------------------------------------------------- ---
http://localhost/public/xsd/binxsd.xsd YES
SQL> CREATE TABLE TEST of XMLTYPE
2 XMLTYPE STORE AS SECUREFILE BINARY XML
3 XMLSCHEMA "http://localhost/public/xsd/binxsd.xsd" ELEMENT "ROOT"
4 ;
Table created.
SQL> select dbms_metadata.get_ddl('TABLE','TEST') from dual;
DBMS_METADATA.GET_DDL('TABLE','TEST')
-------------------------------------------------------
CREATE TABLE "TEST" OF "SYS"."XMLTYPE"
XMLTYPE STORE AS SECUREFILE BINARY XML (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING DECRYPT NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT))
XMLSCHEMA "http://localhost/public/xsd/binxsd.xsd" ELEMENT "ROOT"
ID 4486 DISALLOW NONSCHEMA
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)
TABLESPACE "USERS"
-- Simple generated rows
SQL> DECLARE
2 XMLData xmlType := xmlType(
3 '< ?xml version="1.0" encoding="UTF-8"?>
4
5
6 0
7
8 0
9 Text
10
11 ');
12 BEGIN
13 for i in 1..100000
14 loop
15 insert into TEST
16 VALUES
17 (XMLData);
18 end loop;
19 END;
20 /
PL/SQL procedure successfully completed.
Elapsed: 00:02:36.12
SQL> col segment_name for a40
SQL> comp sum of bytes on report
SQL> break on report
SQL> select segment_name
2 , segment_type
3 , bytes
4 from user_segments
5 order by segment_type;
SEGMENT_NAME SEGMENT_TYPE BYTES
---------------------------------------- ------------------ ----------
SYS_C008601 INDEX 3145728
SYS_IL0000067788C00003$$ LOBINDEX 65536
SYS_LOB0000067788C00003$$ LOBSEGMENT 131072
TEST TABLE 22020096
----------
sum 25362432
SQL> select count(*) from TEST;
COUNT(*)
----------
100000
SQL> select dbms_metadata.get_ddl('TABLESPACE','USERS')
2 from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
------------------------------------------------------
CREATE TABLESPACE "USERS" DATAFILE SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL
AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DATAFILE
'/oracle/oradata/TEST/datafile/o1_mf_users_3d5xl73m_.dbf'
RESIZE 1614675968
OK, as said, a simple environment, we are now can use this environment for some DDL statements and see the effect.
Moving and Renaming XMLType Lob Segments
As demonstrated in an old post and even better described in a post from Jeff Hunter in “DDL Commands for LOBs” (not XMLType specific, but the DDL code can be deducted from this; indirect we are dealing with LOB objects).
The LOBSEGMENT and LOBINDEX of the Binary XML Table can be moved to a different tablespace, but be aware that Oracle advises that both are located on the same tablespace. You are not able to rename the LOBINDEX. The LOBSEGMENT can be renamed and moved via the following statement.
SQL> alter table TEST
2 move tablespace USERS
3 LOB (XMLDATA) store as XMLTEST_LOBSEGMENT
4 (tablespace USERS
5 INDEX (TABLESPACE USERS))
6 -- the lobindex should always remain in the
7 -- same tablespace as the lobsegment
8 -- this accoording to the Oracle documentation
9 ;
Table altered.
Elapsed: 00:00:04.50
SQL> select segment_name
2 , segment_type
3 , bytes
4 from user_segments
5 order by segment_type;
SEGMENT_NAME SEGMENT_TYPE BYTES
---------------------------------------- ------------------ ----------
SYS_C008601 INDEX 3145728
SYS_IL0000067788C00003$$ LOBINDEX 65536
XMLTEST_LOBSEGMENT LOBSEGMENT 131072
TEST TABLE 22020096
----------
sum 25362432
Deduplicating XMLType Securefile Storage
The syntax for XMLType table storage is a little bit different to the ones demonstrated by Jonathan in his Oracle Magazine post. For XMLType Binary SECURE storage it is demonstrated in the following DDL statements (DEDUPLICATE and restore via the KEEP_DUPLICATES parameter). Maybe overkill but be aware that this method can only be used with SECUREFILE storage (the new lob storage).
The following ORA error message (ORA-43854) will be raised if you try to apply the DEDUPLICATION method on a BASICFILE storage object.
SQL> ALTER TABLE BASICFILE_TABLE
2 MODIFY LOB(XMLDATA)
3 (DEDUPLICATE);
ALTER TABLE BASICFILE_TABLE
*
ERROR at line 1:
ORA-43854: use of a BASICFILE LOB where a SECUREFILE LOB was expected
The following demonstrates the syntax for XMLType table objects.
SQL> alter table TEST
2 MODIFY LOB(XMLDATA)
3 (DEDUPLICATE);
Table altered.
Elapsed: 00:01:44.89
SQL> select segment_name
2 , segment_type
3 , bytes
4 from user_segments
5 order by segment_type;
SEGMENT_NAME SEGMENT_TYPE BYTES
---------------------------------------- ------------------ ----------
SYS_C008601 INDEX 3145728
SYS_IL0000067788C00003$$ LOBINDEX 65536
XMLTEST_LOBSEGMENT LOBSEGMENT 131072
TEST TABLE 22020096
----------
sum 25362432
Elapsed: 00:00:00.05
SQL> alter table TEST
2 MODIFY LOB(XMLDATA)
3 (KEEP_DUPLICATES)
4 ;
Table altered.
Elapsed: 00:00:51.99
SQL> select segment_name
2 , segment_type
3 , bytes
4 from user_segments
5 order by segment_type;
SEGMENT_NAME SEGMENT_TYPE BYTES
---------------------------------------- ------------------ ----------
SYS_C008601 INDEX 3145728
SYS_IL0000067788C00003$$ LOBINDEX 65536
XMLTEST_LOBSEGMENT LOBSEGMENT 131072
TEST TABLE 22020096
----------
sum 25362432
XML Securefile – What About Compression?
Compressing the XMLType table is shown in the following example. Four statements are applicable: COMPRESS MEDIUM, COMPRESS HIGH, COMPRESS or NOCOMPRESS. Seen the syntax used with DEDUPLICATION, figuring out the “XMLDATA” piece in the DEDUPLICATE statement, the following is now very simple. NOCOMPRESS is the default when creating tables. The demonstrated ORA-43854 error message is raised again if you are trying to apply this method on a BASICFILE storage LOB / object.
As said by Jonathan in his article, compression can be interesting if used, for example, on data like XML. I noticed (as said by Jonathan) that it is CPU intensive. Or at least, I noticed increased CPU time, during the statements described below on my small Linux laptop environment.
SQL> alter table TEST
2 MODIFY LOB(XMLDATA)
3 (COMPRESS HIGH);
Table altered.
SQL> alter table TEST
2 MODIFY LOB(XMLDATA)
3 (COMPRESS MEDIUM);
Table altered.
SQL> alter table TEST
2 MODIFY LOB(XMLDATA)
3 (COMPRESS);
Table altered.
SQL> alter table TEST
2 MODIFY LOB(XMLDATA)
3 (NOCOMPRESS);
Table altered.
B.t.w. here I encountered one of the features (6364855).
Encryption of XML Securefile storage
As described by Jonathan, after you create a wallet and open it for use in the database (I did it via the DBConsole, as once described on the AMIS Technology Blog site) you are now also able to encrypt your data. If the database doesn’t have an wallet opened for use, you will encounter an ORA-28365 error message (“ORA-28365: wallet is not open”).
SQL> ALTER TABLE TEST
2 MODIFY LOB(XMLDATA)
3 (ENCRYPT USING 'AES256');
ALTER TABLE TEST
*
ERROR at line 1:
ORA-28365: wallet is not open
-- After the wallet has be opened for use...
SQL> ALTER TABLE TEST
2 MODIFY LOB(XMLDATA)
3 (ENCRYPT USING 'AES256');
SQL> ALTER TABLE TEST
2 MODIFY LOB(XMLDATA)
3 (ENCRYPT USING 'AES256');
Table altered.
-- Decrypting it again as it was from the start
SQL> ALTER TABLE TEST
2 MODIFY LOB(XMLDATA)
3 (DECRYPT);
Table altered.
-- Encrypting it again with a little bit more spice ;-)
SQL> ALTER TABLE TEST
2 MODIFY LOB(XMLDATA)
3 (ENCRYPT USING 'AES256' IDENTIFIED BY 'Encrypted?')
4 ;
Table altered.
I currently can’t demonstrate the real differences, because I am a little bit handicapped by the environment, but I hope that showing the syntax for a XMLType Securefile Binary XML table is helpful anyway. As said, for “standard” LOB objects, I really recommend that you read Jonathan’ s great Oracle Magazine article.
Related Posts
- AMIS Technology Blog: DBConsole 11g (part 3) – Transparent Tablespace Encryption and Alerting
- HOWTO: Create XMLType Table for Binary XML Usage
i found you by link from the Directory Listing Script from Ash.. Nice to read your blog ^.^