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;
