AnySQL.net English

    Somebody asked me this question after a try of AUL. They stored data in NCHAR/NVARCHAR2 data type, and then they use AUL to recover their dropped tables as text file. But hit some problem with loading the recovered rows into database, because some of the NCHAR/NVARCHAR2 type columns' data is recovered as UNICODE character set (AL16UTFE as national character set in Oracle 9i).

    Create a demo table with two columns (COL1 NVARCHAR2(100), COL2 NVARCHAR2(100)), and insert two rows, then use AUL to dump the rows to text file for test. By default the columns are seperated by "|", as following:

N C O L 1| N C O L 2
N C O L 1| N C O L 2

    I checked the Oracle document (Oracle Database Utilies Guide), and search on Google, no answer found. Also SQL*Loader have CHARACTERSET and BYTEORDER option, it did not do help in this case, because just few columns are UNICODE format, and I don't know how to write a SQL*Loader control file for the test table.

    Who has the same issue before? Can you give me an correct answer? Thanks very much!

    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.

    Usually we need to recompile the perl DBD-Oracle when we change the Oracle client version, else it may not work properly. If you have multiple version of Oracle on your server, and you have some scripts wrote in perl need to run on all the databases, you need to use a specific version of Oracle client, or install different binary of perl and compile a version specific DBD-Oracle for each perl binary copy. Why? because when you compile you DBD-Oracle, then generated module file will find version specific Oracle client library file, use ldd to verify it as following:

$ldd ./lib/site_perl/5.8.5/sun4-solaris-64/auto/DBD/Oracle/Oracle.so
        libclntsh.so.9.0 =>      /export/home/oracle/products/9201/lib/libclntsh.so.9.0
        libnsl.so.1 =>   /usr/lib/64/libnsl.so.1
        libsocket.so.1 =>        /usr/lib/64/libsocket.so.1
        ......

    But there is always a symbo-link "libclntsh.so" which point to the version specific library file "libclntsh.so.ver", if we can make the perl DBD-Oracle module file find only the symbo-link name, it make work under different version of Oracle client without recompile. All of my OCI utility work under this way, they can be run under 8i/9i/10g client without recompile, use ldd to check one of my free utility "ociuldr.bin", as following:

$ldd ociuldr.bin
        libm.so.1 =>     /usr/lib/libm.so.1
        libclntsh.so =>  /export/home/oracle/products/9201/lib32/libclntsh.so
        ......

    And I try the method which I used to compile my OCI utility, first download the DBD-Oracle source code, and the we could start compile the DBD-Oracle.

    Perl have 32 Bits and 64 Bits version, so DBD-Oracle also have 32 Bits and 64 Bits version. Extract the source and then run the following command to generate the makefile:

32Bit: perl Makefile.PL -r=build32
64Bit: perl64 Makefile.pl -r=build64

    Now we run the make command, and find out where the generated module (Oracle.so) located:

$find ./ -name Oracle.so
./blib/arch/auto/DBD/Oracle/Oracle.so

    If the downloaded source's version is the same with currently used, you could just replace the module file. If you want to make the module work under 8i/9i/10g all without recompile, please compile at 10g client, and set the "otp_V" of DBD-Oracle to 8 (You could edit the generated Makefile). Now you can test under 8i/9i/10g with a demo perl program, the only thing you need to do is setting correctly the LD_LIBRARY_PATH or LIBPATH (aix) environment variable. As following:

32Bit
8i:${ORACLE_HOME}/lib
9i:${ORACLE_HOME}/lib32
10g${ORACLE_HOME}/lib32

64Bit
8i:${ORACLE_HOME}/lib64
9i:${ORACLE_HOME}/lib
10g${ORACLE_HOME}/lib

    I have successfully compile it and test it under 8i/9i/10g, and waiting for 11g environment to test.

    One of my friends use ociuldr to unload 5 billion rows to text file, and finally got a 50GB text file. It's hard to process such a large text file by perl. And he finally made a suggestion to let ociuldr generate few small files. I added this option to implement this feature, a batch in ociuldr means 500000 rows, if you specified a value for this option, it will switch file name after specified batch, the default value is 0 (generate all in one file).

    If you specify the batch option, please also specify the file option for file name pattern (use %d to specify the file sequence, start from 1). Watch the following example:

SQL> SELECT COUNT(*) FROM T_OBJID;

  COUNT(*)
----------
   2430720

    Unload with the following command and check log output:

C:\TEMP>ociuldr user=anysql/anysql query="select * from t_objid" batch=2 file=test_%d.txt

       0 rows exported at 2006-11-23 21:09:19
  500000 rows exported at 2006-11-23 21:09:20
1000000 rows exported at 2006-11-23 21:09:21
         output file test_1.txt closed at 1000000 rows.
  500000 rows exported at 2006-11-23 21:09:21
1000000 rows exported at 2006-11-23 21:09:22
         output file test_2.txt closed at 1000000 rows.
  430720 rows exported at 2006-11-23 21:09:22
         output file test_3.txt closed at 430720 rows.

    Please download the latest binary file, or download source code and compile it. For Linux/Unix compilation, specify the 64-bit IO compile option for large file support.

    Without dictionary, AUL cannot recover rows to dmp format. But we could manually create them, first we talk about the user information stored in "AULUSR.TXT" text file, the file name must be in upper case under Linux/Unix, and contains two columns (User ID and User Name, split by ","). For examples:

0,SYS
1,PUBLIC
2,CONNECT
3,RESOURCE
4,DBA
5,SYSTEM
......
25,TEST

    If you have system tablespace left, it could be generated by "unload table user$;" command.