AnySQL.net English

    When you prepare to do recovery of partitioned table with LOB columns with AUL4, you must modify the LOB index's partition name first in AULOBJ.TXT. I have thought that the LOB index have the same partition name with table, just as normal local index, and in AUL 4 I use the table's partition name to locate the LOB index's partition to get the relative data object id. But the names are not same, let's start with two demo table :

SQL> CREATE TABLE T_HASHLOB (COL1 NUMBER, COL2 CLOB)
  2  LOB(COL2) STORE AS (DISABLE STORAGE IN ROW)
  3  PARTITION BY HASH(COL1) PARTITIONS 2;

Table created.

    Insert few rows and the perform a checkpoint, and start AUL 4 to unload the dictionary table, and describe the table structure:

AUL> desc anysql.t_hashlob

Storage(OBJ#=0 OBJD=0 TS=0 FILE=0 BLOCK=0 CLUSTER=0)
No. SEQ INT Column Name         Type
--- --- --- ------------------- ----------------
  1   1   1 COL1                NUMBER
  2   2   2 COL2                CLOB  (SYS_IL0000010046C00002$$)

    We could use grep to get the partition name information:

C:\MYDUL>grep -i t_hashlob AULOBJ.TXT
10048,25,T_HASHLOB,SYS_P28,19
10047,25,T_HASHLOB,SYS_P27,19
10046,25,T_HASHLOB,,2

C:\MYDUL>grep -i "SYS_IL0000010046C00002\$\$" AULOBJ.TXT
10054,25,SYS_IL0000010046C00002$$,SYS_IL_P32,20
10053,25,SYS_IL0000010046C00002$$,SYS_IL_P31,20
10052,25,SYS_IL0000010046C00002$$,,1

    Then we modify the partition name of LOB index according to the order of object id:

    AUL do support IOT table, but need some extra steps to modify dictionary information unloaded by AUL. Let's start with two demo tables:

CREATE TABLE T_IOT
(
   COL1 NUMBER NOT NULL PRIMARY KEY,
   COL2 VARCHAR2(20)
)
ORGANIZATION INDEX;

CREATE TABLE T_IOT2
(
   COL1 NUMBER NOT NULL CONSTRAINT PK_T_IOT2 PRIMARY KEY,
   COL2 VARCHAR2(20)
)
ORGANIZATION INDEX;

    If we don't specify the constraint name of primary key constraint of IOT table, the data segment name looks like "SYS_IOT_TOP_objid". If we specify a constraint name, the constraint name would be the segment name of IOT table. And the object id of the IOT index should be equal to object id of table plus 1. I will test the partitioned IOT tables later. Then I query the object id and the IOT segment name from the database with SQL*Plus, as following:

OBJECT_ID OBJECT_NAME
---------- ------------------------------
     10077 SYS_IOT_TOP_10076
     10076 T_IOT
     10078 T_IOT2
     10079 PK_T_IOT2

    The problem existing is that AUL cannot match the table name to the IOT segment name, and then cannot get the data object id of IOT tables. So we need to modify the rows in AULTAB.TXT file for the IOT segment, we need to change the object id (first column) of IOT segment, it should be equal to IOT table's object id.

C:\MYDUL>grep SYS_IOT_TOP_10076 AULOBJ.TXT
10077,25,SYS_IOT_TOP_10076,,1

C:\MYDUL>grep T_IOT AULOBJ.TXT
10076,25,T_IOT,,2

C:\MYDUL>grep 10077 AULTAB.TXT
10077,10077,4,4,2451

    I will copy the a row, and then change the object id of IOT segment to 10076 (the IOT table's object id).

    After two weeks of perl study and two days self-study of Movable Type plugin, I wrote a RandomImage plugin, which enable you to publish a random image to your blog items. How does the small picture looks at the left side of the title area? I defined a random image as an image url and a link url, when you click the image, it will open a new window and navigate to the linked url. If you want to implement this plugin, prepare an image list first, save them to a pure text file. As following:

# image_url#link_url

1.jpg#http://.....
2.jpg#http://.....

    Then insert the tag to the template file anywhere you want:

<$MTRandomImage file="path/imagelist.txt"$>

    Every time you rebuild your pages, or somebody post a comment which also result to rebuild the pages, will pick a random image, and insert the following HTML code to you article.

<a href="http://....." target="_blank">
<img src="1.jpg" border="0" />
</a>

    If you are familiar with perl, you could change the code. The idea is a little different with the implementation with Java script. The random is based on rebuild or comment. Enjoy it!

    There is a "query" option in Oracle exp utility, which enables you export filtered rows of a table by providing a where clause with this option. However this option is not widely used, many people do not clearly know how to specify a value for this option in Windows or UNIX platform, including me, and always get the following errors:

LRM-00112: multiple values not allowed for parameter 'query'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully

    This is due the value always contains multiple words, we must to pack them together as one value to the exp utility by quoting the values. On Windows, we could specify this value as following:

exp ... query='where deptno=10'
exp ... query='where deptno=''10'''
exp ... query='where deptno "<" 10'

    On UNIX platform, I tested the following command on Solaris, it works well.

exp ..... query=\"where col1 \< 1000\"
exp ..... query=\"where col1 \< '1000'\"

    On other UNIX platform, it should work also. Now you should be able to use it freely. The simplest way is to prepare a parameter file for exp utility, then you do not need to quote the value at all.

    Few days ago, I told one of my friends that the XMLTYPE column equals to CLOB type, this is not true, it's an object type. Today he ask me how to modify the cache property of XMLTYPE column, I just have a very unclear image of this data type, the first thing I did was describe the XMLTYPE object:

SQL> DESC XMLTYPE

METHOD
------
STATIC FUNCTION CREATEXML RETURNS XMLTYPE
Argument Name    Type    In/Out Default?
---------------- ------- ------ --------
XMLDATA          CLOB    IN

......

    I found that almost every member functions has a parameter called "XMLDATA", then I make suggestion to treat the "column.XMLDATA" as a CLOB object. Later I test it in a test database, found that my suggestion is absolutely correct.

SQL> create table t_xmltype (col1 xmltype);

Table created.

SQL> ALTER TABLE T_XMLTYPE MODIFY LOB (COL1.XMLDATA) (CACHE);

Table altered.

    Then I told he to read more books, which will enable people find out the final solution.