Amit's Oracle DBA Blog: Datafile resize in Oracle

oracle@samik.com:~>

oracle@samik.com:~> ps -ef | grep pmon

oracle 4602 1 0 2020 ? 01:45:32 ora_pmon_ORCL

oracle 15050 14968 0 07:25 pts/1 00:00:00 grep pmon

oracle@samik.com:~>

oracle@samik.com:~> . oraenv

ORACLE_SID = [ORCL] ?

The Oracle base remains unchanged with value /oracle

oracle@samik.com:~>

oracle@samik.com:~> df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda2 20G 14G 5.5G 71% /

udev 2.0G 156K 2.0G 1% /dev

tmpfs 2.0G 958M 1.0G 49% /dev/shm

/dev/sda1 92M 55M 32M 64% /boot

/dev/sda7 2.0G 85M 1.8G 5% /home

/dev/sda5 6.0G 1.6G 4.1G 28% /opt

/dev/sda6 3.0G 284M 2.6G 10% /var/log

/dev/sdc1 79G 67G 8.2G 90% /oradata

/dev/sdb3 9.9G 7.1G 2.3G 76% /oracle

/dev/sdb2 5.0G 139M 4.6G 3% /oradata/arch

/dev/sdb1 2.0G 36M 1.9G 2% /oradata/redo

/dev/mapper/datavg-dpdump 20G 12G 7.0G 63% /oracle/admin/ORCL/dpdump

/dev/mapper/vgoracle-lvexport 25G 12G 12G 52% /oracle/export

oracle@samik.com:~>

oracle@samik.com:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Di Dez 21 07:26:23 2021

Verbunden mit:

Oracle Database 11g Release 11.2.0.2.0 - 64bit Production

set linesize 1000 pagesize 0 feedback off trimspool on

with

hwm as (

-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )

select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks

from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn

