Re: sysdba role in script

On Feb 6, 10:35 pm, bofh1..._at_hotmail.com wrote:
> Hello,
>
> I am writing a script. This script will create a tablespace, a user,
> switch that user, create more tablespaces, etc. Thanks to some
> friendly people I have a mostly working script. Mostly working
> because when I switch to the user in the script the sysdba privledges
> are not dropped.
> This does not work
> connect sys as sysdba
> create tablespace test datafile '/u03/oradata/prod/test01.dbf size
> 200M;
> create user harry identified by password default tablespace test
> temporary tablespace temp quota 24M on test;
> grant connect, resource, sysdba to harry;
> conn harry/potter
> create tablespace data2 datafile '/u03/oradata/prod/data201.dbf' SIZE
> 300M;
>
> The data2 tablespace isn't created in the right area.
>
> This works:
> connect sys as sysdba
> create tablespace test datafile '/u03/oradata/prod/test01.dbf size
> 200M;
> create user harry identified by password default tablespace test
> temporary tablespace temp quota 24M on test;
> grant connect, resource, sysdba to harry;
> exit sqlplus and launch sqlplus again
> connect harry/potter
> create tablespace data2 datafile '/u03/oradata/prod/data201.dbf' SIZE
> 300M;
>
> The data2 tablespace is created correctly.
>
> What am I doing wrong? I am using Oracle 10.2.0.1
>
> Thanks,

I am confused by you statement that the tablespace is not created in the right location but you do not display any error or explain where it was created. Why do you need to switch users when any DBA can create or alter a tablespace. What is the point of switching?

Why do you grant sysdba then not connect as sysdba though any user connected as sysdba is user SYS rather than themselves?

 > connect / as sysdba
Connected.
 > show user
USER is "SYS"
 > connect mpowel01 as sysdba
Enter password:
Connected.
 > show user
USER is "SYS"

Oracle has stated that Connect and Resource are obsolete roles and are provided for backward compatiability only. You should not be using them.

Maybe someone else will understand your problem but I think you need to explain it more clearly and include query results on dba_tablespaces and/or dba_data_files to show what is happening. That or list the error messages.

HTH -- Mark D Powell -- Received on Wed Feb 07 2007 - 09:11:40 CST