Re: Database link creation error - repost & thanks
From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Tue, 10 Mar 2009 11:28:58 -0500
Message-ID: <203315c10903100928s66232e90q4f9d4ef104aa0121_at_mail.gmail.com>
Ram
I am unable to guess why this would happen. One possible guess is that if you create that user as first user, does this still happen? My thinking is that, may be there is only one database link allowed to sysadm in your environment (somehow). May be, some limitations through password file. Just a wild guess!
Date: Tue, 10 Mar 2009 11:28:58 -0500
Message-ID: <203315c10903100928s66232e90q4f9d4ef104aa0121_at_mail.gmail.com>
Ram
I am unable to guess why this would happen. One possible guess is that if you create that user as first user, does this still happen? My thinking is that, may be there is only one database link allowed to sysadm in your environment (somehow). May be, some limitations through password file. Just a wild guess!
Can you also set these two events, run create database link and send output please:
alter session set events '1031 trace name errorstack level 15'; alter session set events '10046 trace name context forever, level 12';
-- Cheers Riyaj Shamsudeen Principal DBA, Ora!nternals - http://www.orainternals.com Specialists in Performance, Recovery and EBS11i Blog: http://orainternals.wordpress.com On Tue, Mar 10, 2009 at 6:44 AM, Ram Raman <veeeraman_at_gmail.com> wrote:Received on Tue Mar 10 2009 - 11:28:58 CDT
> Hi all,
>
> I am wondering if anyone can guide me here, I am reposting. Thanks for the
> help. The database link creation seems to fail with one user id, but
> succeeds with other ids, even though the user creation follow the same
> steps:
>
>
> It looks like a very simple thing, but I am not sure what I am missing. I
> have modified the user name and the directory name slightly, but other than
> it is the same. The link creation seem to work OK with other user names that
> I have created like MM, DUMMY, TEST, etc. They all work. I can assure you
> the passwd was the same in both cases. It is just that when for the user
> DMUE113R (different from the real id I used) I get this error:
>
> /throra10/scripts/DCL/script_location >sqlplus
>
> SQL*Plus: Release 10.2.0.2.0 - Production on Fri Mar 6 15:09:18 2009
>
> Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
>
> Enter user-name: / as sysdba
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit
> Production
> With the Partitioning, OLAP and Data Mining options
>
> SYS_at_HRDEV> _at_create_user.sql\
> Enter value for user_name: VV
> old 1: create user &&user_name
> new 1: create user VV
> old 2: identified by &&user_name
> new 2: identified by VV
>
> User created.
>
> SYS_at_HRDEV> _at_grant_sys_privs.sql
> old 1: grant create session to &&user_name
> new 1: grant create session to VV
>
> Grant succeeded.
>
> old 1: grant create table to &&user_name
> new 1: grant create table to VV
>
> Grant succeeded.
>
> old 1: grant create procedure to &&user_name
> new 1: grant create procedure to VV
>
> Grant succeeded.
>
> old 1: grant create view to &&user_name
> new 1: grant create view to VV
>
> Grant succeeded.
>
> SYS_at_HRDEV>
>
> SYS_at_HRDEV>
> SYS_at_HRDEV> grant create database link to VV;
>
> Grant succeeded.
>
> SYS_at_HRDEV> conn VV
> Enter password:
> Connected.
> VV_at_HRDEV> create database link HRTST
> 2 connect to SYSADM identified by passwd
> 3 using 'HRTST';
>
> Database link created.
>
> VV_at_HRDEV> exit
> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
> - 64bit Production
> With the Partitioning, OLAP and Data Mining options
> /throra10/scripts/DCL/script_location >sqlplus
>
> SQL*Plus: Release 10.2.0.2.0 - Production on Fri Mar 6 15:11:19 2009
>
> Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
>
> Enter user-name: / as sysdba
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit
> Production
> With the Partitioning, OLAP and Data Mining options
>
> SYS_at_HRDEV> _at_create_user.sql
> Enter value for user_name: DMUE113R
> old 1: create user &&user_name
> new 1: create user DMUE113R
> old 2: identified by &&user_name
> new 2: identified by DMUE113R
>
> User created.
>
> SYS_at_HRDEV> _at_grant_sys_privs.sql
> old 1: grant create session to &&user_name
> new 1: grant create session to DMUE113R
>
> Grant succeeded.
>
> old 1: grant create table to &&user_name
> new 1: grant create table to DMUE113R
>
> Grant succeeded.
>
> old 1: grant create procedure to &&user_name
> new 1: grant create procedure to DMUE113R
>
> Grant succeeded.
>
> old 1: grant create view to &&user_name
> new 1: grant create view to DMUE113R
>
> Grant succeeded.
>
> SYS_at_HRDEV> grant create database link to DMUE113R;
>
> Grant succeeded.
>
> SYS_at_HRDEV> conn DMUE113R
> Enter password:
> Connected.
> DMUE113R_at_HRDEV> create database link HRTST
> 2 connect to SYSADM identified by passwd
> 3 using 'HRTST';
> create database link HRTST
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
>
>
> DMUE113R_at_HRDEV>
>
> Thanks.
>
>
-- http://www.freelists.org/webpage/oracle-l