No question at this time
DBA Top 10
1 M. Cadot 17900
2 A. Kavsek 15900
3 M. Hidayathullah ... 9200
4 B. Vroman 8600
5 T. Boles 4900
6 P. Wisse 4700
7 J. Schnackenberg 3800
8 K. Van Nieuwenhov... 2100
9 G. Van Cauter 1400
10 A. Hudspith 800
10 T. P 800
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48079
Total active users1704
Act. users last 24h9
Act. users last hour0
Registered user hits last week295
Registered user hits last month1831
Go up

Move partitions of table to new tablespace
Next thread: Query Output Format
Prev thread: flash back tables

Message Score Author Date
Dear Friends, I have requirement of to move ran...... Prasathi Ji Nov 08, 2017, 23:58
Hello Prasathi, maybe simply:  1. make sure yo...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Nov 09, 2017, 09:25
Dear vroman, Thankyou for quick response. My ta...... Prasathi Ji Nov 09, 2017, 10:51
Hello Prasathi,    <<<I>Expdp ex...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Nov 09, 2017, 12:13
One of the really cool things about Oracle 12c is ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Tim Boles Nov 09, 2017, 18:34
Thankyou Bruno and Tim for your support and guidan...... Prasathi Ji Nov 13, 2017, 06:22
Dear Friends, Table partition movement activity...... Prasathi Ji Nov 13, 2017, 06:40
It depends on what you mean by "released". It is ...... Tim Boles Nov 13, 2017, 15:14

Follow up by mail Click here


Subject: Move partitions of table to new tablespace
Author: Prasathi Ji, India
Date: Nov 08, 2017, 23:58, 14 days ago
Os info: Redhat Linux 5.8
Oracle info: oracle12c release 1
Message: Dear Friends,

I have requirement of to move range partitioned of table xyz partitions to new tablespace B.

Table has local indexes which also need to move different tablespace.

Move table to new tablespace steps given below.Please check and review the steps.

1.Gather information of table xyz partitions information using sql script.

2.Gather partition information of indexes

3.Create new tablespace newtab to move partition data from old to new tablespace

4.Take export backup of table xyz

5.Gather table stats after that check num_rows
of records of given table

6.Move table partitions to new tablespace

ALTER TABLE XYZ MOVE PARTITION p1 TABLESPACE newtab PARALLEL(DEGREE 4) NOLOGGING;

7.Finally rebuild of indexes and check table partitions are in valid state and find num_rows column count to match with previous count.

Thankyou in advance

Regards
Prasathi


Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Move partitions of table to new tablespace
Author: Bruno Vroman, Belgium
Date: Nov 09, 2017, 09:25, 13 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Prasathi,

maybe simply:
 1. make sure you have a valid backup of your database
 2. move table partition to new tablespace
 3. rebuild indexes in new tablespace
 [4. refresh statistics]

If you do the move partition with NOLOGGING (I wouldn't recommend this but I don't know your constraints/volumes), take immediately a new backup.

(Note that "num_rows" is an estimate so it doesn't have to match "before/after"; no need to gather stats before the move)

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Move partitions of table to new tablespace
Author: Prasathi Ji, India
Date: Nov 09, 2017, 10:51, 13 days ago
Message: Dear vroman,

Thankyou for quick response. My table size is 32gb in size.Mount point has 50gb free space.

Expdp export backup is enough for this table or take complete database backup through rman.

Regards
Prasathi
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Move partitions of table to new tablespace
Author: Bruno Vroman, Belgium
Date: Nov 09, 2017, 12:13, 13 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Prasathi,

   <<Expdp export backup is enough for this table or take complete database backup through rman.>>

Well, beware! datapump is NOT a "backup" (think at data consistency: inserts/updates/deletes committed after the start of the expdp session...). It can be very convenient and useful, but the only actual backup is ... a backup ;-)
And to take backups, RMAN is a very nice tool, isn't it?

Remark: you are not obliged to move all the partitions at the "same time", you are not even obliged to move all the partitions: maybe you move old partitions to another tablespace and keep recent ones where they are.

Best regards,

Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Move partitions of table to new tablespace
Author: Tim Boles, United States
Date: Nov 09, 2017, 18:34, 13 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: One of the really cool things about Oracle 12c is that you can move partitions ONLINE.

https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#ADMIN01514

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/Partitioning/12c_parti.html

https://richardfoote.wordpress.com/2014/01/15/12c-online-partitioned-table-reorganisation-part-ii-move-on/

I have been playing around with it and it is pretty cool.

Tim
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Move partitions of table to new tablespace
Author: Prasathi Ji, India
Date: Nov 13, 2017, 06:22, 10 days ago
Message: Thankyou Bruno and Tim for your support and guidance.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Move partitions of table to new tablespace
Author: Prasathi Ji, India
Date: Nov 13, 2017, 06:40, 10 days ago
Message: Dear Friends,

Table partition movement activity is successfully completed.But It has taken extra disk space.Why used space is not released after movement of partition from old tablespace to new tablespace.

Regards
Prasathi
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Move partitions of table to new tablespace
Author: Tim Boles, United States
Date: Nov 13, 2017, 15:14, 9 days ago
Message: It depends on what you mean by "released". It is "released" in the sense that other objects can now use it.

You can reclaim unused space and move the High Water Mark to show less "free" space.

https://docs.oracle.com/database/121/ADMIN/schema.htm#ADMIN11600

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here