Oracle RMAN Backup Script In Oracle Linux

This is rman.sh And this is RMAN configuration,

Oracle Database in Oracle Linux Cannot Start / Open

Today I mess around with the Oracle database that can not start and open. I get some error messages from various places such as alert.log, sqlplus, rman. These errors are, I have do this step : 1. replace the  current spfile with the old one 2. startup with pfile 3. restore and recover database using [...]

Cancel Oracle Trigger to Execute The Commands

Hi, I want to cancel the trigger of a table that does not work if the logic condition is not met. So as if I want the trigger to rollback (for example = cancel delete in before-delete trigger). At first, the trigger like this : CREATE OR REPLACE TRIGGER MIS.TRIG01_TABLE_A BEFORE DELETE ON PANDAZEN.TABLE_A REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN INSERT INTO PANDAZEN.TABLE_B SELECT [...]

Oracle Predefined PL/SQL Exceptions

Exception ORA Error SQLCODE Raise When … ACCESS_INTO_NULL 06530 -6530 A program attempts to assign values to the attributes of an uninitialized object CASE_NOT_FOUND 06592 -6592 None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause. COLLECTION_IS_NULL 06531 -6531 A program attempts to apply collection [...]

Undo Retention

To set the minimum undo retention period: Set UNDO_RETENTION in the initialization parameter file. UNDO_RETENTION = 1800 Using the ALTER SYSTEM statement to change UNDO_RETENTION immediately. ALTER SYSTEM SET UNDO_RETENTION = 1800; Be careful, once you change UNDO_RETENTION parameter, the effect will be immediately and it effected undo tablespace, so make sure your disk have [...]

staticports.ini

When installing Oracle Application Server 10g Release 2 (10.1.2.0.2) you can control the ports configuration using staticports.ini. A sample default staticports.ini show below, # staticports.ini – default # J2EE and HTTP Server Oracle HTTP Server port = 7777 Oracle HTTP Server Listen port = 7777 Oracle HTTP Server SSL port = 4443 Oracle HTTP Server [...]

Select Top-N in Oracle

Use DENSE_RANK to select top-n of a table in Oracle SELECT * FROM (SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DENSE_RANK () OVER (ORDER BY SALARY DESC) TOPRANK FROM HR.EMPLOYEES) WHERE TOPRANK <= 3

Check duplicate index

Rule for good index : If 2 indexes ( I1 and I2 ) exist for a table and    the number of columns in Index I1 is less or equal to the number of column in index I2 and    index I1 has the same columns in the same order as leading columns of index [...]

Reset ias_admin password

If you still remember what your last ias_admin password then to change it, you can use emct, eg: emctl set password <old_password> <new_password> or login to Oracle Enterprise Manager <version>- Application Server Control then click on Preferences on top right to open “Change Password” screen. If you have forgot what the password then you must [...]

Select Last Row in Oracle

SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = (SELECT MAX (EMPLOYEE_ID) FROM HR.EMPLOYEES);

Select Row X to Y in Oracle

the fastest query, SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM HR.EMPLOYEES ORDER BY EMPLOYEE_ID) A WHERE ROWNUM <= :Y) WHERE RN >= :X; another query, 1.SELECT * FROM (SELECT EMP.*, ROWNUM RN FROM HR.EMPLOYEES EMP WHERE ROWNUM < :Y + 1 ORDER BY EMPLOYEE_ID) WHERE RN BETWEEN :X AND :Y; 2.SELECT [...]

Can’t initialize OCI. Error -1

It appears when connect to Oracle and make login fail. The solution to try is to check where is ORACLE HOME and TNS ADMIN location

OCI.dll could not be loaded in PHP-Apache

I’ve installed XAMP 1.6.6a  and running of apache 2.2.8 and PHP 5.2.5 versions. I also install oracle instant client 10.2.0.1.0 so i want to connect to oracle via php using oci.  I got this message when i start apache Apache.exe This application has failed to start because oci.dll was not found. Re-installing the application may [...]

REP-0118: Unable to create a temporary file

Almost the same problem i have found when execute query using form applet when i got this message FRM-41838: Unable to open temporary record buffer file %s.  And then i got this message when i run report. Here the message, REP-0118 Unable to create a temporary file The solution i found that maybe help for [...]

FRM-41838: Unable to open temporary record buffer file %s.

Today, I got this error message when execute query in form applet FRM-41838 Unable to open temporary record buffer file C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\1\s3n4.1 ORA-01403: no data found The result of my search about this message explain this, FRM-41838: Unable to open temporary record buffer file %s. Cause : Unable to open file used as temporary record buffer. [...]

Shrink Table

To shrink an object ALTER TABLE employees SHRINK SPACE [CASCADE] To shrink a LOB (must be issued separately since SHRINK SPACE CASCADE does not cascade to LOB) ALTER TABLE employees MODIFY LOB(resume) (SHRINK SPACE) Shrink the overflow segment of an Index Organized Table (IOT) ALTER TABLE employees OVERFLOW SHRINK SPACE

Use DBMS_SCHEDULER to replace DBMS_JOB

DBMS_JOB has been around forever, and now it is deprecated. Although DBMS_JOB still exists in 10g and 11g, but only for backward compatibility. No new features are being added to dbms_job and you will likely quickly run into its limitations. Oracle recommends the use of DBMS_SCHEDULER in releases 10g and up. DBMS_SCHEDULER is a much [...]

How to clear Oracle Enterprise Manager’s Alert

first, check this – SELECT * FROM SYSMAN.MGMT_CURRENT_SEVERITY ORDER BY COLLECTION_TIMESTAMP if the result is that you want to delete then go with this sql, – DELETE SYSMAN.MGMT_CURRENT_SEVERITY ^_^

History startup & shutdown DB

1. create table to store history startup – shutdown DB CREATE TABLE WI.TMP_DATABASE_ACTIVITY ( USER_NAME      VARCHAR2(20 BYTE), USER_TIME        DATE, DESCRIPTION  VARCHAR2(30 BYTE) ) TABLESPACE TBLSPC_01; 2. create trigger after startup CREATE OR REPLACE TRIGGER WI.TRIG_STARTUP AFTER STARTUP ON DATABASE BEGIN INSERT INTO TMP_DATABASE_ACTIVITY VALUES (USER, SYSDATE, ‘STARTUP’); END; / 3. create trigger before [...]

Manipulate NULL column values for fast sql performance

By default, NULL values means “not present”. Therefore, Oracle indexes will not include NULL values. If you wonder about how slow your sql query, you must check whether your sql include where condition that use IS NULL. When you use IS NOT NULL, your sql run normally. But if where condition include IS Null, sql [...]