Default location for Oracle-managed datafiles:
show parameter db_create_file_dest;
Displays Space Usage for Each Datafile
Set lines 999 pages 100
select
a.tablespace_name,
trunc(SUM(a.bytes)/1024/1024/1024,2) "CurGb",
trunc(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024,
b.maxextend*8192/1024/1024/1024)),2) "MaxGb",
trunc((SUM(a.bytes)/1024/1024/1024 -
round(c."Free"/1024/1024/1024)),2) "TotalUsedGb",
trunc((SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*8192/1024/1024/1024))
- (SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))),2)
"TotalFreeGb",
round(100*(SUM(a.bytes)/1024/1024 -
round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null,
A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
from
dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM
dba_tablespaces d,
DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group
by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024/1024
order by round(100*(SUM(a.bytes)/1024/1024 -
round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null,
A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc
/
Add Data file in a Table Space
alter tablespace DB_TS_Name add datafile size 4G autoextend on;
or
alter tablespace users ADD DATAFILE
'/u05/oracle/oradata/booktst_users_02.dbf'
size 1024M;
No comments:
Post a Comment
If you have any doubt or question, please contact us.