Pubudu: Data and Decisions

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: 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; M...

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.     SQ...

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 SQL> connect SYS as SYSDBA SQL> RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00' USING BACKUP CONTROLFILE;

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;

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

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;