Few Oracle SQL Command

Increase the Size of Tablespace



If you want to increase the size of tablespace, its so simple. You can do this by enterprise manager console. Increase the size of datafile for particular tablespace.

OR

For Example

ALTER DATABASE
DATAFILE ‘(datafile path)/u01/oradata/ userdata03. dbf’
RESIZE 200M;

If you don’t have any free space on that partition of disk then you can add another datafile on the other partition  for particular tablespace.

For example

ALTER TABLESPACE app_data
ADD DATAFILE ‘/u01/oradata/ userdata03. dbf’
SIZE 200M;

-----------------------------------------------------------------------------------------

Tablespace used in Percentage 

# login oracle with sqlplus as  sysadm user
# conn as sysdba

select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
 and round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)  > 75
order by ((a.BYTES-b.BYTES)/a.BYTES) desc
/







any mistake is there or u wanna give any suggestion so please write to me ............