When rebuilding a table with lobs, with "Rebuild with CACHE option and restore to NOCACHE afterwards" checked, the statement for restoring NOCAHE is missing.
This is true both for commercial 13.3 and for beta
By the way, same time ago I had reported that on versions 12 and later, the script for users did not get the encrypted password:
I had even suggested a way to do that, here for sake of brevity:
select REGEXP_REPLACE ( DBMS_METADATA.GET_DDL ('USER', 'Utente' ), '.VALUES (''.+'').', '\1',1,1,'n' ) from dual;
However, I still see DENTIFIED BY in the script
Regards
Mauro
Hi Mauro,
The cache option works on non-partitioned tables and partitioned tables, however, as far as I can tell, Oracle does not allow us to modify the cache option on subpartition lobs. If you know of a way, please send me some DDL for that.
Sorry I didn't make any changes for password. I am working on this now.
-John
Perhaps I was not clear enough, my English is not so good as I wish and I apologize for that.
What I meant is this:
with that option checked, a statemente like this should be generated:
ALTER TABLE Table_Name MODIFY LOB (Lob_Field) ( NOCACHE );
and executed after the move statement, that is all.
Regading subpartitions, here is a complete example:
CREATE TABLE mauro.t1 ( key number, value1 number, value2 number, big_stuff blob )
LOB ( big_stuff ) STORE AS LOB_big_stuff
PARTITION BY RANGE ( value1 )
SUBPARTITION BY HASH ( value2 )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1000),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (MAXVALUE)
);
select table_name, column_name, lob_name, lob_partition_name, lob_subpartition_name, cache
from DBA_LOB_SUBPARTITIONS
where table_owner = 'MAURO'
At this point, you should see CACHE NO but with:
ALTER TABLE mauro.t1 MODIFY LOB (big_stuff) ( CACHE );
the previous query should give CACHE YES
With
ALTER TABLE mauro.t1 MODIFY LOB (big_stuff) ( NOCACHE );
you can revert to CACHE NO
Regards
Mauro
Hi Mauro,
Your English is very good.
The command ALTER TABLE mauro.t1 MODIFY LOB (big_stuff) ( CACHE ); works, but it operates on all lob subpartitions for the entire table.
When that window sends the commands to rebuild lobs for partitions and subpartitions, it is modifying the lob for each subpartition. So when I created this option, I wanted to be able to set the cache for each lob of each subpartition. I can do that for partitions but not subpartitions.
For example: This command works on a partitioned table:
Alter table A_LOB_PART modify partition P1 lob (LOB_COL) (cache);
but trying to do the same thing on a subpartitioned table, like this, I get an error.
Alter table A_LOB_SUBPART modify subpartition SP1 lob (LOB_COL) (cache);
ORA-22877: invalid option specified for a HASH partition or subpartition of a LOB column
That's why the window doesn't do anything to set the cache of lob subpartitions.
There is another way to change cache:
alter table Table_Name
move subpartition Sub_Partition
lob (Lob_Field) store as ( noCACHE );
But there is a bug when hash partitioning / subpartitioning is used, at least up to 12c ( regrettably I have no 18c or 19c to test ).
So the above works with range partitioning, not with hash
My table was not partitioned, by the way ...
And the problem is just the impossibility to manage differently the different subpartitions, it is not a problem if they are managed all the same way
So I think that the statement should be generated in the script if the checkbox is marked.
And moreover, I wonder if there may be really some good reason to manage differently the several subpartitions, namely, if there is a good reason to have a subpartition cache ( or nocache ), why that very same reason should not be good for the others as well?
Regards
Mauro
You're right, you didn't say anything about a partitioned or subpartitioned table in the beginning. I assumed you were talking about a subpartitioned table because in my quick test, it changed CACHE for nonpartitioned and partitioned tables, but not subpartitioned.
Let's go back to talking about your actual case.
Looking at the source code, it appears that we only include CACHE in the 'ALTER TABLE MOVE LOB' statement if the lob had CACHE=NO. If CACHE=YES, then there is no reason to change it because it already has the desired setting. In addition, setting it to NOCACHE after would leave it at a different setting than it was initially.
See below:
Right, as a matter of fact, if the script is generated from "LOB segment" tab, the Alter table ... modify lob (..) (nocache);
the "rebuild nocache" statement is generated as well.
But, in the following case it does not:
- LOB segment, I load some lobs and check one ( or more ) of them
- Thresholds and performance options, I check "Rebuild associated lob with tables" and "Rebuild with nocache"
- from the "tables" tab, I use "Load tables for checked lobs"
- I generate the script
here, the additional statement "Rebuild nocache" is missing
Regards
Mauro
I thought the idea was that if the lob has NOCACHE, then rebuild with CACHE, and then set it back to NOCACHE. At the end if the rebuild, CACHE/NOCACHE remains at its initial setting.
If it starts off at CACHE and we change to NOCACHE after the rebuild, then we have changed the initial setting.
I think if the window is going to change all LOBS to NOCACHE, regardless of their initial setting, this should be a different option.
Am I misunderstanding the purpose of this feature? If I remember correctly, it was your request.
My understanding is that there is some benefit to rebuilding a lob with CACHE, but afterwards, you want the LOB to be at it's initial CACHE/NOCACHE setting.
If you just want to change everything to NOCACHE, that seems different.
OH!
I see the problem now. You are rebuilding tables. And checking the option to bring lobs along. And we aren't setting the lobs back to initial value this way.
Sorry for taking so long to figure it out. 
I have just been on the Lob tab all this time, not tables.
You are quite right, it was a request of mine, to add CACHE just for moving operations ( if it was NOCACHE originally ) because it is faster and then restore the NOCAHE, as it was before.
Only, in the particular case I described in detail in my previous post, the nocache restoring statement is missing
Regards
Mauro
I chose this way because I had a table with 5 lobs; the other one would have leaded to 5 different move operations for the very same table, by far much more efficient move the table once with all of its lobs.
This is how I run into that small bug.
Regards
Mauro
It makes sense. I am logging the bug now and will have it fixed soon.
