TUNED_UNDORETENTION Undo Tablespace Is Not Fixed Size

Apr 14, 2013

rdbms 11gr2 undo tablespace is not fixed size. and the undo guarantee is not set.should we set undo_retention to

select max(tuned_undoretention) from v$undostat ; to avoid 1555 error?

View 22 Replies


ADVERTISEMENT

Undo Tablespace Size

Jun 6, 2012

At a time my 20 GB undo tablespace was full. So i increased the tablespace size upto 48 GB. Then i saw 45 GB was used. Then i changed undo_retention=60. After that am seeing that 48 GB is full.

1) why it's happened?
2) Here what is the effect of undo_retention=60
3) How to resolve?.

View 15 Replies View Related

Increase Undo_retention Value Or Undo Tablespace Size?

Aug 18, 2010

When i takeind export i got error like this..

ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB [TABLE_DATA:"POS"."TBK_POS_FACT":"KROATL200404"]

ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" too small"

from this can we increase the undo_retention value or undo tablespace size?

View 2 Replies View Related

Server Administration :: How To Undo Tablespace Size

Feb 7, 2011

As the undo segments are used in round robin fashion, Is it possible that with varying load (concurrent users, size and number of transactions), the size of Undo tablespace on a particular day is less than the Undo tablespace size few days back, by any chance?

As a basic understanding I know that Undo is preserved for read consistency and transaction, instance recovery So if there are lot of transaction on a database on 05 Feb and before that, but there aren't any transactions on 6,7,8,9, then on 10th Feb can we see the Undo tablespace size is less than that of 05 Feb?

In the following case when data belonging to table is not required for any queries, transactions, even then the undo size is not restored upon dropping the table.

As such for large operations and batch processes shall we keep undo tablespace with files as 'Autoextend' with 'Maxsize' as 'Unlimited'?

