To correctly unload data, you need to tell AUL some option values by the "SET" command. Currently 12 options are available. Some are used to tell AUL the datafile format, some to set the output file format, and some to tune AUL performance, or to limit the rows to be unloaded.
Options to tell AUL the data file format:
- BLOCK_SIZE: Tell AUL the Oracle database's block size of the datafile, valid values are 2048, 4096, 8192, 16384, 32768, you must specify the correct value for this option. Default value is 8192.
- BYTE_ORDER: Tell AUL the byte order of the datafile. Because of hardware platform difference, when different OS write multi-byte type data to data files, it can be saved in different orders, little byte first or big byte first. Solaris on Sun SPARC, AIX, HPUX, IBM zSeries Linux, Mac are big byte order, and Windows, Linux IA, Tru64, Open VMS, Linux 64(AMD) are little order. The default for AUL is LITTLE. Adding this option enables AUL for Windows platform to correctly recover data files from Solaris SPARC platform.
- HEAD_SIZE: On some platforms (IBM AIX), if you use raw device, OS will keep some bytes at the datafile header to save private information. If you use AUL to recover data, you may need to specify HEAD_SIZE to skip these bytes. The default value is 0, which works for most platforms. There're no reserved bytes for datafile.
Options used to set the output file format:
- OUTPUT_STYLE: AUL supports two types of output file, plain text file (TXT) and v817 dump file (.dmp). Dump format is useful in some cases, for example rows with long/long raw columns, or text with new lines. The default output style is TXT.
- FIELD_TAG: set the field separator when output style is plain text. The default is ",". You can set it to any string less than 128 bytes. I use "\" as an escape char, for example "\b" means back, "\l" means "|", "\t" means tab, "\s" means space, "\r" means return, "\n" means new line. You can also specify two hex codes for a char by "\x" prefixed, for example, "\x07", "\x08" etc.
- RECORD_TAG: set the record separator when output style is plan text. The default is "\r\n". You can set it to any string less than 128 bytes. I use "\" as an escape char, for example "\b" means back, "\l" means "|", "\t" means tab, "\s" means space, "\r" means return, "\n" means new line. You can also specify two hex codes for a char by "\x" prefixed, for example, "\x07", "\x08" etc.
- LONG_TAG: This is used for setting an enclose string for long/lob value, because it mainly contains multiple lines. If you set this value, the long/lob value will be output as "<LONG_TAG>lob/long value</LONG_TAG>". It's only valid when output style is set to "TXT"
- CHARSET/NLSCHARSET: When you unload data to dump format, you may need to set this, the default value for this is 367(UTF8).
Options used to limit the rows to be exported:
- COMMITED_ROW: if set to true, AUL will try to unload committed rows only. The default value is FALSE. Even if you set it to true, AUL will not try to find old value from rollback segments, so you may lose more data.
- DELETED_ROW: if set to true, AUL will ignore the deleted tag in row header, and try to unload the rows. However because of Oracle's delayed block clean out, illegible data is possible when you set this option to TRUE. The default option for this is FALSE.
Options used to tune the AUL:
- CACHE_SIZE: setting the read buffer of AUL. The default is 256kb. For most cases, the default is enough. You should set this value to multiples of your block size.
- BLOCK_CHECK: if set to true, AUL will check the block header and block tail. If they don't match, AUL will not process the rows in this block. The default value is FALSE. You rarely need to set it to TRUE.
"SET" is the most complex command in AUL. You need to know some Oracle internals to get familiar with it.