| Backup selected data [message #546569] |
Wed, 07 March 2012 07:55  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi,
I want to take backup of selected data from tables of a schema (as huge data, that is not used, causing slow query performance)
I planned to create a seprate backup schema and tablespace to store the data from these tables. Then write procedures that can move the data to and fro among table of those schema. And create partitioned index on those backup tables.
Want expert suggestion to adopt best approach / alternate approach.
Thank you!!
|
|
|
|
|
|
|
|
|
|
| Re: Backup selected data [message #546578 is a reply to message #546574] |
Wed, 07 March 2012 08:51  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
I am not sure, but partition which tables (orignal tables, backup tables).
If you are talking about orignal tables, can I partition a table having data? And what if the partition contain both the selected and non-selected data?
Well, with respect to partitioning, I was thinking about to create single partitioned index on backed up data.
Can you confirm partitioning thing, or where I can get more info (I already tried to google but no effective solution).
Regards,
Manu
|
|
|
|
|
|
|
|
| Re: Backup selected data [message #546605 is a reply to message #546582] |
Wed, 07 March 2012 09:59 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi BlackSwan,
Its not actually duplicating, its moving data from original tables to backup tables as larger size of orignal tables causing slower query performance.
Hi Michel,
I got you on partitioning thingy. So next fastest way achieve my objective will be CTAS, right? If right, should I use nologging and parallel feature? or some other features for better performance. If not right, what should be my approach?
Regards,
Manu
|
|
|
|
|
|
| Re: Backup selected data [message #546618 is a reply to message #546609] |
Wed, 07 March 2012 13:09 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Thanks for prompt response. I am getting interest in your first suggestion related to partitioning, as I myself think that backing up data with CTAS nologging gonna take a lot of time and processing power, plus a lot of downtime for application, and I am not sure, if it ever fails in between because of some error.
I have one more question about partitioning.
Suppose, if I partition (list partition, I have not myself created partitioned ever) in such a way that a partition will either contain data that should be moved, or should not be moved, then according to you I can exchange the partition.
Then after partition exchange, if we receive any new value in column on which I have created list partition, so how this value will be entered into partitioned table (Will partition for that new value will gets automatically created, or we need to create it manually, or its not possible).
Thank you!!
|
|
|
|
|
|
| Re: Backup selected data [message #546888 is a reply to message #546619] |
Fri, 09 March 2012 12:45 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
OK.
Can you give me some practical advise...
I have data like below (Hash partitioned total 128 partitions, have data in 34 partitions, all else Blank).
Partition Study Rows (Vendor_Data) Study Rows (Vendor_Record)
SYS_P253 564672 17053
SYS_P172 30608708 2788481
SYS_P198 49582384 4608052
SYS_P200 3499365 330046
SYS_P220 21863961 576365
SYS_P198 19107340 535124
SYS_P213 23211228 717335
SYS_P170 732938 23879
SYS_P245 2060534 71108
SYS_P157 546927 20920
SYS_P267 2375787 87088
SYS_P244 1080627 36845
SYS_P183 374603 13597
SYS_P182 592532 21893
SYS_P263 1024136 35956
SYS_P218 1964144 67247
SYS_P159 683619 23076
SYS_P177 155764277 13477759
SYS_P159 37909389 3422686
SYS_P179 11147038 314958
SYS_P168 812016 79088
SYS_P237 5726134 560084
SYS_P178 4309928 426844
SYS_P258 320014 14449
SYS_P199 503594 31665
SYS_P215 7914892 455617
SYS_P256 1411796 66565
SYS_P227 589022 26848
SYS_P195 631792 29682
SYS_P169 716311 31846
SYS_P259 31136286 1765290
SYS_P163 20778277 1445522
SYS_P208 72349406 3828848
SYS_P261 82158468 4298964
SYS_P244 28252673 1843615
SYS_P216 14952316 966922
SYS_P264 12967656 753803
SYS_P202 22446299 1553975
SYS_P227 5263658 251304
SYS_P150 1052908 50492
SYS_P233 614251 21853
With your experience can you tell me how much time it will take to convert that much data from hash to list partition (First of all, if its possible). I want this because only through list partition I can take backup easily.
I am thinking about implementing list partitioning in such a way as soon as I receive a new value, it automatically create a partition on selected tables and doing this will not affect applicatio coding (procedures and packages), am I thinking right?
Other questions are, Can we exchange hash partition in one table to list partition in another table? I think previosuly I got you right, that we can exchange list partition if we have it in both the tables, can we exchange hash partition in same why if both tabels are hash partitioned?
Please help me...
Regards,
Manu
[Updated on: Sat, 10 March 2012 10:44] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
