AnySQL.net English

    The following connection format method is supported by 8i/9i/10g:

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 5 17:40:22 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn anysql/anysql@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=TEST)))
Connected.
SQL>

    Yong Huang, one of my friends said there is another format in 10g, then I think of it should be the same as new JDBC url format, the following format works on 10g, but does not work on 8i/9i:

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 5 17:44:38 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn anysql/anysql@localhost:1521/TEST
Connected.
SQL>

    If you are in trouble with the tnsnames.ora configuration, try this format. OCI and Pro*C program can use this format also, it's implemented at Oracle network layer.

    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.

    In the last two years, I spent a lot of time to rebuild standby (for backup or data moving) for very busy databases, the archive log generated very fast, and the archive data volume have limited free space, which caused some trouble when rebuilding standby. When we put the database into begin backup mode, then archive generation speed was almost doubled, then we had to use RMAN's data file copy feature to perform data file backup without putting the whole tablespace or database into begin backup mode. But we had to copy the datafile to a local directory first with RMAN, we cannot directly copy the data to remote directory in another host. Why not NFS, in a complex environment (multiple data centers), the NFS configure was a relative complex job. In out company, DBA just do DBA's work.

    Rsync is a perfect file copy utility between different hosts under Linux or UNIX, it could compress the contents when copying files to fit for the lower bandwidth between different data centers. If it could be combined with RMAN's data copy feature, it will save us a lot of time on standby rebuilding. Some junior DBAs always copy the data file directly with OS utility without using RMAN's data file copy and without putting the tablespace or database into begin backup mode.

    Then I considered to build a program called osync, but it was too difficult for me and finally dumped this idea. If so we could directly backup data file to remote host by issuing "osync file_id host:path". And I posted this idea on Google oracle BBS, somebody said that it's a useless idea. Then I filed a tar (Cannot remind the tar number back), Oracle's developer said they were considering to implement this feature in RMAN 11g version, which will allow you copy data file to remote host in the RMAN's data file copy command.

    Oracle have announced the 11g beta test program for quite a few days, is there anyone get the beta version? I'd like to verify whethere this feature has been implemented in Oracle 11g. Thanks!

    Sometimes Oracle choose wrong index even with "INDEX" hint due to index maintenance job (rename, creating or dropping), you need to change the hint in the SQLs to make it work. And for developers, they may not know which index is better, but they should know which column is best for selectivity. So is there any way to tell Oracle optimizer to choose indexes on specified columns or leading columns. I was considering an INDEX_COL hint by specifying "index_col (table name, col1, ...)" to get this case work.

    I posted this idea to Google groups, and somebody reminded me that there is already an undocumented hint (found out by Jonathan Lewis) in Oracle 10g. It's really useful for both DBAs and developers, I like it very much.

    The following undocumented hint will let Oracle 10g choose an index leading with column "col1" and "col2", but it will not choose index start with column "col2", not sure whether it is changed in Oracle 11g version:

/*+ index(table_alias tablename(col1, col2) ) */

    Oracle have announced the 11g beta test program for quite a few days, is there anyone get the beta version? I'd like to know whether it become an documented hint now, we cannot use undocumented hint in our application now, but if it becomes an documented hint, we will absolutely use it.