Moving Oracle Text Index

Moving an index from one tablespace to another tablespace is very easy task. It can be accomplished by using rebuild option:

ALTER INDEX index_name REBUILD TABLESPACE NEW_TABLESPACE;

You can even do it online for most indexes:

ALTER INDEX index_name REBUILD TABLESPACE NEW_TABLESPACE ONLINE;

But trying to move a domain index (such as Oracle Text Index) is not so simple. If you follow thw above way it will cause error:

ALTER INDEX my_text_index REBUILD TABLESPACE NEW_TABLESPACE;
ORA-29871: invalid alter option for a domain index

You may ask What is the reson behind that error and how to resolve it? In fact Domain index is a set of other objects. Oracle Text CONTEXT index is set of tables:

* DR$[index_name]$I
* DR$[index_name]$K
* DR$[index_name]$N
* DR$[index_name]$R

Unfortunately to move context index you have to drop and recreate that text index. But first you need to specify storage parameters:

begin
ctx_ddl.create_preference('TEXT_INDEX_STORE', 'BASIC_STORAGE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'I_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'K_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'R_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'N_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'I_INDEX_CLAUSE',
'tablespace NEW_TABLESPACE COMPRESS 2');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'P_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
end;
/

and then just drop the previously created index and recreate that index with changed parameters

create index MY_TEXT_I on MY_TAB(text_column)
indextype is ctxsys.context parameters('storage TEXT_INDEX_STORE');

TEXT Index Size

Oracle Text Index, an interMedia Index, is powerful for searching user specified text within a column or columns. It is widely used in web applications such as search engines, content management systems. As a DBA, accurately identify sizes of text indexes in the database and monitor their growth is important. But the problem is that text index is a domain index, and every text index internally consists of several tables with names prefixed with 'DR$'. Some of these tables have their own indexes and some of them are Index Organized Tables (IOTs). If you search in dba_segments, there is no segment for text indexes. SO when you try to calculate the size of an text index, you should consider all of these DR$ tables and their indexes. The script provided here gives a fast report of the sizes of all text indexes in the schema:

COL table_name format A30;
COL ind_nm format A30;
COL KB 9999999999;

select table_name, x.index_name, sum(KB) KB from
(select substr(table_name, 4, instr(table_name, '$', -1)-4) index_name,
sum(bytes)/1024 KB
from user_tables t, user_segments s
where t.table_name = s.segment_name and t.table_name like 'DR$%$%'
group by substr(table_name, 4, instr(table_name, '$', -1)-4)
union
select substr(table_name, 4, instr(table_name, '$', -1)-4) index_name,
sum(bytes)/1024 KB from user_indexes i, user_segments s
where i.index_name = s.segment_name and i.table_name like 'DR$%$%'
group by substr(table_name, 4, instr(table_name, '$', -1)-4)
) x, user_indexes ind
where x.index_name = ind.index_name
group by table_Name, x.index_name
order by table_name, x.index_name;

Related Topics:

1. Introduction of Oracle Text
2. Oracle Text Installation
   

Features of Oracle Flashback

1. Flashback query

Introduced with Oracle9i, Flashback Query provides the ability to view the data as it existed in the past. By default, operations on the database use the most recent committed data available. If you want to query the database as it was at some time in the past, you can do so with the Flashback Query feature.

SQL> select dbms_flashback.get_system_change_number from dual;

SQL>select id from hasan.test where id in (100,101);
SQL>delete from hasan.test where id in (100,101);
SQL>commit;

SQL>select id from hasan.test as of timestamp systimestamp - interval '20' minute where id not in (select id from hasan.test);

SQL>insert into hasan.test
select * from hasan.test as of timestamp systimestamp - interval '10' minute
where id not in (select id from hasan.test);

SQL>commit;


2. Flashback Table

When a human or application error occurs, you want to be able to restore the state of one or more tables to a point in time before the problem occurred. Flashback Table provides the DBA the ability to recover a table or a set of tables to a specified point in time quickly, easily, and online. Flashback Table restores the tables while automatically maintaining its associated attributes such as - the current indexes, triggers and constraints

SQL> select dbms_flashback.get_system_change_number from dual;

