Hello,
I am working on moving tables from one tablesapce to another tablespace. The table has subpartition and
sub partition index.
After moving the tables, the partition tablespace name is not changed to target tablespace name.
Before move :
SQL> select distinct tablespace_name from dba_tab_partitions where table_name='INVOICES';
TABLESPACE_NAME
------------------------------
USERS
SQL> select distinct tablespace_name from dba_tab_subpartitions where table_name='INVOICES';
TABLESPACE_NAME
------------------------------
USERS
SQL> select distinct tablespace_name from dba_ind_partitions where index_name='INVOICES_IDX';
select distinct tablespace_name from dba_ind_subpartitions where index_name='INVOICES_IDX';
TABLESPACE_NAME
------------------------------
USERS
SQL>
TABLESPACE_NAME
------------------------------
USERS
SQL>
After move :
SQL> select distinct tablespace_name from dba_tab_partitions where table_name='INVOICES';
TABLESPACE_NAME
------------------------------
USERS
SQL> select distinct tablespace_name from dba_tab_subpartitions where table_name='INVOICES';
TABLESPACE_NAME
------------------------------
DEVELOPER
SQL> select distinct tablespace_name from dba_ind_partitions where index_name='INVOICES_IDX';
TABLESPACE_NAME
------------------------------
USERS
SQL>
select distinct tablespace_name from dba_ind_subpartitions where index_name='INVOICES_IDX';SQL>
TABLESPACE_NAME
------------------------------
DEVELOPER
SQL>
After the move, the USERS tablespace empty and i am planning to drop the USERS tablespace.
Would it be an issue after i drop the tablespace?
Here is the complete screenshot.
Table created :
SQL> CREATE TABLE invoices
2 (invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
3 4 comments VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
5 6 SUBPARTITION BY HASH (invoice_no)
SUBPARTITIONS 8
7 8 (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/ YYYY')),
9 PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY' )),
10 PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY' )),
11 PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY' )));
CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL
(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);
Table created.
SQL> SQL> SQL> 2 3 4 5
Index created.
SQL>
checked the tablespace name :
SQL> select distinct tablespace_name from dba_tab_partitions where table_name='INVOICES';
TABLESPACE_NAME
------------------------------
USERS
SQL> select distinct tablespace_name from dba_tab_subpartitions where table_name='INVOICES';
TABLESPACE_NAME
------------------------------
USERS
SQL> select distinct tablespace_name from dba_ind_partitions where index_name='INVOICES_IDX';
select distinct tablespace_name from dba_ind_subpartitions where index_name='INVOICES_IDX';
TABLESPACE_NAME
------------------------------
USERS
SQL>
TABLESPACE_NAME
------------------------------
USERS
SQL>
Move the INVOICES table from USERS tablespace to DEVELOPER tablespace :
SQL> alter table oradbo.invoices move subpartition SYS_SUBP15925 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15924 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15923 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15922 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15921 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15920 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15919 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15918 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15933 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15932 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15931 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15930 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15929 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15928 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15927 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15926 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15941 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15940 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15939 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15938 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15937 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15936 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15935 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15934 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15949 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15948 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15947 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15946 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15945 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15944 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15943 tablespace DEVELOPER;
alter table oradbo.invoices move subpartition SYS_SUBP15942 tablespace DEVELOPER;
ALTER TABLE ORADBO.INVOICES MODIFY DEFAULT ATTRIBUTES TABLESPACE DEVELOPER;
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL> ALTER TABLE ORADBO.INVOICES MODIFY DEFAULT ATTRIBUTES TABLESPACE DEVELOPER;
Table altered.
SQL>
Moved the index to DEVELOPER tablespace
SQL> alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15948 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15947 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15946 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15945 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15944 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15943 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15942 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15941 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15940 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15939 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15938 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15937 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15936 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15935 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15934 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15933 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15932 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15931 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15930 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15929 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15928 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15927 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15926 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15925 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15924 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15923 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15922 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15921 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15920 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15919 tablespace DEVELOPER;
alter index oradbo.invoices_idx rebuild subpartition SYS_SUBP15918 tablespace DEVELOPER;
ALTER index ORADBO.INVOICES_idx MODIFY DEFAULT ATTRIBUTES TABLESPACE DEVELOPER;
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
SQL>
Verified the tablespace..
SQL> select distinct tablespace_name from dba_tab_partitions where table_name='INVOICES';
TABLESPACE_NAME
------------------------------
USERS
SQL> select distinct tablespace_name from dba_tab_subpartitions where table_name='INVOICES';
TABLESPACE_NAME
------------------------------
DEVELOPER
SQL> select distinct tablespace_name from dba_ind_partitions where index_name='INVOICES_IDX';
TABLESPACE_NAME
------------------------------
USERS
SQL>
select distinct tablespace_name from dba_ind_subpartitions where index_name='INVOICES_IDX';SQL>
TABLESPACE_NAME
------------------------------
DEVELOPER
SQL>
The move is successful. But i am wondering why partition tablespace name is not changed..
i am planning to drop the USERS tablespace. Would it complaint after i drop the tablespace?
since partition tablespace name is still showing USERS(original tablespace).