ORA-28009
in [Oracle]
|
Prev: dbms_session.set_context from JDBC fails mysteriously, how can I trap it ?
Next: sqlldr not recognizing a file without an extension on Solaris
From: syd_p on 14 Feb 2010 17:45 Hi, I am running a shell script which call a perl script which uses DBI to connect to a remote (ie another server) which creates schemas ie table spaces, users, tables in the waiting DB. Or rather it does not: Database Connection Error: ORA-28009: connection as SYS should be as SYSDBA or SYSOPER (DBD ERROR: OCISessionBegin) The problem is as follows: ../create_stuff.sh <database type> <database name> <host> <username> <password> <port> I ran one of the many scripts locally on the unix command line with the hardcoded string "sqlplus sys as sysdba/bloggs" and that works OK. The problem is that the shell script, calls more shell scripts, calls perl scripts and passing in "sys" as a parameter fails at the Oracle 10G level cos we need the AS sysdba. And if I call the script with "sys as sysdba" "bloggs" (or similar tried a few things) it goes wrong big time. Possibly the answer is to create a user that has the powers of "sys as sysdba" or something similar. But I am stuck really... can anyone help plz? Syd
From: vsevolod afanassiev on 14 Feb 2010 18:30 Create another user and create tables so they are owned by this user. create user ABC identified by ABC default tablespace USERS temporary tablespace TEMP; grant CONNECT,RESOURCE to ABC; After that connect as ABC/ABC and create tables, indexes, etc.
From: syd_p on 15 Feb 2010 05:17 On 14 Feb, 23:30, vsevolod afanassiev <vsevolod.afanass...(a)gmail.com> wrote: > Create another user and create tables so they are owned by this user. > > create user ABC identified by ABC > default tablespace USERS > temporary tablespace TEMP; > > grant CONNECT,RESOURCE to ABC; > > After that connect as ABC/ABC and create tables, indexes, etc. Thanks - but I need to run the scripts as they are - and the scripts create tablespaces and users.
From: Shakespeare on 15 Feb 2010 07:45 Op 14-2-2010 23:45, syd_p schreef: > Hi, > I am running a shell script which call a perl script which uses DBI to > connect to a remote (ie another server) which creates schemas ie table > spaces, users, tables in the waiting DB. > > Or rather it does not: > Database Connection Error: ORA-28009: connection as SYS should be as > SYSDBA or SYSOPER (DBD ERROR: OCISessionBegin) > The problem is as follows: > ./create_stuff.sh<database type> <database name> <host> <username> > <password> <port> > > I ran one of the many scripts locally on the unix command line with > the hardcoded string "sqlplus sys as sysdba/bloggs" and that works OK. > > The problem is that the shell script, calls more shell scripts, calls > perl scripts and passing in "sys" as a parameter fails at the Oracle > 10G level cos we need the AS sysdba. > And if I call the script with "sys as sysdba" "bloggs" (or similar > tried a few things) it goes wrong big time. > > Possibly the answer is to create a user that has the powers of "sys as > sysdba" or something similar. But I am stuck really... > > can anyone help plz? > > Syd > Try this: username: "sys", database name "DBNAME as sysdba" Shakespeare
From: Mladen Gogala on 15 Feb 2010 08:16
On Sun, 14 Feb 2010 14:45:45 -0800, syd_p wrote: > Hi, > I am running a shell script which call a perl script which uses DBI to > connect to a remote (ie another server) which creates schemas ie table > spaces, users, tables in the waiting DB. > > Or rather it does not: > Database Connection Error: ORA-28009: connection as SYS should be as > SYSDBA or SYSOPER (DBD ERROR: OCISessionBegin) The problem is as > follows: > ./create_stuff.sh <database type> <database name> <host> <username> > <password> <port> > > I ran one of the many scripts locally on the unix command line with the > hardcoded string "sqlplus sys as sysdba/bloggs" and that works OK. > > The problem is that the shell script, calls more shell scripts, calls > perl scripts and passing in "sys" as a parameter fails at the Oracle 10G > level cos we need the AS sysdba. > And if I call the script with "sys as sysdba" "bloggs" (or similar > tried a few things) it goes wrong big time. > > Possibly the answer is to create a user that has the powers of "sys as > sysdba" or something similar. But I am stuck really... > > can anyone help plz? > > Syd This is actually a wrong group for DBI questions but nevertheless, the answer is using ora_session mode. Here is an example: $dsn = "dbi:Oracle:"; # no dbname here $ENV{ORACLE_SID} = "orcl"; # set ORACLE_SID as needed delete $ENV{TWO_TASK}; # make sure TWO_TASK isn't set $dbh = DBI->connect($dsn, "", "", { ora_session_mode => ORA_SYSDBA}); It has been reported that this only works if $dsn does not contain a SID so that Oracle then uses the value of the ORACLE_SID (not TWO_TASK) environment variable to connect to a local instance. Also the username and password should be empty, and the user executing the script needs to be part of the dba group or osdba group. Having said all that, it is a bad idea to write Perl scripts running as SYSDBA. -- http://mgogala.freehostia.com |