Uniform Size in Local Extent Management

I want to create two tablespaces, one for big tables (can grow upto 10G of data a year), and one small tables.

Which uniform extent sizes are appropriate for those tablespaces if I use local extent management?

Here are my two sample SQLs.

CREATE TABLESPACE SMALL_DATA
DATAFILE 'SMALL_DATA.dbf' SIZE 1024M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

CREATE TABLESPACE BIG_DATA
DATAFILE 'SMALL_DATA.dbf' SIZE 2056M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128M;

Thanks
winzawAsked:
Who is Participating?
 
p_yaroslavCommented:
Hello!

Both statements appopriate,but....

If all big tables may increase in size up to 10 Gb,  extent size must be bigger (for example 1Gb). But if in this tablespace may appear tables with lower size, of course you need decrease uniform size to most small table in tablespace.

Also and for small tablespace.

AFAIK.. the best is 8-12 extents for every table.

Best regards!
Yaroslav.
0
 
rcs2010Commented:
do you mean 1 table can grow up to 10gb a year, if so have you considered partitioning the table and using multiple tablespaces for it.

If you want a 10gb+ tablespace are you using multiple datafiles if so the extent size should fit into the datafile size as near as possilbe an even multiple of times
eg extent 128mb datafile size 2100mb. minimising the wasted space.

you may also need to consider recovery times for a tablespace this large.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.