|
|
|
|
| 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 ;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
