Query to find out the preferences and section_groups

mNem

How do I find out what are the ...

1. default preferences available to the current user

2. user defined preferences and their attributes

3. user defined section_groups and their attributes

The schema has CTXAPP granted.

I am looking for queries that do NOT involve dba_* tables or views (if possible).

Thanks.

Roger Ford-Oracle

1. default preferences available to the current user

If you mean pre-defined preferences then:

SELECT pre_name FROM ctx_user_preferences WHERE pre_owner = 'CTXSYS';

2. user defined preferences and their attributes

SELECT pre_name, prv_value FROM ctx_user_preferences, ctx_user_preference_values WHERE pre_name = prv_preference (+);

The outer join is necessary because some preferences will not have attributes.

3. user defined section_groups and their attributes

Depending on what info you're after, you'll want to play around with ctx_user_section_groups and ctx_user_sections

A simple listing of the sections in section groups would be

SELECT sgp_name, sec_name FROM ctx_user_section_groups, ctx_user_sections WHERE sgp_name = sec_section_group (+);

Roger Ford-Oracle

1. default preferences available to the current user

If you mean pre-defined preferences then:

SELECT pre_name FROM ctx_user_preferences WHERE pre_owner = 'CTXSYS';

2. user defined preferences and their attributes

SELECT pre_name, prv_value FROM ctx_user_preferences, ctx_user_preference_values WHERE pre_name = prv_preference (+);

The outer join is necessary because some preferences will not have attributes.

3. user defined section_groups and their attributes

Depending on what info you're after, you'll want to play around with ctx_user_section_groups and ctx_user_sections

A simple listing of the sections in section groups would be

SELECT sgp_name, sec_name FROM ctx_user_section_groups, ctx_user_sections WHERE sgp_name = sec_section_group (+);