LMT TABLESPACE

The LMT tablespace is implemented by adding EXTENT MANAGEMENT LOCAL clause to 
the tablespace definition.LMT tablespaces automate extent management and remove
the ability to specify the NEXT storage parameter. 

The only exception is when NEXT is used with MINEXTENTS at table creation time.
LMT means Locally Management  system,LMT tablespace size is uniform.
By default uniform size is 1,By default lmt tablespace is Autoallocate.

Auto allocate

SQL> create tablespace india datafile '/u01/app/oracle/oradata/TESTDB/india01.dbf' 
size 50m;

Tablespace created.

Uniform Size

SQL> create tablespace USA datafile '/u01/app/oracle/oradata/TESTDB/USA01.dbf' 
size 50m  extent management local uniform size 512k;

Tablespace created.

Deferred segment creation

SQL> create table india (reg_id number,reg_name varchar2(200));

Table created.

SQL>  select count(*) from dba_segments where segment_name='INDIA';

  COUNT(*)
----------
        0
SQL> select count(*) from dba_extents where segment_name='INDIA';

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

SQL> insert into india values (5,'tamil');

1 row created.

SQL>  select count(*) from dba_segments where segment_name='INDIA';

  COUNT(*)
----------
         1

SQL>  select count(*) from dba_extents where segment_name='INDIA';

  COUNT(*)
----------
         1

SQL>  create table italy (reg_no number,reg_name varchar2(2000)) 
segment creation immediate;

Table created.


SQL> show parameter deferred_segment_creation;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

SQL> alter system set deferred_segment_creation=false;

System altered.

SQL>  show parameter deferred_segment_creation;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     FALSE

Extents

Continuous blocks it’s called extents,Data Stored in blocks,blocks Stored in extents.
By default Extents Size 64k.

TYPES OF EXTENTS

Initial          - Initial is First No of transaction by default 100.

Next             - Next no of transaction is 200.
  
Minextents       - minimum number of transactions by default 1.

Maxextents       - maximum number of transactions by default Unlimited.

Pct increase     - pct increase by default 100.

BLOCKS

Smallest storage unit it’s called Blocks.After database creation 
we can specify block size but before database creation we can’t change block size.
block size is 2k,4k,8k,16k,32k. By default Block size is 8k.

Check Block Size in Database

SQL> show parameter db_block_size;

NAME                     TYPE        VALUE
--------------------- ----------- --------------
db_block_size           integer     8192

SQL> select block_size,tablespace_name from dba_tablespaces;

BLOCK_SIZE            TABLESPACE_NAME
----------        ------------------------------
      8192 	        SYSTEM
      8192 		SYSAUX
      8192 		UNDOTBS1
      8192 		TEMP
      8192 		USERS
      8192 		TEST
      8192 		INDIA

create New Tablespace

SQL>  create tablespace spain datafile'/u01/app/oracle/oradata/TESTDB/spain01,dbf'
size 100m blocksize 16k;

Tablespace created.

SQL>  select block_size,tablespace_name from dba_tablespaces;

BLOCK_SIZE        TABLESPACE_NAME
----------      ---------------------
      8192            SYSTEM
      8192            SYSAUX
      8192            UNDOTBS1
      8192  	      TEMP
      8192 	      USERS
      8192 	      TEST
      8192 	      INDIA
     16384 	      SPAIN

Block Utilization Parameters

INITRANS       

MAXTRANS    

PCTUSED        

PCTFREE   

Initans is the initial number of transactions by default 1. 
Maxtrans is Maximum number of Transactions default 255.Inserted data Stored in 
pct used by default allocate space is 89%, in case pct used is full Remaining 
data goes to Next pct used. Update happens in  pct free by default allocate space 10%,
pct used and pct free information stored by block header by default allocated space 1%.

Block-level problems

Row chaining

Row migration

Row chaining

Inserted data stored in pct used,that pct used is full remaining data goes to next pct 
used it is called Row chaining.Row chaining occurs when a row can't physically fit 
into an Oracle block. 

Another block is required to store the remainder of the row.Chaining can cause serious 
performance problems and is especially prevalent with those storing multimedia data or 
large binary objects (blobs). 

You should pay special attention to the DB_BLOCK_SIZE parameter 
when you create your database.Block sizes of 4 kilobytes or more are the norm, 
not the exception. 

Row migration

Date updates happen for  pct free ,that updated data stored in pct used.pct used is 
full that time remaining data goes to the next pct used that is called row Migration.
Migration of an Oracle row occurs when a row is updated in an Oracle block and the 
amount of free space in the block is not adequate to store all of the row's data. 
The row is migrated to another physical block in the table. 
The problem is that the indexes that refer to the migrated row are still pointing 
to the block where the row used to be, and hence the table reads are doubled. 
Note however that full table scans will scan blocks as they come and will perform the 
same number of reads whether the rows are migrated or not. 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8