Re: contexts: getting DDL

From: Nigel Thomas <nigel_at_preferisco.com>
Date: Wed, 5 Jul 2006 04:45:59 -0700 (PDT)
Message-ID: <20060705114559.95489.qmail@web54709.mail.yahoo.com>

>> 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

Received on Wed Jul 05 2006 - 06:45:59 CDT