Oracle Db: Querying out Ctx_Ddl Preferences

Question

In trying to manipulate the storage parameters of context indexes, I've specified a new CTX Preference called mystore, as follows:

DECLARE
   prefs_  VARCHAR2(20) := 'mystore';
BEGIN
   Ctx_Ddl.Drop_Preference (prefs_);
   Ctx_Ddl.Create_preference (prefs_, 'BASIC_STORAGE');
   Ctx_Ddl.Set_Attribute (prefs_, 'I_TABLE_CLAUSE', 'tablespace TIX_TS storage (initial 1M)'); 
   Ctx_Ddl.Set_Attribute (prefs_, 'K_TABLE_CLAUSE', 'tablespace TIX_TS storage (initial 1M)'); 
   Ctx_Ddl.Set_Attribute (prefs_, 'R_TABLE_CLAUSE', 'tablespace TIX_TS storage (initial 1M) lob (data) store as (cache)');
   Ctx_Ddl.Set_Attribute (prefs_, 'N_TABLE_CLAUSE', 'tablespace TIX_TS storage (initial 1M)');
   Ctx_Ddl.Set_Attribute (prefs_, 'I_INDEX_CLAUSE', 'tablespace TIX_TS storage (initial 1M) compress 2');
   Ctx_Ddl.Set_Attribute (prefs_, 'P_TABLE_CLAUSE', 'tablespace TIX_TS storage (initial 1M)');
   Ctx_Ddl.Set_Attribute (prefs_, 'S_TABLE_CLAUSE', 'tablespace TIX_TS storage (initial 1M)');
   Ctx_Ddl.Set_Attribute (prefs_, 'D_TABLE_CLAUSE', 'tablespace TIX_TS storage (initial 1M) LOB(DOC) STORE AS SECUREFILE (tablespace TIX_TS cache)');
   Ctx_Ddl.Set_Attribute (prefs_, 'O_TABLE_CLAUSE', 'tablespace TIX_TS storage (initial 1M) LOB(OFFSET_INFO) STORE AS SECUREFILE (tablespace TIX_TS cache)');
   Ctx_Ddl.Set_Attribute (prefs_, 'FORWARD_INDEX',  'YES');
   Ctx_Ddl.Set_Attribute (prefs_, 'SAVE_COPY',      'PLAINTEXT');
END;

The above code works fine such that when I create my Context Index with the DDL below, the associated DR$ tables are created in the TIX_TS tablespace along with all the other settings I've associated to the mystore handle-name.

CREATE INDEX my_tix ON my_tab (text_id$) INDEXTYPE IS ctxsys.context PARAMETERS ('storage mystore')

My question though, is how to reverse-engineer the settings of mystore? In other words, given that I (or someone else) has set the values of the properties named mystore, how do I query these out to see if they need to be updated or not?

The second level question is to ask how do I know which settings are there to be queried out? Of course I know that settings with the handle mystore are available to use, because I just set them. But how can I know which other handles have been set by other people?

you can use ctx_report.describe_policy or ctx_report.describe_index to get the prefs, or use the ctx_preferences... views. — gsalem, Dec 09 '19 at 11:26

score 0 · Accepted Answer · answered Dec 09 '19 at 16:54

As pointed out by @gsalem, the following SQL gives us visibility of the defined preferences:

select p.*, '' ">>>", v.*
from   ctx_preferences p
       INNER JOIN ctx_preference_values v
               ON v.prv_owner       = p.pre_owner
              AND v.prv_preference  = p.pre_name

That's all there is to it, apparently.

Oracle Db: Querying out Ctx_Ddl Preferences

1 Answers1