MANAGE TABLESPACE AND DATAFILES

1. tablespace,segment,extent,block

    tablespace: database logical is divided into more tablesapces.

    segment:   a special storage structure(table,index),span more datafiles

    extents:allocate unit

    block: use unit,write/read unit

              db_block_size, default block size. standand block size,system tablespace use.

              db_nk_block_size,not standand block size, other tablespace

              Any given tablespace will have a consistent block size

              any given segment(which is contained in a tablespace) will consist of blocks of exactly the        same size

              head,table directory,row directory,free,data

 2.Tablespace Mangement

     LocaL:  use bit map

     Dictionary: rescusive sql, result in update,insert sql to dictionary

   

 3. create tablespace myts1

     datafile '/u01/oradata/lsh/myts1.dbf' size  100m

     extent management local  uniform size 10k;

    

     create tablespace myts2

     datafile '/u01/oradata/lsh/myts2.dbf' size 100m

     extent management local  autoallocate;

   

     create tablespace myts3

     datafile '/u01/oradata/lsh/myts3.dbf' size 100m

     extent management dictionary

     default storage(

     initial 10k

     next 10k

     pctincrease 10%

     maxextent 100m

     )

     

     alter tablespace myts3 minimum extent 10k;

     alter tablespace myts3

     default storage(

     initial 10k

     next 10k

     );

 

 

     drop tablespace myts1 including contents and datafiles;

   

     resize tablespace

     alter database datafile '/u01/oradata/lsh/myts1.dbf' autoextend on next 10m maxsize 100m;

     alter database datafile '/u01/oradata/lsh/myts1.dbf' resize 100m;

     alter tablespace myts1 add datafile '/u01/oradata/lsh/myts1_1.dbf'  size 100m;

 

     rename datafile

     alter tablespace myts1 offline;

     !cp dest one to one dest

     alter tablespace myts1 rename datafile '/u01/oradata/lsh/myts1.dbf' to  '/u01/oradata/lsh/myts2.dbf ';

          

      shutdown immediate

      !cp one dest to one dest

      start mount;

      alter database rename file '' to '';

  

4 status of tablespace

     online/offline     control access data

     read only    drop objects   

 

5.System , undo ,temporary tablespace

   System tablespace: created by create database or dbca

                                   store data dictionary and system rollback segments

                                   can not be offline

                                   can not be delete

                                   in oracle 9i,using dbca create database ,system tablespace is local,other tablespace must be  local.

                                   if system is local ,defualt temporary tablespace must define.

                                  

    undo tablespace: store undo segment.

                                 local management

                                current use undo tablespace can not be delete

                                current use undo tablesapce can not be offline

    create undo tablespace myundo datafile '/u01/oradata/lsh/myundo.dbf' size 100m;

 

    temporary tablespace: store temporary segment

                                        default temporary tablespace can not be delete ,offline

    create temporary tablespace mytemp tempfile '/u01/ordata/lsh/mytemp.dbf' size 100m;

    alter database default temporary tablespace mytemp;