Tuesday, November 27, 2012

1 TABLESPACE(4): Tablespace operations

Tablespace is the logical allocation unit to manage the actual database file on the file system. It is equivalent of the file group in Microsoft SQL Server.

Tablespace –> Segment –> Extent –> Data block.

  • Tablespace is the logical container of segments. Also provide a unit of the backup.
  • Segment is the logical grouping of the extents. Oracle objects is create on the segment. One table is one segment.( For partition, each partition is one segment). One index is one segment. Segment can not cross tablespaces. One segment must only exist in one tablespace.
  • Extents is the continuously data blocks. Segment can contain one or more extents. Extents is the smallest oracle allocation unit.
  • Data block is the smallest space unit. Multiple blocks form a Extents.

Create Tablespace

  • Autoextend: When the space has been used up, it will automatically grow the space.
  • Next: How much it would grow.
  • MAXSIZE: the maxim size of the tablespace.
create tablespace mytbs1 datafile size 10M autoextend on next 10M MAXSIZE 100M;
 
select  TABLESPACE_NAME,BYTES,AUTOEXTENSIBLE,INCREMENT_BY, MAXBLOCKS
from DBA_DATA_FILES Where TABLESPACE_NAME='MYTBS1';
show parameter block_size

image

In the example above, I want to increment grow by 10M, which is 1280 blocks * 8192 bytes . The maxim size is 12800 blocks which is 100 MB.

Create tablespace block size other than default block size

From Oracle 9i, we can create the tablespace with block size other then default block size.

show parameter db_block_size
create tablespace mytbs1_16K datafile size 10M autoextend on next 10M MAXSIZE 100M blocksize 16K;
select tablespace_name,block_size from dba_tablespaces;

image

Shrink Tablespace

Permanent tablespace can not be shrink.

image

Read Only Tablespace

Below example, we create the table on the tablespace and change it to the read only. Since it is read only, the drop column and add column with default value would fail. Because it change the table data itself. However, the add column without default and drop the table would work because this operation only update the data dictionary.

--- Set to read only
create table system.po1 tablespace MYTBS1 AS SELECT * from dba_objects;
alter tablespace mytbs1 read only;
 
-- can not update but table can be drop
col tablespace_name format a21
SELECT
  DT.TABLESPACE_NAME,
  DT.STATUS,
  DT.contents,
  DT.BLOCK_SIZE,
  DT.bigfile,
  DT.EXTENT_MANAGEMENT,
  DT.ALLOCATION_TYPE
FROM
  DBA_TABLESPACES DT
Where DT.TABLESPACE_NAME='MYTBS1';
 
-- Drop column and add column with default fail because it will change the data on the table
alter table system.po1 drop column status;
alter table system.po1 add ( testcol1 varchar2(10) default 'test');
-- add column without default works because it only change the Data dictionary not the table data itself
alter table system.po1 add ( testcol1 varchar2(10) );
-- Drop table works because it only change the Data dictionary not the table data itself
drop table system.po1;

image

Read Write tablespace

by default, all tablespaces are read write.

alter tablespace mytbs1 read write;

image

Offline Tablespace

Below example, we create the table on the tablespace and change it to the offline. Since it is offline, the drop column and add column with default value would fail. Because it change the table data itself. However, the add column without default and drop the table would work because this operation only update the data dictionary.

Offline tablespace has 4 different modes:

  • Offline normal: default mode. DBWR would write the dirty buffer and run check point. If for some reason checkpoint fail. the offline would fail.
  • Offline temporary: DBWR would write the dirty buffer and run check point.  If the check point fail, then skip the checkpoint. When online the tablespace, we need to do the recovery first.
  • Offline immediate: does not check point
  • Offline recovery: Same as immediate. for backward compatible.
create table system.po1 tablespace MYTBS1 AS SELECT * from dba_objects;
alter tablespace mytbs1 offline;
 
-- can not update but table can be drop
col tablespace_name format a21
SELECT
  DT.TABLESPACE_NAME,
  DT.STATUS,
  DT.contents,
  DT.BLOCK_SIZE,
  DT.bigfile,
  DT.EXTENT_MANAGEMENT,
  DT.ALLOCATION_TYPE
FROM
  DBA_TABLESPACES DT
Where DT.TABLESPACE_NAME='MYTBS1';
 
-- Drop column and add column with default fail because it will change the data on the table
alter table system.po1 drop column status;
alter table system.po1 add ( testcol1 varchar2(10) default 'test');
-- add column without default works because it only change the Data dictionary not the table data itself
alter table system.po1 add ( testcol1 varchar2(10) );
-- Drop table works because it only change the Data dictionary not the table data itself
drop table system.po1;

image

Online Tablespace

alter tablespace mytbs1 online;

image

Rename Tablespace

  • SYSTEM,SYSAUX can not be changed.
  • Tablespace must be health and online before we rename it.
Alter tablespace mytbs1 rename to mynewtbs1;
select tablespace_name from dba_tablespaces;

image

Drop Tablespace

If tablespace is not empty, we have to use “INCLUDING CONTENTS” option to drop. Otherwise we would get ORA-01549 error.

If tablespace is managed by OMF, simply drop tablespace would drop the tablespace in the data dictionary and the files on the OS path. ( With including contents if there are objects on the tablespace)

If tablespace is not managed by OMF, we have to use “INCLUDING CONTENTS AND DATAFILES”, this would drop the tablespace and drop the datafile as well.

image

drop tablespace mynewtbs1;

image

 

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28318/logical.htm

1 comments:

  1. If you are interested in earning money from your websites or blogs by popunder ads, you should embed one of the most reputable networks - Propeller Ads.

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates