 |
 |
 |
 |
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
|
|
 |
|
|
|
 |
|
 |
Using escape characters within Oracle SQL
Donald K. Burleson |
Oracle allows the assignment of special escape characters to tell Oracle that the character is interpreted literally. Certain characters such as the underscore “_” are not interpreted literally because they have special meaning within Oracle.
In the example below, we want to find all Oracle parameter that relate to I/O, so we are tempted to use the filter LIKE “%_io_%’. Below we will select from the x$ksppi fixed table, filtering with the LIKE clause:
SQL> select ksppinm from x$ksppi where ksppinm like '%_io_%';
KSPPINM
----------------------------------------------------------------
sessions
license_max_sessions
license_sessions_warning
_session_idle_bit_latches
_enable_NUMA_optimization
java_soft_sessionspace_limit
java_max_sessionspace_size
_trace_options
_io_slaves_disabled
dbwr_io_slaves
_lgwr_io_slaves
As you can see above, we did not get the answer we expected. The SQL displayed all values that contained “io”, and not just those with an underscore. To remedy this problem, Oracle SQL supports an ESCAPE clause to tell Oracle that the character is to be interpreted literally:
SQL> select ksppinm from x$ksppi where ksppinm like '%\_io\_%' ESCAPE '\';
KSPPINM
----------------------------------------------------------------
_io_slaves_disabled
dbwr_io_slaves
_lgwr_io_slaves
_arch_io_slaves
_backup_disk_io_slaves
backup_tape_io_slaves
_backup_io_pool_size
_db_file_direct_io_count
_log_io_size
fast_start_io_target
_hash_multiblock_io_count
_smm_auto_min_io_size
_smm_auto_max_io_size
_ldr_io_size
If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress. It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:
http://www.rampant-books.com/book_2003_1_oracle9i_sga.htm
|
|
