Delete rows except MIN DATE

If you  want to write a delete query for a situation such as below..

Table : DBG.PUBS

Columns are CODE , PRICE , DATE1 , DATE2

001   50    05-JAN-11   05-FEB-11
002  100  01-JAN-11   06-MAR-11
003  40    05-APR-11   06-MAY-11

If you want to delete all the rows except the row which has the oldest DATE1...

you can use below query..

DELETE FROM DBG.PUBS
WHERE date1 != (SELECT MIN(date1)
                FROM DBG.PUBS);

ORA-01555: snapshot too old

ORA-01555:

snapshot too old: rollback segment number string with name "string" too small

Cause:

Rollback records needed by a reader for consistent read are overwritten by other writers

Action:

If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments

Solution 1: Increase UNDO TABLESPACE and retry

Solution 2:Increase undo_retention setting

show parameter undo_management

ALTER SYSTEM SET UNDO_RETENTION = 2400;
alter system set undo_retention=2400 scope=both


-- Guarantee the minimum threshold is maintained.
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;


SELECT tablespace_name, retention FROM dba_tablespaces;


TABLESPACE_NAME RETENTION
-----------
SYSTEM NOT APPLY
UNDOTBS1 GUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
USERS NOT APPLY




---------------------------------------------------------------------------------------------------------------



SQL> select max(maxquerylen) from v$undostat;


MAX(MAXQUERYLEN)
62057


SQL> show parameter undo


NAME TYPE
--------------------------------
VALUE
undo_management string
AUTO
undo_retention integer
25000





SQL> select (62057/60)/60 query,(25000/60)/60 retention from dual



     QUERY        RETENTION
----------           ----------
17.2380556     6.94444444







In above example query exectuing tenure is 17 hours while undo retention is approximate 7 hours,so change retention period to 20% extra with  query execution tenure and then check the query again.


At least set undo retention to 75000

Jboss Error - .....Couldn’t clean volatile data: ORA-00376: file 4 cannot be read at this time

Database just suddenly crash and when you try to login to sqlplus to shutdown immediate and then you get the following error:
ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], []
, [], [], [], [], []


If so, then startup the database. The startup is normal without giving any error but when you startup the jboss it will give an error like below


org.quartz.SchedulerConfigException: Failure occured during job recovery. [See nested exception:  org.quartz.JobPersistenceException: Couldn’t clean volatile data: ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: ‘u01\app\oracle\oradata\ORADB\USERS01.DBF’
[See nested exception: java.sql.SQLException: ORA-00376: file 4 cannot be read at this time
 ORA-01110: data file 4: ‘u01\app\oracle\oradata\ORADB\USERS01.DBF’


Solution


    SQL> recover datafile ‘u01\app\oracle\oradata\ORADB\USERS01.DBF’
    Media recovery complete.
    SQL> alter database datafile ‘u01\app\oracle\oradata\ORADB\USERS01.DBF‘ online;

Database open fails! :ORA-16038,19809, 00312

ORA-16038: log one sequence 3144 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312 online log 1 thread 1 <path to redo log file>


If above error occurs....


You need to do this in RMAN, not SQLPlus. The following commands will delete your archivelogs from your flash recovery area so please make sure that everything is backed up first:

RMAN> connect target sys/*******
RMAN> delete archivelog all;
RMAN> crosscheck archivelog all;

Then try to open the database.

Recover a database until a given time

Recover a database until a given time

SQL> connect SYS as SYSDBA
SQL> RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00' USING BACKUP CONTROLFILE;

Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode

The following statement brings the specified datafile online:
ALTER DATABASE DATAFILE '/u01/oracle/rbdb1/users01.dbf' ONLINE;

To take the same file offline, issue the following statement:
ALTER DATABASE DATAFILE '/u01/oracle/rbdb1/users01.dbf' OFFLINE;

View Triggers in an Oracle Database

Use below query to check Triggers in an Oracle database.

select  
 TABLE_OWNER,
 TABLE_NAME,
 TRIGGER_NAME,
 TRIGGER_TYPE,
 TRIGGERING_EVENT,
 STATUS
from  dba_triggers
order by TABLE_NAME, TRIGGER_NAME

Oracle Database Size

You can roughly find your Oracle database size from the below query...

select (select sum(bytes)/1024/1024 from dba_data_files)+
(select sum(bytes)/1024/1024 from dba_temp_files) "Size in MB" from dual;