Syntax and explanation on creating Oracle database links.

Oracle Subquery syntax and examples

Figure out how long a transaction will roll back.

Here is a neat SQL query that will show you the history of blocking locks on your Oracle database in the past 6 hours:   select j.sid, s.spid, s.serial#, j.log_user, j.job, j.broken, j.failures, j.last_date||’:’||j.last_sec last_date, j.this_date||’:’||j.this_sec this_date, j.next_date||’:’||j.next_sec next_date, (j.next_date – j.last_date) interval, j.what from (select djr.SID, dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, [...]

Saw this script on dba-oracle.com , very handy if you need to create a tiny database to test some parameter changes on. Make sure your environment is set: ORACLE_SID and ORACLE_HOME ( set it in /etc/oratab first for ease of use ). Use this template to create an inittestdb.ora (presuming your db name is testdb) [...]

How to get Oracle Text installed and configured on your database in case it isn’t installed by default: SQL> connect SYS/password as SYSDBA SQL> spool text_install.txt SQL> @?/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP NOLOCK SQL> connect CTXSYS/password@tns_alias SQL> @?/ctx/admin/defaults/drdefus.sql SQL> spool off connect SYS/password as SYSDBA set pages 1000 col object_name format a40 col object_type format a20 [...]

Scenario: Your backup shell script has a zip statement for older backup files to conserve disk space on your backup server. Your RMAN backup scripts however contain a crosscheck command which effectively removes the metadata about the compressed backup pieces from the controlfile. (Let’s presume no recovery catalog is being used). Your problem comes in [...]

Avoid installation time, just clone Oracle Home to your new server..

This is useful when you find that a runaway ghost process is hogging CPU or memory and you can see what the OS level sid is, but you have no idea which job it is related to

Command to check Centos Release: cat /etc/*release*

Need to see how many unique ORA- errors your alert log contains ? cat alert_DBID.log | grep ORA- | sort | uniq That’s all there is to it..

List of companies who offer Oracle database hosting

Oracle Magazine: free subscription

Read up here to find solutions to common db link setup mistakes and errors.

How many database links are in existence on your database, how many are in use ?

Limit the number of allowable db links per session for your database

Want to spare yourself the db link syntax in daily use ?
Hide it with a view or a synonym.

Drop Oracle database link syntax

Syntax to use an Oracle database link

Oracle Database Links Background information

oracle standby databases: quick command reference

Use grep to kill a range of similar oracle processes

Open Cursors
What they are, How to manage them,
Resolving ORA-01000

Identify the oracle session whose shadow process is using excessive resources, with these ‘Match os process to oracle session’ instructions

Use this unsupported but useful package to log your user error code in the database’s alert.log

How to compile another Oracle user schema.

NFS Mount a linux server’s filesystem to another server

sqlplus bindvariables example

demodrop.sql syntax

demobld.sql syntax

Sqlplus demo table setup

A foreign key is one of the different types of Oracle constraints.

Oracle Functions explained

The Oracle Delete statement allows you to remove rows from a table.

Look at these examples of the Oracle SQL Update statement

The Oracle Insert statement allows you to add data to a table using the following syntax:

This tutorial will show you how to use the correct syntax to query data in the Oracle database.

SQL is short for Structured Query Language.

It is the language you will use to access any data structure or its data in the Oracle database.

Finding Large Directories and files on SunOs

Your listener.log file can get quite large if you do not make it part of your housekeeping scripts, and renaming it while the listener is running is not a good idea.

Here is the method to follow to clean it up while your listener is still running.

quick sql to determine your database size exluding tempfiles.

Kill a bunch of related orphan Oracle processes with one command. Permalink — click for full blog post Related Blogs Related Blogs on kill oracle process Related Blogs on oracle process

We’ve partnered with Insight24 to bring you great free technology-focused webcasts and videos. Permalink — click for full blog post Related Blogs Related Blogs on technology webcasts

Quick tutorial to download, install and use Free VNC for Linux. This will give you ‘remote desktop’-type functionality on UNix. Permalink — click for full blog post

Quick page to remind myself what options to use with the linux du command to summarize the list of directories, not all the files. Permalink — click for full blog post

Installing and running vsftpd on your CentOS 4.5 server Permalink — click for full blog post

Use these steps to publish oracle awr reports regularly and mail them to yourself Permalink — click for full blog post

Automate the transfer of files across servers with this oracle ftp script. Permalink — click for full blog post