Re: dbms_aqadm.create_queue_table problems

Hi!

Your session is probably crashing during execution of create_queue_table procedure. Check your user_dump_dest for trace files and alert.log for error messages.

Tanel.

"Randy Nichols" <randynichols_at_yahoo.com> wrote in message news:R2RXa.1378$Q63.82551_at_newsread2.prod.itd.earthlink.net...
> I'm using Oracle 9.2.0.3 (patched) and SQL*PLUS to set up an Oracle queue,
> but keep encountering a problem when trying to run the
> "dbms_aqadm.create_queue_table()" procedure.
>
> This is what happens:
> --------------------------------------------
> SQL> exec
>

dbms_aqadm.create_queue_table(queue_table=>'queue_message_table',queue_paylo
> ad_type=>'aqadm.queue_message_type');
> BEGIN
>

dbms_aqadm.create_queue_table(queue_table=>'queue_message_table',queue_paylo
> ad_type=>'aqadm.queue_message_type'); END;
>
> *
> ERROR at line 1:
> ORA-03113: end-of-file on communication channel
> -----------------------------------------------
>
> Basically, I get logged off somewhere during the process. I don't know if
> that is what causes the error message, or vice versa.
>
> I am following verbatim an example I found on the web (Akadia AG).
>
> These are the steps leading up to the error:
> ----------------------------------------------------------------------
> SQL> connect system/<password>@rms as sysdba
> Connected.
> SQL> create role my_aq_user_role;
>
> Role created.
>
> SQL> grant create session, aq_user_role to my_aq_user_role;
>
> Grant succeeded.
>
> SQL> exec dbms_aqadm.grant_system_privilege(privilege=>'enqueue_any',
> grantee=>'my_aq_user_role',admin_option=>false);
>
> PL/SQL procedure successfully completed.
>
> SQL> exec
>

dbms_aqadm.grant_system_privilege(privilege=>'dequeue_any',grantee=>'my_aq_u
> ser_role',admin_option=>false);
>
> PL/SQL procedure successfully completed.
>
> SQL> create role my_aq_adm_role;
>
> Role created.
>
> SQL> grant connect, resource,aq_administrator_role to my_aq_adm_role;
>
> Grant succeeded.
>
> SQL> create user aqadm identified by aqadm;
>
> User created.
>
> SQL> grant my_aq_adm_role to aqadm;
>
> Grant succeeded.
>
> SQL> create user aquser identified by aquser;
>
> User created.
>
> SQL> grant my_aq_user_role to aquser;
>
> Grant succeeded.
>
> SQL> connect aqadm/aqadm;
> Connected.
> SQL> create type queue_message_type
> 2 as object(
> 3 no number,
> 4 title varchar2(30),
> 5 text varchar2(2000));
> 6 /
>
> Type created.
>
> SQL> grant execute on queue_message_type to my_aq_user_role;
>
> Grant succeeded.
>
> SQL> exec
>

dbms_aqadm.create_queue_table(queue_table=>'queue_message_table',queue_paylo
> ad_type=>'aqadm.queue_message_type');
> BEGIN
>

dbms_aqadm.create_queue_table(queue_table=>'queue_message_table',queue_paylo
> ad_type=>'aqadm.queue_message_type'); END;
>
> *
> ERROR at line 1:
> ORA-03113: end-of-file on communication channel
>
> --------------------------------------------------------------------------

--


> ----------

> I'm having this problem on two separate Oracle 9.2.0.3 installations.  I

> must be omitting something.

>

> Is anyone familiar with this problem?

>

> Thanks,

>

> Randy Nichols

>

>