OraFAQ Forum: SQL & PL/SQL » Partition tablespace name does not change after moving subpartition

Home » SQL & PL/SQL » SQL & PL/SQL » Partition tablespace name does not change after moving subpartition (oracle12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Partition tablespace name does not change after moving subpartition [message #662052]

Sat, 15 April 2017 10:26

shrinika
Messages: 306
Registered: April 2008

Senior Member

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).

Report message to a moderator

Re: Partition tablespace name does not change after moving subpartition [message #662054 is a reply to message #662052]

Sat, 15 April 2017 11:18

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

If you are using subpartitioning, then the tablespace specified at the partition level really doesn't mean anything. The tablespace is set per subpartition.

Report message to a moderator

Re: Partition tablespace name does not change after moving subpartition [message #662056 is a reply to message #662054]

Sat, 15 April 2017 12:59

shrinika
Messages: 306
Registered: April 2008

Senior Member

Thank you sir! That is all i want to confirm. I was worried because, i am going to drop the original tablespace after moving to target tablespace.

Report message to a moderator

Previous Topic:	cursors
Next Topic:	Help required in SQL Analytic Function lag()

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 11:53:11 CDT 2024