AnySQL.net English

    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.

    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
/

    Oracle compress table is an new feature since Oracle 9i, it could save you a lot of storage, I have seen this new feature used in data warehouse. In oracle 9i, compress table have some bugs, for example cannot modify table structure, I do believe Oralce will fix these problems. So I want to add compress table support in AUL/MyDUL, following is a first step of my research, I found this out on the train.

    Compress need some metadata, in Oracle, the compress unit is block, which means that in a compress table, some blocks are compressed, and some are not, depend on whether compress can save space for you. We could call the compress block is self contained, no dictionary information required from system tables when extracting.

    I create a test table contains two column (col1 and col2), the values of col1 are "Compress1" and "Compress2", the values of col2 are "Compress Row1" and "Compress Row2", I insert into the table with different combination of values. Then dump a block, there are 709 rows in one 8k size block.

  The structure of compress block seems likely with cluster block, the table count is marked as 2, table 0 is the different combination of words list, as following:

tab0:
   0 = Compress1
   1 = Compress2
   3 = Compress Row1
   4 = Compress Row2
   5 = 0 3
   6 = 0 4
   7 = 1 3
   8 = 1 4

  Table 1 contains the real rows:

tab1:
    0 = Flag Trans Fieldcount 5
    1 = Flag Trans Fieldcount 6
    2 = Flag Trans Field count 7
    3 = Flag Trans Field count 8
    4 = Flag Trans Field count 6
    5 = Flag Trans Field count 7
    ......

    These is all I have for compress table.

    DEPRECATION OF RAW DEVICES IN LINUX 2.6 KERNELS (357492.1, published on 28-Jun-2006)

    Starting with the 2.6 Linux kernel, raw devices are being phased out in favor of O_DIRECT access directly to the block devices.

    With Oracle RDBMS 10.2.0.2 and higher, block devices can be accessed via any of the following methods, and utilized by RDBMS:

• Directly to the block device
• Via ASMLib mapped devices
• OCFS2 devices (Note: OCFS2 is still Pending Certification)
• LVM2 mapped devices if single-instance

    This is really a good news for Oracle database running on Linux, but this is a new feature started with 10.2.0.2, the latest version, who knows is there any bug. I will do a test on my VMWare host.

    Oracle Context is a good solution for the text search in enterprise, How to install Context in Oracle manually?

  1, create tablespace drsys
  2, run ?/ctx/admin/dr0csys ctxsys drsys temp01
  3, conn to ctxsys user
  4, run ?/ctx/admin/dr0inst ?/ctx/lib/libctxx9.so
  5, run ?/ctx/admin/defaults/drdefus.sql

    Then, create a demo table to verify the result as following:

SQL> create table  cr_ctxdemo (col1 number not null primary key,
  2  col2 varchar2(100));

Table created.

SQL> insert into cr_ctxdemo values (1,'Oracle A B C');

1 row created.

SQL> commit;

Commit complete.

SQL> create index idx_cr_ctxdemo_col2 on cr_ctxdemo(col2)
  2  indextype is ctxsys.context;

Index created.

SQL> select col1 from cr_ctxdemo where contains(col2,'A or C') > 0;

      COL1
----------
        1

SQL> select col1 from cr_ctxdemo where contains(col2,'A and B')> 0;

      COL1
----------
        1
SQL>  select col1 from cr_ctxdemo where contains (col2,'A') > 0;

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=RULE (Cost=2 Card=1 Bytes=72)
  1    0  TABLE ACCESS (BY INDEX ROWID) OF 'CR_CTXDEMO' (Cost=2 ..)
  2    1    DOMAIN INDEX OF 'IDX_CR_CTXDEMO_COL2' (Cost=0)

    There is a more detail article on Eygle.com about how to install Oracle context.