Thanks for the question, Matt.
Asked: September 29, 2021 - 2:27 pm UTC
Last updated: October 21, 2021 - 1:44 am UTC
Viewed 100+ times
Oracle Database, SQL Language Reference 19c E96310-11 August 2021
CREATE TABLESPACE documentation (15 SQL Statements, CREATE TABLESPACE) says that:
'Use the storage_clause to specify storage parameters for all objects created in the tablespace. This clause is not valid for a temporary tablespace or a locally managed tablespace. For a dictionary-managed tablespace, you can specify the following storage parameters with this clause: ENCRYPT, INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, MAXSIZE, and PCTINCREASE. Refer to storage_clause for more information.'
when storage_clause documentation (8 Common SQL DDL Clauses, storage_clause) says that:
'The specification of storage parameters for objects in locally managed tablespaces is supported for backward compatibility. If you are using locally managed tablespaces, then you can omit these storage parameter when creating objects in those tablespaces.'
but later it states that:
'The storage_clause is interpreted differently for locally managed tablespaces. For locally managed tablespaces, Oracle Database uses INITIAL, NEXT, PCTINCREASE, and MINEXTENTS to compute how many extents are allocated when the object is first created. After object creation, these parameters are ignored. For more information, see CREATE TABLESPACE.'
I'm little confused here. Could you clarify it? Now it looks like CREATE TABLESPACE points to storage_clause for more information and storage_clause points back to CREATE TABLESPACE for more information? Moreover CREATE TABLESPACE says that in locally managed tablespaces it it unsupported when storage_clause says something different.
and Connor said...
OK, it is two subtly different things here.
1) Storage clause *on a tablespace* is not valid for locally managed, eg
SQL> create tablespace demo datafile 'X:\ORACLE\ORADATA\DB19\PDB1\DEMO.DBF'
2 storage ( initial 2m next 10m);
storage ( initial 2m next 10m)
ERROR at line 2:
ORA-02180: invalid option for CREATE TABLESPACE
2) Storage clause on a *table* (index etc) which will *into* a locally managed tablespace, will be interpreted differently.
Because a locally managed tablespace has discrete sizes, the storage clause on the *table* will be used to chose extents needed in the tablespace.
SQL> create tablespace demo datafile 'X:\ORACLE\ORADATA\DB19\PDB1\DEMO.DBF' size 200m
2 extent management local
3 uniform size 4m;
SQL> create table QQ ( x int ) storage ( initial 13m next 5m ) tablespace demo;
SQL> insert into QQ values (1) ;
1 row created.
SQL> select bytes from dba_extents
2 where segment_name = 'QQ';
You wanted 1 extent of 13m but we can only give you chunks of 4m, so we gave you 4 extents of 4m.