UTL_FILE_DIR path test

To test if the utl_file_dir path in the init.ora file can indeed be written to . Login as sysdba – 1. Create a test procedure CREATE OR REPLACE procedure utl_file_test_write ( path in varchar2, filename in varchar2, firstline in varchar2, secondline in varchar2) is output_file utl_file.file_type; begin output_file := utl_file.fopen (path,filename, ‘W’); utl_file.put_line (output_file, firstline); […]

Import partition data

A partition in a partitioned table was truncated by mistake and data needs to be imported from an export dump. However instead of importing the data directly into the original table, the plan is to import the partition data into the temporary user and ratify the data before importing it to the original table using […]

Delete duplicate rows in a table

In case you want to identify duplicates and remove them from a table. 1. Identify duplicates. select count(*) from RF_BUSINESS_UNIT_HISTORY WHERE ROWID IN (select rowid from RF_BUSINESS_UNIT_HISTORY MINUS select max(rowid) from RF_BUSINESS_UNIT_HISTORY GROUP BY CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME); COUNT(*) ———- 2528 2. 2258 duplicates exist – these can be deleted with command below – delete from RF_BUSINESS_UNIT_HISTORY WHERE […]

ORA-27123 and Operating System permissions

PERMISSION ON ORACLE FILES FOR OTHER USERS OTHER THAN SOFTWARE OWNERS TO ACCESS DATABASE. Login as another operating system user – gentest. Oracle software owner is oracle. gentest@’s password: gentest@imisd(devu001/u03/users/app/gentest) % sqlplus / SQL*Plus: Release – Production on Wed Dec 3 11:11:17 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. ERROR: ORA-01034: ORACLE […]

RMAN unregister database from catalog

1. Go to the database you wish to unregister and get its DBID: system@HERML> select dbid,name from v$database; DBID NAME —————– —————————— 2082566940 HERML 2. Connect to the recovery catalog-owning schema and determine the DB_KEY of the database in the recovery catalog: rman10@RMAN10P> select db_key,dbid,name from rc_database 2 where dbid = 2082566940; DB_KEY DBID NAME […]

10g RMAN restore on another machine

TASK: Restoring a tape backup onto another machine and bringing up the database. ORIGINAL DATABASE: RPSP ON ITLINUX25 AND TARGET IS RPSP ON ITLINUX26. PRE-REQUISITES: Make sure that the tdpo.opt file uses the same TDP library pointer on the new machine as the original machine – only then will RMAN restore the backup from the […]

10g Flashback and Oracle Data Guard

FLASHBACK INSTEAD OF DELAYING APPLYING OF LOGS ON STANDBY: If you have multiple standby sites, you may utilize the DELAY option in Data Guard to prevent physical/logical corruption or user errors in your primary database. For example If your primary database has only one standby database , a logical or physical corruption in the primary […]

Script – Temporary tablespace usage

— Listing of temp segments.— SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size […]

Data Guard switchover checklist

In some establishments, Dataguard switchovers are manual – Please perform these pre-requisite checks before undertaking a switchover to primary. 1. ON STANDBY SITE: SQL> select database_role from v$database; DATABASE_ROLE —————- PHYSICAL STANDBY…………….PROCEED. If you receive a reply like the one below, then do not proceed- you are most probably firing the sql command in the […]

Enable block change tracking

BLOCK CHANGE TRACKING From version 10.2 onwards, Oracle have provided a new tool which is very useful to reduce the time of RMAN incremental backups. Prior to 10.2, all incremental backups had to read every single block in the database, and if the block has changed, it was backed up. This meant the RMAN backup […]

Resize standby datafile if disk runs out of space on standby site.

PURPOSE: TO AVOID RECREATION OF STANDBY DATABASE IN CASE FILE IS NOT RESIZED ON STANDBY : ITLINUXDEVBLADE07-PRIMARY Database is DGTEST9i [oracle@itlinuxdevblade07 dgtest9i]$ df -h . Filesystem Size Used Avail Use% Mounted on /dev/cciss/c0d0p2 25G 21G 2.0G 92% /opt [oracle@itlinuxdevblade07 dgtest9i]$ 2 gb freespace on disk on PRIMARY. ITLINUXDEVBLADE08 [oracle@itlinuxdevblade08 oradata]$ df -h . Filesystem Size […]

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?