Answer is complex and involves the knowing of different Oracle concepts. I try to explain (sorry if I am going to be too wordy).
The High Water Mark (HWM) is the point in a segment beyond which data blocks are unformatted and have never been used (Oracle Concept definition). Considering that you are using ASSM (Automatic Segment Space Management)
all blocks above HWM are unformatted and never been used. All blocks below HWM might be:
Formatted and contain data
Formatted and empty because the data was deleted
Allocated, but currently unformatted and unused
DML operations do not release free space from the table below the HWM. Therefore a lot of DML on a table might leave the table fragmented. DDLs always reset the HWM. More consideration about the block use can be done considering two impotant storage parameters: PCTFREE and PCTUSED.
What about HWM in files? The high water mark of a file shows you the smallest size the file could be "shrunk" down to. Any DBA has its own query to find the HWM in a datafile. Personally I use one taken by Tom Kyte and is this:
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
The HWM position in a datafile does no represent necessarily the free space available in a datafile. You may have a 20Gb tablespace where dba_free_space shows 19gb free but latest HWM is set at 18Gb position.
In conclusion: the position of HWM in a datafile does not represent the real space freely available in the datafile. For this, the query on sm$ts_free is right. Additionally tables can have allocated more space than one is really used.
Your initial idea was not bad at all. What you can do is store the unwanted partitions with their local indexes in separate tablespaces. Use rman for the cloning but use the SKIP TABLESPACE option to not clone the tablespaces with the unwanted partitions. (assuming online backup)
After the clone, the skipped tablespaces have datafiles with status RECOVER.
see RMAN DUPLICATE DATABASE: Options
In the end you just drop the unwanted partitions. To be able to do that you first have to get rid of things like constraints and indexes that need to be re-created later on. This worked in 10gR2. Make sure that you don't drop the last partition of a table, in that case drop the table.
It is a bit of work but certainly possible. If the difference in Volume is huge or there are lots of copies, it might be worth spending some time for it.
Best Answer
Yes.
No. Depends on the amount of data in your LOB segment. If your segment is 200 GB, but you have only 5 GB data in it (because the rest of the data was deleted), then you do not need 200 GB.
Not necessarily. Starting with 12.2,
alter tablehas themove onlineoption:The above option is not available in 12.1.