1

Is there any way to specify different tablespaces for each partition if I use interval partitioning?

My table is (it is just example, I have more columns in the table):

create table MY_TABLE
(
  id                        NUMBER(20) not null,
  type                      VARCHAR2(1 char) not null,
  session_id                NUMBER(12) not null,
  date_of_beginning         DATE not null,
  account_number            NUMBER not null
)
PCTFREE 1
PARTITION BY RANGE (date_of_beginning) 
INTERVAL(NUMTOYMINTERVAL(1, ''MONTH'')) 
SUBPARTITION BY HASH(account_number) subpartitions 50
(  
   PARTITION p_1 VALUES LESS THAN (TO_DATE(''01.06.2015'', ''dd.mm.yyyy''))
)';

I want each partition to be stored in separate tablespase. Any ideas how can I achieve it? Maybe some trigger?

For other tables like this one, we have a job, which creates tablespace and partition in the beginning of the month. But this one creates partitions automatically because of intervals.

Tatiana
  • 1,489
  • 10
  • 19
  • In your case you can for sure specify tablespace for those 50 subpartitions. However, interval partitioning is too unmanageable and I prefer range instead. – Florin Ghita Dec 18 '15 at 08:25
  • The doc says: _The optional STORE IN clause lets you specify one or more tablespaces into which the database will store interval partition data._ – Florin Ghita Dec 18 '15 at 08:26
  • @FlorinGhita thank for tip about `STORE IN` - will check it – Tatiana Dec 18 '15 at 08:31
  • I'm curious as to why you want each partition in a different tablespace? The only reason I'd separate partitions out into different tablespaces is when I know the contents won't change and I can make the tablespace readonly. – Boneist Dec 18 '15 at 09:08
  • @Boneist As far as I know it is for deletion purpose. I have only a feedback from DBA: new tables’s partitions should be created in separated table spaces in order to drop partition together with tablespace – Tatiana Dec 18 '15 at 09:17
  • @FlorinGhita "*interval partitioning is too unmanageable and I prefer range instead*" Isn't it the other way round? Interval partitioning needs less maintenance, no worries to keep adding the partitions. YMMV... – Lalit Kumar B Dec 18 '15 at 09:20
  • That's the problem. Creation of partitions is automatically but purge is not. Tatiana's problem is just another one. – Florin Ghita Dec 18 '15 at 09:25
  • @Tatiana it's perfectly possible to drop partitions without needing to drop the corresponding tablespace. In fact, I would say that by going down the route of having a separate tablespace per partition, you're actually creating much more of a maintenance headache than you would if you just had a single tablespace to hold all tables and their corresponding partitions! – Boneist Dec 18 '15 at 10:08
  • @Boneist it is requirement from DBA. I didn't ask why we need it. And I know that we can drop partition without dropping TS. – Tatiana Dec 18 '15 at 10:18
  • I'd go back to the DBA and ask why they want to implement it this way; it sounds like it'll be a maintenance nightmare for them! And why would they want to drop the tablespace after the partition(s) have been dropped? Surely easier to reuse it for new partitions? – Boneist Dec 18 '15 at 10:25
  • @Boneist In data warehouse projects is a common practice to have monthly(on annual) tablespaces(named like DATA_HIST_201501 for January) with daily partitions(so, many partitions per tablespace per table). There is no need of code to purge partitions for hundreds/thousands of tables. The DBA archive it and then drop it. Every tablespace has own purging rules. Similar data is put in similar tablespace. etc... So, the question is good and I upvoted it. – Florin Ghita Dec 18 '15 at 15:20
  • @Boneist it is an answer from DBA regarding this question: "Specific tablespaces are better for maintanance disk space (we don’t have enough disk space actually). Drop parition -> drop tablespace (remove data files) -> more disk space " – Tatiana Jan 05 '16 at 10:11
  • @Tatiana hmm, but if that space is simply going to be used (and filled up) by a new tablespace for a new partition, there's an argument to be said that dropping the old tablespace and creating the new tablespace is unnecessary work. But hey, it's not my database and I don't have to maintain it! *{;-) – Boneist Jan 05 '16 at 10:15

0 Answers0