下面的工作是并没有表现出索引视图等处理信息,是因为这次工作是在imp导入后发生个别表因为表空间名称不同,使表没有导入指定表空间
操作环境:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
==================================================================
#生产环境真的不需要这部分
SQL> alter user jhwl identified by oracle;
User altered.
SQL> conn jhwl/oracle;
Connected.
#上面的操作是因为忘记密码所以重置一下
===================================================================
#查询导入时没有进入指定表空间的哪些表
SQL> select table_name from all_tables where TABLESPACE_NAME='USERS';
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
PRODUCT_REF_LIST_NESTEDTAB
SUBCATEGORY_REF_LIST_NESTEDTAB
BOOK
NEWS
NEWS_EC
ORDER_USER
10 rows selected.
==========================================================
#查询需要移动的表中有多少数据
SQL> select count(*) from book;
COUNT(*)
----------
5
SQL> select count(*) from news;
COUNT(*)
----------
1207
SQL> select count(*) from news_ec;
COUNT(*)
----------
53
=========================================================
#表移动至指定表空间命令
SQL> alter table BOOK move tablespace jhwl;
Table altered.
SQL> alter table ORDER_USER move tablespace jhwl;
Table altered.
SQL> alter table NEWS_EC move tablespace jhwl;
Table altered.
SQL> alter table NEWS move tablespace jhwl;
Table altered.
========================================================
#查看表是否移动至指定表空间中
#下面SQL是指此表空间下面有哪些表
SQL> select table_name from all_tables where TABLESPACE_NAME='JHWL';
TABLE_NAME
------------------------------
BOOK
NEWS
NEWS_EC
ORDER_USER
#下面SQL是指此用户,此表所在的表空间
SQL> select table_name,tablespace_name from dba_tables where owner='JHWL' and table_name='BOOK';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
BOOK JHWL
操作环境:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
==================================================================
#生产环境真的不需要这部分
SQL> alter user jhwl identified by oracle;
User altered.
SQL> conn jhwl/oracle;
Connected.
#上面的操作是因为忘记密码所以重置一下
===================================================================
#查询导入时没有进入指定表空间的哪些表
SQL> select table_name from all_tables where TABLESPACE_NAME='USERS';
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
PRODUCT_REF_LIST_NESTEDTAB
SUBCATEGORY_REF_LIST_NESTEDTAB
BOOK
NEWS
NEWS_EC
ORDER_USER
10 rows selected.
==========================================================
#查询需要移动的表中有多少数据
SQL> select count(*) from book;
COUNT(*)
----------
5
SQL> select count(*) from news;
COUNT(*)
----------
1207
SQL> select count(*) from news_ec;
COUNT(*)
----------
53
=========================================================
#表移动至指定表空间命令
SQL> alter table BOOK move tablespace jhwl;
Table altered.
SQL> alter table ORDER_USER move tablespace jhwl;
Table altered.
SQL> alter table NEWS_EC move tablespace jhwl;
Table altered.
SQL> alter table NEWS move tablespace jhwl;
Table altered.
========================================================
#查看表是否移动至指定表空间中
#下面SQL是指此表空间下面有哪些表
SQL> select table_name from all_tables where TABLESPACE_NAME='JHWL';
TABLE_NAME
------------------------------
BOOK
NEWS
NEWS_EC
ORDER_USER
#下面SQL是指此用户,此表所在的表空间
SQL> select table_name,tablespace_name from dba_tables where owner='JHWL' and table_name='BOOK';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
BOOK JHWL