Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: User default directories using imp

Re: User default directories using imp

From: Frank <fvanbortel_at_netscape.net>
Date: Mon, 28 Apr 2003 22:38:33 +0200
Message-ID: <3EAD9149.50307@netscape.net>


Daniel Morgan wrote:

> Randy Harris wrote:
> 
> 

>>"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message
>>news:3EAAE701.888DA97A_at_exxesolutions.com...
>>
>>>Randy Harris wrote:
>>>
>>>
>>>>"Frank" <fvanbortel_at_netscape.net> wrote in message
>>>>news:3EAA9DB6.2090402_at_netscape.net...
>>>>
>>>>>Randy Harris wrote:
>>>>>
>>>>>>Is there any way to
>>>>>>
>>>>>>
>>>>>>>get imp to restore tables to the user's default rather than the
>>>>>>
>>source
>>
>>>>>>>tablespace?
>>>>>>>
>>>>>>
>>>>>If you knew that beforehand, you could have created
>>>>>the user with the correct default tablespace, revoke unlimted
>>>>>tablespace (user gets it with the resource role), and grant
>>>>>quota on default (and other) tablespaces. Explicitely do
>>>>>a grant 0 on system.
>>>>>The import (ignore=y), and the import will use the user's default
>>>>>tablespace.
>>>>>
>>>>>
>>>>>--
>>>>>Regards, Frank van Bortel
>>>>>
>>>>
>>>>Can I grant unlimited quota on his default tablespace and 0 on system?
>>>
>>And
>>
>>>>if so, that would then force his objects to get created in his default?
>>>
>>(I
>>
>>>>have to do the import again for the production roll out)
>>>>
>>>>Thanks for your help
>>>>--
>>>>Randy Harris
>>>
>>>Yes. And it is a good idea to always grant 0 on the system tablespace to
>>>everyone except SYS and SYSTEM.
>>>
>>>BTW: Does anyone know why it is that when Oracle installs it thinks SYSTEM
>>
>>is a
>>
>>>temporary tablespace for the SYSTEM schema? This has always struck me as
>>
>>quite
>>
>>>strange. Oracle builds a rollback segment in the SYSTEM tablespace and
>>
>>this I
>>
>>>understand. But why not a small temp tablespace to serve the same purpose
>>
>>rather
>>
>>>than configuring it as it does?
>>>--
>>>Daniel Morgan
>>>http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
>>>(remove one 'x' from my email address to reply)
>>>
>>
>>Please forgive my slowness. How do I "grant 0 on system"? I tried ALTER
>>USER CM QUOTA 0 ON SYSTEM, but unless I revoke RESOURCE his objects keep
>>ending up in SYSTEM.
> 
> 
> Good lord man ... revoke RESOURCE. Oracle advises that the default roles be
> dropped just as they advise that SYS and SYSTEM have their passwords changed.
> Only grant that which is required. Nothing more. Create your own roles that have
> appropriate privileges.

That's another new... to me. Would appreciate pointers. What roles should be removed, apart from resource? imp_full_database, etc?

create user cm identified by cm
default tablespace users temporary tablespace temp; (temporary not needed for 9i).
grant connect, resource to cm;
revoke unlimited tablespace from cm;
alter user cm quota 0 on system;
alter user cm quota unlimited on users;

Should do the trick. In that order

Some Oracle versions were "slow" on quotas - beats me why, but there's a bug (8.0/8i) somewhere.
Bouncing the instance if the user has been created earlier, and just changed to quotas, is a good idea. Unless 9i, that should have it resolved (cannot verify that, though)

-- 
Regards, Frank van Bortel
Received on Mon Apr 28 2003 - 15:38:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US