11g – using the PIVOT and UNPIVOT commands

Posted by Gavin Soorma
On June 24, 2009
0 Comments

11g has a very good new feature for SQL*PLUS called PIVOT and UNPIVOT - it is very useful for creating cross tabular reports and aggregates. In earlier Oracle versions this would have needed a lot of coding using the DECODE function .... Objective - find the count of employees in each department broken down by […]

Posted by Gavin Soorma
On June 24, 2009
0 Comments

11g New Feature – Standby Database creation from active database without having to take a backup …. Note the command output – APEXDG is created by copying the datafiles directly from an open database APEX without having to restore the backup from tape RMAN> run { allocate channel c1 type disk; allocate auxiliary channel c2 […]

Posted by Gavin Soorma
On June 24, 2009
1 Comments

By using a combination of Oracle 10g Block Change Tracking and RMAN Optimised Incremental Backups we can significantly reduce backup durations and more importantly restore and recover time. The optimised incremental backup will ensure that subsequent incremental backups after a full backup only contain blocks changed since the last backup and will also apply archive […]

Posted by Gavin Soorma
On June 23, 2009
2 Comments

If LOB data is less than 4000 bytes, then it can be stored in line or on the same database page as the rest of the row data. LOB data can be stored in line only when the block size is large enough to accommodate it. LOB data that is stored out of line, on […]

Posted by Gavin Soorma
On June 23, 2009
1 Comments

This script will query the RMAN catalog and report on the backup status of every database in the catalog. set lines 80 set pages 250 ttitle "Daily Backup........" select DB NAME,dbid, NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP, NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP from ( select a.name DB,dbid, decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db, decode(b.bck_type,'L', max(b.completion_time)) BACKUPTYPE_arch from rc_database a,bs b where a.db_key=b.db_key […]

Posted by Gavin Soorma
On June 23, 2009
1 Comments

I have quickly thought about some of the events which had caused unplanned downtime at my past organisation. Idea is to share the same and ensure our current monitoring infrastructure is taking into account aspects discussed below – better to learn from other’s experiences than to experience it yourself. Listener log file grew > […]

Posted by Gavin Soorma
On June 23, 2009
1 Comments

Oracle 10g has simplified one important aspect of tuning which is the wait event interface by broadly classifying wait events into 8-10 major classes This will give us a good overview of which area is contributing the most to a performance problem and enable us to concentrate our efforts in those areas. While […]

Posted by Gavin Soorma
On June 22, 2009
0 Comments

Purpose: Use the following procedure to generate the 10g SQL Tuning Advisor reports and recommendations from the command line when Database Control or Grid Control GUI is not available. CREATE A SQL TUNING TASK DECLARE my_task_name VARCHAR2 (30); my_sqltext CLOB; BEGIN my_sqltext := ‘INSERT INTO BWINT.PAYSLIP_CURRENT_RUN (ASSIGNMENT_ID, FULL_NAME, ASSIGNMENT_NUMBER, LOCATION_CODE, PAYROLL_ACTION_ID,PAY_DATE) […]

Posted by Arjun Raja
On June 22, 2009
0 Comments

In 11g – Passwords are case sensitive but this can be controlled by an init.ora parameter – SQL> show parameter SEC_CASE_SENSITIVE_LOGON ; NAME TYPE VALUE ———————————— ———– —————————— sec_case_sensitive_logon boolean TRUE SQL> create user arjun identified by ARJUN; – Password in Upper Case. User created. SQL> grant create session to arjun; Grant succeeded. SQL> conn […]

Posted by Arjun Raja
On June 22, 2009
0 Comments

Active Standby : PRIMARY DB- APEX STANDBY DB- APEXDG Now you can bring up the standby database in read-only mode and continue to have the standby database updated from the primary. Here are the steps to achieve that: 1. Stop the managed recovery process on STANDBY database : In APEXDG ( Standby database ) SQL […]

Posted by Gavin Soorma
On June 22, 2009
0 Comments

Very often, with packaged vendor applications we do not have access to the source code, but would like to customise the application to some specific requirements or in many cases would like an optimised piece of SQL code to be executed which is actually sub-optimal when run directly via the vendor application. We can use […]

Posted by Arjun Raja
On June 22, 2009
0 Comments

find /u01/local/logs -mtime +30 -exec ls -l {} \; find /u01/local/logs -mtime +30 -exec rm {} \;

Posted by Gavin Soorma
On June 21, 2009
0 Comments

CREATE OR REPLACE PROCEDURE SEND_MAIL ( msg_to varchar2, msg_subject varchar2, msg_text varchar2 ) IS c utl_smtp.connection; rc integer; msg_from varchar2(50) := ‘Gavin’; mailhost VARCHAR2(30) := ‘harpoon.hq.emirates.com’; — local database host BEGIN c := utl_smtp.open_connection(mailhost, 25); — SMTP on port 25 utl_smtp.helo(c, mailhost); utl_smtp.mail(c, msg_from); utl_smtp.rcpt(c, msg_to); utl_smtp.data(c,’From: Oracle Database’ || utl_tcp.crlf || ‘To: ‘ || […]

Posted by Gavin Soorma
On June 21, 2009
2 Comments

For example – to find all files > 100 MB in size find . -size +200000 -exec ls -l {} \;

Posted by Gavin Soorma
On June 20, 2009
2 Comments

select trunc(completion_time) rundate ,count(*) logswitch ,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)" from v$archived_log group by trunc(completion_time) order by 1;

Posted by Gavin Soorma
On June 20, 2009
0 Comments

In order to prevent you from the task from running a recompile script several times so as compile all objects that are dependant on other objects, we need to take into account the dependency of objects – this is maintained by the view DEPENDENCY$ in the SYS schema. This script was taken by me from […]

Posted by Gavin Soorma
On June 19, 2009
0 Comments

CREATE GLOBAL SCRIPT full_backup { BACKUP DATABASE PLUS ARCHIVELOG; DELETE FORCE NOPROMPT OBSOLETE; } Script can also be created from existing files as well …… CREATE GLOBAL SCRIPT full_backup FROM FILE ‘backup_db.rcv’; For each registered database then : RUN { EXECUTE GLOBAL SCRIPT full_backup; }

Posted by Gavin Soorma
On June 19, 2009
0 Comments

By using the procedure shown below, we can replicate a ‘master’ copy of an Oracle software installation on any number of target servers. Not only will it ensure that Oracle software installations are standardised and include all the required patches, but will save considerable time and effort required to stage the software on multiple servers. […]

Posted by Gavin Soorma
On June 19, 2009
0 Comments

This example takes VPD a bit further and also restricts access at the column level as well as shown in the example below – the earlier example will work in 8i and 9i, but this is an enhancement in 10g Release 2. SQL> conn sysadmin_vpd/sysadmin Connected. CREATE THE SECURITY FUNCTION SQL> create […]

Posted by Gavin Soorma
On June 18, 2009
1 Comments

CREATE A PROGRAM begin dbms_scheduler.create_program ( program_name => ‘UPDATE_VERITAS’, program_type => ‘EXECUTABLE’, program_action => ‘/opt/oracle/scripts/blade08/update_veritas.sh’, enabled => TRUE, comments => ‘Check the Time’ ); end; / CREATE A SCHEDULE begin dbms_scheduler.create_schedule ( schedule_name => ‘EVERY_6_HOURS’, repeat_interval => ‘FREQ=HOURLY; INTERVAL=6?, comments => ‘Every 6 Hours’ ); end; / CREATE A JOB begin dbms_scheduler.create_job […]

Page 3 of 41 234

News

11g – using the PIVOT and UNPIVOT commands

11g Standby Database creation without a backup

10g optimized incremental backups to disk

LOB's – some Performance Tuning considerations

Query the RMAN catalog to check backup status

Some top causes for unplanned outages

10g Wait Event Classes and Histograms

How to manually submit a 10g SQL Tuning Advisor task

11g Passwords can be case sensitive

11g ACTIVE STANDBY

DBMS_ADVANCED_REWRITE – tuning without touching the code

List and remove all files older than 30 days

Using UTL_SMTP to send email alerts and Resumable Operations

How to find files based on size in Unix

Script- Track redo generation by day

Recompiling Objects based on dependencies

Using GLOBAL Scripts in RMAN

Cloning an existing ORACLE_HOME on Unix

Virtual Private Database – part 2

Using DBMS_SCHEDULER to run a UNIX shell script