Quick copy-paste ready blocks for your script SQL
text
-- Modern recommended version (2025–2026 standard)
v_common_clause CONSTANT VARCHAR2(500) :=
'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NOLOGGING '
|| 'NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE '
|| 'BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON '
|| 'ROW STORE COMPRESS ADVANCED';
SQL
text
-- Slightly shorter + old-school compatible version
v_common_clause CONSTANT VARCHAR2(400) :=
'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED NOLOGGING ONLINE '
|| 'EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO '
|| 'COMPRESS FOR OLTP';
what does this bothdo?
Both versions do almost exactly the same thing — they define the storage and compression options that will be applied when your script creates new bigfile tablespaces.
The only real difference is syntax style and how modern/future-proof the command is.
Here’s a clear comparison:
Aspect Modern version (ROW STORE COMPRESS ADVANCED) Old-school version (COMPRESS FOR OLTP) Which one is better in 2026? Official Oracle syntax Current / recommended (since ~12.1, strongly preferred in 19c/21c/23ai) Old syntax (still 100% working) Modern Compression type Advanced Row Compression (aka OLTP compression) Advanced Row Compression (same thing) Same functionality License required Advanced Compression Option Advanced Compression Option Same Typical compression ratio 2× – 4× (sometimes more) 2× – 4× (exactly the same) Same DML overhead (insert/update) Low to moderate Low to moderate Same Future-proof Yes — this is the syntax Oracle pushes now Still supported, but considered legacy Modern wins Readability / clarity Very clear what kind of compression it is Less explicit (OLTP sounds like only for OLTP) Modern wins Length Longer Shorter Old-school wins Risk of deprecation Very low Low (but possible in far future) Modern wins
Summary – What each one actually does
Both create tablespaces with these properties:
- Bigfile tablespace
- Starts with 1 GB autoextend chunks
- Can grow without limit (MAXSIZE UNLIMITED)
- Uses advanced row compression (good balance between compression + acceptable DML performance)
- Local extent management + autoallocate (Oracle manages extent sizes automatically)
- 8 KB block size
- Automatic segment space management (ASSM)
- Flashback enabled
- Online creation
- No In-Memory column store
- Logging / Nologging setting (you have different preferences in each version)
Recommendation for January 2026
SQL-- ★ This is the best choice right now ★
v_common_clause CONSTANT VARCHAR2(500) :=
'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED '
|| 'NOLOGGING ONLINE ' -- ← faster creation, common choice
|| 'NO INMEMORY '
|| 'EXTENT MANAGEMENT LOCAL AUTOALLOCATE '
|| 'BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO '
|| 'FLASHBACK ON '
|| 'ROW STORE COMPRESS ADVANCED';
Why this one?
- Uses current Oracle-recommended syntax
- Very clear what compression you're getting
- Least chance of any future surprise/warning
- Everyone who reads your code in 2026–2030 will immediately understand it
The old COMPRESS FOR OLTP still works perfectly fine today — but it's like still writing VARCHAR2 instead of VARCHAR — it works, but it's not the modern way anymore.
Pick whichever style your team is more comfortable with — functionally they are identical.
Here is a bullet-proof, modern, production-grade version of the tablespace creation clause (2025–2026 best practices).
Recommended final version – most conservative & safe
SQLv_common_clause CONSTANT VARCHAR2(400) :=
'AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED '
|| 'NOLOGGING '
|| 'ONLINE '
|| 'NO INMEMORY '
|| 'EXTENT MANAGEMENT LOCAL AUTOALLOCATE '
|| 'BLOCKSIZE 8192 '
|| 'SEGMENT SPACE MANAGEMENT AUTO '
|| 'FLASHBACK ON '
|| 'ROW STORE COMPRESS ADVANCED';
Why these exact settings are considered "bullet-proof" today
Setting Value used Why this is safer / better Risk if changed / omitted AUTOEXTEND NEXT 1024M (instead of 1G) 1G = 1024M, but written as 1024M is more explicit Very low MAXSIZE UNLIMITED Standard for bigfile tablespaces Almost none NOLOGGING Yes Much faster initial creation (especially 6 TB+) Redo generation during creation ONLINE Yes Tablespace is usable immediately Required anyway NO INMEMORY Explicit Prevents accidental In-Memory population Very useful in mixed environments EXTENT MANAGEMENT LOCAL AUTOALLOCATE Most flexible, Oracle manages extent sizes best Very safe choice BLOCKSIZE 8192 (instead of 8K) More explicit, zero chance of confusion Practically none SEGMENT SPACE MANAGEMENT AUTO ASSM – the only reasonable choice since ~11g None FLASHBACK ON Yes Allows flashback operations on the tablespace Important for recovery scenarios ROW STORE COMPRESS ADVANCED Yes Best balance compression vs DML performance Requires Advanced Compression license
Even more conservative / extra-safe version (if you want maximum clarity)
SQLv_common_clause CONSTANT VARCHAR2(500) :=
'DATAFILE SIZE 10G ' -- ← explicit initial size (recommended)
|| 'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED '
|| 'NOLOGGING '
|| 'ONLINE '
|| 'NO INMEMORY '
|| 'EXTENT MANAGEMENT LOCAL '
|| 'AUTOALLOCATE '
|| 'BLOCKSIZE 8192 '
|| 'SEGMENT SPACE MANAGEMENT AUTO '
|| 'FLASHBACK ON '
|| 'ROW STORE COMPRESS ADVANCED';
Quick checklist before you go live with this
text☑ Advanced Compression license is enabled (DBA_FEATURE_USAGE_STATISTICS)
☑ You are creating BIGFILE tablespace
☑ Your database is >= 12.1 (preferably 19c+)
☑ You are okay with NOLOGGING during initial creation
☑ Your storage can handle ~6TB+ per tablespace (since initial_size_gb = 6268)
☑ You tested insert/update performance with compression in your environment
☑ You have monitoring for tablespace usage (especially autoextend events)
Most bullet-proof pattern used in large environments (2025–2026)
SQL-- The one most careful DBAs are actually using right now
v_common_clause CONSTANT VARCHAR2(512) :=
'BIGFILE ' -- explicit
|| 'DATAFILE SIZE 10G ' -- safe starting size
|| 'AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED '
|| 'NOLOGGING ONLINE '
|| 'NO INMEMORY '
|| 'EXTENT MANAGEMENT LOCAL AUTOALLOCATE '
|| 'BLOCKSIZE 8192 '
|| 'SEGMENT SPACE MANAGEMENT AUTO '
|| 'FLASHBACK ON '
|| 'ROW STORE COMPRESS ADVANCED';
Pick whichever version feels most comfortable to you and your team — all of them are very safe when used with BIGFILE.
Good luck Revanth — may your tablespaces be fast, compressed, and never run out of space! 🚀
No comments:
Post a Comment