Oracle DBA by Example. Code samples, Scripts, Reference.: Create Tablespace and Space Managment by example.

The Flow
Step 1 - Create a new Tablespace on a new Datafile.
Step 2 - Elaborate on Space Usage, and CREATE TABLESPACE optionsStep 3 - Create a new Table on that Tablespace.
Step 4 - Elaborate on Extents allocation
Step 5 - Grow the Table, and follow Extents size growth
Step 6 - Estimate a row size
Step 7 - Estimate number of rows per oracle block
Step 8 - Delete all entries from table, and check storage parameters

Step 1 - Create a new Tablespace on a new Datafile.
Connect as system and create new Tablespace on a new datafile, with autoextend option.
conn system/****

CREATE TABLESPACE MY_TABLESPACE DATAFILE '/oracle_db/db1/db_igt/alec_01.dbf' SIZE 500M AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Tablespace created.

SQL> exit

Step 2 - Elaborate on Space Usage, and CREATE TABLESPACE options
Now lets see what is the current status.

cd /oracle_db/db1/db_igt

ls -l

-rw-r----- 1 oracle dba 524296192 Feb 4 07:42 alec_01.dbf

SQL> select FILE_ID from dba_data_files where FILE_NAME='/oracle_db/db1/db_igt/alec_01.dbf';

FILE_ID
----------
11

SELECT TABLESPACE_NAME,

BYTES/1024/1024 AS BYTES_MB,

ROUND(MAXBYTES/1024/1024) AS MAXBYTES_MB,

USER_BYTES/1024/1024 AS USERBYTES_MB,

MAXBLOCKS,

AUTOEXTENSIBLE,

INCREMENT_BY
FROM DBA_DATA_FILES

WHERE FILE_ID=11;

TABLESPACE_NAME BYTES_MB MAXBYTES_MB USERBYTES_MB MAXBLOCKS AUTOEXTENSIB INCREMENT_BY

--------------- ---------- ----------- ------------ ---------- ------------ ------------

MY_TABLESPACE 500 32768 499 4194302 YES 1

The new datafile was created, sized to 500M.

With maxsize unlimited option, maxbytes defaults to 32 Gb.

BYTES and USERBYTES both equal the allocated space, 500 Mb.

BYTES - represent the actual file size

USERBYTES represent the size available for storage, which is roughly BYTES minus space dedicated to metadata.

SQL> SELECT SEGMENT_NAME, BYTES
FROM USER_SEGMENTS
WHERE TABLESPACE_NAME='MY_TABLESPACE';

no rows selected

SELECT TABLESPACE_NAME, BYTES/1024/1024 as "Free Mb"
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME='MY_TABLESPACE';

TABLESPACE_NAME Free Mb

--------------- ----------

MY_TABLESPACE 499

Although TABLESPACE_NAME MY_TABLESPACE might be extented to 32Bg, in dba_free_space the space is limited to currently allocated file size.

To get the space available for a tablespace to grow to, need to do
DBA_DATA_FILES.maxbytes - DBA_DATA_FILES.BYTES

SELECT TABLESPACE_NAME,

ROUND(SUM((MAXBYTES - BYTES)/1024/1024)) AS "Max Free Space in Mb"

FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'MY_TABLESPACE'

GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME Max Free Space in Mb

--------------- --------------------

MY_TABLESPACE 32268

Check the db_block_size

SQL> SELECT NAME, VALUE
FROM V$PARAMETER

WHERE NAME = 'db_block_size';

db_block_size 8192

SQL>SELECT ROUND(8192*4194302/1024/1024) AS MAXBYTES_MB FROM DUAL;

32768

As expected, db_block_size*MAXBLOCKS equals MAXBYTES

Lets limit the maxsize from a whopping 32Gb to 1 Gb

conn system/*******

SQL> alter database datafile '/oracle_db/db1/db_igt/alec_01.dbf' autoextend on maxsize 1000M;

Database altered.

SELECT TABLESPACE_NAME,

BYTES/1024/1024 AS BYTES_MB,

ROUND(MAXBYTES/1024/1024) AS MAXBYTES_MB,

USER_BYTES/1024/1024 AS USERBYTES_MB,

MAXBLOCKS,

AUTOEXTENSIBLE,

INCREMENT_BY
FROM DBA_DATA_FILES

WHERE FILE_ID=11;

TABLESPACE_NAME BYTES_MB MAXBYTES_MB USERBYTES_MB MAXBLOCKS AUTOEXTENSIB

--------------- ---------- ----------- ------------ ---------- ------------

INCREMENT_BY

------------

MY_TABLESPACE 500 1000 499 128000 YES

Notice "INCREMENT_BY" value

The datafile was created with AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE options

AUTOEXTEND ON - Oracle will autoextend the datafile as needed.

MAXSIZE UNLIMITED - The limit for datafile size is 32 Gb

EXTENT MANAGEMENT LOCAL - The dafault, as opposed to the older option of DICTIONARY

AUTOALLOCATE- As opposed to UNIFORM option.

The units for INCREMENT_BY are Oracle blocks

Initially INCREMENT_BY is set to 1.

Step 3. - Create a new Table on that Tablespace.
Lets create a table.

CREATE TABLE MY_OBJECTS

TABLESPACE MY_TABLESPACE

AS SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID

FROM DBA_OBJECTS

WHERE 1=2;

Table created.

Step 4. - Elaborate on Extents allocation

select count(*) from MY_OBJECTS;

COUNT(*)

----------

SELECT SEGMENT_NAME, BYTES, BLOCKS, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, MAX_SIZE, PCT_INCREASE, FREELISTS
FROM USER_SEGMENTS

WHERE TABLESPACE_NAME='MY_TABLESPACE';

SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE

-------------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ----------

PCT_INCREASE FREELISTS

------------ ----------

MY_OBJECTS 65536 8 1 65536 1048576 1 2147483645 2147483645

Lets check the output:

SEGMENT_NAME - MY_OBJECTS

BYTES - 65536

BLOCKS - 8

EXTENTS - 1

INITIAL_EXTENT - 65536

NEXT_EXTENT - 1048576

MIN_EXTENTS - 1

MAX_EXTENTS - 2147483645

MAX_SIZE - 2147483645

PCT_INCREASE - NULL
PCT_INCREASE - NULL

The table, was created with 1 extent.
By default first extent size is 8 Oracle blocks
As noted above, db_block_size equals 8192 bytes

8192*8=65536 bytes, or 64 Kb

By default the next extent size would be 128 Oracle blocks

8192*128=1048576 bytes, or 1Mb

Per Oracle documentation:
MAX_EXTENTS: Maximum number of extents allowed in the segment

MAX_SIZE: Maximum number of blocks allowed in the segment

In this case: 2147483645*8192/1024/1024= 16,777,216 Mb

Which is obviously wrong!!!

Step 5 - Grow the Table, and follow Extents size growth.

Now lets start to populate the table

INSERT INTO MY_OBJECTS

SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID

FROM DBA_OBJECTS

WHERE ROWNUM < 2

1 row created.

SQL> SELECT EXTENTS
FROM USER_SEGMENTS
WHERE TABLESPACE_NAME='MY_TABLESPACE';

EXTENTS

----------

SELECT AVG_COL_LEN
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME ='MY_OBJECTS';

no rows selected

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ALEC', tabname => 'MY_OBJECTS' , estimate_percent => 100, method_opt => 'FOR ALL COLUMNS' );

SELECT AVG_COL_LEN

FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME ='MY_OBJECTS';

AVG_COL_LEN

-----------

SQL> SELECT SUM(AVG_COL_LEN)
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME ='MY_OBJECTS';

SUM(AVG_COL_LEN)

----------------

So theoretically one oracle block would be able to store 8192/16=512 rows.

SQL> INSERT INTO MY_OBJECTS

SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID

FROM DBA_OBJECTS

WHERE ROWNUM < 512;

511 rows created.

SQL> select count(*) from MY_OBJECTS;

COUNT(*)

----------

512

SQL> SELECT EXTENTS FROM USER_SEGMENTS WHERE TABLESPACE_NAME='MY_TABLESPACE';

EXTENTS

----------

Even when creating 513, 514, 515, rows - still there is only one segment

SQL> SELECT COUNT(*) FROM MY_OBJECTS;

COUNT(*)

----------

515

SQL> SELECT EXTENTS
FROM USER_SEGMENTS
WHERE TABLESPACE_NAME='MY_TABLESPACE';

EXTENTS

----------

Another option to calculate length of a column is to use VSIZE function.

It would return the size, in bytes, of a column.
SELECT last_name, VSIZE (last_name) AS BYTES

Lets try with the VSIZE() function:
SELECT SUM(rowlenght) AS "rows length in Bytes"
FROM(
SELECT NVL(VSIZE(OWNER),0)+
NVL(VSIZE(OBJECT_NAME),0)+
NVL(VSIZE(SUBOBJECT_NAME),0)+
NVL(VSIZE(OBJECT_ID),0)+
NVL(VSIZE(DATA_OBJECT_ID),0) AS rowlenght
FROM MY_OBJECTS
);

rows length in Bytes

--------------------

11008

Compare this to output of SUM(AVG_COL_LEN)

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ALEC', tabname => 'MY_OBJECTS' , estimate_percent => 100, method_opt => 'FOR ALL COLUMNS' );

SELECT SUM(AVG_COL_LEN) FROM DBA_TAB_COLUMNS WHERE TABLE_NAME ='MY_OBJECTS';

SUM(AVG_COL_LEN)
----------------
27

SELECT COUNT(*) FROM MY_OBJECTS;
COUNT(*)
----------
515

515*27=13905

So, this is the physical space occupied by the rows in table MY_OBJECTS:
11,008 bytes using VSIZE() function.

13,905 bytes using DBMS_STATS.GATHER_TABLE_STATS and SUM(AVG_COL_LEN)

Initial Extent size was 65,536 bytes.

Lets see which one is more accurate, for calculating row size:
VSIZE()or SUM(AVG_COL_LEN).

Per VSIZE():
11,008/515=21 bytes is the size of one row.
65,536/21=3121 rows can fit into one segment
3121 - 515 = 2606 rows that can still can go into one segment

Per AVG_COL_LEN():
13,905/515=27 bytes is the size of one row.
65,536/27=2428 rows can fit into one segment
3121 -515 = 1913 rows that can still can go into one segment

Lets go for 2250, about the middle.....

INSERT INTO MY_OBJECTS
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID
FROM DBA_OBJECTS
WHERE ROWNUM < 2250;

2249 rows created.

SELECT BYTES, BLOCKS, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT
FROM USER_SEGMENTS
WHERE TABLESPACE_NAME='MY_TABLESPACE';

BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
---------- ---------- ---------- -------------- -----------
131072 16 2 65536 1048576

So, AVG_COL_LEN()was more accurate to calculate the row size.

Now, the table size has grown to two Extents.

First one - was sized to 65536 bytes, or 8 blocks

Second one: is also 65536, or 8 blocks

This is not same as the value for NEXT_EXTENT: 1048576 bytes, or

128 blocks.

Lets try to grow the table by another extent.

INSERT INTO MY_OBJECTS
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID
FROM DBA_OBJECTS

WHERE ROWNUM < 2001;

2000 rows created.

SQL> SELECT BYTES, BLOCKS, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT
FROM USER_SEGMENTS

WHERE TABLESPACE_NAME='MY_TABLESPACE';

BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
---------- ---------- ---------- -------------- -----------

262144 32 4 65536 1048576

Lets check USER_EXTENTS

SELECT segment_name, extent_id, bytes, blocks
FROM USER_EXTENTS
WHERE segment_name = 'MY_OBJECTS'
ORDER BY extent_id

SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------- ---------- ---------- ----------
MY_OBJECTS 0 65536 8
MY_OBJECTS 1 65536 8
MY_OBJECTS 2 65536 8
MY_OBJECTS 3 65536 8

Why does the extent size remain 8 blocks, and is not rowing to next_extent size of 16 block ?

Lets grow the table some more:
The 5th segment is of the same size as well.

SELECT segment_name, extent_id, bytes, blocks
FROM USER_EXTENTS
WHERE segment_name = 'MY_OBJECTS'
ORDER BY extent_id;

SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------- ---------- ---------- ----------
MY_OBJECTS 0 65536 8
MY_OBJECTS 1 65536 8
MY_OBJECTS 2 65536 8
MY_OBJECTS 3 65536 8
MY_OBJECTS 4 65536 8

SQL> SELECT ROUND(COUNT(*)/5)"Lines per Extent" FROM MY_OBJECTS;

Lines per Extent

----------------

1254

This is significantly lower that AVG_COL_LEN()value.

Now keep in mind that oracle stores data only in approx 60% of the block.
Other space is divided between Header, Table Directory, Row Director, and Free Space.
Header, Table Directory, Row Director are commonly referred to as "overhead", and per Oracle documentation account up to 108 bytes per block
see Data Blocks in Oracle documentation.

The Free Space size is managed by Oracle internally, since since Oracle 9i it is the default.
When the Tablespace was created, we did not specify value for SEGMENT SPACE MANAGEMENT clause.
It can be either MANUAL or AUTO:
[SEGMENT SPACE MANAGEMENT MANUAL|AUTO]
In AUTO mode extent FREELIST management is automated, and one cannot specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters.

See this very nice page in Burlson Consulting:
Oracle tablespace change extant management local uniform to autoallocate

Lets try to estimate the PCTUSED and PCTFREE.
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ALEC', tabname => 'MY_OBJECTS' , estimate_percent => 100, method_opt => 'FOR ALL COLUMNS' );
SELECT SUM(AVG_COL_LEN)
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME ='MY_OBJECTS';

28

SELECT COUNT(*) FROM MY_OBJECTS;
6269

SELECT COUNT(*) FROM USER_EXTENTS WHERE segment_name = 'MY_OBJECTS'
5

65536 - is the size of one segment in bytes.
6269/5 = 1254 rows per segment
1254*28=35106 bytes - Size occupied by rows data.
35106/65536=54% of the block is used by rows data. The rest is PCTFREE.

Lets try to grow the table by 1685 rows:
Now table got 6 segments, all of same size, 8 blocks.

To get a better estimate, lets try to grow the table by 20 rows, until a new segment is allocated, and look at the 6 full segments:

SELECT COUNT(*)-20 FROM MY_OBJECTS;

9713

SELECT COUNT(*)-1 FROM USER_EXTENTS WHERE segment_name = 'MY_OBJECTS'

9713/6 = 1620 rows per segment

1620 *28=45360 bytes - Size occupied by rows data.
45360/65536=69% of the block is used by rows data. The rest is PCTFREE.

Lets try to grow the table by 20000 rows:
Running below SQL twice:

INSERT INTO MY_OBJECTS
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID
FROM DBA_OBJECTS
WHERE ROWNUM < 10000;

Now check extents:

SELECT segment_name, extent_id, bytes, blocks
FROM USER_EXTENTS
WHERE segment_name = 'MY_OBJECTS'
ORDER BY extent_id

SEGMENT_NAME EXTENT_ID BYTES BLOCKS

--------------- ---------- ---------- ----------

MY_OBJECTS 0 65536 8

MY_OBJECTS 1 65536 8

MY_OBJECTS 2 65536 8

MY_OBJECTS 3 65536 8

MY_OBJECTS 4 65536 8

MY_OBJECTS 5 65536 8

MY_OBJECTS 6 65536 8

MY_OBJECTS 7 65536 8

MY_OBJECTS 8 65536 8

MY_OBJECTS 9 65536 8

MY_OBJECTS 10 65536 8

MY_OBJECTS 11 65536 8

MY_OBJECTS 12 65536 8

MY_OBJECTS 13 65536 8

MY_OBJECTS 14 65536 8

MY_OBJECTS 15 65536 8

MY_OBJECTS 16 1048576 128

17 rows selected.

After 16 segments sized to 8 blocks, Oracle increases the extent size to 128 blocks.

Lets check in DBA_TABLESPACES

SELECT BYTES, BLOCKS, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT

FROM USER_SEGMENTS

WHERE TABLESPACE_NAME='MY_TABLESPACE';

BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT

---------- ---------- ---------- -------------- -----------

2097152 256 17 65536 1048576

Next Extent is sized to 1048576 bytes, which are finally what we have in USER_EXTENTS as well.

So Oracle keeps first 16 extents of same size, 8 oracle blocks, although the next_extent is 128 blocks right from the first extent.

Estimate to how many rows are needed to fill up the 128 blocks extent:

16*8=128 blocks
So this would need approx the same number of rows currently in the table:

SELECT COUNT(*) FROM MY_OBJECTS;

33068

SELECT segment_name, extent_id, bytes, blocks
FROM USER_EXTENTS
WHERE segment_name = 'MY_OBJECTS'
ORDER BY extent_id;

SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------- ---------- ---------- ----------
MY_OBJECTS 0 65536 8
MY_OBJECTS 1 65536 8
MY_OBJECTS 2 65536 8
MY_OBJECTS 3 65536 8
MY_OBJECTS 4 65536 8
MY_OBJECTS 5 65536 8
MY_OBJECTS 6 65536 8
MY_OBJECTS 7 65536 8
MY_OBJECTS 8 65536 8
MY_OBJECTS 9 65536 8
MY_OBJECTS 10 65536 8
MY_OBJECTS 11 65536 8
MY_OBJECTS 12 65536 8
MY_OBJECTS 13 65536 8
MY_OBJECTS 14 65536 8
MY_OBJECTS 15 65536 8
MY_OBJECTS 16 1048576 128
MY_OBJECTS 17 1048576 128

As expected, the next extent is 128 blocks.

Lets keep growing the table...

The next 62 extents are of same size of 128 blocks.
The 80th extent size is increased to 1024 oracle blocks:

So we have:
16 Extents, 0-15, with size of 8 oracle blocks
63 Extents, 16-78, with size of 128 oracle blocks
Extent ID 79 got a new size of 1024 oracle blocks.

SQL> SELECT segment_name, extent_id, bytes, blocks
FROM USER_EXTENTS
WHERE segment_name = 'MY_OBJECTS'
ORDER BY extent_id DESC;

SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------- ---------- ---------- ----------
MY_OBJECTS 79 8388608 1024
MY_OBJECTS 78 1048576 128
MY_OBJECTS 77 1048576 128
MY_OBJECTS 76 1048576 128
MY_OBJECTS 75 1048576 128

This is different than what is listed in USER_SEGMENTS.

The value of next_extent is always 1048576, which is not accurate.

SQL> SELECT BYTES, BLOCKS, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT FROM USER_SEGMENTS

WHERE TABLESPACE_NAME='MY_TABLESPACE';

BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT

---------- ---------- ---------- -------------- -----------

75497472 9216 80 65536 1048576

Lets increase the table, to see if extent size would continue to grow.

The number of oracle blocks in the table, without the last extent: (8*16)+(128*63) = 8192

The number of rows in the table: 1633000
Number of rows per oracle block: 1633000/8192=200
Number of rows in the 1024 blocks sized segment: 1024 * 200 = 204800
Thus lets increase the table by 205000 records at a time.

The tables grows, but the extent size remains 1024 blocks

SQL> SELECT segment_name, extent_id, bytes, blocks
FROM USER_EXTENTS
WHERE segment_name = 'MY_OBJECTS'
ORDER BY extent_id DESC;

SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------- ---------- ---------- ----------
MY_OBJECTS 187 8388608 1024
MY_OBJECTS 186 8388608 1024
MY_OBJECTS 185 8388608 1024
MY_OBJECTS 184 8388608 1024

MY_OBJECTS 183 8388608 1024

Now, lets check the space usage status:

SELECT SUM(blocks) FROM USER_EXTENTS

WHERE segment_name = 'MY_OBJECTS'

SUM(BLOCKS)

-----------

119808

SELECT BLOCKS FROM USER_SEGMENTS

WHERE TABLESPACE_NAME='MY_TABLESPACE';

BLOCKS

----------

119808

SQL> SELECT TABLESPACE_NAME, BLOCKS

FROM DBA_FREE_SPACE

WHERE TABLESPACE_NAME='MY_TABLESPACE';

TABLESPACE_NAME BLOCKS

------------------ ----------

MY_TABLESPACE 6000

SQL> SELECT TABLESPACE_NAME, BLOCKS, MAXBLOCKS, USER_BLOCKS

FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME='MY_TABLESPACE';

TABLESPACE_NAME BLOCKS MAXBLOCKS USER_BLOCKS

----------------- ---------- ---------- -----------

MY_TABLESPACE 125936 128000 125808

Few Notes:
USER_EXTENTS and USER_SEGMENTS report same number of blocks.

Values from DBA_DATA_FILES
DBA_DATA_FILES.user_blocks
Represent the value of "can be used" blocks.

The number of blocks occupied (119808) + number of free blocks (6000) match the number of user_blocks as reported by DBA_DATA_FILES: 119808+6000= 125808

DBA_DATA_FILES.blocks
Represent the OS file size in oracle blocks

ls -l /oracle_db/db1/db_igt/alec_01.dbf
-rw-r----- 1 oracle dba 1031675904 Feb 5 12:22 /oracle_db/db1/db_igt/alec_01.dbf

1031675904/8192=125936

DBA_DATA_FILES.maxblocks
Per Oracle documentation maxblocks represents "Maximum file size in blocks".
This is the value taken from "ALTER DATABASE DATAFILE... MAXSIZE <bytesM>" divided by db_block_sizebl

In this case: 128000*8192=1048576000
1048576000/1024/1024=1000 Mb - which is the value for the datafile, when the command below was run:

alter database datafile '/oracle_db/db1/db_igt/alec_01.dbf' autoextend on maxsize 1000M;

Step 8 - Delete all entries from table, and check storage parameters
Current situation:
SQL> SELECT blocks, count(*) AS Extents
FROM USER_EXTENTS
WHERE segment_name = 'MY_OBJECTS'
GROUP BY blocks
ORDER BY blocks;

BLOCKS EXTENTS

---------- ----------

8 16

128 63

1024 109

SQL> SELECT COUNT(*) FROM MY_OBJECTS;
COUNT(*)
----------
21727750

SQL> DELETE FROM MY_OBJECTS;
SQL> COMMIT;
SQL> SELECT COUNT (*) FROM MY_OBJECTS;

COUNT(*)
----------
0

SQL> SELECT blocks, count(*) AS Extents
FROM USER_EXTENTS
WHERE segment_name = 'MY_OBJECTS'
GROUP BY blocks
ORDER BY blocks; 2 3 4 5

BLOCKS EXTENTS
---------- ----------
8 16
128 63
1024 109

Obviously DELETE does not free Segments that were occupied by a table.
There is the well known option of TRUNCATE TABLE MY_OBJECTS.
Lets check the option of ALTER TABLE MOVE.
ALTER TABLE MOVE allows to move a table from one Tablespace to another, alonk the way changing storage characteristics.
Without Tablespace parameter, Oracle just rearrange Table rows in the same Tablespace.

SQL> ALTER TABLE MY_OBJECTS MOVE;

Table altered.

SELECT blocks, count(*) AS Extents

FROM USER_EXTENTS

WHERE segment_name = 'MY_OBJECTS'

GROUP BY blocks

ORDER BY blocks; 2 3 4 5

BLOCKS EXTENTS

---------- ----------

8 1

Appendix

A. SQL statement, that would return true, in case there is no more space for the extent to grow.
What would be the trigger to return true?
Option A - cannot extend by next_extend size*2
Option B - 95% percent of available space is already occupied.

SELECT LAST_EXTENT.tablespace_name,
LAST_EXTENT.segment_name,
LAST_EXTENT.partition_name,
MAX_BLOCKS.max_blocks,
MAX_BLOCKS.user_blocks,
DBA_FREE_SPACE.blocks free_blocks ,
LAST_EXTENT.blocks last_extent_blocks,
(DBA_FREE_SPACE.BLOCKS-(LAST_EXTENT.blocks*2))
two_blocks_size,
CASE
WHEN (DBA_FREE_SPACE.BLOCKS-(LAST_EXTENT.blocks*2)) > 0 THEN 'false'
ELSE 'true'
END AS current_space_sts,
CASE
WHEN (MAX_BLOCKS.max_blocks-(LAST_EXTENT.blocks*2)) > 0 THEN 'false'
ELSE 'true'
END AS max_space_sts,
ROUND(((MAX_BLOCKS.max_blocks - MAX_BLOCKS.user_blocks + DBA_FREE_SPACE.blocks)/MAX_BLOCKS.max_blocks)*100) AS free_pct FROM DBA_FREE_SPACE ,
(
SELECT USER_EXTENTS.tablespace_name,
USER_EXTENTS.segment_name,
USER_EXTENTS.partition_name,
USER_EXTENTS.bytes,
USER_EXTENTS.blocks
FROM USER_EXTENTS,
(
SELECT tablespace_name,
segment_name,
MAX(extent_id) max_extent_id
FROM USER_EXTENTS INNNER_EXTENTS
WHERE tablespace_name NOT IN ('SYSAUX', 'UNDOTBS', 'SYSTEM', 'WORKAREA')
AND extent_id > 80
GROUP BY tablespace_name, segment_name
)MAX_USER_EXTENTS
WHERE USER_EXTENTS.extent_id =
MAX_USER_EXTENTS.max_extent_id
AND USER_EXTENTS.tablespace_name =
MAX_USER_EXTENTS.tablespace_name
AND USER_EXTENTS.segment_name =
MAX_USER_EXTENTS.segment_name
)LAST_EXTENT,
(SELECT tablespace_name,
SUM(maxblocks) max_blocks,
SUM(user_blocks) user_blocks
FROM DBA_DATA_FILES
WHERE tablespace_name NOT IN ('SYSAUX', 'UNDOTBS', 'SYSTEM', 'WORKAREA')
GROUP BY tablespace_name
)MAX_BLOCKS
WHERE DBA_FREE_SPACE.tablespace_name NOT IN ('SYSAUX', 'UNDOTBS', 'SYSTEM', 'WORKAREA')
AND MAX_BLOCKS.tablespace_name =
DBA_FREE_SPACE.tablespace_name
AND LAST_EXTENT.tablespace_name =
MAX_BLOCKS.tablespace_name

Where:
current_space_sts - Does the segment NOT have space to grow without allocation new space.
max_space_sts - does the segment NOT have space to grow at all
free_pct - Free space percent availale for the segment.

B. Segment Type = ASSM
ASSM Stands for Automatic Segment Space Management.
Automatic Free Space Management is new from Oracle 9i.

Automatic Free Space Management is only available in locally managed tablespaces.

In the old way, Oracle managed freelists and freelist groups withinn segment header. This was a cause for contention on the segment header.

In the new way, Oracle is using bitmaps to describe the space usage of each block is within a segment.

The bitmap is stored in separate blocks known as bitmapped blocks (BMBS).

When Creating a Tablespace, one has to provide the SEGMENT SPACE MANAGEMENT clause

CREATE TABLESPACE mytbs1
DATAFILE '/u01/oracle/data/mytbs01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

DBA_TABLESPACES.segment_space_management and USER_TABLESPACES.segment_space_management column
indicates the type of segment management used within each tablespace, AUTO or MANUAL.

AUTO stands for the new way: SEGMENT SPACE MANAGEMENT AUTO;
MANUAL stands for the old way: Freelists.

USER_SEGMENTS.segment_subtype value would be ASSM.

C. Allocation Type - UNIFORM vs SYSTEM
When creating Tablespace, one has the option to specify extent allocation type:
AUTOALLOCATE:
CREATE TABLESPACE MY_TBS
DATAFILE '/oracle/some/path/autoallocate_tbs_01.dbf'
SIZE 100M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

In this case Oracle manages Extent size, and they grow as in the example here.
In short:(with block size=8192 Kb)
First 16 extents: 8 blocks (64Kb)
Next 64 extents: 128 blocks(1Mb)
Next extents:1024 blocks(8Mb)

SELECT ALLOCATION_TYPE FROM DBA_TABLESPACES
ALLOCATION_TYPE
---------------
SYSTEM

UNIFORM:
CREATE TABLESPACE MY_TBS
DATAFILE '/oracle/some/path/uniform_tbs_01.dbf'
SIZE 100M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

In this case Oracle all Extents have same size;

INITIAL_EXTENT=10485760 (1280 blocks)

MIN_EXTLEN=10485760 (1280 blocks)

SELECT ALLOCATION_TYPE FROM DBA_TABLESPACES
ALLOCATION_TYPE
---------------
UNIFORM

D. Drop Tablespace

DROP TABLESPACE MY_TBS
INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

E. Strange data in DBA_DATA_FILES after datafile resize.
Consider following scenario:
There is a Datafile with size limited to 12000M

SQL> SELECT bytes/1024/1024 bytes_Mb,
user_bytes/1024/1024 user_bytes_Mb,
maxbytes/1024/1024 maxbytes_Mb
FROM DBA_DATA_FILES
WHERE tablespace_name = 'MY_TBS';

BYTES_MB USER_BYTES_MB MAXBYTES_MB
---------- ------------- -----------
11930 11900 12000

Now, extend the Datafile size to 20000M

ALTER DATABASE DATAFILE '/path/to/data/file/name' RESIZE 2000M;

F. useful URLs:
Burleson - Oracle tablespace change extant management local uniform to autoallocate
Altering Tables

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Tuesday, February 11, 2014

Create Tablespace and Space Managment by example.

No comments:

Post a Comment