oracle_tablespace


Jennie Learns

 

TABLESPACE AND DATAFILES

Jennie : Tell me something about DATA BLOCKS?

Dad : Remember the following points :

Point 1 : Eventhough physically data is stored in  DATAFILES, logically the data is kept  like  this  ::: Tablespace>>Segments>extents>blocks

Point 2 : Block size of any database can be viewed as shown here:

 

 

 

 

Point 3 : Data Block is the smallest unit of storage.

Point 4 : A block has block header, freespace,dataspace.

Point 5 : Transaction slots are present in block header. Whenever there is a dml action, transaction  entries are made in the transaction slots.

Point 6 : By default ,Maximum 255 transaction slots are possible in a block header. That means, maximum 255 concurrent transactions can be made in a block.  But if you are reducing the MAXTRANS value, then you will get more space for data inside the data block.

Point 7 : By default, 10% of space is kept free inside each Block , for updating. This percentage can be increased by increasing PCTFREE.

Point 8 : Default value for PCTUSED is 40%. If the space used falls below 40%, the block is kept under FREELIST.

 

Point 7. INITRANS : It is the space reserved in the block header initially.  Whenever there is a dml command, the transaction id is kept in block header in a space called as ITL(Initial Transaction Slot). By default INITRANS is 1.  Normally there is no need to increase this value. But imagine this situation : There is only one ITL, it is already occupied. If a new DML is coming? If the data block has space, then another ITL will be dynamically created (upto a maximum of MAXTRANS value). But if there is no space? The dml  action has to wait until any of the existing transaction is committed. Each ITL needs 23 Bytes. MAXTRANS is deprecated in 10g.

 

Point 9 : Oracle block size is different from OS block size. The OS block size can be viewed like this:

 

 

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

How to create a tablespace?

CREATE TABLESPACE SALES

DATAFILE 'C:/ORCLE/DATABSE/SALES.DBF' SIZE 32M,

DATAFILE 'C:/ORCLE/DATABSE/SALES1.DBF'   SIZE 50M,

DATAFILE 'C:/ORCLE/DATABSE/SALES2.DBF'   SIZE 32M

AUTOEXTEND ON

NEXT 32M MAXSIZE 2048M

EXTENT MANAGEMENT LOCAL;

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

CREATE TABLESPACE SALES

DATAFILE 'C:/ORCLE/DATABSE/SALES.DBF'

SIZE 32M

AUTOEXTEND ON

NEXT 32M MAXSIZE 2048M

EXTENT MANAGEMENT LOCAL

UNIFORM SIZE 64K;

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

How to add a datafile to an existing tablespace?

SQL> ALTER TABLESPACE ts1 ADD DATAFILE 'c:ts1.dbf' SIZE 10M;

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

 How to view the details about a datafile?

 SQL> SELECT * FROM dba_data_files WHERE tablespace_name = 'ts1'

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

How to take a tablespace; into offline?

SQL> ALTER TABLESPACE users OFFLINE;

 

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

How to drop a tablespace?

SQL> DROP TABLESPACE users INCLUDING CONTENTS CASCADE CONSTRAINTS;

But you have to manually delete datafiles using  host erase ....  command, if the datafiles are not omf.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If extent management clause is not specified, whether the tablespace is locally managed or dictionary managed?

From 9i, it is locally managed (for non-system and permanent tablespace)

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

What is the meaning of LOCALLY MANAGED tablespace?

Point 1 : The details of extents issued are kept LOCALLY in the datafile header itself , not in the tables of datadictionary.

Point 2 : The details of extents issued are stored in the form of bitmaps.

Point 3: When using LOCALLY MANAGED, you can use  AUTOALLOCATE(default) or UNIFORM SIZE  clause 

Point 4: If you use AUTOALLOCATE, then the extent size is decided by the tablespace itself.

Point 5: If you specify UNIFORM SIZE, but if you dont specify the size (say 64K), then 1M is the default.

Point 6:  If you specify AUTOALLOCATE, a minimum of 64k is first allocated.

Point 7: If you specify  UNIFOMR SIZE 256K, then   each bit in the bitmap represents 256k.

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

 How to create TEMPORARY TABLESPACE

 

CREATE  TEMPORARY TABLESPACE TEMPSALES

TEMPFILE 'C:/ORCLE/DATABSE/SALES.DBF'

SIZE 32M

EXTENT MANGEMENT LOCAL

UNIFORM SIZE 32K;

Point 1: AUTOALLOCATE is not allowed in temporary tablespace.

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

ALTER TABLESPACE TEMPSALES

TEMPFILE 'C:/ORCLE/DATABSE/SALES1.DBF'

SIZE 32M;

Point 1: you should not use the word TEMPORARY in alter commands.

 

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

Point 2: if you create temporary tablespace, dictionary managed, then use this method:
 
CREATE TABLESPACE SALES
 DATAFILE 'c:/oracle/sales.dbf' SIZE 50M
 DEFAULT STORAGE (
 INITIAL 2M
 NEXT 2M
 MINEXTENTS 1
 PCTINCREASE 0)
 EXTENT MANAGEMENT DICTIONARY
 TEMPORARY;

 

 

ALTER DATABASE TEMPFILE 'c:/temp/temp1.dbf' RESIZE 4M;

--------------------------------------------------------------------------
ALTER DATABASE TEMPFILE 'c:/temp/temp1.dbf'  DROP
     INCLUDING DATAFILES;

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

 

What is OMF?

Point 1 : OMF stands for Oracle managed files.

Point 2 : In case of OMF, you NEED NOT mention datafile name. Oracle itself will create datafiles with a certain name.

Point 3: When you drop a tablespace, datafiles also dropped if OMF is used.

 

But, in which directory, it will create these datafiles?

See this screen capture.Initially there is no folder name as per the init.ora file. Aferwards, I have created a folder. This folder will be used by oracle to save the datafiles.

 Jennie : Tell something about  UNDO?

Point 1: It is for  rollback,for recover data, For read consistency

Point 2 : Oldname for undo logs is  rollback segments

Point 3 : To prevent 'SNAPSHOT TOO OLD' errors, UNDO_RETENTION parameter to be increased. Default value is 5 minutes.

Jennie : How to create UNDO tablespace?

Point 1 : UNDO tablespace can be created automatically when the database is created as shown here:

   CREATE DATABSE ctms

  .........

  UNDO TABLESPACE UNDOTBS1

  DATAFILE 'c:\oracle\oradata\ts\undo1.dbf'

  SIZE 100M  REUSE AUTOEXTEND ON;

Point 2: Otherwise, you can create like this :

  CREATE UNDO TABLESPACE UNDOTBS1

  DATAFILE 'c:\oracle\oradata\ts\undo1.dbf'

  SIZE 100M  REUSE AUTOEXTEND ON;

Point 3: You can resize the undo tablespace:

ALTER DATABASE DATAFILE 'c:\Oracle\Ordata\ts\undo1.dbf'   RESIZE 10M;

 Point 4: An unused undo tablespace can be dropped 

SQL > DROP TABLESPACE UNDOTBS1;

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

Jennie : How to calculate the undo tablspace being used by my  database?