Disclaimer

Tuesday, 21 December 2021

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:~>
oracle@samik.com:~>
oracle@samik.com:~>
oracle@samik.com:~>
oracle@samik.com:~>
oracle@samik.com:~> . oraenv
ORACLE_SID = [ORCL] ?

The Oracle base remains unchanged with value /oracle
oracle@samik.com:~>
oracle@samik.com:~>
oracle@samik.com:~>
oracle@samik.com:~>
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:~>
oracle@samik.com:~>
oracle@samik.com:~>
oracle@samik.com:~>
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

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


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>
SQL>
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>
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>
SQL>
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>
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>
SQL>
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>
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>
SQL>
SQL>
SQL>
SQL> exit
Verbindung zu Oracle Database 11g Release 11.2.0.2.0 - 64bit Production beendet
oracle@samik.com:~>
oracle@samik.com:~>
oracle@samik.com:~>
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

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


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>
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>
SQL> select sum(bytes)/1024/1024 "mb" from dba_free_space where tablespace_name ='UNDOTBS1';

        mb
----------
 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>
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>
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>
SQL> alter database datafile '/oradata/ORCL/undotbs01.dbf' resize 33M;
SQL>
SQL>
SQL>
SQL>
SQL> exit
Verbindung zu Oracle Database 11g Release 11.2.0.2.0 - 64bit Production beendet
oracle@samik.com:~>
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:~>
oracle@samik.com:~>
oracle@samik.com:~>
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

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


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>
SQL>
SQL>
SQL>
SQL> select sum(bytes)/1024/1024 "mb" from dba_free_space where tablespace_name ='UNDOTBS1';

        mb
----------
    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>
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>
SQL>
SQL> exit
Verbindung zu Oracle Database 11g Release 11.2.0.2.0 - 64bit Production beendet
oracle@samik.com:~>
oracle@samik.com:~>
oracle@samik.com:~>
oracle@samik.com:~>
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

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


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>
SQL>
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>
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>
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>
SQL> alter database datafile '/oradata/ORCL/icms01.dbf' autoextend on maxsize 5G;

Datenbank wurde ge▒ndert.

SQL>
SQL>
SQL> exit
Verbindung zu Oracle Database 11g Release 11.2.0.2.0 - 64bit Production beendet
oracle@samik.com:~>
oracle@samik.com:~>
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:~>
oracle@samik.com:~>
oracle@samik.com:~>

No comments:

Post a Comment

Understanding SQL Plan Baselines in Oracle Database

  Understanding SQL Plan Baselines in Oracle Database SQL Plan Baseline is the feature in Oracle started from Database 11g that helps to pre...