B)
Tip :- How to move Table from one tablespace to another ?
Answer A)
Move index from one
tablespace to another :-
Test case : Assume you have a index FB1 in SYSTEM tablespace and you want to move it in TEST tablespace
ora816 SamSQL :> select index_name,tablespace_name from
dba_indexes where table_name='EMP' and owner='SCOTT';
INDEX_NAME
TABLESPACE_NAME
------------------------------ ------------------------------
FB1
SYSTEM
FBI_TEST
TEST
Test case :
Solution
ora816 SamSQL :> connect scott/tiger
Connected.
ora816 SamSQL :> alter index FB1 rebuild tablespace TEST;
Index altered.
ora816 SamSQL :> connect sys
Enter password:
Connected.
ora816 SamSQL :> select index_name,tablespace_name from dba_indexes where table_name='EMP' and owner='SCOTT';
INDEX_NAME
TABLESPACE_NAME
------------------------------ ------------------------------
FB1 TEST
FBI_TEST
TEST
Conclusion : Index name
FB1 move from tablespace SYSTEM to TEST by
ALTER INDEX <INDEX NAME> REBUILD TABLESPACE <TABLESPACE_NAME>
Answer
B)
Move
Table from one tablespace to another ?
Test case : Assume you have a
table DEPT in SYSTEM tablespace and you want to move it in TEST tablespace
ora816 SamSQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';
TABLE_NAME
TABLESPACE_NAME
------------------------------ ------------------------------
DEPT
SYSTEM
Test case :
Solution
ora816 SamSQL :> connect scott/tiger
Connected.
ora816 SamSQL :> alter table DEPT move tablespace TEST;
Table altered.
ora816 SamSQL :> connect
Enter user-name: sys
Enter password:
Connected.
ora816 SamSQL :> select table_name,tablespace_name from
dba_tables where table_name='DEPT' and owner='SCOTT';
TABLE_NAME
TABLESPACE_NAME
------------------------------ ------------------------------
DEPT
TEST
Conclusion : Table name DEPT move from tablespace SYSTEM to TEST by
ALTER TABLE <TABLE NAME> MOVE TABLESPACE <TABLESPACE_NAME>
End of Tip
|
Welcome
to send comments or feedback at Wadhwa_s@Hotmail.com |
|
Sameer Wadhwa |
Copyright © Oracle Techniques All Rights Reserved
|