Oracle Tablespace

How the data is stored in the Oracle Database?

Oracle stores data logically in tablespaces and physically in datafiles. Tablespace is a logical storage container used to store database objects such as tables, indexes, and large objects (LOBs). It is a logical storage unit that organizes a database's data into one or more datafiles.

DBA can use tablespaces (Use Multiple Tablespaces) to do the following:

control disk space allocation for database data
assign specific space quotas for database users
perform partial database backup or recovery operations (Back up individual tablespaces)
Store the data files of different tablespaces on different disk drives to reduce I/O contention.
Take individual tablespaces offline while others remain online, providing better overall availability

Types of tablespaces

  • type of data
  • size of data

Type of data

  1. permanent tablespace (to store object)
  2. undo tablespace (old data to maintain)
  3. temporary tablespace (sorting & joining operations)

Size of data

Small File – Per tablespace 1022 data files

The block size is 8 KB, the maximum size of a data file would be 32 GB (4 GB × 8 KB).
The block size is 32 KB, the maximum size of a data file would be 128 GB (4 GB × 32 KB).

Big File – Per Tablespace One data file

A Big file tablespace with 8K blocks can contain a 32-terabyte data file.
A big file tablespace with 32K blocks can contain a 128-terabyte data file.

The maximum number of data files in an Oracle Database is limited to 64K files.

Default tablespace:

  1. SYSTEM
  2. SYSAUX
  3. TEMP
  4. UNDOTBS1
  5. USERS

System:

It is default tablespace. Oracle manages the information automatically. It contains data dictionary like tables' definitions, views, and stored procedures. Can't access all users. Can't rename and drop the system tablespace. Not recommended store the user data.

Sysaux: (system auxiliary)

It is an auxiliary tablespace that has become mandatory since Oracle 10g. It contains some indexes and non-sys-related tables that earlier belonged to the SYSTEM tablespace. SYSAUX takes those elements to reduce the load on the SYSTEM tablespace.

Temp:

It containing temporary data and indexes. Temporary tablespaces are necessary for work with large tables and clauses like DISTINCT, GROUP BY, and ORDER BY. (Sort & join operations)

Undotbs1:

It containing the undo data for automatic undo management. (rollback)

Users:

It containing the user data.

Online and offline the tablespace:

Online: Default mode. tablespace is up and running. Oracle database can read and write from these tablespaces.

Offline: tablespace is offline. oracle database can't read and write from these tablespaces.

Why need offline:

  • Particular tablespace backup.
  • Recover an tablespace or data file.
  • Move a data file without closing the database.

Create tablespace:

create tablespace tbs1 datafile '/u01/data/data01.dbf' size 50m;
create tablespace tbs1 datafile '/u01/data/data01.dbf' size 50m autoextend on;
create tablespace tbs1 datafile '/u01/data/data01.dbf' size 50m autoextend on next 1m maxsize 500m;

Add a datafile:

Alter tablespace tbs1 add datafile '/u01/data/data02.dbf' size 50m autoextend on next 1m maxsize 500m;

Alter operations:

alter tablespace tbs1 add datafile '/u01/data/data02.dbf'  autoextend on;
alter tablespace tbs1 add datafile '/u01/data/data02.dbf' autoextend on maxsize 1g;
alter tablespace tbs1 add datafile '/u01/data/data02.dbf'  autoextend off;

Resize the datafile:

alter database datafile '/u01/data/data02.dbf' resize 100m;

List out the tablespace names and datafiles:

select tablespace_name, file_name, bytes/1024/1024 as bytes_in_MB from dba_data_files;
select tablespace_name from dba_tablespaces;

Find tablespace free space:

select tablespace_name,bytes/1024/1024 from dba_free_space where tablespace_name='tbs1';

Rename the tablespace:

alter tablespace tbs1 rename to tbs2;

How to remove datafile in tablespace:

alter tablespace tbs2 drop datafile '/u01/data/data02.dbf';

How do drop the tablespace:

drop tablespace tbs2;
drop tablespace tbs2 including contents and datafiles;

Offline and online the tablespace:

alter tablespace tbs2 offline;
alter tablespace tbs2 online;

How to Rename/Move the datafile in system and sysaux – downtime needed:

Down the Database:

shut immediate
mv '/u01/data/data02.dbf' '/u02/data/data03.dbf';

Start the Database with mount stage:

startup mount
alter database rename file '/u01/data/data02.dbf' to '/u02/data/data03.dbf';
alter database open;

How to Rename/Move the datafile in users(use tablespace offline) – no need downtime

alter tablespace tbs2 offline:

mv '/u01/data/data02.dbf' '/u02/data/data03.dbf';
alter database rename file '/u01/data/data02.dbf' to '/u02/data/data03.dbf';
alter tablespace tbs2 online;

12-c Feature:

