Check Tablespace Utilization
-- https://www.support.dbagenesis.com/post/tablespace-utilization-in-oracle set colsep | set linesize 100 pages 100 trimspool on numwidth 14 col name format a25 col owner format a15 col "Used (GB)" format a15 col "Free (GB)" format a15 col "(Used) %" format a15 col "Size (M)" format a15 SELECT d.status "Status", d.tablespace_name "Name", TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'99999999.99') "Used (GB)", TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used) %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') UNION ALL SELECT d.status "Status", d.tablespace_name "Name", TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'99999999.99') "Used (GB)", TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used) %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';
Report Tablespace Status
set pagesize 2000
set linesize 180
set feedback 1
col tablespace_name Format a30
col maxBytes_MB Format 999,999,999,999,990
col Used_MB Format 999,999,999,999,990
col free_MB Format 999,999,999,999,990
col Usage_per Format 999,999,999,999,990
select tablespace_name
,maxBytes_MB
,Used_MB
,free_MB
,Usage_per Usage_per
from (select (select value from v$parameter where name ='db_unique_name') db_unique_name
,a1.tablespace_name
,round(allocation/1024/1024,2) allocation_MB
,round(maxBytes/1024/1024,2) maxBytes_MB
,round((maxBytes-case when UsedBytes is null then 0 else UsedBytes end)/1024/1024,2) free_MB
,round((case when UsedBytes is null then 0 else UsedBytes end)/1024/1024,2) Used_MB
,round((case when UsedBytes is null then 0 else UsedBytes end / maxBytes) * 100,2) Usage_per
from (select tablespace_name
,sum(bytes) allocation
,sum(CASE AUTOEXTENSIBLE WHEN 'YES' THEN maxbytes
WHEN 'NO' THEN bytes
END) maxBytes
from dba_data_files
--where tablespace_name not like 'UNDO%'
group by tablespace_name ) a1
,(select TABLESPACE_NAME,sum(bytes) UsedBytes
from dba_segments
group by tablespace_name) a2
where a1.tablespace_name = a2.tablespace_name(+)
UNION ALL
select (select value from v$parameter where name ='db_unique_name') db_unique_name
,a1.tablespace_name
,round(allocation/1024/1024,2) allocation_MB
,round(maxBytes/1024/1024,2) maxBytes_MB
,round((freebytes+(maxBytes-allocation))/1024/1024,2) free_MB
,round((allocation-freebytes)/1024/1024,2) Used_MB
,round(((allocation - freebytes) / maxBytes) * 100,2) Usage_per
from (select tablespace_name
,sum(bytes) allocation
,sum(CASE AUTOEXTENSIBLE WHEN 'YES' THEN maxbytes
WHEN 'NO' THEN bytes
END) maxBytes
from dba_TEMP_files
--where tablespace_name not like 'UNDO%'
group by tablespace_name ) a1
,(select tablespace_name,sum(bytes_free) freebytes from v$temp_space_header group by tablespace_name) a2
where a1.tablespace_name = a2.tablespace_name(+)
) b1
where tablespace_name not like '%TEMP%'
order by tablespace_name desc;
Drop Tablespace and remove datafiles
drop tablespace tablespace_name including contents and datafiles;
Tablespace add a datafile using omf
alter tablespace tablespace_name add datafile size 32767M autoextend on maxsize unlimited;
Tablespace add mutiple datafiles using omf
-- Tablespace begin for i in 1..50 loop execute immediate 'alter tablespace tablespace_name add datafile size 32767M autoextend on maxsize unlimited'; end loop; end; / -- Temp Tablespace begin for i in 1..10 loop execute immediate 'alter tablespace temp add tempfile size 32767M autoextend on maxsize unlimited'; end loop; end; /
Check tablespace datafiles
select count(*), tablespace_name from dba_data_files group by tablespace_name order by 2;
Drop Tablespace Datafile
When you drop a datafile or tempfile, references to the datafile or tempfile are removed from the data dictionary and control files, and the physical file is deleted from the file system or Oracle Automatic Storage Management (Oracle ASM) disk group.
alter tablespace C_PFSD drop datafile '+DATADG/ORCL/DATAFILE/c_pfsd.525.1120237865';
Query Datafile resize max size
set line 200
col tablespace_name format a30
col file_name format a100
select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id = b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name;
Fix query dba_segment slow performance
Ref: http://www.dadbm.com/oracle-slow-sql-query-against-dba_segments-solved/
dbms_space_admin.tablespace_fix_segment_extblks('TablespaceName');
){kind=link}
0 留言