SQL>select count(*) from hasan.test;
SQL>delete from hasan.test;
SQL>commit;


SQL>alter table hasan.test enable row movement;
SQL>Flashback table hasan.test to SCN ;
SQL>Flashback table hasan.test to timestamp systimestamp - interval '5' minute ;

SQL>select count(*) from hasan.test;


3. Flashback Version Query

Flashback Versions Query provides a way to audit the rows of a table and retrieve information about the transactions that changed the rows. It retrieves all committed versions of the rows that exist or ever existed between the time the query was issued and a point in time in the past. It accomplishes this by utilizing Automatic Undo Management.

SQL> select dbms_flashback.get_system_change_number from dual;

SQL> select password from hasan.test where id in (100,101);

SQL> update hasan.test set password='Bang'where id in (101);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> update hasan.test set password='ladesh'where id in (100);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> delete from hasan.test where id in (100,101);
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> insert into hasan.test
select * from hasan.test as of timestamp systimestamp - interval '20' minute
where id not in (select id from hasan.test);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> SELECT versions_startscn start_scn
, versions_endscn end_scn
, versions_xid transection_id
, DECODE(versions_operation,'I','Insert','U','Update','D','Delete',NULL) oper
, id, password
FROM hasan.test versions between scn 85174629 AND 85186130
WHERE id in (100,101) Order by start_scn;


4. Flashback Transaction Query

You may discover that somehow data in a table has been inappropriately changed. To research this change, you can use multiple flashback queries to view row data at specific points in time. More efficiently, you can use Flashback Versions Query feature to view all changes to a row over a period of time and the associated transaction id's. This feature allows you to append VERSIONS BETWEEN clause to a SELECT statement that specifies an SCN or timestamp range between which you want to view changes to row values. Once you identify an erroneous transaction, you can then use the Flashback Transaction Query feature to identify other changes that were done by the transaction, and to request the undo SQL to reverse those changes.

SQL> sqlplus / as sysdba

SQL> SELECT * FROM flashback_transaction_query x
where x.table_name='LOGIN' AND x.table_owner='HASAN'
AND x.start_timestamp >(systimestamp - interval '10' minute);


5. Flashback Drop

Dropping of objects by accident has always been a problem for users and DBAs alike. Users soon realize their mistake but then it's too late and historically there is no easy way to recover those dropped tables, indexes, constraints, triggers, etc. Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin.

SQL> create table table1 (a number(1));
SQL> INSERT INTO table1 values(1);
SQL> INSERT INTO table1 values(2);
SQL> INSERT INTO table1 values(3);
SQL> INSERT INTO table1 values(4);
SQL> commit;

SQL> select dbms_flashback.get_system_change_number from dual;

SQL> DROP TABLE hasan.table1;

SQL> SELECT ur.base_object, ur.object_name,ur.original_name FROM user_recyclebin ur;

SQL> FASHBACK TABLE table1 TO BEFORE DROP;


6.Flashback Database

Flashback Database quickly rewinds an Oracle database to a previous time, to correct any problems caused by logical data corruptions or user errors. Flashback Database is like a 'rewind button' for your database. It provides database point in time recovery without requiring a backup of the database to first be restored. When you eliminate the time it takes to restore a database backup from tape, database point in time recovery is fast

Related Topics:

1. Flashback Part #1 Basics
2. Flashback Part #2 Setup and Maintenance
   

Uninstall Oracle 10gR2 On Solaris 5.10

To uninstall Oracle 10gR2 from Solaris 5.10, please do the followings

1. Remove all database, by running dbca.
2. Stop aall oracle running process : A. Database Control : $ORACLE_HOME/bin/emctl stop dbconsole B.Oracle Net listener : $ORACLE_HOME/bin/lsnrctl stop C. iSQL*Plus : $ORACLE_HOME/bin/isqlplusctl stop D.Ultra Search : $ORACLE_HOME/bin/searchctl stop
3. Start Oracle Universal installer locating at $ORACLE_HOME/oui/bin/runInstaller.
4. In the Welcome window, click Deinstall Products.
5. In the Inventory screen, select the Oracle home and the products that you want to remove, then click Remove.