alter database move datafile '/u01/data/data02.dbf' to '/u02/data/data03.dbf';
alter database move datafile '/u01/data/data02.dbf' to '/u02/data/data03.dbf' keep;

Offline and online the datafile:

alter database datafile '/u01/data/data02.dbf' offline;
alter database datafile '/u01/data/data02.dbf' online;
select name,status from v$datafile where file#=5;

How to Rename/Move the datafile (use datafile offline) – no need the downtime

alter database datafile '/u01/data/data02.dbf' offline;
mv '/u01/data/data02.dbf' '/u02/data/data03.dbf';
alter database rename file '/u01/data/data02.dbf' to '/u02/data/data03.dbf';
recover datafile '/u02/data/data03.dbf';
select file_id,file_name from dba_data_files;
alter database datafile 5 online;
alter database datafile '/u01/data/data02.dbf' online;

How to enable the archivelog:

archive log list
shut immediate
startup mount
alter database archivelog;
alter database open;

How to get the meta data in oracle objects:

select dbms_metadata.get_ddl('TABLE','EMP') from dual;
set long 20000
select dbms_metadata.get_ddl('TABLESPACE','TBS2') from dual;

How to set default tablespace: database level

select * from database_properties;
alter database default tablespace tbs1;

How to set default tablespace: table level

create table emp(id number,name varchar2(20)) tablespace tbs1;
alter table salary move tablespace tbs1;
select table_name,tablespace_name from user_tables;

How to set default tablespace: user level

create user kumar identified by kumar123 default tablespace tbs1;
alter user raji default tablespace tbs1;
select username,default_tablespace from dba_users where username='kumar';
alter user kumar quota 500m on tbs1;
alter user raji quota unlimited on tbs1;
select username,tablespace_name,bytes/1024/1024,max_bytes from dba_ts_quotas;

Some other operations for the tablespace:

alter tablespace tbs1 read only;
alter tablespace tbs1 read write;
alter tablespace tbs1 begin backup; // select * from v$backup; -> active
alter tablespace tbs1 end backup; // select * from v$backup; -> not active

How to create & manage the bigfile tablespace:

drop tablespace bigtbs1 including contents and datafiles;
select * from database_properties;
alter  database set default smallfile tablespace;
alter database set  default bigfile tablespace;

Tablespaces with different block size: default size is 8KB

Why need a bigger block sizes:
  • Most indexed
  • Large tables
  • Table with large objects(BLOB,CLOB)
  • Tables with large rows
  • Temporary tablespace for sorting
show parameter db_block_size;
create tablespace tbs3 datafile '/u01/prod/data/data03.dbf' size 10m blocksize 16k;
alter system set db_16k_cache_size=60m scope=both;
create tablespace tbs3 datafile '/u01/prod/data/data03.dbf' size 10m blocksize 16k;

Temporary tablespace management:

desc dba_temp_files;
select tablespace_name, file_name,bytes/1024/1024, status from dba_temp_files;
alter database tempfile '/uo1/prod/data/temp01.dbf' resize 30m;
select tablespace_name, file_name,bytes/1024/1024, status from dba_temp_files;
create temporary tablespace temp1 tempfile '/u01/prod/data/temp02.dbf' size 10m;
select tablespace_name, file_name,bytes/1024/1024, status from dba_temp_files;

Find default temporary tablespace:

select * from database_properties where property_name like '%TABLESPACE%';
alter database default temporary tablespace temp1;
alter database tempfile '/u01/prod/data/temp02.dbf' autoextend on;
select file_name,autoextensible from dba_temp_files;
alter tablespace temp1 add tempfile '/u01/prod/data/temp03.dbf' size 200m;
alter database tempfile '/u01/prod/data/temp02.dbf' resize 200m;
select file_id,tablespace_name,bytes from dba_temp_files;
alter tablespace temp1 offline;
alter tablespace temp1 online;
select * from database_properties where property_name like '%TABLESPACE%';

How to shrink(compress) the temp:

alter tablespace temp1 shrink space;
alter tablespace temp1 shrink tempfile '/u01/prod/data/temp02.dbf' keep 5m;

Create a temporary tablespace group:

  • Temporary tablespace groups allow an oracle database to write to multiple temp tablespaces simultaneously.
  • A user is assigned to a group of temp tablespaces instead of a single temporary tablespace.
  • It allows a single SQL operation to use multiple temporary tablespace for sorting.

select * from dba_tablespace_groups;
create temporary tablespace temp1 tempfile '/u01/prod/data/temp01.dbf' size 50m tablespace group tempgroup1;
select * from dba_tablespace_groups;
select tablespace_name, file_name,bytes/1024/1024, status from dba_temp_files;

Add existing tablespace to group:

alter tablespace temp1 tablespace group to tempgroup1;
select * from dba_tablespace_groups;

Assign the group to default temporary tablespace:

select * from database_properties where property_name like '%TABLESPACE%';
alter database default temporary tablespace tempgroup1;

Comments

Post a Comment