From: syd_p on
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
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
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
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
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