SQL & PL/SQL :: Find Out How Much Undo Will Be Generated By DML / DDL

Mar 29, 2010

How to find out how much undo will be generated by a dml / ddl statement in Oracle 9i? With Oracle 10g we can use the famous mystat.sql and mystat2.sql with argument as 'undo change vector size'.

However with Oracle 9i there is no statname as 'undo change vector size'

View 3 Replies


ADVERTISEMENT

Undo Table Space Not Working - REDO Being Generated?

Feb 14, 2013

I am using Oracle 10.2.0.3. Since yesterday i am seeing a session with sid 1160 using undo tablespace but not able to find how much it is using .I need to know which session and from which module and how much is the Undo being used by those sessions. I have tried searching but all the queries provide me with some different results each time.

Also i need the same information for REDO being generated .

View 20 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

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

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

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

SQL & PL/SQL :: Undo Dropped Column From Table

Jul 13, 2013

Flashback query is working fine for dropped table,but throwing error when trying to get back the previous date by dropping a column from a table.

FLASHBACK TABLE emp TO TIMESTAMP TO_TIMESTAMP('2013-07-13 05:00:00', 'YYYY-MM-DD HH24:MI:SS')

SQL Error: ORA-01466: unable to read data - table definition has changed

Is that a limitation of Oracle by not having query to flashback a column but a table.

View 4 Replies View Related

Performance Tuning :: Undo Space?

Jan 15, 2012

Query to find out who is eating up most of the undo space and what query he is running..

View 3 Replies View Related

SQL & PL/SQL :: How To Undo A Drop Table Operation

Feb 24, 2013

How do I perform Undo a drop table operation?

View 12 Replies View Related

Undo Application During Instance Recovery?

Apr 10, 2013

During rollback phase of instance recovery, where is the undo applied from:

- redo logs

or

- undo generated during redo application in roll forward phase?

View 4 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

System Generated Constraints

Jul 5, 2012

I was wondering with dba_constraints and i found unexpected result shown as below

select constraints_name, constraint_type from dba_constraints

constraint_name constraint_type
SYS_C00141 c
SYS_C00142 c
SYS_C00143 c
SYS_C00144 c
SYS_C00145 c
SYS_C00146 c
SYS_C00147 c
SYS_C00148 c
SYS_C00149 c

i jst want to know that what is this system generated constraints ?

View 3 Replies View Related

Trc Files Generated From Triggers?

Dec 3, 2010

The trace directory is full of trc files with the text below:

--------Dumping Sorted Master Trigger List --------
Trigger Owner : INPUT
Trigger Name : CUS_TST
--------Dumping Trigger Sublists --------

There is like a file generated every minute, and i cant stop it from happening.I have tried setting the trace_enabled parameter to FALSE but no success.

View 2 Replies View Related

Hourly AWR Snapshots Are Not Being Generated

Oct 9, 2012

i just noticed on my 11g database - no AWR snapshots are generated.

View 4 Replies View Related

Forms :: 10g FMX Is Generated But Not Viewed

May 15, 2011

Forms 10g is insatalled recently on my machine. New form is developed,when we run the form it generates FMX file for the same but output is not dispalyed. The output window get closed automatically.

View 3 Replies View Related

SQL & PL/SQL :: Generated Multiple Rows?

Nov 5, 2010

in eache record we are receiving information from differente city, we pretend to get an output where we have a row for each city (delimited by comma) that we have in column CITY

Input data

SELECT '1001001' as CLIENT_ID, 'LONDON, PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION
SELECT '1022201' as CLIENT_ID, 'MADRID, OSLO' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION
SELECT '1033001' as CLIENT_ID, 'PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION
SELECT '1033004' as CLIENT_ID, 'MADRID, OSLO, PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL

Output expected
CLIENT_IDDT_REGCITY
100100105/11/2010 LONDON
100100105/11/2010 PARIS
102220105/11/2010 MADRID

[code]...

View 3 Replies View Related

Redo Generated In Last 2 Hour

Feb 7, 2013

Is there any way to get the amount of redo generated in last 2 hour. which has below chareteistic

1. redo generated by currently connected session from last 2 hour.
2. redo generated by session disconnected during last 2 hour.

total_redo = disconnected sessoin during last 2 hour + connected session generating redo during last 2 hour.

View 7 Replies View Related

Archive Log Generated Each 4 Minutes?

Jul 2, 2012

in our database 10.0.2.4 with RAC archive log generated each 4 min , did this increase the performance of database and how i can fix it

View 14 Replies View Related

SQL & PL/SQL :: Sequence Number Generated

Jun 6, 2011

I am facing issue with my sequence number about sequence number generated..I want it to follow the sequence all the time, i.e. it should take next incremental number to last generated seauence, but it generates random one all the time, i.e

s.nextval() = 4
s.nextval() = 5
s.nextval() = 10
s.nextval() = 543
s.nextval() = 544

My sequence defination is as below

CREATE SEQUENCE s
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20

View 11 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

Alter Table Fails - Ran Out Of Undo Space?

Jun 11, 2009

I want to alter a very large table.

ALTER TABLE MYTABLE ADD
(
ENTRY_TSTMP DATE DEFAULT SYSDATE NOT NULL
)

My table is very large and I am getting an error saying I am out of undo space.

The dba says the undo space is as big as the table.

View 1 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 :: Roll Of Undo In Recovery?

Aug 4, 2011

what is the role of undo cache or undo tablespace in recovery?

View 3 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

Server Administration :: UNDO In Parameter File?

Dec 9, 2011

I 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 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

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 View Related







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