| Oracle® Database Lite Oracle Lite Client Guide Release 10.3 Part Number E12548-02 |
|
|
View PDF |
| Oracle® Database Lite Oracle Lite Client Guide Release 10.3 Part Number E12548-02 |
|
|
View PDF |
The following sections describe how to manage the Oracle Lite Relational Database Management System (RDBMS):
Section 7.1, "Moving Your Client Data Between an Oracle Lite Database and an External File"
Section 7.5, "Discovering Oracle Lite Database Version Number"
Section 7.6, "Row Sort Limitations of the Oracle Lite Database"
Section 7.7, "Troubleshooting the Source of a Checksum Error Against Database"
You can move data between an Oracle Lite database and an external file either through programmatic APIs or the Load Utility (OLLOAD). The following sections describe both methods:
Using the Oracle Database Lite Load APIs, you can develop applications to load data from an external file into a table in Oracle Database Lite, or to unload (dump) data from a table in Oracle Database Lite to an external file. The details of the APIs and file formats are provided in Appendix C, "Oracle Database Lite Load Application Programming Interfaces (APIs)".
The Oracle Database Lite Load Utility enables you to load data from an external file into a table in Oracle Database Lite, or to unload (dump) data from a table in Oracle Database Lite to an external file. For more information on the OLLOAD utility, see Appendix C, "OLLOAD".
You can backup the Oracle Lite database either by using the backupdb utility or by copying the files to another location.
Oracle Database Lite uses the ODB and OBS files with dependent log files that can be backed up by copying to another location. Before any files can be copied, disconnect all applications that access the database and shut down the multi-user service, if running. Once that has been accomplished, execute the backupdb utility, which copies the *.odb, *.obs, and *.opw files to the filename of your choice to make a backup of the database.
backupdb DSN|NONE DBName backup_filename [DB_password]
For full details, see Appendix C, "BACKUPDB".
You can encrypt the Oracle Lite database. Once encrypted, the data stored in the database files cannot be interpreted by examining the files. A password is used to derive a 128-bit encryption key. Oracle Database Lite uses the Advanced Encryption Standard (AES) encryption.
For information on encrypting the database used by the client, see Appendix C, "ENCRYPDB".
If you do not want to use AES encryption, then you can insert your own encryption module to supplant AES; see Section 14.3, "Providing Your Own Encryption Module for the Client Oracle Lite Database" for complete details.
Linguistic sort is a feature for the ASCII version of the Oracle Lite database. It produces culturally acceptable order of strings for a specified language or collation sequence. The ASCII version supports several code pages defined by single-byte 8-bit encoding schemes. Each of these code pages is a super set of 7-bit ASCII, and the additional accented characters necessary to support certain European languages are included in the upper 128 bytes.
A new string comparison mechanism is provided that produces strings in a linguistically correct order by mapping each collation element of a string to the corresponding 8-bit value of the supported code page.
The only supported languages for linguistic sort are French, German, Czech and XCzech. The collation sequence for these Oracle Lite databases can be specified with the NLS_SORT parameter.
All other languages use the BINARY collation sequence, which does not enable linguistic sort.
The linguistic sort capability must be enabled when the database is created using the CREATEDB command line utility with the <collation_sequence> enabled.
The behavior of the ORDER_BY clause and the WHERE condition are determined by how the NLS_SORT parameter is implemented. Binary sorting is the default setting, and is used unless the <collation_sequence> parameter is set to use the linguistic sort ordering rules.
NLSRT is not supported in the current version of Oracle Database Lite. Therefore, NCHAR data type is not yet available.
Collation refers to ordering of strings into a culturally acceptable sequence. A collation sequence is a sequence of all collation elements from an alphabet from smallest collation order to the largest. Once a collation sequence is given, orders of all strings from the same alphabet are fixed. As such, the collation sequence encodes the linguistic requirements on collation. A collation element is the smallest sub-string that can be used by the comparison function to determine the order of two strings.
Normally, a collation element is just one character. In binary sorting, only one property, the code value that represents a character, is used. But in linguistic sorting, usually three properties. The primary level of difference is the base character. The secondary level of difference is for diacritical marks on a given base character. The tertiary level of difference is for the case of a given character. Punctuation can function as a fourth level of difference, but comparisons for punctuation occur last and are made at the binary rather than the linguistic level. These are used for each collation element. The following sections contain examples that demonstrate sorting priorities.
This section lists a set of examples that describe how to sort normal characters.
Example 1
'a' < 'b'. There is a primary difference between them on the character level.
Example 2
'À' > 'a'. This difference occurs on the secondary level. Note that 'À'and 'a' are considered "equal" on the primary level.
Example 3
'À' < 'à' in FRENCH but 'À' > 'à' in GERMAN. This difference on the tertiary level. Note that 'À' and 'à' are considered being "equal" on the primary and secondary level. Also note that the case convention may be different for different language.
Example 4
'às' < 'at'. This is a difference on the primary level. This example shows the role of difference levels: the lower level differences are ignored if there is a primary level difference anywhere in the strings.
Example 5
'+data' < '-data' <'data' <'data-'. If strings are compared and present no difference on the primary, secondary, or tertiary levels, they are compared for punctuation.
Some languages, particularly French, require words to be ordered on the secondary level according to the last accent difference. This behavior is known as French secondary sorting or French accent ordering.
Example
'côte' < 'coté' in FRENCH but 'coté' < 'côte' in GERMAN. Note that the secondary difference of 'e' and 'é' occurred later than those of 'ô' and 'o'.
There are some special cases where two or more characters in a group can function as a single collation element. These types of collation elements are called 'contracting characters' or 'group characters'. In these cases each of these characters properties are assigned appropriate values.
Example
'h' < 'ch' < 'i' in XCZECH. Here 'ch' is assigned a primary property value which differentiates it from 'h' and 'i', such that 'h' < 'ch' < 'i'. Note that 'ch' is treated as a single character.
If a letter sorts as if it were a sequence of more than one letter, it is called an 'expanding character'. For example, in German the sharp s (ß) is treated as if it were a string of two characters 'ss' when comparing with other letters.
Only sorting of single digit characters from '0' to '9' is currently supported. For the supported European languages a digit character is always sorted as greater than any alphabetic character. For other languages this may be not the same. Other numeric characters such as Roman numeric characters and counting sequences, such as "one", "two", "three", are not supported at this time.
Example
'1' > 'z' in any European language, '1' < 'a' in LATVIAN. Note that this difference occurs on the primary level.
Use the ODBINFO utility to discover the version number and volume identifier of the Oracle Lite database. See Section C.9, "ODBINFO" for full details.
Currently, the Oracle Database Lite engine cannot sort any row that exceeds 4040 bytes in length. If the selected columns exceed this length, then the database engine issues an error. Therefore, you cannot recover queries that use the UNION operation where both select clauses sort intermediate results, where the returned results are long rows with size greater than 4040 bytes.
You can perform diagnostics if you experience database corruption due to file system write errors, I/O errors, or a media device problem. If you receive a POL-3207 error, you may wish to execute the validatedb tool to see if it is a checksum error. Then, setting OLITE_WRITE_VERIFY to TRUE generates error reporting if a checksum error occurs on the device for the Mobile client.
For more information, see Section A.2.16, "OLITE_WRITE_VERIFY".
When an unexpected error is reported, users need to identify the location and cause of the error. Errors can be caused due to problems in the application code, Oracle tools—such as forms, SQLJ—or in the Oracle Lite database. Errors also occur in simple environments where a user application talks directly to the Oracle Lite database through JDBC or ODBC drivers. It may not be obvious which component is at fault—whether it is the user application, JDBC or ODBC drivers, or the core database runtime system.
If the optimizer spends too much time evaluating alternative plans or collecting index statistics, a query may take a long time for compilation. If the execution plan selected by the optimizer is not optimal, the query may also take a long time during execution. Based on these criteria, the tracing facility provides the compilation time and the execution plan.
The following sections describe how to set and use tracing.
By setting the parameter OLITE_SQL_TRACE = YES in the polite.ini or polite.txt file on the client device, Oracle Database Lite generates a trace file named oldb_trc.txt that shows the following:
Note:
Any value other than
YES disables the tracing feature. The parameter value is checked once during database startup. Hence, users must set this value before connecting to the database.The order tables are accessed by a query.
The table scan access method used.
The value of any bind variables utilized by the query.
The time it takes for the first record to be retrieved.
These are the main items reported that you can use to tune the majority of SQL queries.
If the trace file identifies that a full table scan is occurring, the most common way to get better performance from the query is to add an index that accommodates that query.
When you enable tracing, the trace information is dumped to a file named oldb_trc.txt in the current working directory of the database process. If the file already exists, then the trace output is appended to the end. If it does not exist, then a new file is automatically created. For a database service on Windows or the Oracle Lite database daemon for a Linux platform, the current working directory is specified by the wdir parameter during startup of the database service or daemon.
Note:
To implement the tracing feature, the database process must contain permissions to create the trace file in the current working directory.The following trace information is provided:
Table 7-1 Trace Output
| Trace Output | Description |
|---|---|
|
Statement Text |
Each time a SQL statement is prepared, its text is dumped into the trace file. The SQL statement itself is output without any formatting. If a SQL statement contains a new line character, it is also included in the SQL statement output. |
|
Compilation Time |
After the SQL statement is compiled, the compilation time is printed. |
|
Execution Plan |
If there are no errors, the execution plan is printed when available. Only statements that contain a |
|
Bind Value |
If a SQL statement contains markers, then the bind value is printed for every line. Each value for the marker or bind variable is printed on a separate line in the following format. Marker [<number>]: <Value> Where, |
|
Temporary Table Created |
Each time a temporary table is created, its name is dumped into the trace file. |
|
Table Access |
Each time a table is accessed, the following information is dumped into the trace file:
For a description of how this information is presented, see Section 7.8.2.1, "Table Name Output". |
|
Temporary Table Sorted |
Each time a temporary table is sorted, its name and sorting time (in milliseconds) are dumped into the trace file. |
|
First Fetch Time |
If the SQL statement is a SELECT statement, the time spent on fetching the first row is dumped into the trace file. |
|
Tid |
The thread ID is dumped into the trace file in front of some of the dumped information. The thread is displayed in the following format:
|
The name of the table that is currently being accessed and the method used to access the table are printed in the following formats.
If the table is accessed sequentially, the format is:
Table Name: <table name>
Access Method: Sequential
Where <table name> is the name of the table being accessed.
If indices are used, the format is:
Table Name: <table name>
Access Method: Term[<number>], Index No: <index number>, IndexName: <index name>
<table name> is the name of the table being accessed.
Term[<number>] is the internal representation of the conjunct search conditions in the WHERE clause.
<index number> is the index number. Each index has an unique number in the database.
<index name> is the name of the index if any.
|
 Copyright © 1997, 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|