Server Administration :: Undo Tablespace Which Has Autoextend On Feature
May 18, 2011you 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?
ADVERTISEMENT
Server Administration :: Sizing Undo Tablespace And Setting Undo-retention?
Jan 30, 2004regarding 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
Server Administration :: How To Undo Tablespace Size
Feb 7, 2011As 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]....
Server Administration :: Undo Tablespace 100 Percent Full With Offline Extended?
Jul 26, 2010i'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 RelatedServer Administration :: Tablespaces In Autoextend Settings
Jun 9, 2011I just want to know what are precautionary measures if tablespaces in a database is in autoextend mode. I'm wondering if these tablespaces reached its maximum sizes.
In our case, we are administering a database (turned over by our outsourcer after a 2-year maintenance) with SAP interface, and we noticed that most of it's tablespaces were created with initial size of 2Gb up to a maximum size of 10Gb, all were 'autoextensible'.
Tablespace Autoextend Datafile
Apr 18, 2007I have a small problem, i will try to explain it, My tablespaces are Automatically extend datafile when full (AUTOEXTEND). It had checked "autoextend ON" checkbox.
What I need to know is, when happened that autoextend accion(day/hour), is it possible?.
Server Utilities :: Added A Datafile In Undo Tablespace
Apr 28, 2011When 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.
Server Administration :: ORA-03001 - Unimplemented Feature?
May 15, 2012for error: ORA-03001: unimplemented feature.
SQL> SELECT /*+ PARALLEL (a 8) PARALLEL (b 8) */
SUM (space_used_delta) / 1024 / 1024 "USED_MB",
2 3 SUM (c.bytes) / 1024 / 1024 "TOTAL_MB",
4 ROUND (SUM (space_used_delta) / SUM (c.bytes) * 100, 2) || '%'"PERCENT"
[code]...
ERROR at line 5:
ORA-03001: unimplemented feature
SQL>
All the tables are present, still..??
Server Administration :: Disable Partition Feature
Feb 2, 2012I am using Oracle 10g enterprise edition .
Currently, Partition is enabled in my database.I want to disable it.
SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Partitioning';
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Partitioning
TRUE
the steps to disable the partition feature.
Server Administration :: New Feature Of 11g Called-Pending Statistics
Mar 31, 2011What is the scenario, we should use the new feature of 11g called-Pending Statistics.
Eg:-
EXEC DBMS_STATS.set_table_prefs ('SCOTT', 'EMP', 'PUBLISH', 'false');
Server Administration :: ORA-00439 / Feature Not Enabled / Real Application Clusters
Jan 30, 2012i installed my oracle 10g R2 in my pc but while startup my database database manually i got error msg cmd> startup nomount
ORA - 00439 : Feature not enabled: Real Application Clusters
Server Administration :: Roll Of Undo In Recovery?
Aug 4, 2011what is the role of undo cache or undo tablespace in recovery?
View 3 Replies View RelatedServer Administration :: UNDO In Parameter File?
Dec 9, 2011I have a question ragarding undo tablespace. I want to ask that why only undo tablespace information we need to specify in parameter file. We do not specify any other tablespace information. Not even for temporary tablespace. Then why we need to give undo tablespace name while instance is creating.
View 5 Replies View RelatedServer Administration :: Undo Info Of Insert Statement
Jun 24, 2012If we insert a row in a database table then the new row stays at database buffer cache in SGA (until commit), right?. The target table is not affected (before commit). The new row is saved after commit.
I saw a concepts at Sybex oracle 10g oca book (Page 406) as follows:
" INSERT statements use little space in an undo segment; only the pointer to the new row is stored in the undo tablespace. To undo an INSERT statement, the pointer locates the new row and deletes it from the table if the transaction is rolled back. "
My question is If the row is not saved at table before commit, if we issue rollback then how oracle delete from table? I think the new row is deleted from database buffer cache in SGA.
Server Administration :: How Oracle Implements Undo Management
Jul 31, 2010I have been reading various articles about the undo management. This basic concept of undo management is simple but how oracle implements it is bit harder for me to grasp.
What i have read and understood is that whenever a DML(Update, Delete, Insert) statement is issued by a user, the data is fetched from datafile to database buffer cache and at the same time a copy of the original data is saved in undo segment. Now if other users requests the same data, they are presented with the unchanged copy in the undo segment.
Now I have the following questions:
1) In case of Insert statement, what data is saved in undo segment. Is it the complete data in the table to which we want to insert the new row?
2)When the user issues DML statement, there are three copies of the same data, one in Memory (which is changed and not the same as original data), second in Undo segment (Which is unchanged copy of original data) and third in datafile file ( which is original data). What is the difference in the data in undo segment and data in datafile at this stage. Why are the other users presented with the data from undo segment rather than original data from the datafile to maintain read consistency.
3)When the user issues rollback, the changes made to the copy of data in memory are undone.The copies of data in memory and undo segment are now same?. What happens to the before change copy in undo segment. Is it still there or deleted.
Server Administration :: Creating A New Tablespace With Datafile / Assign Users To Current Tablespace
May 27, 2011i have a tablespace which contains 121 datafile(max limit reached) as a dba what we have to do?
creating a new tablespace with a datafile and assign the users to the current tablespace which i created now.iif the above process is correct,after some time the tablespace which was filled up got freed up.now can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces
Server Administration :: Single Big Tablespace Versus Multiple Tablespace?
Jan 26, 2011My database version is
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
My os version is
Linux damdat01 2.6.18-128.7.1.el5 #1 SMP Wed
Aug 19 04:00:49 EDT 2009 x86_64 x86_64 x86_64
GNU/Linux
My database is OLP system.
My question is what are the advantages and disadvantages having one single tablespace versus multiple tablespace?
Easy to maintain when you have single tablespace. but hard to track the IO issues if you have one single tablespace.
Server Administration :: Query On Redo And Undo Basic Concept
Nov 17, 2010understanding a redo/undo concept . Refer following data
create table t(n number);
insert into t values(10);
commit;
now I update as following
update t set n=20;
As per my understanding the before image i.e. n=10 is stored in undo (to be used for rollback, transaction recovery and even in instance recover but not in media recovery) and after image n=20 is stored in redo (to be used for various recovery purposes including media recovery in case of consistent backup).
So it is redo logs for rolling forward and undo for rolling back making transaction, db consistent . If my above understanding is true then what is meant by the term 'redo required for undo'?
Also, if there are 2 database db1 and db2 connected using database link where we are populating t1 table in db1 using t2 table in db2 using db link where redo and undo will be updated db1 or db2?
Undo Tablespace Size
Jun 6, 2012At 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?.
Can Undo Tablespace Be Too Large
Apr 26, 2013Can 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 RelatedUndo Tablespace Management
Apr 23, 2013I 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.
Recovery Of Undo Tablespace?
Apr 2, 2013S: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.
While Dropping Old Undo Tablespace / Getting Error
Dec 23, 2012i 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$
Increase Undo_retention Value Or Undo Tablespace Size?
Aug 18, 2010When 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?
Difference Between Redo Logs And Undo Tablespace
Jan 17, 2007I'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 RelatedDifference Between Redo Logs And Undo Tablespace?
Apr 25, 2013it 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 RelatedSize Of Undo Tablespace Datafile Different From Same Files In OS
Jan 22, 2013why 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]....
TUNED_UNDORETENTION Undo Tablespace Is Not Fixed Size
Apr 14, 2013rdbms 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?
Backup & Recovery :: UNDO Tablespace Deleted From OS Level?
Mar 24, 2013Operating 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.
Performance Tuning :: Undo Tablespace Full / Which Is Rectified But Now Having Big WAIT
Mar 7, 2012we 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