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


ADVERTISEMENT

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

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

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

Server Administration :: Minimum Datafile Size Required For Creating Non-system Tablespace?

May 21, 2007

what is the Minimum datafile size required for creating a non-system tablespace?

I am trying to create a tablespace by giving the syntax like,

SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 72k;
create tablespace t1
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required

SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 73k;

Tablespace created.

The blocksize for my database is 4096, as i have heard that the minimum size of the datafile is decided by blocksize, but i want to know that how it is calculated as by giving the above syntax the other values will be default. I am trying the syntax in oracle 9.2.0.1.0 version.

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

Server Administration :: Creating A New Tablespace With Datafile / Assign Users To Current Tablespace

May 27, 2011

i 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

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

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 Utilities :: Imp / Exp - Max Size Assigned To Datafile?

Jul 22, 2010

i have exp dump of size 1gb but when i tried to imp ,it showing error of space , it asking for space of 4gb. But i have 1gb on c: drive and 32gb on d: ,can i add datafile on d: locaion and what is max size i can assign to that datafile .

View 4 Replies View Related

Tablespace Autoextend Datafile

Apr 18, 2007

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

View 10 Replies View Related

Delete One Datafile From Tablespace Which Contain 2 Datafiles?

Oct 29, 2008

i want to delete one datafile from tablespace witch contain 2 datafiles

i did

ALTER DATABASE DATAFILE 'ton_fichier' OFFLINE DROP;

and delete the file physiquly but i have already my datafile witch i have delete in my tablespace

View 14 Replies View Related

Server Administration :: Unable To Calculate Datafile Shrink Possible Size

Jul 28, 2011

I need to resize my datafile as i have allocated more space and need to reduce ( i.e.data load completed now). my tablespace is having 11.74 gb free space now. it has 3 datafile.

TABLESPACE TOTAL USED FREE PCT_FREE LARGEST FRAGMENTS
------------------------ ---------- ---------- ---------- ---------- ---------- ----------
CFC_DATA 150528 138780.6 11747.4 7.80412946 1251 992

TABLESPACE_NAME FILE_ID FILE_NAME Size(MB)
------------------ ---------- ------------------------------------------------------- ----------
CFC_DATA 71 +DATA/dedw/datafile/cfc_data.4074.731085435 65535.9688
CFC_DATA 334 +DATA/dedw/datafile/cfc_data.4473.757566557 20480
CFC_DATA 1710 +DATA/dedw/datafile/cfc_data.2012.728095695 64512I used below script to find out HWM in order to resize the datafile.
db_block_size is 16KB.
[code]....

in TOAD, we have an option, that is "Minimum size" button against each datafile.. Need the SQL which is running behind when we press this button from TOAD ?

View 1 Replies View Related

Datafile Namedisplayed For SYSTEM Tablespace In Dba_data_files

Nov 23, 2011

Datafile name is +DISK_GROUP_1/oratst1/datafile/system.1122.764387443

displayed for the SYSTEM tablespace in dba_data_files.Infact, datafile name starts with +DISK_GROUP_1 for tablesspaces. Where is the reference to +DISK_GROUP_1.

What file name should I use to resize or to add new datafile to a tablespace?

View 1 Replies View Related

Server Administration :: Move Datafile To Another Tablespace

Feb 16, 2012

Erroneously created datafile, re: "/path/../large_rbs_03.dbf" was created under the SYSTEM tablespace which is supposedly be in the LARGE_RBS tablespace.

How do I make the said datafile be under LARGE_RBS?

View 3 Replies View Related

RAC & Failsafe :: Adding / Extending Datafile For Tablespace?

Sep 16, 2010

Recently, I'm encountering the ORA-01654 error. I did some research and found out that I need to extend or add new datafile for that tablespace.

I'm working in RAC environment with ASM enabled.

My question is

-Do I need to restart the DB after making the changes?

-Do I need to execute the commands on both nodes?

View 4 Replies View Related

SQL & PL/SQL :: Fetch Tablespace And Datafile Count With Used / Freespace?

Apr 30, 2013

I'm trying to fetch tablespace and its datafile count along with used space,freespace and total size of a tablespace. I've written a query like this, but the logic is wrong it is giving me incorrect rows. It is running in loops and shows me incorrect number of datafiles.

Quote: select DISTINCT b.tablespace_name "Tablespace Name",count(b.file_ID) "Num of Datafiles",
TO_CHAR( b.bytes/(1024*1024*1024),'990.00') "Total SIZE(GB)" ,
TO_CHAR((b.bytes - sum(nvl(a.bytes,0)))/(1024*1024*1024),'990.00') "Used Space(GB)" ,
TO_CHAR(sum(nvl(a.bytes,0))/(1024*1024*1024),'990.00') "Free Space(GB)"
from sys.dba_free_space a, sys.dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name,b.file_name, b.file_id, b.bytes,online_status
order by b.tablespace_name;

View 3 Replies View Related

Server Administration :: Trying To Change Tablespace And Datafile Properties On Fly

Aug 21, 2011

This facility has one last 10g database and a very problematic tablespace and last datafile associated with it. The tablespace was set up with INITIAL_ EXTENT of 131,072 (128K) instead of the more 'normal' 4,194,304 (4M) and NEXT_EXTENT of 262,144 (256K) instead of 4,194,304 (4M).

More worryingly, the datafile has INCREMENT_BY set to 1 (8K) instead of 1,280 (10M) or 2,048 (16M).Has anyone ever updated sys.ts$.dflinit and sys.ts$. dflincr to modify the INITIAL_EXTENT and NEXT_EXTENT, and sys.file$.inc to modify the INCREMENT_BY?

View 7 Replies View Related







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