bofh1234_at_hotmail.com wrote:
> On Feb 7, 9:11 am, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
>> On Feb 6, 10:35 pm, bofh1..._at_hotmail.com wrote:
>>> 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 --
>
> This is what happens when I run the script:
> login in sys as sysdba
> Tablespace created.
> User created.
> Grant succeeded.
> Connected.
> DROP TABLESPACE STORE INCLUDING CONTENTS AND DATAFILES CASCADE
> CONSTRAINTS
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
>
> CREATE TABLESPACE STORE DATAFILE '/u03/oradata/prod/store01.dbf' SIZE
> 3M
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
>
> Table created.
>
> If I create the user, exit sqlplus, login as harry the script works
> without error.
>
> Thanks,
Why don't you post what you are actually doing instead of your
interpretation of it?
GRANT succeeded? What GRANT?
Connected? Connected as who and why should whoever that is have the
privilege to drop a tablespace?
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Feb 07 2007 - 14:51:01 CST
