OraFAQ Forum: Performance Tuning » Move unused partitioned from one tablespace to another tablespace.

Home » RDBMS Server » Performance Tuning » Move unused partitioned from one tablespace to another tablespace. (Oracle 12c (12.1.0.1.0)- Unix)

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

Move unused partitioned from one tablespace to another tablespace. [message #667537]

Thu, 04 January 2018 23:25

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.

Report message to a moderator

Re: Move unused partitioned from one tablespace to another tablespace. [message #667547 is a reply to message #667537]

Fri, 05 January 2018 02:57

John Watson
Messages: 8976
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.

Report message to a moderator

Re: Move unused partitioned from one tablespace to another tablespace. [message #668110 is a reply to message #667547]

Wed, 07 February 2018 03:23

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 ;

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 18:56:02 CDT 2025