Here is how I move users to another tablespace. I will use the schema
scott as an example and the tablespace will be DATA (was in USERS). exp userid=system/manager parfile=exp_user.par file=exp_scott.dmp
log=exp_scott.log
owner="(scott)" Parameter File: BUFFER=4096000
COMPRESS=Y
GRANTS=Y
INDEXES=Y
ROWS=Y
CONSTRAINTS=Y
DIRECT=Y Enter the following at the SQL prompt: revoke DBA,RESOURCE from scott; REM make sure they only have connect.
alter user scott quota unlimited on DATA;
alter user scott quota 0M on USERS;
alter user scott quota 0M on SYSTEM;
REM The last three grants give the user added privs that RESOURCE
was giving them. grant create procedure to scott;
grant create trigger to scott;
grant create type to scott; Now import the user: imp userid=system/manager parfile=imp_user.par file=exp_scott.dmp
log=imp_scott.log fromuser="(scott)" touser="(scott)" Parameter File: BUFFER=4096000
GRANTS=Y
INDEXES=N
IGNORE=Y
ROWS=Y I realize "Y" is the default for the parameters in the par file but
there are times you want to change them to "N" so I leave them in.
Once you have moved the user you can grant the privileges back to them.
scott as an example and the tablespace will be DATA (was in USERS). exp userid=system/manager parfile=exp_user.par file=exp_scott.dmp
log=exp_scott.log
owner="(scott)" Parameter File: BUFFER=4096000
COMPRESS=Y
GRANTS=Y
INDEXES=Y
ROWS=Y
CONSTRAINTS=Y
DIRECT=Y Enter the following at the SQL prompt: revoke DBA,RESOURCE from scott; REM make sure they only have connect.
alter user scott quota unlimited on DATA;
alter user scott quota 0M on USERS;
alter user scott quota 0M on SYSTEM;
REM The last three grants give the user added privs that RESOURCE
was giving them. grant create procedure to scott;
grant create trigger to scott;
grant create type to scott; Now import the user: imp userid=system/manager parfile=imp_user.par file=exp_scott.dmp
log=imp_scott.log fromuser="(scott)" touser="(scott)" Parameter File: BUFFER=4096000
GRANTS=Y
INDEXES=N
IGNORE=Y
ROWS=Y I realize "Y" is the default for the parameters in the par file but
there are times you want to change them to "N" so I leave them in.
Once you have moved the user you can grant the privileges back to them.
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货