hwmts as (

-- join ts# with tablespace_name

select name tablespace_name,relative_fno,hwm_blocks

from hwm join v$tablespace using(ts#)

hwmdf as (

-- join with datafiles, put 5M minimum for datafiles with no extents

select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes

from hwmts right join dba_data_files using(tablespace_name,relative_fno)

)

select

case when autoextensible='YES' and maxbytes>=bytes

then -- we generate resize statements only if autoextensible can grow back to current size

'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)

||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '

||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'

else -- generate only a comment when autoextensible is off

'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)

||'M from '||to_char(ceil(bytes/1024/1024),999999)

||'M after setting autoextensible maxsize higher than current size for file '

|| file_name||' */'

end SQL

from hwmdf

where

bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed

order by bytes-hwm_bytes desc

/* reclaim 10208M from 10240M */ alter database datafile '/oradata/PPMS/undotbs01.dbf' resize 33M;

/* reclaim 870M from 21313M */ alter database datafile '/oradata/PPMS/htag01.dbf' resize 20444M;

/* reclaim 805M from 21456M */ alter database datafile '/oradata/PPMS/htag02.dbf' resize 20652M;

/* reclaim 295M from 300M after setting autoextensible maxsize higher than current size for file /oradata/PPMS/users.dbf */

/* reclaim 118M from 550M after setting autoextensible maxsize higher than current size for file /oradata/PPMS/icms01.dbf */

/* reclaim 61M from 454M */ alter database datafile '/oradata/PPMS/system01.dbf' resize 394M;

/* reclaim 33M from 626M */ alter database datafile '/oradata/PPMS/sysaux01.dbf' resize 594M;

SQL>

SQL> !df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda2 20G 14G 5.5G 71% /

udev 2.0G 156K 2.0G 1% /dev

tmpfs 2.0G 958M 1.0G 49% /dev/shm

/dev/sda1 92M 55M 32M 64% /boot

/dev/sda7 2.0G 85M 1.8G 5% /home

/dev/sda5 6.0G 1.6G 4.1G 28% /opt

/dev/sda6 3.0G 284M 2.6G 10% /var/log

/dev/sdc1 79G 67G 8.2G 90% /oradata

/dev/sdb3 9.9G 7.1G 2.3G 76% /oracle

/dev/sdb2 5.0G 139M 4.6G 3% /oradata/arch

/dev/sdb1 2.0G 36M 1.9G 2% /oradata/redo

/dev/mapper/datavg-dpdump 20G 12G 7.0G 63% /oracle/admin/ORCL/dpdump

/dev/mapper/vgoracle-lvexport 25G 12G 12G 52% /oracle/export

SQL>

SQL> col OBJECT_NAME for a25

col USERNAME for a20

select s.username, s.sid, rn.name, rs.extents

,rs.status, t.used_ublk, t.used_urec

SQL> SQL> 2 3 ,do.object_name

from v$transaction t

4 5 ,v$session s

,v$rollname rn

,v$rollstat rs

,v$locked_object lo

,dba_objects do

where t.addr = s.taddr

and t.xidusn = rn.usn

and rn.usn = rs.usn

and t.xidusn = lo.xidusn(+)

and do.object_id = lo.object_id; 6 7 8 9 10 11 12 13 14

SQL>

SQL> col OBJECT_NAME for a30

SQL> SQL> select s.username, s.sid, rn.name, rs.extents

,rs.status, t.used_ublk, t.used_urec

,do.object_name

from v$transaction t

,v$session s

,v$rollname rn

,v$rollstat rs

,v$locked_object lo

2 3 4 5 6 7 8 9 ,dba_objects do

where t.addr = s.taddr

and t.xidusn = rn.usn

and rn.usn = rs.usn

and t.xidusn = lo.xidusn(+)

and do.object_id = lo.object_id and s.username=upper('&username')

order by USED_UREC desc

/ 10 11 12 13 14 15 16

Geben Sie einen Wert f▒r username ein: ^C

SQL>

SQL> select tablespace_name,sum(bytes/1024/1024) FREE_SPACE_MB

from dba_free_space

where tablespace_name in (select tablespace_name from dba_tablespaces where tablespace_name like 'UNDO%') group by tablespace_name; 2 3

UNDOTBS1 10221,375

SQL>

SQL> col OBJECT_NAME for a25

col USERNAME for a20

select s.username, s.sid, rn.name, rs.extents

,rs.status, t.used_ublk, t.used_urec

,do.object_name

SQL> SQL> 2 3 4 from v$transaction t

,v$session s

,v$rollname rn

,v$rollstat rs

,v$locked_object lo

,dba_objects do

where t.addr = s.taddr

and t.xidusn = rn.usn

and rn.usn = rs.usn

and t.xidusn = lo.xidusn(+)

and do.object_id = lo.object_id; 5 6 7 8 9 10 11 12 13 14

SQL>

SQL> select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB

from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name

from dba_data_files a, dba_tablespaces b

where a.tablespace_name = b.tablespace_name

and b.contents = 'UNDO'

group by b.tablespace_name) a,

2 3 4 5 6 7 (select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB

from DBA_UNDO_EXTENTS c

where status <> 'EXPIRED'

group by c.tablespace_name) b

where a.tablespace_name = b.tablespace_name; 8 9 10 11

UNDOTBS1 10240 10,3125 10229,6875

SQL>

SQL> exit

Verbindung zu Oracle Database 11g Release 11.2.0.2.0 - 64bit Production beendet

oracle@samik.com:~>

oracle@samik.com:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Di Dez 21 07:36:30 2021

Verbunden mit:

Oracle Database 11g Release 11.2.0.2.0 - 64bit Production

SQL> select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB

from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name

from dba_data_files a, dba_tablespaces b

2 where a.tablespace_name = b.tablespace_name

and b.contents = 'UNDO'

group by b.tablespace_name) a,

