Re: Moving a user out of the SYSTEM tablespace
Date: 23 Jun 92 22:49:41 GMT
Message-ID: <1992Jun23.224941.18216_at_cmutual.com.au>
In article <50886_at_seismo.CSS.GOV>, cooper_at_beno.CSS.GOV (Dale Cooper) writes:
>In article <1992Jun23.053320.27007_at_ml.csiro.au> peter_at_ml.csiro.au (Peter
Campbell) writes:
>>I am trying to move a user out of the SYSTEM tablespace by exporting,
>>revoking RESOURCE privilege to SYSTEM, dropping everything, changing
>>the default tablespace, and importing. Unfortunately despite having no
>>quota the user is still able to create things in SYSTEM so everything is
>>being recreated there instead of being forced to the new tablespace.
>>
>>What am I doing wrong?
>>
>>Peter Campbell peter_at_ml.csiro.au
>>CSIRO Marine Labs, Hobart, Australia
>
>Ah, the joys of imp/exp!
>
>To get right to the point, IMP ignores what is currently defined as the user's
>default tablespace when and if the same tablespace name is encountered upon
>import (Utilities User's Guide pg 2-2). In other words, if you EXPorted a
>user' objects and those objects all resided in the SYSTEM tablespace, IMP will
>attempt to reload those objects into the SYSTEM tablespace first - regardless
>of the default tablespace definition! (This has always been a pet peve of
>mine, but that's another article entirely) If a SYSTEM tablespace does not
>exist in the target database, then and only then will IMP attempt to use the
>default tablespace definition. Of course since every Oracle database that I
>have ever had the pleasure to deal with has a SYSTEM tablespace, you can
pretty
>much punt the IMP/EXP route.
Dale is wrong here!!!. Oracle will import into the same tablespace as defined in the export IF AND ONLY IF
(1) the original tablespace exist and (2) the user has resource in the original tablespace.
If you revoke resource from the user from the original tablespace, the import WILL NOT import the table back into the old tablespace. it will attempt to import the table into the users default tablespace.
Now, if you have not changed the default TS, it will fail to import. But if you have already alterd the user's default TS to be someother TS, it will create the table in the new default TS (if you have granted resource).
>
>Try as a DBA...
>
> SQL> alter user blahblah
> 2 default tablespace new_tablespace;
>
>then as the user...
>
> SQL> connect blahblah/password
> Connected.
> SQL> create table baba2 as select * from baba;
>
> Table created.
>
> SQL> drop table baba;
>
> Table dropped.
>
> SQL> rename baba2 to baba;
>
>
>For all you SQL police, I know there are other methods...that's the joy of
SQL.
>
You are right, there are other ways.
>some key problems to consider with this method:
>
>1) When you do the CREATE TABLE AS you ignore all indices. All indices
> must be recreated for the new table.
>
True.
>2) When you use CREATE TABLE AS you also have no control over storage
> parameters. You are stuck with what is defined as your default.
> In other words, one cannot issue:
>
> CREATE TABLE blahdeeblah2 as select * from blahdeeblah
> storage (initial ...) tablespace ...;
>
Wrong again!
Try
"CREATE TABLE blahdeeblah2
STORAGE (.....)
as select * from blahdeeblah"
=============================================================================
> This can be overriden by issuing a CREATE TABLE statement,
> then issuing INSERT INTO ... SELECT * FROM ...;
>
>3) Forget array inserts. If you have tiny rollback segments and
> the table is quite large, you may have trouble doing this. You
> may opt for PL/SQL to load chunks of records.
>
>
>Personally (in version 6.X) I have difficulty understanding why Oracle
>prevents you from overriding the default tablespace definition. Now that
>editable IMP files are becoming a reality, the problem is minimized. Before
>that, the only way we could get around that problem here is courtesy of EMACS.
>
>The joys of Oracle...
>
>
>Dale Cooper, DBA
>Center for Seismic Studies
>Arlington, VA
OK, now to answer Peter's problem.
- Have you had a look at the table/view DBA_TS_QUOTAS. Make sure that the column MAX_BLOCKS has the value 0 for TABLESPACE_NAME = user and USERNAME = user.
- Reconfirm that the user's default TS is what you think it is from the DBA_USERS.DEFAULT_TABLESPACE view.
- Make sure the user is not a DBA. Look at DBA_USERS.DBA_PRIV.
- Now, this is what I believe is the cause of your problem. Did you grant resource to the user using the command "GRANT RESOURCE TO USER"? If you did, he has resource to ALL TS and revoking resource explicitly on individual TS will not do the trick.
Have a look at DBA_USERS.RESOURCE_PRIV.
I hope that is enough info for you to carry out some investigation. Word of warning, the user will still be able to see the tables that have resource revoked from him/her in user_tablespaces butm have no resource. I have been told that this has been fixed in V7.0.
-- ----------------------------------------------------------------------------- _____ ________ / ____ |Tony Jambu, Database Administrator /_ __ /_ __ / |Colonial Mutual Life Australia. (ACN 004021809) /(_)/ ((_/ \_/(///(/_)/_( |EMAIL: TJambu_at_cmutual.com.auReceived on Wed Jun 24 1992 - 00:49:41 CEST