Tablespace management:
>Table space consists of one or more data files.
>Data files belongs to only one table space.
>Oracle stores the data logically in table space and physically in data files.
>The database is divided into logical stored units called table space, which can be used to group related logical structure to gather.
>You can also create big file table space, which is a table space with a single data file but very large (up to 8000TB).
>Small file table spaces are default, which contains multiple data files but files can not be as large as a big file table space.
>You can place the tables and indexes associated with an application into a set of table space in order to manage that data more easily.
>Another common use for table space is performance related you can physically separate table from indexes on to separate disk drives with all using logical volume manager.
>Big file table space are intended to very large database (if we are going with small files, oracle has thousands of read and write data files, operations that must update the all data files header such as ckpt can take long time which performance degrades.
> One small file table space can contain 1024 data file.
>System and sysaux table space always created in small file table space.
>You cannot make system table space offline, by default it is online.
>We cannot make undo table space from read only.
>We cannot drop tablespace which is have active segment.
>We cannot decrease the size of the datafile beyond the used space.
>A tablespace is containing one or more datafiles ,where as one datafile is again divided into some set of blocks.
>BLOCK: It is smallest space allocation in oracle(generally reads/writ to disk).
>The block size from database is 2^nk. Where n is rang from(n=1,2,3,4,5) that is 2k,4k,8k,16k,32k.
>The default block size 2k, from 10g onwards the default block size is 8k.
> Once you create a tablespace we can also alter the tablespace
1)Add one or more data file
2)Resize the datafiles.
3)Drop the datafiles.
4)Make tablespace online(default)/ offline.
5)Make tablespace read only/read write (default).
6)We can also the rename the datafiles.
7)We can also the rename the tablespace from 10g onwards.
8)We can drop the tablespace also.
LMTS extents information:
AUTO EXTEND ON:
>Table space consists of one or more data files.
>Data files belongs to only one table space.
>Oracle stores the data logically in table space and physically in data files.
>The database is divided into logical stored units called table space, which can be used to group related logical structure to gather.
>You can also create big file table space, which is a table space with a single data file but very large (up to 8000TB).
>Small file table spaces are default, which contains multiple data files but files can not be as large as a big file table space.
>You can place the tables and indexes associated with an application into a set of table space in order to manage that data more easily.
>Another common use for table space is performance related you can physically separate table from indexes on to separate disk drives with all using logical volume manager.
>Big file table space are intended to very large database (if we are going with small files, oracle has thousands of read and write data files, operations that must update the all data files header such as ckpt can take long time which performance degrades.
> One small file table space can contain 1024 data file.
>System and sysaux table space always created in small file table space.
>You cannot make system table space offline, by default it is online.
>We cannot make undo table space from read only.
>We cannot drop tablespace which is have active segment.
>We cannot decrease the size of the datafile beyond the used space.
>A tablespace is containing one or more datafiles ,where as one datafile is again divided into some set of blocks.
>BLOCK: It is smallest space allocation in oracle(generally reads/writ to disk).
>The block size from database is 2^nk. Where n is rang from(n=1,2,3,4,5) that is 2k,4k,8k,16k,32k.
>The default block size 2k, from 10g onwards the default block size is 8k.
> Once you create a tablespace we can also alter the tablespace
1)Add one or more data file
2)Resize the datafiles.
3)Drop the datafiles.
4)Make tablespace online(default)/ offline.
5)Make tablespace read only/read write (default).
6)We can also the rename the datafiles.
7)We can also the rename the tablespace from 10g onwards.
8)We can drop the tablespace also.
>We can chose the extent management
I. You
can use the tablespace with either local extent management or dictionary extent management.
II. Up
to oracle 8,it support only DMTS.
III. But
from oracle 8i we have DMTS(default) and LMTS(optional).
IV. From
oracle 9i we have DMTS(optional) and LMTS(dfault).
DMTS extents information:
Free and used extents
information will be stored in data dictionary
LMTS extents information:
Database tracks extents
through the use of bitmap
AUTO EXTEND ON:
While tells the database
to automatically enlarge a data file which the tablespace runs of free space. This will apply to individual data file not to the tablespace.
I.
Alter
database datafile < file _id> autoextend on;
II.
Move
it to target location by using os level command.
III.
Rename
it to new name at database level.
IV.
Sync
the datafile header with the rest of level.
V.
Bring
the tablespace to online
Once we create a brand new table
space by default
I.
Online
II.
Read/Write
III.
Logging=yes
(LWGR write data into redo log files)
IV.
Plugged
in=No(it will same data base)
V.
Perment
objects
VI.
Locally
managed table space
VII. Small file table space
VII. Small file table space
No comments:
Post a Comment