(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB

from DBA_UNDO_EXTENTS c

3 where status <> 'EXPIRED'

group by c.tablespace_name) b

where a.tablespace_name = b.tablespace_name; 4 5 6 7 8 9 10 11

TABLESPACE_NAME SIZEMB USAGEMB FREEMB

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

UNDOTBS1 10240 10,3125 10229,6875

SQL>

SQL> SELECT /* + RULE */ df.tablespace_name "Tablespace",

df.bytes / (1024 * 1024) "Size (MB)",

SUM(fs.bytes) / (1024 * 1024) "Free (MB)",

Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

2 3 4 5 6 FROM dba_free_space fs,

(SELECT tablespace_name,SUM(bytes) bytes

FROM dba_data_files

GROUP BY tablespace_name) df

WHERE fs.tablespace_name (+) = df.tablespace_name

GROUP BY df.tablespace_name,df.bytes

UNION ALL

SELECT /* + RULE */ df.tablespace_name tspace,

fs.bytes / (1024 * 1024),

SUM(df.bytes_free) / (1024 * 1024),

Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),

Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)

FROM dba_temp_files fs,

(SELECT tablespace_name,bytes_free,bytes_used

7 8 9 10 11 12 13 14 15 16 17 18 19 20 FROM v$temp_space_header

GROUP BY tablespace_name,bytes_free,bytes_used) df

WHERE fs.tablespace_name (+) = df.tablespace_name

GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

ORDER BY 4 DESC;

21 22 23 24

Tablespace Size (MB) Free (MB) % Free % Used

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

USERS 300 299 100 0

UNDOTBS1 10240 10221,375 100 0

TEMP 6761 5061 87 25

TEMP 6761 5862 87 13

TEMP 5961 5862 85 2

TEMP 5961 5061 85 15

ICMS 550 117,0625 21 79

HTAG 42769 6007,9375 14 86

SYSTEM 454 60,125 13 87

SYSAUX 626 33,1875 5 95

10 Zeilen ausgew▒hlt.

SQL> select sum(a.bytes) as undo_size from v$datafile a, v$tablespace b, dba_tablespaces c where c.contents = 'UNDO' and c.status = 'ONLINE' and b.name = c.tablespace_name and a.ts# = b.ts#;

UNDO_SIZE

----------

1,0737E+10

SQL>

SQL> select sum(bytes)/1024/1024 "mb" from dba_free_space where tablespace_name ='UNDOTBS1';

----------

10221,375

SQL> select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB

2 from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name

from dba_data_files a, dba_tablespaces b

where a.tablespace_name = b.tablespace_name

and b.contents = 'UNDO'

3 4 5 6 group by b.tablespace_name) a,

(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB

from DBA_UNDO_EXTENTS c

7 8 9 where status <> 'EXPIRED'

group by c.tablespace_name) b

where a.tablespace_name = b.tablespace_name; 10 11

TABLESPACE_NAME SIZEMB USAGEMB FREEMB

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

UNDOTBS1 10240 10,3125 10229,6875

SQL> select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status;

STATUS NUM_EXTENTS NUM_BLOCKS MB

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

EXPIRED 27 936 7,31

UNEXPIRED 15 1320 10,31

SQL>

SQL> set lines 500

col file_name format a85

col TABLESPACE_NAME for a15

select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='&tablespace_name';

SQL> SQL> SQL> Geben Sie einen Wert f▒r tablespace_name ein: UNDOTBS1

alt 1: select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='&tablespace_name'

neu 1: select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='UNDOTBS1'

TABLESPACE_NAME FILE_NAME BYTES/1024/1024 MAXBYTES/1024/1024 AUT

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

UNDOTBS1 /oradata/ORCL/undotbs01.dbf 10240 32767,9844 YES

SQL>

SQL> set linesize 1000 pagesize 0 feedback off trimspool on

with

SQL> 2 hwm as (

-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )

select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks

from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn

