tablespace creation parameters

[Roshan]

Hi Erman,

I would like like to know what parameters I need to choose for initial extent and maximum extent block size ...

The tablespaces will be used for reporting purposes where there will be lots of joins.

I have attached the list of tablespaces

export.xlsx

Kindly advise.

[ErmanArslansOracleBlog]

What is your db version? & Do you use locally managed or dictionary managed tablespaces?

Are you aware of this fact? -> MAXEXTENTS - This storage parameter is valid only for objects in dictionary-managed tablespaces.
MAXEXTENTS is ignored for objects residing in a locally managed tablespace, unless the value of ALLOCATION_TYPE is USER for the tablespace in the DBA_TABLESPACES data dictionary view.

[Roshan]

Oracle Database 12.2.0

I am using locally manged tablespace

[ErmanArslansOracleBlog]

INFO:
*MAXEXTENTS - This storage parameter is valid only for objects in dictionary-managed tablespaces.
*You cannot change the value of MINEXTENTS for an object that resides in a locally managed tablespace.a

As for the "Initial" :

You should classify your objects in to 4 categories.

1)small objects
2)slightly larger than small objects
3)medium objects
4)truely large objects

small objects can have 64k extent sizes
slightly larger than small objects can have 256k-512k extent sizes
Medium sized objects can have 1MB extent sizes
truely large objects can have more than 1MB extent sizes.

You may have a seperate tablespace for these objects as well.ç
For ex: all the small objects should reside in a tablespace, and this tablespace should only be used by the small objects..
This will prevent the fragmentation..

Actually my reference for the above is AskTOM..

Here you can read the whole thread -> https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:228413960506

[Roshan]

Hi Erman,

thanks for the link.

I would like to know what is the benefit of using dictionary managed tablespace over locally manged tablespace.

Thanks a lot for your support.

[ErmanArslansOracleBlog]

I don't see any benefit of using dictionary tablespaces over locally managed tablespaces.

For more details -> Check -> https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:895028559085