How to move a table from an empty tablespace to another tablespace

Question

I have many tables that are in an empty tablespace and I want to move the tables to another tablespace.

I have created the tables with this script

-- Create table
create table GSIB_BC_SUB_IDEN
(
  sub_iden_id    NUMBER(20) not null,
  sub_id         NUMBER(20) not null,
  cust_id        NUMBER(20) not null,
  network_type   VARCHAR2(1),
  sub_iden_type  VARCHAR2(4) not null,
  sub_identity   VARCHAR2(64) not null,
  primary_flag   VARCHAR2(1) not null,
  eff_date       DATE not null,
  exp_date       DATE not null,
  create_oper_id NUMBER(20),
  create_dept_id NUMBER(20),
  create_time    DATE,
  modify_oper_id NUMBER(20),
  modify_dept_id NUMBER(20),
  modify_time    DATE,
  upload_date    DATE,
  file_name      VARCHAR2(200)
)
partition by range (EFF_DATE)
(
  partition GSIB_BC_SUB_IDEN_PAR_202101 values less than (TO_DATE(' 2021-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition GSIB_BC_SUB_IDEN_PAR_202102 values less than (TO_DATE(' 2021-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition GSIB_BC_SUB_IDEN_PAR_202103 values less than (TO_DATE(' 2021-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition GSIB_BC_SUB_IDEN_PAR_202104 values less than (TO_DATE(' 2021-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition GSIB_BC_SUB_IDEN_PAR_202105 values less than (TO_DATE(' 2021-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition GSIB_BC_SUB_IDEN_PAR_202106 values less than (TO_DATE(' 2021-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table GSIB_BC_SUB_IDEN
  add constraint PK_GSIB_BC_SUBIDEN primary key (SUB_IDEN_ID, EFF_DATE);

How can I move the tables and their partitions to the TB_DAT tablespace

Run this script but it returns this error

ALTER TABLE CBSPROD.GSIB_BC_SUB_IDEN MOVE TABLESPACE TB_DAT;

ORA-14511: cannot perform operation on a partitioned object

Does this answer your question? [How do you move a partitioned table from one tablespace to another in Oracle 11g?](https://stackoverflow.com/questions/1998627/how-do-you-move-a-partitioned-table-from-one-tablespace-to-another-in-oracle-11g) — Marmite Bomber, Jan 06 '21 at 15:18

score 0 · Answer 1 · answered Jan 06 '21 at 13:59

0

login as dba, alter table cbsprod.gsib_bc_sub_iden move tablespace dathw_idx;

answered Jan 06 '21 at 13:59

scott yu

125
1
3

On a partitioned table? I don’t think so. – Jan 06 '21 at 14:28

score 0 · Answer 2 · edited Jan 08 '21 at 13:04

SQL> alter table gsib_bc_sub_iden move partition GSIB_BC_SUB_IDEN_PAR_202101  tablespace users
  2  /

Table altered.
SQL> alter table gsib_bc_sub_iden move partition GSIB_BC_SUB_IDEN_PAR_202102  tablespace users
  2  /

Table altered.

SQL> alter table gsib_bc_sub_iden move partition GSIB_BC_SUB_IDEN_PAR_202103 tablespace users
  2  /

Table altered.

SQL> alter table gsib_bc_sub_iden move partition GSIB_BC_SUB_IDEN_PAR_202104 tablespace users
  2  /

Table altered.

SQL> alter table gsib_bc_sub_iden move partition GSIB_BC_SUB_IDEN_PAR_202105 tablespace users
  2  /

Table altered.

SQL> alter table gsib_bc_sub_iden move partition GSIB_BC_SUB_IDEN_PAR_202106 tablespace users
  2  /

Table altered.

How to move a table from an empty tablespace to another tablespace

2 Answers2