SQL> select b.tablespace_name, Total_Kbytes_Available/1024 Tot_Mbytes_Available,
Kbytes_alloc/1024 Mbytes_allocated, kbytes_free/1024 Mbytes_Free_from_allocated,
((Kbytes_alloc - kbytes_free)*100/ Total_Kbytes_Available) Pctused
2 from ( select sum(bytes)/1024 Kbytes_free,
3 tablespace_name
4 from sys.dba_free_space

[code]....

View 12 Replies View Related

Size Of Undo Tablespace Datafile Different From Same Files In OS

Jan 22, 2013

why total size for undotbs1 is different from the acutal data file size in Operating system.

select tablespace_name, sum(bytes/1024/1024) from dba_data_files
where tablespace_name like 'UNDO%'
group by tablespace_name;

tablespacename total size
UNDOTBS1                      2000
UNDOTBS2     7284
[code]....

View 7 Replies View Related

Server Administration :: Sizing Undo Tablespace And Setting Undo-retention?

Jan 30, 2004

regarding sizing undo tablespace and undo_retention parameter.we have to implement the database in production system with 40 users but how much space should be allocated to undo tablespace is there any propotions related to virtual memory and the
parameter.i have gone thru oracle doc's and some related sites.its an ERP aplications that contains 20 modules .I am an new one to this dba level

View 8 Replies View Related

Can Undo Tablespace Be Too Large

Apr 26, 2013

Can an undo tablespace be too large and actually hurt performance? I have seen a system with a dedicated 1 TB drive for undo tablespace (no guarantee) with an undo_retention of 7 days. Would this hurt performance? What about setting an undo_retention of 24 hours with no guarantee? The only mention I could find online said that it would not hurt performance but I wanted to double check. You would think that Oracle does not care if it deletes the undo at 15 minutes or if it deletes the undo at a later date such as 7 days later and the performance should stay the same.

View 3 Replies View Related

Undo Tablespace Management

Apr 23, 2013

I am trying to drop 90 columns from a big partitioned table. I was trying a physical drop first and since it is taking longer time I decided make the columns unused state and drop them. However I was able to set them to unused state.

Now I am trying to drop those unused columns from the table, it is running since 22hours Apporox. I am keep increasing the undo tablespace to retain the undo data.

I also have decreased the undo_retention to 300 from 900.

My question is there any better way to drop these columns. And is there any way to flush out the data from undo.

View 4 Replies View Related

Recovery Of Undo Tablespace?

Apr 2, 2013

S:Solaris
DB:10g

I am simulating a recovery of drop undo tablespace. for the same I have done the following things:

1. Dropped the only undo datafile from the os level while the database was open.

2. Then I created a table and inserted and updated some values in it and I was also allowed to commit.

When I am updating the rows of the table,the before images must have been written to UNDO but in my case there is no UNDO datafile.

View 1 Replies View Related

While Dropping Old Undo Tablespace / Getting Error

Dec 23, 2012

i Cannot drop old undo tablespace. While dropping the old undo tablespace we get an error

ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU77$' found, terminate dropping tablespace

SQL> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

TABLESPACE_NAME STATUS SEGMENT_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
APPS_UNDO NEEDS RECOVERY _SYSSMU77$

View 11 Replies View Related

Performance Tuning :: ORA-01555 - Increase Undo Size Or Retention?

Apr 22, 2013

I have got the following error yesterday

ORA-01555 caused by SQL statement below (SQL ID: fdxcyoin67ty8t, Query Duration=380128 sec, SCN: 0x0229.ff00afd0):

following are the existing settings

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 96000
undo_tablespace string undo

[code]....

following are the details from v$undostat

select begin_time, end_time, undotsn, undoblks, maxquerylen, maxqueryid, activeblks, unexpiredblks, expiredblks, tuned_undoretention from v$undostat
where trunc(begin_time)=trunc(sysdate)-1 order by begin_time;
BEGIN_TIME END_TIME UNDOTSN UNDOBLKS MAXQUERYLEN MAXQUERYID ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
-------------- -------------- ---------- ---------- ----------- ------------- ---------- ------------- ----------- -------------------
21-04-13 00:08 21-04-13 00:18 1 12733 378446 duqnawh32hp4u 91152 7068448 225440 345600
21-04-13 00:18 21-04-13 00:28 1 8951 379047 duqnawh32hp4u 99344 7072800 225440 345600
21-04-13 00:28 21-04-13 00:38 1 14073 379650 duqnawh32hp4u 90128 7075872 234656 345600

[code]....

Following are the details in AWR report (00:00 til 01:00 of 21-Apr-2013) .... not thet the error was produced at 00:42

Undo Segment Summary DB/Inst: DBCPY/dbcpy01 Snaps: 18853-18854
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out of Space count
-> Undo segment block stats:
-> uS - unexpired Stolen, your - unexpired Released, uU - unexpired reUsed

[code]....

Undo Advisor information taken 'now' is as following

SQL> select dbms_undo_adv.longest_query(sysdate-2,sysdate) from dual;
DBMS_UNDO_ADV.LONGEST_QUERY(SYSDATE-2,SYSDATE)
----------------------------------------------
379650
SQL> select dbms_undo_adv.required_retention from dual;

[code]....

In above situation what should be my first choice (assuming increasing space is not an issue) - increase undo tablespace or increase undo retention?

If latter is the choice then what should be the value? Because as I understand present 96000 value is taken as lower limit and because of auto tuning the actual value (TUNED_UNDORETENTION) being used was 345600 In that case shall I set it to something > max(maxquerylen) i.e 379,650 + X?Or I shall increase the undo tablespace size?

From Undo Advisor output it looks to me that even if I increase the undo retention to 379650 current undo size will be able to support it (may be at the expense of DMLs)Is that right?

View 13 Replies View Related

Difference Between Redo Logs And Undo Tablespace

Jan 17, 2007

I'm an Oracle novice and from what I've read so far, it seems that you should be able to do rollbacks and data recovery using the redo logs. I'm having a difficulty understanding the need for the undo tablespace.

View 2 Replies View Related

Difference Between Redo Logs And Undo Tablespace?

Apr 25, 2013

it seems that you should be able to do rollbacks and data recovery using the redo logs. I'm having a difficulty understanding the need for the undo tablespace.

View 3 Replies View Related

Server Administration :: Undo Tablespace Which Has Autoextend On Feature

May 18, 2011

you have an undo tablespace which has autoextend on feature.after a timegap your undo tablespace presently is 100GB

here as a DBA what you will do?

View 3 Replies View Related

Server Utilities :: Added A Datafile In Undo Tablespace

Apr 28, 2011

When I am importing, I get these errors

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (XXXXXXXXXXXXXXXX) violated
Column 1 2
Column 2 OFFLINE
[code]....

I added a datafile in undo tablespace (its an ASM database). I doubt that since I added the datafile to undo tablespace, I am getting this error.

View -1 Replies View Related

Backup & Recovery :: UNDO Tablespace Deleted From OS Level?

Mar 24, 2013

Operating System - WindowXP
oracle version 10.2.0.1

I was learning some recovery part in my home laptop. Database is in Archivelog, flashback mode. All of sudden, i deleted it from OS level with out taking backup of it.

When i tried to open database, it failed to start. Database is in mount mode.while trying to open, it gives message -

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'F:ORACLEPRODUCT10.2.0ORADATADBSYSTEM01.DBF'

I tried to create on file named "UNDOTBS01.DBF" but oracle is not recognizing it.

View 11 Replies View Related

Performance Tuning :: Undo Tablespace Full / Which Is Rectified But Now Having Big WAIT

Mar 7, 2012

we have a situation where both undo tablespaces were almost filled i.e UNDOTBS1 99% and UNDOTBS2 100% filled so i add data files to it and then i found a lot of blocking session and was just killing them through EM then i stop my front end listener and also down the service, now i don't have any blocking session but on EM a big WAIT is coming. alert log shows nothing serious, it was showing deadlock but now it is over as well.

View 8 Replies View Related

Oracle Data Changes In Undo Tablespace / Database Buffer Cache

May 30, 2013

I have a serious doubt in oracle architecture functionality, when a user issues a update statement the data blocks are carried to db buffer cache and where does the changes to the data blocks are made???? Does a copy of the data block is kept in db buffer cache and the changes are made to the block in buffer cache?? or the a copy of the data block is kept in undo tablespace and changes are made to the blocks in the undo tablespace???

In simple the changes to the data blocks are made at db buffer cache or undo tablespace?

View 7 Replies View Related

Server Administration :: Undo Tablespace 100 Percent Full With Offline Extended?

Jul 26, 2010

i'm facing a problem while i'm inserting millions of record from table to table that undo tablespace reach 100% full and execution aborted. , how can free the undo tablespace ??? many of extendes are offline. will it flush automatically ??? or what i should do

View 4 Replies View Related

Server Administration :: Max Size Of Tablespace

Jan 4, 2010

I have one tablespace PSINDEX with Maxsize of 6 GB. But when I query the tablespace its showing the BYTES is greater than MAXBYTES.

View 5 Replies View Related

Undo Segments In Undo Table Space

Nov 29, 2012

Can we find out no of undo segments in undo Table space ? If so , how to find? what's their max limit ?

View 9 Replies View Related

Server Administration :: Increasing Tablespace Size?

Mar 7, 2010

I want to increase the size of the tablespace but when i login as sysdba or admin user i can just see the 21 tables in the dba_tablespaces or user_tablespaces. I want to see the tablespaces related to the application.

View 8 Replies View Related

Server Administration :: Reducing Tablespace Size?

Jan 24, 2012

we have a tablespace of size 900 GB where 90% of space is occupied by two tables having BLOB data and now i need to drop these two tables and then to recover the space, i need to resize the tablespace (datafiles).

View 3 Replies View Related

Server Administration :: Reduce The System Tablespace Size?

Aug 6, 2011

how to reduce the system tablespace size

my system01.dbf size is 6gb

i want reduce from 6gb to 2gb

View 3 Replies View Related

Server Administration :: Tablespace Size Required To Be Increased?

Nov 19, 2011

I found some of the tablesapces whose used% exceeded 80%.Based on this output i thought of increasing the datafile.But there are many tablespaces of DATA1 whose autoextensible column some are set to NO, and some are set to YES. I believe we dont need to bother about the tablespace whose Autoextensible column set to YES as Oracle will automatically manage until the Maxsize of it. But some tablesapce of DATA1 are set to NO. Should i recommend to add size for this DATA1 tablespace whose autoextensible are set to YES/NO or leave it as it is.

Please see the column below.

Quote:
FILE_IDTABLESPACE_NAMESIZE(M)USED(M)FREE(M)MAX(M)AUTOEXTENSIBLE% USED
59DATA13276732741.1325.7532767YES100
60DATA13276732741.6925.1932767YES100
61DATA1327673274026.8832767YES100
76DATA13270032193.31506.560NO98
77DATA13270030858.51841.380NO94
78DATA13270029476.563223.310NO90

View 25 Replies View Related

Server Administration :: Tablespace Size Is Same As Before Deletion Of User?

Oct 1, 2012

I have deleted a user with CASCADE option.

After deletion, the size of tablespace should be reduced.

But the tablespace size is same as before deletion of user.

Currently , that USER is deleted and not present in the database . Still space is not released after user deletion.

View 4 Replies View Related

Find Tablespace Size (used And Free) For Partitioned Table?

Aug 9, 2013

I need to find the tablespace size (used and free) for a partitioned table

View 3 Replies View Related

Performance Tuning :: Tablespace With Different Block Size Inside Same Database?

Nov 25, 2011

All the analysis till now on our system proves that our system is clearly I/O bound and db sequential read is the biggest culprit.

We have even identified the index which is being affected by sequential read. I am thinking of creating a new tablespace with 32K blocksize (currently all table spaces are 8k) and migrate this index to the new space. That way, Oracle will have to do less number of reads to get the required data.

But is there anything wrong in having just one tablespace with a differnt block size? Or is there anything that I have to be watchful about while doing it?

View 14 Replies View Related

Server Administration :: Freeing Tablespace - Returning To Initial Size?

May 9, 2010

Here is my problem : I create a tablespace named ts_photos.I create then a table on this tablespace.Then I insert some blobs records.The space grows up to 2 Gb.

I perform a truncate statment. Then the space grows up to 4Gb.I re-insert the same records and the size stays in 4Gb.The database in in archivelog mode. How can I return to the initial size (2Gb of effective data) : should I drop the tablespace and recreate it?

View 3 Replies View Related

Server Administration :: How To Free Up Tablespace Size When It Reaches Max Limit

Feb 15, 2011

How to free up the tablespace size when it reaches its threshold (Max limit)?

Say for example USERS & SYSAUX tablespace reaches above 90%.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved