AnySQL.net English

    In previous chapter, we have learned how to install it, now we will remove manually. Check the status of the options from dba_registry(9i or above) view.

SQL> col version format a14
SQL> col comp_name format a30
SQL> select COMP_NAME,VERSION,STATUS from dba_registry;

COMP_NAME                      VERSION        STATUS
------------------------------ -------------- -----------
Oracle9i Catalog Views        9.2.0.5.0      VALID
Oracle9i Packages and Types    9.2.0.5.0      VALID
Oracle Text                    9.2.0.5.0      VALID

    Drop all the context index in the database, here I will drop the index created in previous chapter.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
CR_CTXDEMO                    TABLE
DR$IDX_CR_CTXDEMO_COL2$I      TABLE
DR$IDX_CR_CTXDEMO_COL2$K      TABLE
DR$IDX_CR_CTXDEMO_COL2$N      TABLE
DR$IDX_CR_CTXDEMO_COL2$R      TABLE

SQL> DROP TABLE CR_CTXDEMO;

Table dropped.

SQL> select * from tab;

no rows selected

    Then we need to run two scripts, first run as ctxsys user ($ORACLE_HOME/ctx/admin/dr0drop.sql), second run as sys ($ORACLE_HOME/ctx/admin/dr0dsys.sql). Finally check the rows in dba_registry again.

SQL> col version format a14
SQL> col comp_name format a30
SQL> select COMP_NAME,VERSION,STATUS from dba_registry;

COMP_NAME                      VERSION        STATUS
------------------------------ -------------- -----------
Oracle9i Catalog Views        9.2.0.5.0      VALID
Oracle9i Packages and Types    9.2.0.5.0      VALID

    You could see that we have successfully remove it.

    Lot's of oracle sort will cause high CPU load, even memory sort, we could use index to avoid sort, but in partitioned table, there is some difference. Following is a test case designed by me, you could know how bind variable usage in partitioned key columns affect the sort. Obviously in this case only one partition will be queried whether bind variables or not, but Oracle cannot recognize this and still doing sort when run. Sometime Oracle database is really stupid.

    Run the following script to create the test table:

CREATE TABLE TEST_SORT
(
  COL1 NUMBER,
  COL2 NUMBER,
  pkey number,
  col3 varchar2(30)
)
partition by range(pkey)
(
  partition p0 values less than (1),
  partition p1 values less than (2),
  partition p2 values less than (3)
) tablespace data02;

INSERT INTO TEST_SORT
  SELECT ROWNUM, MOD(ROWNUM,100), mod(rownum,3), OBJECT_NAME
    FROM DBA_OBJECTS WHERE ROWNUM < 5001;

commit;
CREATE INDEX IDX_TEST_SORT ON (COL1,COL2,PKEY) LOCAL;

    I took a 10046 level 12 trace of previous example. I really want to know how much CPU will be consumed by this small sort operation. At lease sort require Oracle allocate the sort memory, and then run the sort function. Some time you system may have thousands of sort per second, if we cut half of the sort by change the SQL, how much load will be reduced? I cannot image it. I will do more research on sort later.

    Following is the trace output with bind variable on partitioned column (with sort):

SELECT * FROM TEST_SORT
  WHERE COL2=:p_co1 AND PKEY=:p_key AND COL1 IN (810,510,210)
  ORDER BY COL1

Rows    Row Source Operation
-------  ---------------------------------------------------
      3  SORT ORDER BY (cr=9 r=0 w=0 time=390 us)
      3  PARTITION ... (cr=9 r=0 w=0 time=246 us)
      3    INLIST ITERATOR  (cr=9 r=0 w=0 time=240 us)
      3    TABLE ACCESS BY ... (cr=9 r=0 w=0 time=205 us)
      3      INDEX RANGE SCAN ... (cr=6 r=0 w=0 time=141 us)

    This command is used to quickly remove a column from a table with reorganize all the blocks, And you could do the blocks reorganization when system is free. I noticed the problem when someone set unused a column by mistake and asking "Can I rollback the operation when no backup available?". Let's start with the following example:

SQL> DESC T_FID
Name                    Null?    Type
----------------------- -------- -------
COL1                            NUMBER
COL2                            NUMBER
COL3                            NUMBER
SQL> ALTER TABLE T_FID SET UNUSED (COL3);
Table altered.
SQL> alter system checkpoint;
System altered.

    Then you could query the COL$ directly (Columns: COL#, SEGCOL# and NAME), what's the new values for this columns? Here I will use AUL/MyDUL's DESCRIBE command to display the table structure after the operation.

    There is an MVIEW (MV_FACT_SALES) in my personal database, direct update is not allowed unless in replication, following error will be reported:

SQL> DESC MV_FACT_SALES
Name                    Null?    Type
----------------------- -------- -------------
F_MONTH                          VARCHAR2(7)
M_AMOUNT1                        NUMBER
M_AMOUNT2                        NUMBER

SQL> INSERT INTO MV_FACT_SALES VALUES ('3000-1',1500,1600);
INSERT INTO MV_FACT_SALES VALUES ('3000-1',1500,1600)
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

    But we can do DML through exchange partition, like manually refresh the MVIEW's data, check the MVIEW status first:

SQL> SELECT STALENESS,STALE_SINCE FROM USER_MVIEWS;

STALENESS           STALE_SINCE
------------------- ------------
FRESH

    Let's create a partitioned table to perform partition exchange: