Sql to find check Percent of Rollback Segment Used?

Advertisement
November 30

I have the following queries to find information about rollback segments.
I want the sql to find % of rollback segment used.
--rollsegs.sql
select SEGMENT_NAME, OWNER, TABLESPACE_NAME, SEGMENT_ID, status
from dba_rollback_segs order by segment_name;
select name, extents, writes, xacts, gets,waits, shrinks, aveactive, status
from v$rollname n, v$rollstat s
where n.usn = s.usn
order by name;
--rolbstat.sql
select name, rssize, hwmsize "High", optsize "Opt", wraps, extends, shrinks,
aveshrink "Aveshr", aveactive "Aveact"
from v$rollname n, v$rollstat s
where n.usn = s.usn
order by name;
set numformat 999999990;

Advertisement

Replay

DrBurgs, there was a time when your session was assigned a rollback segment that rollback segment held all the updates performed by your session but by Oracle version 8.1 every transaction executed by your session could be assigned to a different rollback segment.
So every 100,000 or whatever value of N use choose as your commit point could result in a different rollback segment being used. You can use v$rollstat and v$transaction to monitor rollback segment usage while the job is running.
You stated that you had one rollback segment for every tablespace. There is no relation in Oracle between the number of tablespace and the number of rollback segments you should have.
You should choose the number of rollback segments to create based on the expected user load and how large a transaction you need to be able to support. For manually defined rollback segments set the initial extent size and the next extent size equal with pctincrease = 0. Then set optimal to the size you would like each rollback segment to be. This size should be large enough that shrinks do not happen often but small enough that one segment that expands will not result in other rollback segments being able to extend. By using uniform extent size you guarentee that every free extent is usable by any other rollback segment that needs to extend.
With a true warehouse you probably have very little update compared to your select load and the updates are probably load jobs. In this case you may need very few, very large rollback segments to handle the load.
HTH -- Mark D Powell --

View 6 Replies

Different rollback segment used. why?

November 30

Hi. I am trying to use rollback segment RB6 in a delete process in a stored procedure using SET TRANSACTION USE ROLLBACK SEGMENT RB6; However, when the procedure is called and performed, an error is thrown: ORA-01562: failed to extend rollback segmen

View All Replies

Forcing a specific rollback segment on a transaction does not seem to work

October 11

Hi! We're using Oracle 9.2.0.5.0 on Sun Solaris and we're still configured to use Rollback Segments. We have an issue with Snapshot too old due to RBS too small on a long query I attach below for reference: set heading off set pagesize 0 set feedback

View All Replies

How to check/find the size of current ROLLBACK segment in oracle 10g

November 30

How to check/find the size of current ROLLBACK segment in oracle 10g ? Kindly helpA rollback segment name like "_SYSSMU231$" is used when you have Automatic Undo Management enabled. The only relevant parameters are : 1. UNDO_MANAGEMENT=AUTO 2. U

View All Replies

Trying to understand rollback segments - offline tblspaces for backup

October 11

Using Oracle 8i, on Windows platform - The question - receive an error ORA-01556 when performing a sql "ALTER TABLESPACE MY_TBS_LT OFFLINE IMMEDIATE"; ============================================= First off I created a tablespace and table on an

View All Replies

ROLLBACK SEGMENTS OPERATION

November 30

Hello Everyone This is the first time I am a forum member and I'm figuring out how it works because I have a very big problem Look, I'm a support engineer and, as you know, the code of the application is not own by me. Three weeks ago I received a re

View All Replies

ORA-01534 rollback segment 'R1' does not exist

November 30

Greetings HELP!! We are using oracle 8.0.5 ... The database will not open because it says the system rollback segment does not exist. The database mounts but won't open. from the file system checks, the dbf file that contains the system rollback segm

View All Replies

SQL Server Express checking DB Size

November 30

Hello, I frequently connect to customers using Sage 300 with SQL Express. Many times for a quote I need to check the SQL database size. Many times I am connecting to a workstation and the customer either does not have access to their SQL server to ac