hwmts as (

-- join ts# with tablespace_name

select name tablespace_name,relative_fno,hwm_blocks

from hwm join v$tablespace using(ts#)

hwmdf as (

-- join with datafiles, put 5M minimum for datafiles with no extents

select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes

from hwmts right join dba_data_files using(tablespace_name,relative_fno)

)

3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 select

case when autoextensible='YES' and maxbytes>=bytes

then -- we generate resize statements only if autoextensible can grow back to current size

'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)

||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '

||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'

else -- generate only a comment when autoextensible is off

'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)

||'M from '||to_char(ceil(bytes/1024/1024),999999)

||'M after setting autoextensible maxsize higher than current size for file '

|| file_name||' */'

end SQL

from hwmdf

where

bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed

order by bytes-hwm_bytes desc

/ 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

/* reclaim 10208M from 10240M */ alter database datafile '/oradata/ORCL/undotbs01.dbf' resize 33M;

/* reclaim 870M from 21313M */ alter database datafile '/oradata/ORCL/htag01.dbf' resize 20444M;

/* reclaim 805M from 21456M */ alter database datafile '/oradata/ORCL/htag02.dbf' resize 20652M;

/* reclaim 295M from 300M after setting autoextensible maxsize higher than current size for file /oradata/ORCL/users.dbf */

/* reclaim 118M from 550M after setting autoextensible maxsize higher than current size for file /oradata/ORCL/icms01.dbf */

/* reclaim 61M from 454M */ alter database datafile '/oradata/ORCL/system01.dbf' resize 394M;

/* reclaim 33M from 626M */ alter database datafile '/oradata/ORCL/sysaux01.dbf' resize 594M;

SQL>

SQL> alter database datafile '/oradata/ORCL/undotbs01.dbf' resize 33M;

SQL>

SQL> exit

Verbindung zu Oracle Database 11g Release 11.2.0.2.0 - 64bit Production beendet

oracle@samik.com:~>

oracle@samik.com:~> df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda2 20G 14G 5.5G 71% /

udev 2.0G 156K 2.0G 1% /dev

tmpfs 2.0G 958M 1.0G 49% /dev/shm

/dev/sda1 92M 55M 32M 64% /boot

/dev/sda7 2.0G 85M 1.8G 5% /home

/dev/sda5 6.0G 1.6G 4.1G 28% /opt

/dev/sda6 3.0G 284M 2.6G 10% /var/log

/dev/sdc1 79G 57G 19G 76% /oradata

/dev/sdb3 9.9G 7.1G 2.3G 76% /oracle

/dev/sdb2 5.0G 139M 4.6G 3% /oradata/arch

/dev/sdb1 2.0G 36M 1.9G 2% /oradata/redo

/dev/mapper/datavg-dpdump 20G 12G 7.0G 63% /oracle/admin/ORCL/dpdump

/dev/mapper/vgoracle-lvexport 25G 12G 12G 52% /oracle/export

oracle@samik.com:~>

oracle@samik.com:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Di Dez 21 07:41:58 2021

Verbunden mit:

Oracle Database 11g Release 11.2.0.2.0 - 64bit Production

SQL> select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB

2 from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name

from dba_data_files a, dba_tablespaces b

where a.tablespace_name = b.tablespace_name

and b.contents = 'UNDO'

group by b.tablespace_name) a,

(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB

from DBA_UNDO_EXTENTS c

where status <> 'EXPIRED'

group by c.tablespace_name) b

where a.tablespace_name = b.tablespace_name; 3 4 5 6 7 8 9 10 11

TABLESPACE_NAME SIZEMB USAGEMB FREEMB

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

UNDOTBS1 33 10,3125 22,6875

SQL>

SQL> select sum(bytes)/1024/1024 "mb" from dba_free_space where tablespace_name ='UNDOTBS1';

----------

14,375

SQL> set linesize 1000 pagesize 0 feedback off trimspool on

with

hwm as (

-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )

select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks

from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn

SQL> 2 3 4 5 6 7 hwmts as (

-- join ts# with tablespace_name

select name tablespace_name,relative_fno,hwm_blocks

from hwm join v$tablespace using(ts#)

hwmdf as (

-- join with datafiles, put 5M minimum for datafiles with no extents

select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes

from hwmts right join dba_data_files using(tablespace_name,relative_fno)

)

select

case when autoextensible='YES' and maxbytes>=bytes

then -- we generate resize statements only if autoextensible can grow back to current size

'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)

||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '

||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'

8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 else -- generate only a comment when autoextensible is off

'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)

||'M from '||to_char(ceil(bytes/1024/1024),999999)

||'M after setting autoextensible maxsize higher than current size for file '

|| file_name||' */'

end SQL

from hwmdf

where

bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed

order by bytes-hwm_bytes desc

/ 24 25 26 27 28 29 30 31 32 33

/* reclaim 870M from 21313M */ alter database datafile '/oradata/ORCL/htag01.dbf' resize 20444M;

/* reclaim 805M from 21456M */ alter database datafile '/oradata/ORCL/htag02.dbf' resize 20652M;

/* reclaim 295M from 300M after setting autoextensible maxsize higher than current size for file /oradata/ORCL/users.dbf */

/* reclaim 118M from 550M after setting autoextensible maxsize higher than current size for file /oradata/ORCL/icms01.dbf */

/* reclaim 61M from 454M */ alter database datafile '/oradata/ORCL/system01.dbf' resize 394M;

/* reclaim 33M from 626M */ alter database datafile '/oradata/ORCL/sysaux01.dbf' resize 594M;

SQL>

SQL> set lines 500

col file_name format a85

col TABLESPACE_NAME for a15

select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='&tablespace_name';SQL> SQL> SQL>

Geben Sie einen Wert f▒r tablespace_name ein: UNDOTBS1

alt 1: select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='&tablespace_name'

neu 1: select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='UNDOTBS1'

UNDOTBS1 /oradata/ORCL/undotbs01.dbf 33 32767,9844 YES

SQL>

SQL> exit

Verbindung zu Oracle Database 11g Release 11.2.0.2.0 - 64bit Production beendet

oracle@samik.com:~>

oracle@samik.com:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Di Dez 21 07:45:42 2021

Verbunden mit:

Oracle Database 11g Release 11.2.0.2.0 - 64bit Production

SQL>

set lines 500

col file_name format a85

col TABLESPACE_NAME for a15

select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='&tablespace_name';SQL> SQL> SQL> SQL>

Geben Sie einen Wert f▒r tablespace_name ein: UNDOTBS1

alt 1: select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='&tablespace_name'

neu 1: select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='UNDOTBS1'

TABLESPACE_NAME FILE_NAME BYTES/1024/1024 MAXBYTES/1024/1024 AUT

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

UNDOTBS1 /oradata/ORCL/undotbs01.dbf 33 32767,9844 YES

SQL>

SQL> set pages 120

select t.tablespace_name "TABLESPACE", t.TOTAL "TOTAL SIZE",

nvl(f.FREE,0) "FREE SPACE",round(nvl(f.FREE,0)*100/t.TOTAL) "% FREE"

FROM

(select tablespace_name,trunc(sum(bytes)/1024/1024) as "TOTAL" from dba_data_files group by tablespace_name) t,

(select tablespace_name,trunc(sum(bytes)/1024/1024) as "FREE" from dba_free_space group by tablespace_name) f

where t.tablespace_name=f.tablespace_name(+)

order by 4

SQL> 2 3 4 5 6 7 8

TABLESPACE TOTAL SIZE FREE SPACE % FREE

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

SYSAUX 626 33 5

SYSTEM 454 60 13

HTAG 42769 6007 14

ICMS 550 117 21

UNDOTBS1 33 14 42

USERS 300 299 100

6 Zeilen ausgew▒hlt.

SQL> alter database datafile '/oradata/ORCL/htag01.dbf' resize 20444M;

Datenbank wurde ge▒ndert.

SQL> alter database datafile '/oradata/ORCL/htag02.dbf' resize 20652M;

Datenbank wurde ge▒ndert.

SQL>

SQL> set pages 120

select t.tablespace_name "TABLESPACE", t.TOTAL "TOTAL SIZE",

nvl(f.FREE,0) "FREE SPACE",round(nvl(f.FREE,0)*100/t.TOTAL) "% FREE"

FROM

(select tablespace_name,trunc(sum(bytes)/1024/1024) as "TOTAL" from dba_data_files group by tablespace_name) t,

(select tablespace_name,trunc(sum(bytes)/1024/1024) as "FREE" from dba_free_space group by tablespace_name) f

where t.tablespace_name=f.tablespace_name(+)

order by 4

SQL> 2 3 4 5 6 7 8

TABLESPACE TOTAL SIZE FREE SPACE % FREE

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

SYSAUX 626 33 5

HTAG 41096 4334 11

SYSTEM 454 60 13

ICMS 550 117 21

UNDOTBS1 33 14 42

USERS 300 299 100

6 Zeilen ausgew▒hlt.

SQL> SQL>

SQL>

set lines 500

col file_name format a85

col TABLESPACE_NAME for a15

select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='&tablespace_name';SQL> SQL> SQL> SQL>

Geben Sie einen Wert f▒r tablespace_name ein: HTAG

alt 1: select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='&tablespace_name'

neu 1: select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='HTAG'

TABLESPACE_NAME FILE_NAME BYTES/1024/1024 MAXBYTES/1024/1024 AUT

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

HTAG /oradata/ORCL/htag01.dbf 20444 30720 YES

HTAG /oradata/ORCL/htag02.dbf 20652 30720 YES

SQL> /

Geben Sie einen Wert f▒r tablespace_name ein: ICMS

alt 1: select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='&tablespace_name'

neu 1: select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='ICMS'

TABLESPACE_NAME FILE_NAME BYTES/1024/1024 MAXBYTES/1024/1024 AUT

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

ICMS /oradata/ORCL/icms01.dbf 550 0 NO

SQL>

SQL> alter database datafile '/oradata/ORCL/icms01.dbf' autoextend on maxsize 5G;

Datenbank wurde ge▒ndert.

SQL>

SQL> exit

Verbindung zu Oracle Database 11g Release 11.2.0.2.0 - 64bit Production beendet

oracle@samik.com:~>

oracle@samik.com:~> df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda2 20G 14G 5.5G 71% /

udev 2.0G 156K 2.0G 1% /dev

tmpfs 2.0G 958M 1.0G 49% /dev/shm

/dev/sda1 92M 55M 32M 64% /boot

/dev/sda7 2.0G 85M 1.8G 5% /home

/dev/sda5 6.0G 1.6G 4.1G 28% /opt

/dev/sda6 3.0G 284M 2.6G 10% /var/log

/dev/sdc1 79G 55G 20G 74% /oradata

/dev/sdb3 9.9G 7.1G 2.3G 76% /oracle

/dev/sdb2 5.0G 139M 4.6G 3% /oradata/arch

/dev/sdb1 2.0G 36M 1.9G 2% /oradata/redo

/dev/mapper/datavg-dpdump 20G 12G 7.0G 63% /oracle/admin/ORCL/dpdump

/dev/mapper/vgoracle-lvexport 25G 12G 12G 52% /oracle/export

oracle@samik.com:~>

Amit's Oracle DBA Blog

Disclaimer

Tuesday, 21 December 2021

Datafile resize in Oracle

No comments:

Post a Comment

How to recovery PDB when PDB database is dropped in Oracle

Labels