How do you move a partitioned table from one tablespace to another in Oracle 11g?

You have to consider indexes that may be invalidated as well - to cover your question about resetting the default tablespaces in addition to this, I think this is the full process that you'll want to implement:

1) Move partitions (a PL/SQL loop as per zürigschnäzlets' answer)

These are procedures I use within an anonymous block wrapper that defines a_tname, a_destTS, vTname, and vTspName - they should give you the general idea:

procedure mvTabPart (a_tname in varchar2, a_destTS in varchar2) iscursor pCur(vTname varchar2, vTspName varchar2) is  select table_name, partition_name  from user_tab_partitions  where table_name = vTname      and tablespace_name not like vTspName  order by partition_position desc;beginfor pRow in pCur(a_tname, a_destTS) loop sqlStmnt := 'alter table '||pRow.table_name||             ' move partition '||pRow.partition_name||             ' tablespace '||a_destTS;execute immediate sqlStmnt;end loop;end mvTabPart;

2) Set table default partition tablespace so new partitions are created there:

    procedure setDefTabPart (a_tname in varchar2, a_destTS in varchar2) is    cursor tCur(vTname varchar2) is      select table_name      from user_part_tables      where table_name = vTname;    begin    for tRow in tCur(a_tname) loop     sqlStmnt := 'alter table '||tRow.table_name||                 ' modify default attributes '||                 ' tablespace '||a_destTS;    execute immediate sqlStmnt;    end loop;end setDefNdxPart;

3) Set index default partition tablespace so new index partitions (if any) are created where you want them:

procedure setDefNdxPart (a_tname in varchar2, a_destTS in varchar2) iscursor iCur(vTname varchar2) is  select index_name  from user_part_indexes  where index_name in (select index_name             from user_indexes where table_name = vTname);beginfor iRow in iCur(a_tname) loop sqlStmnt := 'alter index '||iRow.index_name||             ' modify default attributes '||             ' tablespace '||a_destTS;execute immediate sqlStmnt;end loop;end setDefNdxPart;

4) rebuild any partitioned indexes that need rebuilding and are not in the desired tablespace:

procedure mvNdxPart (a_tname in varchar2, a_destTS in varchar2) iscursor ndxCur(vTname varchar2, vTspName varchar2) isselect i.index_name index_name, ip.partition_name partition_name  from user_ind_partitions ip, user_indexes i  where i.index_name = ip.index_name     and i.table_name = vTname     and i.partitioned = 'YES'     and (ip.tablespace_name not like vTspName or ip.status not like 'USABLE')  order by index_name, partition_name ;beginfor ndxRow in ndxCur(a_tname, a_destTS) loop sqlStmnt := 'alter index '||ndxRow.index_name||             ' rebuild partition '||ndxRow.partition_name||             ' tablespace '||a_destTS;execute immediate sqlStmnt ;end loop;end mvNdxPart;

5) Rebuild any global indexes

procedure mvNdx (a_tname in varchar2, a_destTS in varchar2) iscursor ndxCur(vTname varchar2, vTspName varchar2) is  select index_name  from user_indexes  where table_name = vTname       and partitioned = 'NO'       and (tablespace_name not like vTspName or status like 'UNUSABLE')  order by index_name ;beginfor ndxRow in ndxCur(a_tname, a_destTS) loop sqlStmnt := 'alter index '||ndxRow.index_name||             ' rebuild tablespace '||a_destTS;execute immediate sqlStmnt ;end loop;end mvNdx;

You can either do it with PL/SQL or generate the statements with sql. I decided to generate the alter table statements with simple SQL:

--set linesizeset lines 100--This Query generates the alter table statements:SELECT 'ALTER TABLE '       ||table_name       ||' MOVE PARTITION '       ||partition_name       ||' TABLESPACE REPORT;'FROM   all_tab_partitionsWHERE  table_name = 'requestLog'; 

You can execute the output from the previous statement.

Every user has a default tablespace. New database objects are created in that default tablespace if nothing else is specified on creation/alteration

The easiest way to move the data within tablespaces:

Moving all non-partitioned tables

SELECT 'ALTER TABLE '||OWNER|| '.'||TABLE_NAME||' MOVE TABLESPACE ARCHIVE;'  FROM ALL_tables where owner = 'owner_name' and temporary != 'Y'and partitioned != 'YES';

Partitioned tables

SELECT 'ALTER TABLE '|| TABLE_OWNER||'.'||TABLE_NAME||' MOVE PARTITION ' ||  PARTITION_NAME||  ' TABLESPACE ARCHIVE;'  FROM ALL_tab_partitions WHERE TABLE_OWNER = 'owner_name' AND table_NAME NOT LIKE 'BIN$%';

Non-partitioned indexes

SELECT 'ALTER INDEX '|| OWNER||'.'||OBJECT_NAME ||' REBUILD TABLESPACE ARCHIVE ;' FROM ALL_OBJECTS WHERE OBJECT_TYPE ='INDEX'AND OWNER = 'owner_name';

Partitioned indexes

SELECT  'ALTER INDEX '||I.INDEX_NAME||'REBUILD PARITION'|| S.PARTITION_NAME || ' TABLESPACE  ARCHIVE '                    FROM  DBA_INDEXES I,    DBA_SEGMENTS S                  WHERE  I.INDEX_NAME = S.SEGMENT_NAME                    AND I.INDEX_TYPE IN ('NORMAL', 'BITMAP')                    AND I.OWNER = 'owner_name';