是因为当你存在和以前的表空间同名的表空间时候,他不会用新用户的默认表空间的,
当不存在以前的表空间的时候,才用新用户的默认表空间对于表,可以采用move方式到新的表空间
对于索引,采用rebuild方式到新的表空间
当不存在以前的表空间的时候,才用新用户的默认表空间对于表,可以采用move方式到新的表空间
对于索引,采用rebuild方式到新的表空间
解决方案 »
- MYSQL表锁问题
- @@@求能够将中文名字转为拼音的方法@@@
- 求一条SQL
- 导入dmp时失败,提示:未启用partitioning特性,如何解决?
- 在更新后,如何知道更新了几条数据?
- 请问各位sqlplus如何获得某个表的主键信息?
- 如何在oracle导入excle表里面的数据
- oracle树状查询,如何截取结果集的部分数据
- oracle 11g RAC 更改了remote_listener参数后EM出问题,日志:ORA-12514, TNS:listener does not cu
- 请问各位大佬,我一张表里几十亿条数据怎么备份最优
- 安装8.05后有documentation,是有用的帮助。但是在8i中没有找到。请问在什么地方能安装。
- 一个关于SQL语句的easy问题!
select 'alter table '||table_name||' move tablespace TABLESPACE123;' from user_tables;两个sql取出的语法,执行!!!
select 'alter index '||index_name||' rebuild tablespace TABLESPACE123;' from user_indexes;
select 'alter table '||table_name||' move tablespace TABLESPACE123;' from user_tables;
就把这个用户的所有表和索引都取出来了
然后只要执行sql的结果(也就是相应的脚本)就可以了!理论上这是最简单的了!
to import data into. Objects will be re-created in the
tablespace they were originally exported from. One can alter
this behaviour by following one of these procedures: 1. Pre-create the table(s) in the correct tablespace: Import the dump file using the INDEXFILE= option
Edit the indexfile. Remove res and specify the
correct tablespaces.
Run this indexfile against your database, this will
create the required tables in the appropriate tablespaces
Import the table(s) with the IGNORE=Y option. 2. Change the default tablespace for the user: Revoke the "UNLIMITED TABLESPACE" privilege from the user
Revoke the user's quota from the tablespace from where the
object was exported. This forces the import utility to create
tables in the user's default tablespace. Make the tablespace to which you want to import the default
tablespace for the user.
Import the table.
Q2: Import user to another tablespace
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.
select 'alter index '||index_name||' rebuild tablespace TABLESPACE123;' from user_indexes;
select 'alter table '||table_name||' move tablespace TABLESPACE123;' from user_tables;
可以实现的,可以试试。
我试你的方法还有chinaghf的方法,是可以将表和索引移过过去。但是还有其它属于该用户的数据库对象(过程、函数、触发器、序列),会不会受影响?因为这次是向正式数据库转。再者数据库目前也非常的大。所以还得请二们给个更完整的方案。问题解决马上给分。
注:你也可以自己测试一下,随便搞个表试试,省得心里害怕!
生产系统,安全还是第一位的
indexs=n
对于有建索引的语法是不错的选择,导入导出索引与直接建索引比较是不划算的
但如果没有建索引语法,indexs=y 还是必要的呵呵
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.
我今天已试了。。是可以将表和索引移到指定表空间。但是从表空间使用率上没有发现这些表已被移到别处,好像还是在原来的原空间。这是怎么回事?