Skip to Main Content
  • Questions
  • CREATE TABLESPACE documentation doubt


Question and Answer

Connor McDonald

Thanks for the question, Matt.

Asked: September 29, 2021 - 2:27 pm UTC

Last updated: October 21, 2021 - 1:44 am UTC

Version: 19c

Viewed 100+ times

You Asked

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;

Tablespace created.

SQL> create table QQ ( x int ) storage ( initial 13m next 5m ) tablespace demo;

Table created.

SQL> insert into QQ values (1) ;

1 row created.

SQL> commit;

Commit complete.

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.


  (1 rating)


storage_clause in CREATE TABLESPACE

Matt, October 16, 2021 - 9:38 pm UTC

It is now little more clear. But the documentation of CREATE TABLESPACE says that storage_clause must be preceded by DEFAULT clause when it is part of default_tablespace_params, permanent_tablespace_attrs. When I add DEFAULT there then the tablespace in such shape will be created without any errors???
Connor McDonald
October 21, 2021 - 1:44 am UTC

Apologies my typo error there.

If you set the default, then future objects will use as their default but will be subject to the same conditions as part (2) of my answer.

More to Explore


Need more information on Administration? Check out the Administrators guide for the Oracle Database