Tablespaces

     Tablespace 

      A tablespace is a logical storage unit in an oracle database (its not a physical file), But tablespace inturn consists of atleast one datafile from the database. and that datafile is physicaly located in the file system on the database server , 

Our actual data's (Table,Index) will be segregated on this datafile . 

The above details are very basic understanding of an oracle tablespace and datafile. But i am not intended to write about the datafiles/tablespaces here . I am trying to have some space given here help me/others when we perform some task on the datafile and tablespaces . 

 At first , There are three types of tablespaces in oracle 

• Permanent Tablespaces
• Undo Tablespaces
• Temporary Tablespaces

Tasks that may involve to deal with permenant tablespaces were written below , if i miss anything here pls post back to add them, bcz i am trying to have some site to guide all the tasks that would help others / me . 

At First to check the tablespace usage :

I felt the below query helps to figure out what tablespaces that may require our attention to add more space . 

set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from  dba_data_files group by tablespace_name union all
 select  tablespace_name || '  **TEMP**'
 , sum(bytes)/1024/1024 used_mb
 from  dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
 from  dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4

/

Lets say if we need to see what are the files associated with each tablespace 

Query the below one ,

set lines 100
col file_name format a70
select file_name
,      ceil(bytes / 1024 / 1024) "size MB"
from   dba_data_files
where  tablespace_name like '&TSNAME'

/

Tablespaces that are >=80% full, and how much to add to make them 80% again

set pages 999 lines 100
col "Tablespace" for a50
col "Size MB"  for 999999999
col "%Used"  for 999
col "Add (80%)"  for 999999
select tsu.tablespace_name "Tablespace"
, ceil(tsu.used_mb) "Size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
, ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from    dba_data_files group by tablespace_name) tsu
,  (select ts.tablespace_name
 ,       nvl(sum(bytes)/1024/1024, 0) free_mb
 from    dba_tablespaces ts, dba_free_space fs
 where   ts.tablespace_name = fs.tablespace_name (+)
 group by ts.tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order by 3,4
/

User quotas on all tablespaces

col quota format a10
select username
,      tablespace_name
,      decode(max_bytes, -1, 'unlimited'
       , ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from   dba_ts_quotas
where  tablespace_name not in ('TEMP')
/

List all objects in a tablespace

 

set pages 999
col owner format a15
col segment_name format a40
col segment_type format a20
select owner
,      segment_name
,      segment_type
from   dba_segments
where  lower(tablespace_name) like lower('%&tablespace%')
order by owner, segment_name
/

 

 

Show all tablespaces used by a user

select tablespace_name
, ceil(sum(bytes) / 1024 / 1024) "MB"
from dba_extents
where owner like '&user_id'
group by tablespace_name
order by tablespace_name
/

 

Create a temporary tablespace

create temporary tablespace temp
tempfile '' size 500M
/

Alter a databases default temporary tablespace

alter database default temporary tablespace temp
/

Show segments that are approaching max_extents

col segment_name format a40
select owner
, segment_type
, segment_name
, max_extents - extents as "spare"
, max_extents
from dba_segments
where owner not in ('SYS','SYSTEM')
and (max_extents - extents) < 10
order by 4
/

List the contents of the temporary tablespace(s)

set pages 999 lines 100
col username format a15
col mb format 999,999
select  su.username
,       ses.sid 
,       ses.serial#
,       su.tablespace
,       ceil((su.blocks * dt.block_size) / 1048576) MB
from    v$sort_usage    su
,       dba_tablespaces dt
,       v$session ses
where   su.tablespace = dt.tablespace_name
and     su.session_addr = ses.saddr
/

Alter tablespace: Backups

ALTER TABLESPACE my_data_tbs BEGIN BACKUP;
ALTER TABLESPACE my_data_tbs END BACKUP;

Alter tablespace: Data Files and Tempfiles

ALTER TABLESPACE mytbs
ADD DATAFILE '/ora100/oracle/mydb/mydb_mytbs_01.dbf' SIZE 100M;
12 Portable DBA: Oracle
ALTER TABLESPACE mytemp
ADD TEMPFILE '/ora100/oracle/mydb/mydb_mytemp_01.dbf'
SIZE 100M;
ALTER TABLESPACE mytemp AUTOEXTEND OFF;
ALTER TABLESPACE mytemp AUTOEXTEND ON NEXT 100m MAXSIZE 1G;

Alter tablespace: Rename

ALTER TABLESPACE my_data_tbs RENAME TO my_newdata_tbs;

Alter tablespace: Tablespace Management

ALTER TABLESPACE my_data_tbs DEFAULT
STORAGE (INITIAL 100m NEXT 100m FREELISTS 3);
ALTER TABLESPACE my_data_tbs MINIMUM EXTENT 500k;
ALTER TABLESPACE my_data_tbs RESIZE 100m;
ALTER TABLESPACE my_data_tbs COALESCE;
ALTER TABLESPACE my_data_tbs OFFLINE;
ALTER TABLESPACE my_data_tbs ONLINE;
ALTER TABLESPACE mytbs READ ONLY;
ALTER TABLESPACE mytbs READ WRITE;
ALTER TABLESPACE mytbs FORCE LOGGING;
ALTER TABLESPACE mytbs NOLOGGING;
ALTER TABLESPACE mytbs FLASHBACK ON;
ALTER TABLESPACE mytbs FLASHBACK OFF;
ALTER TABLESPACE mytbs RETENTION GUARANTEE;
ALTER TABLESPACE mytbs RETENTION NOGUARANTEE;

Create tablespace: Permanent Tablespace

CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m FORCE LOGGING BLOCKSIZE 8k;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 10G;

Create tablespace: Temporary Tablespace

CREATE TABLESPACE temp_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_temp_tbs_01.tmp'
SIZE 100m;
create tablespace: Undo Tablespace
CREATE TABLESPACE undo_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_undo_tbs_01.tmp'
SIZE 1g RETENTION GUARANTEE;