Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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:</p> <p><strong>1) Move partitions (a PL/SQL loop as per zürigschnäzlets' answer)</strong></p> <p>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:</p> <pre><code>procedure mvTabPart (a_tname in varchar2, a_destTS in varchar2) is cursor 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; begin for 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; </code></pre> <p><strong>2) Set table default partition tablespace so new partitions are created there:</strong></p> <pre><code> 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; </code></pre> <p><strong>3) Set index default partition tablespace so new index partitions (if any) are created where you want them:</strong></p> <pre><code>procedure setDefNdxPart (a_tname in varchar2, a_destTS in varchar2) is cursor 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); begin for 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; </code></pre> <p><strong>4) rebuild any partitioned indexes that need rebuilding and are not in the desired tablespace:</strong></p> <pre><code>procedure mvNdxPart (a_tname in varchar2, a_destTS in varchar2) is cursor ndxCur(vTname varchar2, vTspName varchar2) is select 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 ; begin for 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; </code></pre> <p><strong>5) Rebuild any global indexes</strong></p> <pre><code>procedure mvNdx (a_tname in varchar2, a_destTS in varchar2) is cursor 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 ; begin for 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; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow do you move a partitioned table from one tablespace to another in Oracle 11g?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USdpbradley
    1. USdpbradley
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow do you move a partitioned table from one tablespace to another in Oracle 11g?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COVery detailed answer. 1+
      singulars
      1. PO
      1. USThomas Aregger
    2. COI am using Oracle SQL Developer and I am not sure how to call the procedures you have given. However, I rearranged the first one (mvTabPart) a little bit and managed to execute it (got anonymous block completed). The only thing is that I had to add quotes around the partition name (‘move partition “’ and ‘” tablespace ‘), but then it worked like a charm.
      singulars
      1. PO
      1. USHenrik Warne
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload