Re: backup UNDO Tablespace (dba_undo_extents)
Date: Wed, 16 Dec 2009 11:35:08 -0600
Message-ID: <eb64345d0912160935s52c6c94fg96b352ebfb202c4a_at_mail.gmail.com>
10R2 and I am using backupset (backup datafile #;)
SQL> select * from v$version;
BANNER
Oracle Database 10g Release 10.2.0.1.0 - Production PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
SQL> show parameter undo
NAME TYPE VALUE ------------------------------------ ----------- ----------------------------- undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1SQL> select bytes/1024/1024 from dba_data_files 2 where TABLESPACE_NAME = 'UNDOTBS1';
BYTES/1024/1024
24545
SQL> select sum(bytes)/1024/1024 from dba_free_space 2 where TABLESPACE_NAME = 'UNDOTBS1';
SUM(BYTES)/1024/1024
24474
SQL> select 24474-24545 from dual;
24474-24545
-71
SQL>
(Sorry, it's 24GB not 20GB.)
Thanks,
Roger
On Wed, Dec 16, 2009 at 9:43 AM, Yong Huang <yong321_at_yahoo.com> wrote:
> > I think RMAN will back up all blocks that have ever been part of
> > any segment.
>
> Jeremiah,
>
> That's the case up to 10gR1. Beginning with 10gR2, unused blocks in
> LMT are ignored, unused in the sense that the segment is dropped or
> truncated. See "Unused Block Compression" at
> https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=360443.1
>
> > Oracle 10g
> > We have only one datafile (20 GB) in the undo tablespace.
> > When I ran RMAN backup of this datafile, it created a 20GB backupset
> > file.
> > I query dba_undo_extents and it shows less than 80 MB used.
>
> Roger,
>
> Tell us the exact version of Oracle. Could it be 10gR1? Your total
> undo tablespace is 20 GB. Is total free space (sum(bytes) from
> dba_free_space) for undo more than 19 GB? And I suppose you're not
> doing datafile image copy.
>
> Yong Huang
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 16 2009 - 11:35:08 CST