AnySQL.net English

    My old personal site (http://www.anysql.net) was hacked about two weeks ago, and very unfortunately I have no backup of my site, finally I lost about 4 months of data. AnySQL.net was hosted on my friend's WEB space, due to security requirement of my friends, I was unable to upload updated software to anysql.net, please come here to download latest version of my utilities.

    The hacker's behavior of destroy personal site should be blamed, there is no reason to do like this. And the hacker is obviously not professional at all.

    Thanks for your visit.

    ociuldr is a free OCI utility to extract table rows from Oracle database to flat file. In previous release, when extracting long type column, it will read maximum 32767 bytes, but now I have add a new command line option "long=maxbytes" to control the maximum length to extract. This could save some client memory when extract short long column.

    The new default maximum value is 4000, for example:

$ociuldr user=anysql/anysql@prod sql=test.sql
    4100 bytes allocated for column COL1 (1)
  400100 bytes allocated for column COL2 (2)

    Test the new command line option, you will found the difference.

$ociuldr user=anysql/anysql@prod sql=test.sql long=8000
    4100 bytes allocated for column COL1 (1)
  800100 bytes allocated for column COL2 (2)

    New binary have been uploaded, you could download or update it.

    I have seen someone asking this question few times, there is the Oracle binary bits and the database bits. Following are the three methods can be used to identify this:

    According to the V$VERSION view, for 64 bits version, it will print out, else 32 bits:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

    According to the column type of address column of some V$ views:

SQL> desc v$sqltext
Name                             Null?    Type
-------------------------------- -------- --------------
ADDRESS                                   RAW(8)
HASH_VALUE                                NUMBER
COMMAND_TYPE                              NUMBER
PIECE                                     NUMBER
SQL_TEXT                                  VARCHAR2(64)

    Using "file oracle" command on Unix/Linux:

$> file oracle
oracle:         ELF 64-bit MSB executable SPARCV9 Version 1, ...

    There are another easy way but I cannot remember it.

    We have a very hot post on itpub.net talking about this, the poster is me. I think of this topic after receiving several times of AUL/MyDUL recovery requests for dropping tablespace or dropping tables by wrong mouse click on GUI utilities (OEM, Toad, PL/SQL Developer).

    Another reason was that after interviewing some people, I found lots of people cannot write the correct simple command, or did not know how to query performance data from some important "V$XXX" views.

    I prefer command line tools as a DBA. SQL*Plus, AnySQL, OTop/OPMon/OTune have been my favorite tools when managing and tuning a database. AnySQL have been my replacement of SQL*Plus for common queries, it's java based, do not need "COLUMN xxx FORMAT xxx" to adjust the column width. OTop/OPMon/OTune have completely replay the role of Oracle statspack for me.

    Of cause we should not blame the tools itself, in the first cases, they should do the privilege management better. For the second case, command line utilities may really help. What's your opinion?

    When you use old version of exp to export tables with LOB column from Oracle 9.2.0.5 or higher version, you will get an error "EXP-00003 : no storage definition found for segment .....", actually this is an Oracle bug, you could temporary get it resolved by replace a view "exu9tne", as following:

    Before exporting, run the following SQL under sys:

CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
UNION ALL
SELECT * FROM SYS.EXU9TNEB
/

    After exporting, run the following to restore the view definition according to Metalink Notes.

CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
/