There are many ways to move a table from one tablespace to another. For example,
ORA-01502: index 'MANU.SYS_C0010803' or partition of such index is in unusable state
conn with sys
see the tablespace
SQL> select tablespace_name,contents from dba_tablespaces;TABLESPACE CONTENTS
---------- ---------
SYSTEM PERMANENT
UNDOTBS1 UNDO
SYSAUX PERMANENT
TEMP TEMPORARY
USERS PERMANENT
EXAMPLE PERMANENT
AD PERMANENT
AD12 TEMPORARY
8 rows selected.
see the default tablespace of the users
SQL> select username,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MDSYS SYSAUX
ORDSYS SYSAUX
EXFSYS SYSAUX
DMSYS SYSAUX
DBSNMP SYSAUX
SCOTT USERS
WMSYS SYSAUX
TSMSYS USERS
MANU AD
BI USERS
PM USERS
-----to see the tables in the tablespace;
SQL> col owner format a10
SQL> col segment_name format a15
SQL> col tablespace_name format a10
SQL> select owner,segment_name,tablespace_name,segment_type from dba_segments where owner='MANU';
OWNER SEGMENT_NAME TABLESPACE SEGMENT_TYPE
---------- --------------- ---------- ------------------
MANU AD AD TABLE
MANU DEPT AD TABLE
MANU EMP AD TABLE
----now change the default tablespace ad into users---
SQL> alter user manu default tablespace users quota unlimited on users;
SQL> select username,default_tablespace from dba_users where username='MANU';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MANU USERS
manu's tables are in the ad tablespace and now i want to transfer into the one tablespace "users"
SQL> select owner,segment_name,tablespace_name,segment_type from dba_segments where owner='MANU';
OWNER SEGMENT_NAME TABLESPACE SEGMENT_TYPE
---------- --------------- ---------- ------------------
MANU AD AD TABLE
MANU DEPT AD TABLE
MANU EMP AD TABLE
--move the table from ad tablespace to users tablespace---
SQL> alter table manu.ad move tablespace users;
Table altered.
SQL> alter table manu.emp move tablespace users;
Table altered.
SQL> alter table manu.dept move tablespace users;
Table altered.
SQL> select owner,segment_name,tablespace_name,segment_type from dba_segments where owner='MANU';
OWNER SEGMENT_NAME TABLESPACE SEGMENT_TYPE
---------- --------------- ---------- ------------------
MANU AD USERS TABLE
MANU DEPT USERS TABLE
MANU EMP USERS TABLE
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';
'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
ALTER INDEX MANU.SYS_C0010803 REBUILD;
ALTER INDEX MANU.INDX REBUILD;
SQL> ALTER INDEX MANU.SYS_C0010803 REBUILD;
Index altered.
All the best ....... :)
