I created a local index on a subpartitioned table without specifying the tablespace so it built it on the same tablespace as the table, AGGS_DATA_SSD. I subsequently rebuilt the subpartitions into the desired tablespace, AGGS_IDX_SSD. I am, however, seeing some partitions that have the previous tablespace. So, I have few questions.

1) Why do any of the index partitions have a tablespacce value at all? I would think only the subpartitions would have a tablespace specification.
2) Can I change that, either to get rid of the specification or change it to AGGS_IDX_SSD?
3) The table is partitioned by interval, so when a new partition key value comes in, it will create a new partition and subpartition. In which tablespace will those be created?

Why do you have different tablespace for data & index when they are accessed sequentially. First look up index details then go get row block.
If both tablespaces reside on the same disk volume/mount point, please quantify what is gained by having separate tablespaces.

<wild guessing>
Your DBA wants the indexes to be read as fast as possible. So he decided to store them not on a normal (disc) hard drive but on an SSD (which is expensive but increases the performance significantly). The only way I know to increase the performance even more is to put them in memory (that's an other story).

You can rebuild the indexes on the new tablespace with a statement like that:

Seriously? Why would you post twice to give the illusion to somebody who might actually have a suggestion that my question has already been answered? Nobody, of course, is forcing you to help me actually address my problem, but if you prefer not to, I wish you would just not respond at all.

Never the less:
<wild guessing>
Did you notice, that we are not looking at the partition(s) itself but on the indexes (which need a space to be stored for themselves)? For the partitions itself try this statement:

The DBAs requested it? So what is your role in all this? Managing tablespaces IS the DBA's job.

Again, seriously? I asked a fairly straightforward question. I know we all volunteer our time here to try to help each other, but you all are not helping at all. You either know how I can do what I asked or you don't. I don't need suggestions on what our roles should be. I have no idea why some of you feel the compulsion to offer opinions on questions that were never asked. You're just cluttering my topic and creating the illusion that my actual question is being addressed.

Did you notice, that we are not looking at the partition(s) itself but on the indexes (which need a space to be stored for themselves)?

Was there anything in what I posted that would lead you to the conclusion that I'm a complete idiot, or at least a complete newbie? I would honestly like to know so I can refrain from doing so in the future. Of course I noticed that I was looking at the index tablespace, not the table tablespace. That's exactly what I wrote. I don't care about the table's tablespace so I don't need the query to find it.

quirks wrote on Thu, 05 October 2017 01:07

The (local) indexes of the subpartition level are stored at AGGS_IDX_SSD.
The (local) indexes on the partition level which spans over all subpartitions are stored at AGGS_DATA_SSD.

You mean exactly like I posted? Well, mostly. If there were partition indexes that spanned over all subpartitions (whatever that could possibly mean), I would expect those segments to show up in dba_segments and occupy some space, i.e. segment_names would have multiple segment_types associate with them. There are, it appears, none.

This leads me to the conclusion that there are no index partitions on subpartitioned indexes. The info in user_ind_partitions is there for the CBO and possibly for creating new index subpartitions for interval partitioned tables, which is the part that concerns me.

quirks wrote on Thu, 05 October 2017 01:07

You probably need to drop the index and rebuild it on its new location (this might take a while depending on the size of the table and your hardware)?!?

Thanks, I know how to drop and build an index. I could have done that without having to post here. Given that my table is interval partitioned, as stated in question three, specifying the partitions and subpartitons at index creation time won't work. I was asking if I could address my particular problem without dropping the indexes. If you don't specifically know how to do this, I would really appreciate if you don't clutter my topic.

you probably didn't set an index partition while creating the index. Then the index (sub)partitions will be in the same tablespace as the table partitions.
You can alter the index to set a default tablespace for new (sub)partitions. But there is no way to move the index partitions to a new tablespace without dropping and recreating the index.