Skip to Main Content
  • Questions
  • Database context needs to auto create every time the DB startup

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Dennis.

Asked: October 31, 2016 - 2:06 pm UTC

Last updated: October 31, 2016 - 11:23 pm UTC

Version: 11.0.1.3

Viewed 1000+ times

You Asked

I'm using 11.2.0.3 DB.

I created a database context using the sql below.

This works fine but if I restart the DB this context does not exists any more.

I have to logon and run this command again.

I'm not sure if this is intended functionality or not but is there a way that I do not need to manually run this every time the DB startup?

We have an application that is being used in many DBs in different locations and it would be too much to maintain in by manually running this command every time a DB startup.

Thanks!

CREATE OR REPLACE CONTEXT apex_ctx using kvs_apex_ctx accessed globally
/

and Chris said...

What makes you think the context doesn't exist anymore?

The context should survive a restart of the instance. If it isn't, either you're doing something wrong or you've hit a bug!


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

To Dennis

J. Laurindo Chiappa, October 31, 2016 - 6:35 pm UTC

Hi, Dennis : maybe the question here is about the DATA inside a context, not about the context creation per se ? We must think about a CONTEXT much alike a Global Temporary Table, in the sense that, after the Creation, the object per se will remain even after a restart, but the DATA inside it will not... An example :

==> will create a GLOBAL CONTEXT defined with a Trusted source :

SYSTEM:@XE:SQL>create context gctx
2 using set_gctx
3 accessed globally;

Context created.

=> creating the trusted source :

SYSTEM:@XE:SQL>create or replace procedure set_gctx
2 (
3 p_attr1_val varchar2
4 ) as
5 begin
6 dbms_session.set_context ('GCTX','ATTR1', p_attr1_val);
7 end;
8 /

Procedure created.

=> obviously, just after the creation the Context does not contains any data :

SYSTEM:@XE:SQL>select sys_context('GCTX','ATTR1') from dual;

SYS_CONTEXT('GCTX','ATTR1')
------------------------------------------------------------------

=> put data in the Context :

SYSTEM:@XE:SQL>exec set_gctx('Attrib1_Value');

PL/SQL procedure successfully completed.

=> the result :

SYSTEM:@XE:SQL>select sys_context('GCTX','ATTR1') from dual;

SYS_CONTEXT('GCTX','ATTR1')
--------------------------------------------------------------
Attrib1_Value

=> being a Global Context, the data remains for other sessions/users :

HR:@XE:SQL>select sys_context('GCTX','ATTR1') from dual;

SYS_CONTEXT('GCTX','ATTR1')
---------------------------------------------------------
Attrib1_Value

HR:@XE:SQL>

====> The main point : much alike a GTT, after a database reboot the DATA will not persist, BUT the Object will exist - empty, but Will Exist :

SYS:AS SYSDBA@xe:SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS:AS SYSDBA@xe:SQL>exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 31 16:18:50 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SYS:AS SYSDBA@XE:SQL>startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260048 bytes
Variable Size 679478192 bytes
Database Buffers 381681664 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.

SYS:AS SYSDBA@XE:SQL>select sys_context('GCTX','ATTR1') from dual;

SYS_CONTEXT('GCTX','ATTR1')
-----------------------------------------------------------------------------


SYS:AS SYSDBA@XE:SQL>


This was your issue, abou DATA PERSISTENCE in the context, or not ? If yes, you will need to persist the data in a real table and later in a startup trigger, put the data from the real table back inside the Context, maybe ...

Regards,

J. Laurindo Chiappa
Connor McDonald
October 31, 2016 - 11:23 pm UTC

Nice input.

Dennis - if you need context data to persist through a restart, why not just use a table ?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library