--In 11.2.0.2.0 DB dev1
create table dbacode.test (ARTIST_ID number not null, artist_nm varchar2(50),first_nm varchar2(50));
insert into dbacode.test values(1,'John Constable','John');
insert into dbacode.test values(2,'Roy Lichtenstein','Roy');
insert into dbacode.test values(3,'Jackson Pollock','Jackson');
insert into dbacode.test values(4,'Alexander Calder','Alexander');
insert into dbacode.test values(5,'John P. Benson','John; P.');
commit;
select * from test;
alter table dbacode.test add SEARCH_COLUMN  CHAR(1);

exec ctx_ddl.create_preference('test_mcds', 'MULTI_COLUMN_DATASTORE');
exec ctx_ddl.set_attribute( 'test_mcds','columns','artist_nm,first_nm');

--CTX_Preferences and CTX_Preference_values will have entry for 'TEST_MCDS'
col PRV_VALUE for a10
set linesize 120
select * from ctx_preference_values where prv_preference like 'TEST%';
col PRE_VALUE for a10
select * from ctx_preferences  where pre_name like 'TEST%';


begin
  ctx_ddl.create_section_group('test_secgrp','BASIC_SECTION_GROUP');
  ctx_ddl.add_field_section('test_secgrp','ARTIST_NM','ARTIST_NM',TRUE);
  ctx_ddl.add_field_section('test_secgrp','FIRST_NM','FIRST_NM',TRUE);
end;
/

--Now may see field info in CTX_SECTIONS & ctx_section_groups
COLUMN SEC_OWNER FORMAT a10
COLUMN SEC_SECTION_GROUP FORMAT a30
COLUMN SEC_TYPE FORMAT a10
COLUMN SEC_NAME FORMAT a30
COLUMN SEC_TAG FORMAT a30
set linesize 200
SELECT SEC_SECTION_GROUP,SEC_OWNER,SEC_TYPE,SEC_ID,SEC_NAME,SEC_TAG 
FROM CTX_SECTIONS
WHERE SEC_SECTION_GROUP=UPPER('test_secgrp')
AND SEC_NAME=SEC_TAG 
ORDER BY 1,2,3,4;

select * from ctx_section_groups where sgp_name='TEST_SECGRP';


--Create CTX
CREATE INDEX dbacode.TEST_CTX ON TEST (SEARCH_COLUMN)
   INDEXTYPE IS CTXSYS.CONTEXT  
   PARAMETERS ('datastore test_mcds 
              section group test_secgrp 
               sync (on commit)') ;

--Query by CTX
select artist_nm,first_nm from test
where contains
(search_column,'John within artist_nm
or
Roy within first_nm',1)>0;

set long 200000000
set pagesize 0
set head off
set ECHO OFF FEEDBACK OFF
set linesize 400
set trimspool on
col SCRIPT format a300 word_wrapped

SELECT CTX_REPORT.CREATE_INDEX_SCRIPT('test_ctx') from dual;

--expdp
expdp directory=DB TABLES=TEST  dumpfile=DBACODE.TEST.2013-10-10.dmp logfile=expdp.DBACODE.TEST.2013-10-10.log
/*
Username: dbacode
Password: xxxxxxx
*/

--Drop table in dev1
drop table dbacode.test;
--Query again after drop table
COLUMN SEC_OWNER FORMAT a10
COLUMN SEC_SECTION_GROUP FORMAT a30
COLUMN SEC_TYPE FORMAT a10
COLUMN SEC_NAME FORMAT a30
COLUMN SEC_TAG FORMAT a30
set linesize 200
SELECT SEC_SECTION_GROUP,SEC_OWNER,SEC_TYPE,SEC_ID,SEC_NAME,SEC_TAG 
FROM CTX_SECTIONS
WHERE SEC_SECTION_GROUP=UPPER('test_secgrp')
AND SEC_NAME=SEC_TAG 
ORDER BY 1,2,3,4;

select * from ctx_section_groups where sgp_name='TEST_SECGRP';
col PRV_VALUE for a10
set linesize 120
select * from ctx_preference_values where prv_preference like 'TEST%';
col PRE_VALUE for a10
select * from ctx_preferences  where pre_name like 'TEST%';

--query results show that information still there in DB dev1

--impdp
impdp directory=DB TABLES=TEST  dumpfile=DBACODE.TEST.2013-10-10.dmp logfile=impdp.DBACODE.TEST.2013-10-10.log

----Query by CTX after impdp to DB dev1
select artist_nm,first_nm from test
where contains
(search_column,'John within artist_nm
or
Roy within first_nm',1)>0; 

--query ater impdp to DB dev1
COLUMN SEC_OWNER FORMAT a10
COLUMN SEC_SECTION_GROUP FORMAT a30
COLUMN SEC_TYPE FORMAT a10
COLUMN SEC_NAME FORMAT a30
COLUMN SEC_TAG FORMAT a30
set linesize 200
SELECT SEC_SECTION_GROUP,SEC_OWNER,SEC_TYPE,SEC_ID,SEC_NAME,SEC_TAG 
FROM CTX_SECTIONS
WHERE SEC_SECTION_GROUP=UPPER('test_secgrp')
AND SEC_NAME=SEC_TAG 
ORDER BY 1,2,3,4;

select * from ctx_section_groups where sgp_name='TEST_SECGRP';
col PRV_VALUE for a10
set linesize 120
select * from ctx_preference_values where prv_preference like 'TEST%';
col PRE_VALUE for a10
select * from ctx_preferences  where pre_name like 'TEST%';

--!!! The information in CTX_* are not removed after impdp the table in same database

===== ** Now import to another database - ucd1 - same version 11.2.0.2.0
-- Query before impdp to ucd1
SELECT * from CTX_SECTIONS;  
select * from ctx_section_groups where sgp_name='TEST_SECGRP';
col PRV_VALUE for a10
set linesize 120
select * from ctx_preference_values where prv_preference like 'TEST%';
col PRE_VALUE for a10
select * from ctx_preferences  where pre_name like 'TEST%';

--All above queries return 'no rows selected'
impdp directory=DB TABLES=TEST   dumpfile=DBACODE.TEST.2013-10-10.dmp logfile=impd_to_ucd1.DBACODE.TEST.2013-10-10.impdp.log

----Query by CTX index after impdp to ucd1
select artist_nm,first_nm from test
where contains
(search_column,'John within artist_nm
or
Roy within first_nm',1)>0; 

---Query catalog after impdp to ucd1

SELECT * from CTX_SECTIONS;  
select * from ctx_section_groups where sgp_name='TEST_SECGRP';
col PRV_VALUE for a10
set linesize 120
select * from ctx_preference_values where prv_preference like 'TEST%';
col PRE_VALUE for a10
select * from ctx_preferences  where pre_name like 'TEST%';

--!! Now all return 'no rows selected'

set long 3000
select ctx_report.describe_index('TEST_CTX') from dual;
--The report has the 2 fields which indexed in oracle text.