The logging option is a part of the STORE AS clause and determines if REDO data is logged when a LOB is updated. After you finish loading data, if necessary, you can use the ALTER TABLE statement to modify the LOB storage characteristics for the LOB data segment for normal LOB operations (for example: to CACHE or NOCACHE LOGGING). Only some storage parameters can be modified. For example, you can use the ALTER TABLE. MODIFY LOB statement to change RETENTION, PCTVERSION, CACHE or NOCACHE LOGGING or NOLOGGING, and the STORAGE clause. If you must modify the LOB storage parameters on an existing LOB column, then use the ALTER TABLE.
; CREATE TABLE AS SELECT; LOBNOCACHE NOLOGGING LOB INSERTUPDATE DELETE. ALTER TABLE cache_test MODIFY LOB (testlob) (NOCACHE);. ALTER TABLE blobtab MODIFY LOB(blobcol) (SHRINK SPACE CASCADE);.
When creating a table with a LOB column, keep in mind that under the covers, Oracle will create a LOB segment and LOB index for each LOB column. By default, the LOB segment and LOB segment will be created in the same tablespace as the table, but you have the option of changing this. Values that can be changes are PCTVERSION, CACHE, and LOGGING options as well as any of the storage clause values. ALTER TABLE test_lob MODIFY LOB (image) ( STORAGE (NEXT 1M) CACHE ); Table altered. ALTER TABLE deduplicate_tab MODIFY LOB(clob_data) ( KEEP_DUPLICATES ); EXEC DBMS_STATS.gather_table_stats(USER, ‘deduplicate_tab’); COLUMN segment_name FORMAT A30 SELECT segment_name, bytes FROM user_segments WHERE segment_name IN (‘KEEPDUP_LOB’, ‘DEDUP_LOB’); SEGMENT_NAME BYTES —————————— ———- DEDUP_LOB 76808192 KEEPDUP_LOB 68091904 2 rows selected. NOLOGGING is not applicable to any UPDATE operations resulting from the MERGE statement. NOCACHE CACHE READS (to change logging of existing LOB columns) 5.
ALTER TABLE branch MODIFY PARTITION branch_ny STORAGE(MAXEXTENTS 75) LOGGING; I still find confusion out there about the LOGGING and NOLOGGING clauses when performing DML and DDL operations, the reality is that the NOLOGGING clause will work only on particular conditions, bu. NOLOGGING is not applicable to any UPDATE operations resulting from the MERGE statement. Oracle technology is changing and we strive to update our BC Oracle support information. The second is to use the BLOB or a CLOB data type and store the entire files inside the database. You can’t modify an existing large object column. ALTER TABLE item ADD (another CLOB) LOB (another) STORE AS BASICFILE item_clob (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768 PCTVERSION 10 NOCACHE LOGGING STORAGE (INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645));. 32768 PCTVERSION 10 NOCACHE LOGGING STORAGE (INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645)); Primary key supplemental logging includes the primary key for rows affected by UPDATE and DELETE changes. Oracle implements supplemental logging at database level, schema level and at table level. SQL ALTER DATABASE DROP SUPPLEMENTAL LOG DATA; Database altered.