View All Replies

Convert from Rollback segment(RBS)  to undotablespace

November 30

i have a database 9i and it using rollback segment and i would like to convert to undotablespace. question is there is anyway i can find it out what size undo i need to created base on the database right now i have it. the database is very critical i

View All Replies

Transaction table in rollback segments

November 30

hi guys, I have read the following, but am still having a little difficulty conceptualising. Would someone be kind enough to give me a very brief example? thanks For each rollback segment, Oracle maintains a transaction table--a list of all transacti

View All Replies

Disable Rollback Segments

November 30

Hello friends, 1) can I say to Oracle not to use the Rollback segments since I am updating huge Number of records. 2) Is it possible to cache a huge table (say with 10 million records) using dbms_...keep. Please let me know your response as soon as p

View All Replies

Error rollback segment - urgent

November 30

hi i have created locally managed tablesspace and in init.ora file i have sent unto management as manual as our application needs to specify rollback segments when i created rollback segments and attached to a locally managed tablespace i am getting

View All Replies

Oracle 9i - Rollback Segment Needs Recovery

November 30

I have been noticing for a few days that my UNDO tablespace has been growing in size for no apparent reason. My datafiles have only increased by 400 Meg over last 4 days, but UNDO tablespace has grown from 100 Meg - 5.6 Gig.... I decided to create a

View All Replies

Oracle error: Ora-01555 : snapshot too old: rollback segment number 1......

November 30

System Error: Unknown Database error (type qqdb_ResourceException) on WMS_WH1: Execute failed for SQL statement ............... error from database is: ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" too small... Any

View All Replies

ORA01555"snapshot too old:rollback segment num %s with name "%s" to small

November 30

Hi All i have and 11gR1(11.1.0.6) DB source instance in wich i have i a partitioned table of 1TB size. I want to IMPDP this table using NETWORK_LINK in a 11gR2(11.2.0.3) destination RAC instance. when a try to import a get the following error message

View All Replies

ROLLBACK SEGMENT VERSION 7.3

November 30

We have an oracle 7.3 database on solaris(sparc) 2.6. In the midddle of the transactions, the following error message occured: ORA 1578 Data block corrupted(file #2, block#8274) ORA-1110 Datafile 2:'/user1/oradb/rbstbl.dbf' (this datafile corresponds

View All Replies

Is "SET TRANSACTION USE ROLLBACK SEGMENT" only a hint

October 11

I have two users, one makes some inserts in a table. The other makes a select which visits many rows. I have a big rollback-segment. To ensure, both users use this rollback-segment I created a logon-trigger: CREATE OR REPLACE TRIGGER a_logon AFTER LO

View All Replies

Pb with rollback segment

October 11

Hi guys. Am using oracle 8i and am having problems with rollbackup segment. My rbs tablespace is 35GB and will like to shrink it! Its a production db, so my question is shoudl I fire the below query? alter rollback segment SYSTEM shrink; alter rollba

View All Replies

Can rollback segments span one datafile like data segments

November 30

1.Can rollback segments span one datafile like data segments? For e.g we have undo tablespace with 2 data files We have one active transaction in rbs01,now when rollback segment grows and extents are allocated to it ,if all the extents will be alloca

View All Replies

Rollback segments - database logging

November 30

Got a problem... We are trying to move data from one database to another using: INSERT INTO table1 (SELECT a,b,c FROM [email protected]). The problem we are having is that the amount of data being moved with this in sert command is too great - we are gettin

View All Replies

Tags:

  1. bapi mir7 for asset order
  2. how to post miro for zero grn value
  3. sony kdl 42w805a netflix problem
  4. descargar iBooks 3.1.1 .ipa
  5. ba amex cli when bad credit
  6. BAPI_PO_CREATE1 WBS
  7. cisco ise authentication failure
  8. A1NN
  9. UFE3
  10. DAQmx_Val_ChanPerLine
Copyrights 2019 Fcffair BigData Resource, All rights reserved