>> How to get DDL for contexts?

I was just playing with these...

As user LOG4PLSQL:

select * from all_context
/
NAMESPACE               SCHEMA                 PACKAGE                
------------------------------ ------------------------------ ------------------------------
MY_CONTEXT              LOG4PLSQL               MY_CONTEXT_PKG          
1 rows selected

select owner, object_name, object_type from all_objects where object_name like 'MY_CONTEXT'
/
OWNER                 OBJECT_NAME             OBJECT_TYPE      
------------------------------ ------------------------------ -------------------
SYS                   MY_CONTEXT              CONTEXT        
1 rows selected

Note that the ALL_CONTEXT view doesn't list contexts until they are activated (when a session calls the specified package to set an attribute in a namespace). Meanwhile note also that the context object is owned by SYS even though it was created (in my case) by LOG4PLSQL.

The best source of information is the DBA_CONTEXT view

select * from dba_context
/

NAMESPACE               SCHEMA                 PACKAGE                TYPE            
------------------------------ ------------------------------ ------------------------------ ----------------------
REGISTRY$CTX             SYS                   DBMS_REGISTRY_SYS         ACCESSED LOCALLY    
DR$APPCTX               CTXSYS                 DRIXMD                 ACCESSED LOCALLY    
MY_CONTEXT              LOG4PLSQL               MY_CONTEXT_PKG           ACCESSED LOCALLY    
3 rows selected

You should be able to reconstruct your contexts by querying this view and presumably skipping the first two:

select 'create or replace context '||namespace||
    ' using '||schema||'.'||package||
    ' '||type||';' sqlcmd
from dba_context
where schema not in ('SYS','CTXSYS')
/

Regards Nigel
--
http://www.freelists.org/webpage/oracle-l