Home » RDBMS Server » Performance Tuning » Move unused partitioned from one tablespace to another tablespace. (Oracle 12c (12.1.0.1.0)- Unix)
Move unused partitioned from one tablespace to another tablespace. [message #667537] Thu, 04 January 2018 23:25 Go to next message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
I want to know the steps to find out the unused partitioned for a particular table and how can move them from one tablespace to another in Oracle 12c((12.1.0.1.0).

Also Please let us know the prerequisites & dependencies.
Re: Move unused partitioned from one tablespace to another tablespace. [message #667547 is a reply to message #667537] Fri, 05 January 2018 02:57 Go to previous messageGo to next message
John Watson
Messages: 8268
Registered: January 2010
Location: Global Village
Senior Member
ALTER TABLE ... MOVE PARTITION ... UPDATE INDEXES ONLINE ;

You'll need to look up the detail. It is all in the docs.
Re: Move unused partitioned from one tablespace to another tablespace. [message #668110 is a reply to message #667547] Wed, 07 February 2018 03:23 Go to previous message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
Thanks Johan, I have moved my unused partitions successfully. These are the steps to move.

Identify what all are the empty partitions. Example is for single partition.
SQL> SELECT 'partition_name', COUNT(*) FROM user.table PARTITION (partition_name);

SQL> select count(1) from conn.SNAP_DAY_SLICES partition (partition_name) ;

COUNT(1)
----------
0

Identify the associated index & partition status of the table.

SQL> select index_name, partitioned from dba_indexes where table_name='TEST' ;

INDEX_NAME PAR
------------------------------ -------- -------- ---
IDX_TEST YES



SELECT index_owner, index_name, partition_name, status from DBA_IND_PARTITIONS where tablespace_name='CONN_test_PART_INDEXES' and partition_name LIKE'%2018';


The next thing is to find out is if this is a local index or global index.

SQL> select locality from dba_part_indexes where index_name='IDX_SDS_SLICE_DATE_NUM';

LOCALI
------
LOCAL

ALTER TABLE CONN.test MOVE PARTITION part_name ONLINE TABLESPACE tbs_name ;

Previous Topic: View Performance Tuning
Next Topic: Can we delete specific execution plan in the db buffer for a statement
Goto Forum:
  


Current Time: Sat May 30 11:23:13 CDT 2020