Tablespace Related Queries

Create Tablespace   

SQL> create tablespace xxli_ts01 datafile '/finsys/UAT1/db/apps_st/data/xxli_data.dbf' SIZE 1500M autoextend on next 256M maxsize 10000M;

Resize Tablespace 

SQL> alter database datafile '/finsys/UAT1/db/apps_st/data/sysaux02.dbf' resize 7000M;

Add Datafile

SQL> alter TABLESPACE APPS_TS_TX_DATA add datafile '/finsys/UAT1/db/apps_st/data/a_txn_data05.dbf' SIZE 1800M autoextend on next 256M maxsize 3500M;

Auto extend on/off

SQL> ALTER DATABASE DATAFILE '/finsys/UAT1/db/apps_st/data/xxkia01.dbf' AUTOEXTEND ON maxsize unlimited;

SQL> ALTER DATABASE DATAFILE '/finsys/UAT1/db/apps_st/data/xxkia01.dbf' AUTOEXTEND OFF;

Unlimited Quota

SQL> alter user apps quota unlimited on apps_ts_tx_data;

Drop datafile

SQL> alter tablespace APPS_TS_TX_DATA drop datafile '/finsys/UAT1/db/apps_st/data/xxkia01.dbf';

Move Datafile

1)

SQL> ALTER TABLESPACE XXTEST OFFLINE NORMAL;

2)

Copy the datafile (/tmp/test.dbf) to their new location (/finsys/UAT1/db/apps_st/data/) and rename (XXTEST1.DBF) them using the operating system command

3)

SQL> ALTER TABLESPACE XXTEST RENAME DATAFILE '/tmp/test.dbf' TO '/finsys/UAT1/db/apps_st/data/XXTEST1.DBF';

4)

SQL> ALTER TABLESPACE XXTEST ONLINE;

Tablespace Related Views

dba_data_files

dba_free_space

v$tablespace

Create Temp Tablespace

SQL> create temporary tablespace temp4 tempfile '/finsys/UAT1/db/apps_st/data/temp04.dbf' size 1000M tablespace group TEMP;

Resize Temp Tablespace

SQL> ALTER DATABASE TEMPFILE '/finsys/UAT1/db/apps_st/data/temp01.dbf' RESIZE 1000M;

Add Temp File

SQL> alter TABLESPACE TEMP1 add tempfile '/finsys/UAT1/db/apps_st/data/temp01.dbf' SIZE 2000M autoextend on next 256M maxsize 10240M;

Drop Temp File

SQL> alter TABLESPACE TEMP1 drop tempfile '/finsys/UAT1/db/apps_st/data/temp01.dbf';

Drop Temp Tablespace

SQL> DROP TABLESPACE temp1 including CONTENTS AND DATAFILES;

Make  Temp Tablespace default

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp4;

Adding tablespce to group

SQL> ALTER TABLESPACE temp01 TABLESPACE GROUP TEMP;

Remove tablespce from group

SQL> ALTER TABLESPACE temp01 TABLESPACE GROUP '';

Temp Tablespace Related Views

DBA_TEMP_FILES

dba_temp_free_space

DBA_TABLESPACE_GROUPS;

V$TEMP_SPACE_HEADER

Details/Usage of Tablespaces

set linesize 5000;
col tablespace_name for a30;
col FreePC for a10;
set pagesize 1000;
select
b.tablespace_name,
round(tbs_size,0) SizeMb,
round((tbs_size- a.free_space),0)UsedMb,
round(a.free_space,0) FreeMb,
to_char((round(((a.free_space*100)/tbs_size),2)),'00.00')||'%' FreePC,
to_char((100-round(((a.free_space*100)/tbs_size),2)),'00.00')||'%' FullPC
from 
(  select 
     tablespace_name, 
     round(sum(bytes)/1024/1024 ,2) as free_space 
   from dba_free_space 
   group by tablespace_name
 UNION
   select 
     tablespace_name,
     ((free_space/1024)/1024) free_space
   from dba_temp_free_space
)a, 

(  select 
     tablespace_name,
     sum(bytes)/1024/1024 as tbs_size 
   from dba_data_files 
   group by tablespace_name
 UNION
   select 
     tablespace_name,
     sum(bytes)/1024/1024 tbs_size
   from dba_temp_files
   group by tablespace_name 
)b
where a.tablespace_name=b.tablespace_name
order by 100-round(((a.free_space*100)/tbs_size),2) desc;

OR

set linesize 5000;
col tablespace_name for a30;
col FreePC for a10;
set pagesize 1000;
select
b.tablespace_name,
round(tbs_size,0) SizeMb,
round((a.tot_used),0)UsedMb,
round((tbs_size-a.tot_used),0) FreeMb,
decode((round(((a.tot_used*100)/tbs_size),2)),0,(100-round(((a.tot_used*100)/tbs_size),2)||'%'),(to_char((100-round(((a.tot_used*100)/tbs_size),2)),'00.00')||'%')) FreePC,
to_char((round(((a.tot_used*100)/tbs_size),2)),'00.00')||'%' FullPC
from 
(  select 
     tablespace_name, 
     sum(bytes/1024/1024) tot_used
   from dba_segments
   group by tablespace_name
 UNION
   select 
     tablespace_name,
     round(used_blocks*8/1024) tot_used
   from v$sort_segment 
)a, 
(  select 
     tablespace_name,
     sum(bytes)/1024/1024 as tbs_size 
   from dba_data_files 
   group by tablespace_name
 UNION
   select 
     tablespace_name,
     sum(bytes)/1024/1024 tbs_size
   from dba_temp_files
   group by tablespace_name 
)b
where a.tablespace_name=b.tablespace_name
order by round(((a.tot_used*100)/tbs_size),2) desc;