Server Administration :: How To Check Error ORA-01555 / Snapshot Too Old
Dec 23, 2011
i am getting this error during expdp ORA-01555 -snapshot too old
1. i increased undo_retention i.e 50000
2. i execute expdp during late night when almost zero user is online so no commit from any other session.
3. we have a table having BLOB data type field in a table which is 500 GB in size.complete DB size is 700 GB
where and why this error exists.if corruption of BLOB so how to check that etc. the same error is occurring in three of our data centres during expdp.
View 4 Replies
ADVERTISEMENT
Jun 23, 2011
We are running a query in one of our databases using the database the link. The query fails in the middle with "ORA-01555: snapshot too old". Not sure, about the database which this error message points to? Will it be the database we have logged in or it is the database where db link points ?
View 7 Replies
View Related
Jan 12, 2012
I have a problem regarding with Oracle Flashback Query.if I'm doing flashback a database two hours before,I'm receiving ORA-01555: snapshot too old error.My question is:How to able to extend the based on the hour or day?
View 1 Replies
View Related
May 15, 2013
data pump export is very slow. For 50GB export has taken more than 24Hrs with one below error:
Database Version:11.2.0.2.0
OS: Windows server 2008 r2
Increased 10GB RAM and CPU 6 to 8 then also same issue
Error:
ORA-31693: Table data object "BNCSDB"."MS_DATA_PTORE" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20_4037596720$" too small
Export log:
Export: Release 11.2.0.2.0 - Production on Tue May 14 20:03:25 2013
Copyright � 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orcl dumpfile=BCSDB04_19.dmp logfile=BCSDB04_19.log
[code]...
View 12 Replies
View Related
Nov 3, 2010
We are taking full logical backup ( export) in freeze hours( no user login). But prompting error "ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" too small".
View 6 Replies
View Related
Jul 21, 2009
I am having problems with my procedure which is refreshing materialized views. This is the error i am getting : -12008: ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 14...
I have two database with the same procedure and the same objects. They run at the same time, however, there are times when i get the error on the other database while the other database runs the procedure without any error.
When i checked the net, they say that this error is caused by data that is old, so the solution is to make the source a predecessor of my procedure/job. But what i am thinking is that how was it possible for the other database to run it completely even if it is not yet linked as the successor of the source?
View 8 Replies
View Related
Apr 29, 2010
I am getting the ORA-01555 Snapshot Too Old error while excecuting my procedure (which is running in OLTP system). My procedure contains one table which master table of my DB which holds 1.2 GB of data. The proc is using the MERGE statement which is inserting or updating the data in the master table, while the condition of MERGE is using the subquery in which 2 DBlink table is used (in which one table is updating quite frequently). The proc. is executing in the interval of 6 hrs i.e 4 times a day and its is scheduled in job. find the proc. as an attachment.
View 3 Replies
View Related
Jan 19, 2012
One of the users received the error
ora-01555: rollback segment too small
I have read about the error and saw that it is caused by transactions made upon same data, filling to maximum one of the UNDOTBS rollback segments.It happens only once in a while, each time on a different Rollback Segment.
I've read that i should do a few things to enlarge those segments, such as bring a 1. segment offline, 2. drop it and then 3. create it with a bigger size & possibly a bigger extent setting
i've also read that those actions aren't relevant if you have the parameter UNDO_MANAGEMENT set on AUTO, which is actually the case.is that why when i execute ALTER ROLLBACK SEGMENT RB_SEG_NAME_11$ OFFLINE;
1. How do i solve my issue with the Rollback segment being too small to contain the snapshot with the requested SCN?
2. what does the parameter UNDO_MANAGEMENT mean? should i change it, in order to adjust my Rollback Segments attributes, to prevent my error of re-occurring?
View 10 Replies
View Related
Jan 25, 2012
I am getting below ORA-01555 error in alert log everyday.
ORA-01555 caused by SQL statement below (SCN: 0x09ad.86a4562a):
Sat Jan 21 08:39:45 2012
SELECT (NVL(MAX(BLOCK_ID + BLOCKS ),0) * :b1 ) / 1024
FROM DBA_EXTENTS WHERE TABLESPACE_NAME = :b2 AND FILE_ID = :b3
I cannot able to find the sql_id here. So how can I find from which process or session this query is firing? before increasing the undo size, I need to analyze as why it is occuring?
undo_management string MANUAL
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string
View 9 Replies
View Related
Jul 1, 2010
i am getting "snapshot too old " error while take in export backup of a database(with CONSISTENT=y), it actually runs for 3 hours.
it always fails for table1 with snapshot error
i pulled the awr for that 3 hours, to see any long running SQL hitting table1 . and i found 3 , Two SELECT and one INSERT.
I assume it is INSERT not letting me take a consistent export backup of Table1 .
View 9 Replies
View Related
Sep 11, 2007
I've got to collect statistics by DBMS_STAT. For the first time, the spcreate.sql is to be called, however, it returned one error following:
If this script is automatically called from spcreate (which is the supported method), all STATSPACK segments will be created in the PERFSTAT user's default tablespace.
Using perfstat tablespace to store Statspack objects
... Creating STATS$SNAPSHOT_ID Sequence Sequence created.
create public synonym STATS$SNAPSHOT_ID for STATS$SNAPSHOT_ID
*
ERROR at line 1:
ORA-00955: name is already used by an existing object Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
And then, I checked it again with its owner:
SQL> select owner, synonym_name
2 from dba_synonyms
3 where synonym_name='STATS$SNAPSHOT_ID';
OWNER SYNONYM_NAME
------------------------------ ------------------------------
PUBLIC STATS$SNAPSHOT_ID
Then, I checked again after dropping schema PERFSTAT:
SQL> conn /as sysdba
Connected.
SQL>
SQL> select username, account_status
[code]...
What's the STATS@SNAPSHOT_ID synonym? Can I remove it from PUBLIC owner and recreate?
View 13 Replies
View Related
Feb 5, 2007
how to tackle the error ORA-01555: Snapshot too old: rollback segment number XX with name NNN too small.I am using Oracle 8i .ways to tackle it without extending the rollback segment
View 2 Replies
View Related
Mar 14, 2012
We a running oracle9i database and forms 6i application.
We have 4 gb ram size and sized the sga for 700mb pga for 400mb for past 4 months there is no problem.
Now from last week we encounter the server hang, and we do restart. How to check reason for hanging.what are all the check list to carried out.
View 19 Replies
View Related
Jul 8, 2010
How can I check whether the ASM is used in the environment? how I can check for it and if being used, how to connect to that instance later on.
View 5 Replies
View Related
Mar 3, 2012
you have some 500 Db how will u check the alertlog file. do u do it manually or is their any tool to check the alertlog files. how to check it.
View 7 Replies
View Related
Oct 20, 2011
How can i check a partition whether it has been compressed? just as flowing test,i can not get the information about partition P_L1 whether been compressed.
SQL> Select
2 aa.compression,
3 aa.partition_name
4 From dba_tab_partitions aa
5 Where aa.table_name = 'TB_HXL_LIST';
COMPRESS PARTITION_NAME
-------- ------------------------------
DISABLED P_L1
DISABLED P_L2
DISABLED P_L3
DISABLED P_L4
SQL> Alter Table tb_hxl_list compress;
Table altered.
SQL> Alter Table TB_HXL_LIST
2 Move Partition P_L1 compress;
Table altered.
SQL> Select
2 aa.compression,
3 aa.partition_name
4 From dba_tab_partitions aa
5 Where aa.table_name = 'TB_HXL_LIST';
COMPRESS PARTITION_NAME
-------- ------------------------------
ENABLED P_L1
ENABLED P_L2
ENABLED P_L3
ENABLED P_L4
View 5 Replies
View Related
Feb 3, 2012
How to check whether database is dedicated or shared ?
View 1 Replies
View Related
Mar 20, 2013
quick check on a hidden parameter? I need to know the default value and possible values for _serial_direct_read on various releases, I have only 11.2.0.3 available right now and I'ld like to know this for 11.2.x.x 11.1.x.x, and 10.2.x.x.
Below is the query that will show what it is currently which (unless you've changed it) will be the default: auto for my 11.2.0.3. Then to see the options, try to set it to something wrong, my options are false/true/never/auto/always. I think false/true were the only choices for 10.2 and are maintained only for compatibility. But I can't rememebnr 11.1.
orcl>
orcl> set lin 80
orcl> select KSPPSTVL from x$ksppcv join x$ksppi using (indx) where ksppinm='_serial_direct_read';
KSPPSTVL
--------------------------------------------------------------------------------
auto
orcl>
orcl> alter system set "_serial_direct_read"=rubbish;
alter system set "_serial_direct_read"=rubbish
*
ERROR at line 1: ORA-00096: invalid value RUBBISH for parameter _serial_direct_read, must be from among false, true, never, auto, always
orcl>
update: added 11.1.x.x to my wishlist
View 10 Replies
View Related
Jul 23, 2010
Actually in our environment , sysadmins are upgrading java versions on Unix (HP & Solaris) Machines and they have asked the query whether oracle database uses Java or not?
If it uses then all the databases need to be shut down for their planned activities and if not no need to shutdown the databases.
View 17 Replies
View Related
Aug 6, 2010
would want to check which tables located in which datafiles.
Tried with this command:
select B.FILE_NAME from dba_segments a, dba_data_files b
where a.header_file=b.file_ID and segment_name='TABLE_NAME'
Seems like it only shows 1 datafile. Some tables are large and i suppose it's resided in more than 1 datafile.
View 1 Replies
View Related
Apr 29, 2012
which data dictionary view would you query to see the free extents in a tablespace.
i use dba_free_space;
SQL> select tablespace_name,sum(bytes),sum(blocks) from dba_free_space where
tablespace_name = 'SYSTEM' GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME SUM(BYTES) SUM(BLOCKS)
------------------------------ ---------- -----------
SYSTEM 9830400 1200
View 2 Replies
View Related
Jun 13, 2010
I need to display the parameter and status of DB for listener and Read Only.
I know those value could be get from command line , but could we get the values of Listener and Read only by SQL/PlSQL? So I can get it through the query of DB.
View 8 Replies
View Related
Feb 9, 2010
we have a few profiles in dba_profile. How to check what is the last modification date of the the resources inside each profile.
View 6 Replies
View Related
Oct 8, 2013
we are getting "snapshot too old" error when we executing a procedure. The error details are as below:
"ORA-01555: snapshot too old: rollback segment number 208 with .."
The code framework is below. basically we are having 3 cursor to select data and the base tables seems to update frequently. hence we understood that this error might come. if any other better way to restructure the below code framework.
Create or replace procedure proc1 as
CURSOR C1 is
select a from tab1
CURSOR C2 (p1 VARCHAR2)
is
select * from tab2;
[code]...
Since DBA not willing to change UNDO_RETENTION and other UNDO* parameters, we need to work around with restructure the code.
View 2 Replies
View Related
Sep 4, 2012
We are getting the below errors while migrating partitioned tables using expdp.
The source and target databases are both running on 10.2.0.5 and the main thing is source database doesn't have any active sessions. This is a clone of a Prod Database and no one is accessing it.
ORA-31693: Table data object "DPMMGR"."WHSE_CTNR_EVNT_W":"MSG_PRCS_N"."MSG_PRCS_N_DC556" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 31 with name "_SYSSMU31$" too small
ORA-31693: Table data object "DPMMGR"."RLTM_PRDCT_LOG":"RPL_20120814" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 14 with name "_SYSSMU14$" too small
Undo Tablespace has enough space but still the expdp is failing.
SQL>/
TABLESPACE Totalspace(MB) Used Space(MB) Freespace(MB) % Used % Free
--------------- --------------- -------------- ------------- ---------- ----------
UNDO01 145096 115338 29758 79.49 20.51
SQL> show parameter undo
[code]....
View 2 Replies
View Related
Jul 26, 2010
While creating the index we are getting the error "ORA-00603: ORACLE server session terminated by fatal error".
We have the space in tablespaces and also in the file systesm.
View 13 Replies
View Related
Jun 10, 2010
It's been 2 days from now that I'm bugging with this kind of error in my manual dB creation. I used DBCA first but we had a default directory settings, that's why I resorted to create the db manually. The dB was already created and currently has a status of STARTED.. I cannot mount it due to this error:
SQL> startup nomount
ORACLE instance started.
Total System Global Area 247463936 bytes
Fixed Size 2235856 bytes
Variable Size 177840688 bytes
[code]....
View 3 Replies
View Related
Mar 14, 2012
I am installing oracle10 10.0.4 in windows server 2008 r2 sp1. while installing it is getting installed and when i start creating the database after accepting the default steps finally . while crating the error i get TNS:protocal adaptor error.
View 3 Replies
View Related
Mar 30, 2012
Got following error while droping user
DB:9.2.0.8.0 OS:Windows 2003
SQL> drop user custom cascade;
drop user custom cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-21700: object does not exist or is marked for delete
View 1 Replies
View Related
Dec 9, 2010
I try to alter the SGA it give me error The Steps which I do show sga
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
[code]...
View 5 Replies
View Related
