Quick Tips File

Last Updated 11-Sep-14

UNIX Tips

Cron Jobs
If you want to see all your cron jobs just type  crontab -l
If you want to edit a cron jobs just type  crontab -e
Lines that begin with "#" are considered comments and are ignored.
An environment setting is of the form
    NAME = VALUE
Cron will look at a special environment variable, MAILTO. Any output generated by your cron jobs will be sent to the address specified by MAILTO (if it is not specified it will be sent to the owner of the crontab). If MAILTO is defined by empty (MAILTO=""), no mail will be sent.
The format of the cron table entry includes five (5) time fields followed by a command. Commands are executed when the time specified by the date fields matches the current time. The five time fields are as follows:

Field       Allowed Values
minute        0-59
hour          0-23
day of month  0-31
month         1-12 (or names, see below)
day of week   0-7 (0 or 7 is Sun, or use names)

A field may be an asterisk (*), which indicates all values in the range are acceptable. Ranges of numbers are allowed, i.e. "2-5" or "8-11", and lists of numbers are allowed, i.e. "1,3,5" or "1,3,8-11". Step values can be represented as a sequence, i.e. "0-59/15", "1-31/3", or "*/2".
Names can be used for the "month" and "day of week" fields. Use the first three letters (case-insensitive) of the particular month or day. Ranges or lists of names are not allowed.
Examples:
MAILTO="someone@somewhere.com"
15 1 * * *   [COMMAND]
Explanation: executes the command [COMMAND] at 1:15 AM every day

40 23 1 * *   [COMMAND]
Explanation: executes the command [COMMAND] on the first of every month at 11:40 PM

0-30/10 9,17 * * 1-5   [COMMAND]
Explanation: executes the command [COMMAND] on Monday-Friday (1-5) every 10 minutes for the first half hour (0-30/10) of the 9 AM and 5 PM hours (9,17)

0 */4 * jan sun   [COMMAND]
Explanation: executes the command [COMMAND] on each Sunday in January at midnight, 4 AM, 8 AM, noon, 4 PM, and 8 PM

30 4 1,15 * fri   [COMMAND]
Explanation: executes the command [COMMAND] at 4:30 AM on the 1st and 15th of each month, plus every Friday

0 0 19 8 *   [COMMAND] or
0 0 19 aug *   [COMMAND]

esta es una linea de ejemplo:
00 20 * * * /usr/bin/find / -name core -exec rm -f {} \;
minute hour monthday month weekday command
 

Deleting Files by 'n' Dates
There are three times associated with a file
    atime  - last access time
    ctime  - last status change time
    mtime  - last modify time

Remove all files from /home/dpafumi older than 5 days:
find /home/dpafumi -type f -mtime +5 -exec rm -f {} \;

Print out what files from /home/dpafumi older than 5 days:
find /home/dpafumi -type f -ctime +5 -print

Finding the top 5 largest files
find . -ls | sort -nrk 7 | head -5

Find files larger than 100MB
find . -size +100000k

Delete audit records that’s older than 30 days
find $ORACLE_HOME/rdbms/audit -name "*.aud" -mtime +30 -exec rm {} \;

Delete files in /tmp that’s older than 30 days
find /tmp -group dba -type f -mtime +5 -exec rm -f {} \; find /tmp/dba -group dba -type f -mtime +5 -exec rm -f {} \;

Delete *.trc files more than 5 days old
find $TRACE_DIR -name '*.trc' -type f -mtime +5 -exec rm {} \;

Display top 5 largest files in a current directory
ls -alS | head -5

Largest space-consuming directories (Does not include sub-directories)
du -Sm . | sort -nr | head -5

Report the sum of space consumed by a directory and its subdirectories
du . | sort -nr | head -5

List files in a directory greater than 300M
ls -l | awk '{if ($5 >314572800) print $0}' |sort +4


Switching ORACLE_HOME and ORACLE_SID in Unix.
Have you ever had the need to change your environment settings to connect to another database on the same server? The following Unix shell script will ask you which database you want to connect to, and export your new environment variables. You may need to add additional variables, depending on your system and application.
#!/bin/ksh
echo " "
echo "Enter the Oracle database you want: "
echo " "
echo " 1. PROD 8.0.5.2.1 "
echo " "
echo " 2. TEST 8.1.6.0.0 "
echo " "
echo " 3. DEV 7.3.4.1.0 "
echo " "
read TermType
case $TermType in
1) export ORACLE_SID=prod
export ORACLE_HOME=/u01/app/oracle/product/8.0.5
export LD_LIBRARY_PATH=/u01/app/oracle/product/8.0.5/lib;;
2) export ORACLE_SID=test
export ORACLE_HOME=/u01/app/oracle/product/8.1.6
export LD_LIBRARY_PATH=/u01/app/oracle/product/8.1.6/lib;;
3) export ORACLE_SID=dev
export ORACLE_HOME=/u01/app/oracle/product/7.3.4
export LD_LIBRARY_PATH=/u01/app/oracle/product/7.3.4/lib;;
esac
echo
echo Setting ORACLE_SID to $ORACLE_SID
echo Setting ORACLE_HOME to $ORACLE_HOME
echo Setting LD_LIBRARY_PATH to $LD_LIBRARY_PATH
echo
echo
 

- How to find O/S VERSION or system name
        uname -ap

- Different uses of 'ps -ef'
         ps -ef | grep smon &nb sp;  (Checks for databases)
        ps -ef | grep ows (Checks fo r web listeners)
         ps -ef | grep f45 (Checks f or Form listeners)

- Finding process id for trace or other process
         ps -fu username

- Zipping up a directory for backup
         1. zip -r file [Directory], or
         2. zip -r file *fresh* (This would zip up all files with fresh in the name, plus any directories, and all dirs underneath with fresh in the name.

- How to tar a directory
         tar -cvf /tmp/filename *
         do this from the top direct ory that you want to bundle into the tar'd file

 - using 'tar' to list contents of tar file, without extracting
         tar -tvf [filename]


SETUP Display
If you have problems with the display, try the following in your startup file:
  if ( $?DISPLAY == 0 ) setenv DISPLAY `who -m | cut -f 2 -d "(" | cut -f 1 -d ")"`:0.0

- To copy a directory structure (Directory, sub-directories, and all their files)
        cp -rp

- Checking space on a disk
        df -tk

- Show size of directories (All files under a specific directory)
         du -sk

- Useful grep options
         grep -c  This counts the number of lines that contain the pattern
         grep -i  Ignore upper/lower case
         grep -l  Print only the name of the files that contain the pattern. Does not repeat file names.

- Count the number of files in a directory
         ls | wc -l

- How to send a message to everyone on a UNIX machine
         1. Create a file that contains the message
         2. $ wall < [filename] ( Ex: wall < message )
         3. You may need to specify the directory 'wall' is in. /usr/sbin/wall < message

- Removing some files to free up space
         1. Go to the destination directory
         2. Run 'find . -name "*.*O" -print'. Make sure this returns files you want to delete
         3. Run 'find . -name "*.*O" -exec rm -f {} \;
         WARNING! Make sure you do this right or you can remove more files that you want.

Find Memory in CPU
/usr/sbin/prtconf
or
dmesg | more


- To find the printers available on a server
         lpstat -p

- rcp: Setting up UNIX system to do a remote copy from another box
          1. Create or modify file '.rhosts' in your login's home directory
          2. Add a line such as...
             machinename.us.oracle.com loginname
          Example
            apptest15.us.oracle.com applmgr
          3. telnet to the machine, and login as user specified in .rhosts
          4. issue following command:
             rcp -r * login_to_your_machine@yourmachine:/directory_to_copy_to
          example:
             rcp -r * stuck@apptest9:/u01/stuck/

- How to do a search and replace in vi
          :%s,[string to replace], [replacing string], g
             example: :%s,oracle,stuck,gc {would replace oracle with stuck}
          The above command does this for the whole document without confirming, if
          you would like to confirm each change use the command:
             :%s,[string to replace], [replacing string], gc
          This will stop after each search, type a y to confirm the change


  - Stripping control character (^M) from an ascii file
          Sometimes, in a patch, you will receive a file that was created in NT, so on
          Solaris it has a ^M at the end of each line. This can cause problems running
          the script sometimes. To strip out the ^M use the following
              cat filename | tr -d ^M > newfilename
             NOTE: When typing the ^M in the command above use Cntl V Cntl M to type it

- Cleaning up memory and semaphores when a db process is killed, rather than being shut down via svrmgrl
          If a database process id is killed, rather than being shutdown properly via svrmgrl, then it will leave memonry and semaphores,
          which may prevent the db from being recreated. Do the following to clean it up.
          1. Run the UNIX command, 'ipcs -a'. You may want to stretch your screen as wide as it will go to get all the data returned to be on one line.
          2. Look for the line where the value for the column NATTCH is 0. This is the Memory for your killed process that you need to delete.
          3. Get the id# for that line from the ID column (first column)
          4. Run the command 'ipcrm -m id#' to delete this memory
          5. Figure out which line in the Semaphores zone is for your database.
          If there are multiple lines, you may be able to determine which one by comparing the value in the NSEMS column to value of 'processes=XX' in your init.ora.
          If only one line matches, then that is the line you need to delete.
          If you delete the wrong line you will crash someone else's database.
          If you cannot find a distinct line, you may want to try shutting down databases on the system until you can get one distinct line.
          Once the line is identified, you need to remove it also.
          6. Again, get the id# for that line from the first column.
          7. Run 'ipcrm -s id#

- To lock archive file for updating
         co -l tele.html
- To unlock
        ci -u tele.html

Performing Loop Actions with Files

- Deleting Trace Files
for FILE in *.trc
do
  echo $FILE
  rm $FILE
done

- Rename Files
OLDSUFFIX=aaa
NEWSUFFIX=bbb
for FILE in *."$OLDSUFFIX"
do
  NEWNAME=´echo "$FILE" | sed -e "s/${OLDSUFFIX}\$/$NEWSUFFIX/"´
  mv "$FILE" "$NEWNAME"
done

- Set my filenames to lowercase
for FILE in *
do
  mv -i "$FILE" ´echo "$FILE" | tr '[A-Z]' '[a-z]'´ 2> /dev/null
done


Run Programs in background even if you disconnect
As a DBA, you have more than likely been faced with the need to run a command in UNIX that you know will take a long time to complete. Most often, you will want to run the command and "exit" from the terminal. When the shell exits though, it sends its children a SIGHUP signal, which by default causes them to be killed. All stopped, running and background jobs will ignore SIGHUP and continue running if their invocation is preceded by the nohup or if the process programmatically has chosen to ignore SIGHUP.
The nohup utility invokes the named command with the arguments supplied. When the command is invoked, nohup arranges for the SIGHUP signal to be ignored by the process.
Syntax
/usr/bin/nohup command [ argument ...]
    Processes run by /usr/bin/nohup are immune to SIGHUP (hangup) and SIGQUIT (quit) signals.

Examples:
To run a command in the background after you log off, enter:
$ nohup find / -print &

To run a command in the background and redirect the standard output to a different file, enter:
$ nohup find / -print >filenames &
This example runs the find / -print command and stores its output in a file named filenames.Now only the process ID and prompt are displayed:
677
$
Wait before logging off because the nohup command takes a moment to start the command specified by the Command parameter. If you log off too quickly, the command specified by the Command parameter may not run at all. Once the command specified by the Command parameter starts, logging off does not affect it.


- Finding the IP address for different systems
         grep -i finsun /etc/hosts

- How to find the tcpip address for a machine
         nslookup [machine name]


- Different examples of the FIND command

         find . -exec grep -lis fdugpi.lpc \{\} \\;
         (searches in this directory down)

         find / -exec grep -lis fdugpi.lpc \{\} \\;
         (searches from root directory down)

         find . -exec grep -lis file {} \;
         (searches this directory down)

         find . -follow -name bug734234 -print
         (use if there are linked sub-directories, such as in tcpatch)

         find . -name "*.pls" -exec egrep -il ARP_STANDARD {} \;
         (This searches all .pls files for a file that contains the string ARP_STANDARD)


Detecting 32-bit or 64-bit OS

On Linux
$uname -a
64 Bits
Linux gaylord.stata.com 2.6.11-1.27_FC3 #1 Tue May 17 20:24:57 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux

64 Bits
Linux caddo.stata.com 2.6.9-5.0.5.EL #1 SMP Fri Apr 8 14:20:58 EDT 2005 ia64 ia64 ia64 GNU/Linux

32 Bits
Linux tango.stata.com 2.6.10-1.771_FC2smp #1 SMP Mon Mar 28 01:10:51 EST 2005 i686 i686 i386 GNU/Linux



$uname -m
It seems like the uname -m actually gives
    * x86_64 when it is an kernel 64 bits
    * i686 for 32 bits kernel



$getconf LONG_BIT
which returns either 32 or 64

On Solaris
isainfo -b -v
/usr/bin/isainfo -kv
On AIX
$ getconf -a | grep KERN
$ file /usr/lib/boot/unix*
On HP-UX
/usr/bin/ getconf KERNEL_BITS
/usr/bin/file /stand/vmunix


Change HostName and IP Address on Linux

First IDENTIFY what you have:
Display current IP address and setting for network interface called eth0
# /sbin/ifconfig eth0

Output:

eth0      Link encap:Ethernet  HWaddr 00:30:48:5A:BF:46
inet addr:10.5.123.2 Bcast:10.5.123.63 Mask:255.255.255.192
inet6 addr: fe80::230:48ff:fe5a:bf46/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:728204 errors:0 dropped:0 overruns:0 frame:0
TX packets:1097451 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:62774749 (59.8 MiB) TX bytes:1584343634 (1.4 GiB)
Interrupt:177
You can change ip address using ifconfig command itself. To set IP address as 192.168.1.5, enter command:
# ifconfig eth0 192.168.1.5
To set IP address as 192.168.1.5 and the netmask to 255.255.255.0, enter command:
# ifconfig eth0 192.168.1.5 netmask 255.255.255.0 up

# /sbin/ifconfig eth0


Change the IP Address

METHOD 1
Open network configuration file. In this example, it’ll configure on interface eth0. Type
vi /etc/sysconfig/network-scripts/ifcfg-eth0

As an example, I'm showing that the current configuration is DHCP:
DEVICE=eth0
BOOTPROTO=dhcp
HWADDR=00:D0:B7:08:09:BB
ONBOOT=yes

Modify the file and perform the following modifications/additions (Change BOOTPROTO to static and add IP Address and Netmask as new lines if they’re not existed yet)
DEVICE=eth0
IPADDR=192.168.1.100
NETMASK=255.255.255.0
BOOTPROTO=static
HWADDR=00:D0:B7:08:09:BB
ONBOOT=yes
# The following settings are optional
BROADCAST=192.168.1.255   #Always Finish with 255
NETWORK=192.168.1.0       #Always Finish with 0
IPV6INIT=yes
IPV6_AUTOCONF=yes

Save and close the file. Define default gateway (router IP) and hostname in /etc/sysconfig//network file:
vi /etc/sysconfig/network

Append/modify configuration as follows:
NETWORKING=yes
HOSTNAME=machinename.domain.com
TYPE=Ethernet
GATEWAY=10.10.29.65


Save and close the file. Restart networking::
# /etc/init.d/network restart
or
#
service network restart

Make sure you have correct DNS server defined in /etc/resolv.conf file:
# vi /etc/resolv.conf

Setup DNS Server as follows:
nameserver 10.0.80.11
nameserver 10.0.80.12
nameserver 202.67.222.222

Save and close the file. Now you can ping the gateway/other hosts:
$ ping 10.0.80.12

You can also check for Internet connectivity with nslookup or host command:
$ nslookup yahoo.com

Review the configuration. Type
ifconfig


METHOD 2
You can also execute the following command from the X Window and it will show you the GUI Network Tool:
$ system-config-network &

METHOD 3
If you don’t have X windows GUI installed type the following command at shell prompt:
# system-config-network-tui &

Change the HOST NAME
Make sure you are logged in as root:
vi /etc/sysconfig/network

Look for the HOSTNAME line and replace it with the new hostname you want to use. In this example I want to replace localhost with redhat9.
HOSTNAME=redhat9

When you are done, save your changes and exit vi.

Next we will edit the /etc/hosts file and set the new hostname.
vi /etc/hosts

In hosts, edit the line that has the old hostname and replace it with your new one.
192.168.1.110 redhat9

Save your changes and exit vi.
The changes to /etc/hosts and /etc/sysconfig/network are necessary to make your changes persistent (in the event of an unscheduled reboot).

Now we use the hostname program to change the hostname that is currently set.
hostname redhat9

And run it again without any parameters to see if the hostname changed.
hostname

Finally we will restart the network to apply the changes we made to /etc/hosts and /etc/sysconfig/network.
service network restart

Finally, if you are using Oracle, remember to change your tnsnames.ora, listener.ora, etc


VMWare Image Clone Problem: eth0 Renamed As eth1
After cloning a VMWare image you may noticed that the Linux server renamed eth0 as eth1, eth1 as eth2 and so on.
This is due to wrong or duplicate mac address. To fix this problem login to your Linux server using console and type the following command:

mv /etc/udev/rules.d/70-persistent-net.rules /etc/udev/rules.d/70-persistent-net.rules.old
reboot

This file was automatically generated by the /lib/udev/write_net_rules program, run by the persistent-net-generator.rules rules file. Simple delete it and reboot the system to recreate the same. This should fix the problem:
# /sbin/ifconfig | grep "^eth"
Sample outputs:

eth0      Link encap:Ethernet  HWaddr 00:0C:29:F3:E4:14
eth1      Link encap:Ethernet  HWaddr 00:0C:29:F3:E4:1E

You need to remove the MAC address in /etc/sysconfig/networking-scripts/ifcfg-eth*. To find out current MAC address, enter:
# ifconfig -a | grep eth1 | grep HWaddr | awk '{ print $5}'
# ifconfig -a | grep eth2 | grep HWaddr | awk '{ print $5}'

Update your /etc/sysconfig/networking-scripts/ifcfg-eth0 with eth1 HWaddr (MAC address), enter:
# cat /etc/sysconfig/networking-scripts/ifcfg-eth0
Sample outputs:

 
DEVICE="eth0"
BOOTPROTO="dhcp"
NM_CONTROLLED="yes"
ONBOOT="yes"
HWADDR="00:0C:29:69:0C:4A"
 

Save and close the file. Restart the network service, enter:
# service network restart
You can verify new settings with the following commands:
# /sbin/ifconfig -a
# route -n


Note=
The relevant filename is slightly different in other Linux distributions. For example, in Debian, the filename is /etc/udev/rules.d/z25_persistent-net.rules





Add New Disk to VM Environments
Steps with Screen Shots

Adding a new Disk
1- Add new Hardware on the VM Console.
2- Adding the Disk to Linux System
To see all your disks and its size type:
# sfdisk -s
/dev/sda:  23068672
/dev/sdb:  31457280


Then, review each one of those partitions (just showing 1 example):
# fdisk /dev/sda
The number of cylinders for this disk is set to 3916.
There is nothing wrong with that, but this is larger than 1024, and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs   (e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): p    --> To see the current partition
Disk /dev/sdb: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1               1        3916    31455238+  83  Linux
Here I see that the full partition is used.
This utility works very much like the DOS utility of the old days and allows you to create and manage partitions. To create a new partition we enter the command n to create a new partition. This is going to be a primary partition p, and the first partition number 1. Because I want this disk to consume the full 12 GB I specified earlier we start at the first cylinder and end it at the last cylinder. We then want to write the partition table with the new partition we have just created so we enter the command w which writes the new table and exits fdisk.

Let's see the next one:
# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

The number of cylinders for this disk is set to 1566.
p

   Device Boot      Start         End      Blocks   Id  System

Here I see that this is empy, so I can proceed with this one:
Enter n, p, 1, specify the size (leave defaults to use the full disk)

n   --> To add a new partition
p   primary partition (1-4)   --> To define the Primary Partition

p
Partition number (1-4): 1
First cylinder (1-1566, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1566, default 1566):
Using default value 1566

Command (m for help): w   --> write table to disk and exit
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.


Now that we've create the partition, we now want to format the first with the new file system. I've decided to use ext3 filesystem for this disk
So, to format the new partition we enter the command mkfs -t ext3 /dev/sdb1. This command makes a new files system with the type t ext3 on the /dev/sdb1 partition, this is the first partition on the sdb disk.
# mkfs -t ext3 /dev/sdb1

3- Create the mount point
Determine where you want to add the new virtual disk you've created. I like to create this partition specifically for all Oracle related files, so I will call it /u02.
To do that we run mkdir /u02, just a simple make directory command. Once that is complete we then want to mount the newly created partition.
Because we haven't added the partition to the /etc/fstab yet we have to mount it manually. To do that we run
# mkdir /u02
# mount -t ext3 /dev/sdb1 /u02

Now if I perform a df -h, I will see that new disk:
# df -h
Filesystem            Size  Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00
                       20G   15G  4.2G  78% /
/dev/sda1             145M   12M  126M   9% /boot
tmpfs                 2.4G  1.3G  1.2G  53% /dev/shm
/dev/sdb1              30G   23G  5.2G  82% /u02


4- Open the fstab file
The fstab file holds all of the used disks and partitions, and determines how they are supposed to be used by the operating system. So we edit the file to add the newly created partition
# vi /etc/fstab

After we open the fstab file in the previous step we add the following line:
/dev/sdb1                /u02                   ext3    defaults        1 1

The first column is the partition name, the second is the default mount point, the third is the filesystem type.
The fourth is the mount options, in this case I used default which mounts the drive rw, suid, dev, exec, auto, nouser and asynchronous.
The 5th and 6th options are for the dump and fsck options. If dump is set to 1 the filesystem is marked to be backed up, if you are going to have sensitive material on the drive its a good idea to set it to 1. If fsck is set to greater than 1, then the operating system uses the number to determine in what order fsck should be run during start up. If it is set to 0 it will be ignored such as in the case of a cdrom drive since its a solid state disk. For more information on the fstab file check out this article: http://www.tuxfiles.org/linuxhelp/fstab.html
Lastly, we write and quit the file with the :wq command.

Finally I will make oracle the owner of that disk:
# mkdir /u02/oradata
# cd /u02
# chown oracle /u02/oradata
# chgrp dba /u02/oradata



Install or Application Setup Tips

- Checking OS block_size. Oracle Block_Size must be equal or multiple of this one
        df -g

- Pin Objects
1- As Internal Run:
    @dbmspool.sql
    @prvtpool.plb

2-Create the following Trigger
CREATE OR REPLACE TRIGGER db_startup_keep
AFTER STARTUP ON DATABASE
BEGIN
   sys.dbms_shared_pool.keep('SYS.STANDARD');
   sys.dbms_shared_pool.keep('SYS.DBMS_STANDARD');
   sys.dbms_shared_pool.keep('SYS.DBMS_UTILITY');
   sys.dbms_shared_pool.keep('SYS.DBMS_DESCRIBE');
   sys.dbms_shared_pool.keep('SYS.DBMS_OUTPUT');
END;

3- The following Oracle core packages owned by user SYS should be pinned in the shared PL/SQL area:
    DUTIL
    STANDARD
    DIANA
    DBMS_SYS_SQL
    DBMS_SQL
    DBMS_UTILITY
    DBMS_DESCRIBE
    DBMS_JOB
    DBMS_STANDARD
    DBMS_OUTPUT
    PIDL

4- Run the following Script to check pinned/unpinned packages
SELECT substr(owner,1,10)||'.'||substr(name,1,35) "Object Name",
                '  Type: '||substr(type,1,12)||
                '  size: '||sharable_mem ||
                ' execs: '||executions||
                ' loads: '||loads||
                ' Kept: '||kept
  FROM v$db_object_cache
  WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
--   AND  executions > 0
  ORDER BY executions desc,
           loads desc,
           sharable_mem desc;


How to delete a database midway through creation
1. Shutdown DB
2. delete lk.. and control files from $ORACLE_HOME/
3. delete log and dbf files
4. recreate db


 
Start DB, Listener at Boot Time
To make the database and listeners start up automatically when the server reboots and shut down automatically when the server shuts down, you’ll need to create a dbora file in /etc/init.d and link it to /etc/rc2.d and /etc/rc0.d. You’ll need to do this as the root user. First create a file called dbora in /etc/init.d as follows as root:

vi /etc/init.d/dbora

#!/bin/sh
###############################################
#    Database Automatic Startup / Shutdown
###############################################
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME from which you wish to execute dbstart and dbshut
# Set ORA_OWNER to the user id of the owner of the Oracle database in ORA_HOME.
#
# Remember to Edit the /etc/oratab file setting flag for each instance to 'Y' and remove empty lines

export ORA_DB_HOME=/apps/oracle/product/10.2.0/DB
export SH=/var/opt/oracle/sh
ORA_OWNER=oracle

if [ ! -f $ORA_DB_HOME/bin/dbstart ]
then
  echo "Oracle startup: cannot start"
  echo "Oracle startup: cannot start" > /tmp/dbstart.log 2>&1
  exit
fi

case "$1" in
 'start')
   # Start ASM, the Oracle database, listener and Web Control
   #su - $ORA_OWNER -c "$SH/start_shutdown_asm.ksh start" > /tmp/start_asm.log 2>&1
   su - $ORA_OWNER -c "$ORA_DB_HOME/bin/dbstart $ORA_DB_HOME" > /tmp/dbstart.log 2>&1
   su - $ORA_OWNER -c "export ORAENV_ASK=NO; export ORACLE_SID=NEWDEV;. oraenv; lsnrctl start" > /tmp/listener_start.log 2>&1
   #su - $ORA_OWNER -c "$ORA_DB_HOME/bin/emctl start dbconsole"
   ;;
 'stop')
   # Stop the Oracle database, listener and Web Control
   # The following command assumes that the oracle login will not prompt the user for any values
   su - $ORA_OWNER -c "$ORA_DB_HOME/bin/lsnrctl stop"
   #su - $ORA_OWNER -c "$ORA_DB_HOME/bin/emctl stop dbconsole"
   su - $ORA_OWNER -c $ORA_DB_HOME/bin/dbshut>/tmp/dbshut.log 2>&1
   #su - $ORA_OWNER -c "$SH/start_shutdown_asm.ksh stop" > /tmp/shutdown_asm.log 2>&1
   ;;
esac



Link the script:
ln -s /etc/init.d/dbora /etc/rc2.d/S99dbora     
ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora

Test the script created. To test the script created above, without rebooting do the following:
su - root
/etc/init.d/dbora start     (for startup)
/etc/init.d/dbora stop      (for shutdown)



- Umask and Permission
   You need to change the "umask" to the required 022 and set the permissions on all relevant directories.
    Example:
        # umask 022
        # cd $ORACLE_HOME
        # chmod 755 (on all sub-dirs)
        # cd $ORACLE_BASE/admin/(SID NAME)
        # chmod 755 (on all sub-dirs)

- TWO_TASK to another database
         1. Find Values from system you are connecting to...
             echo $ORACLE_SID,  echo $FNDNAM,   echo $GWYUID
             from the system you will be joining to.
         2. Set Environment Variables
             TWO_TASK=[net_alias]; export TWO_TASK
             FNDNAM=value from $FNDNAM; export FNDNAM
             GWYUID=value from $GWYUID; export GWYUID


Reading a Listener.Log File

The listner.log file provides useful information in terms of what App/Machine tried to connect to the DB using the Listener.
Analyzing it a little more, this is the logic used to save data into that file:
- Each New Entry is delimited by *
- A successful connection returns 0. Failure connection returns oracle error code.

The format of the file is:
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE

Example

TNSLSNR for Linux: Version 10.2.0.1.0 - Production on 20-MAY-2009 13:50:16

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Trace information written to /u01/app/oracle/product/10.2.0/db_1/network/trace/listener.trc
Trace level is currently 0

Started with pid=5933
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LINUXDB.imagesoft.fiserv.net)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
20-MAY-2009 13:50:16 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=hostname.domain.com)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
20-MAY-2009 13:50:46 * service_register * LINUXDEV * 0
20-MAY-2009 13:53:49 * service_update * LINUXDEV * 0
20-MAY-2009 13:53:53 * service_died * LINUXDEV * 12537
20-MAY-2009 13:54:00 * service_register * LINUXDEV * 0
20-MAY-2009 13:54:03 * service_update * LINUXDEV * 0
20-MAY-2009 13:54:05 * service_update * LINUXDEV * 0
20-MAY-2009 13:54:23 * service_update * LINUXDEV * 0
20-MAY-2009 13:54:47 * service_update * LINUXDEV * 0
20-MAY-2009 13:54:50 * service_update * LINUXDEV * 0
20-MAY-2009 13:55:02 * service_update * LINUXDEV * 0
20-MAY-2009 13:55:04 * service_update * LINUXDEV * 0
20-MAY-2009 13:55:07 * service_died * LINUXDEV * 12537
20-MAY-2009 13:55:12 * service_register * LINUXDEV * 0
20-MAY-2009 13:55:18 * service_update * LINUXDEV * 0
20-MAY-2009 13:55:21 * service_update * LINUXDEV * 0
20-MAY-2009 13:55:27 * service_update * LINUXDEV * 0
20-MAY-2009 13:55:30 * service_update * LINUXDEV * 0
20-MAY-2009 13:55:33 * service_update * LINUXDEV * 0
20-MAY-2009 13:56:14 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=LINUXDEV)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=12783)) * establish * LINUXDEV * 0
20-MAY-2009 13:56:15 * service_update * LINUXDEV * 0
20-MAY-2009 13:56:19 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=LINUXDEV)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=43547)) * establish * LINUXDEV * 0
20-MAY-2009 13:56:19 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=LINUXDEV)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=43548)) * establish * LINUXDEV * 0
20-MAY-2009 13:56:20 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=LINUXDEV)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=43550)) * establish * LINUXDEV * 0
20-MAY-2009 13:56:21 * service_update * LINUXDEV * 0
20-MAY-2009 13:57:28 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=LINUXDEV)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=50695)) * establish * LINUXDEV * 0
20-MAY-2009 13:57:30 * service_update * LINUXDEV * 0
20-MAY-2009 13:57:30 * (CONNECT_DATA=(SID=LINUXDEV)(CID=(PROGRAM=emagent)(HOST=
hostname.domain.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=43597)) * establish * LINUXDEV * 0
20-MAY-2009 13:57:34 * (CONNECT_DATA=(SID=LINUXDEV)(CID=(PROGRAM=emagent)(HOST=
hostname.domain.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=43599)) * establish * LINUXDEV * 0
20-MAY-2009 13:57:39 * ping * 0
WARNING: Subscription for node down event still pending
20-MAY-2009 13:57:41 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=
hostname.domain.com)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domain.com)(PORT=1521)))(VERSION=169869568)) * status * 0
20-MAY-2009 13:58:03 * service_update * LINUXDEV * 0
20-MAY-2009 13:58:07 * (CONNECT_DATA=(SID=LINUXDEV)(CID=(PROGRAM=perl)(HOST=
hostname.domain.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=43608)) * establish * LINUXDEV * 0
20-MAY-2009 13:58:13 * log_status * 0
WARNING: Subscription for node down event still pending
20-MAY-2009 13:58:18 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=
hostname.domain.com)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
20-MAY-2009 14:00:33 * service_update * LINUXDEV * 0
20-MAY-2009 14:02:38 * ping * 0
WARNING: Subscription for node down event still pending
20-MAY-2009 14:02:39 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=
hostname.domain.com)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domain.com)(PORT=1521)))(VERSION=169869568)) * status * 0
20-MAY-2009 14:02:45 * (CONNECT_DATA=(SID=LINUXDEV)(CID=(PROGRAM=perl)(HOST=
hostname.domain.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1979)) * establish * LINUXDEV * 0
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=
hostname.domain.com)(PORT=1521)))
Listener completed notification to CRS on stop
20-MAY-2009 14:03:24 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=
hostname.domain.com)(USER=oracle))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * stop * 0
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
12-OCT-2010 10:05:56 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=
hostname.domain.com)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
12-OCT-2010 10:06:45 * (CONNECT_DATA=(SERVICE_NAME=LINUXDEV)(CID=(PROGRAM=C:\oracle\product\10.2.0\db_1\bin\sqlplus.exe)(HOST=CONCORD)(USER=dpafumi))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.16.253.15)(PORT=4449)) * establish * LINUXDEV * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
12-OCT-2010 10:07:01 * ping * 0
12-OCT-2010 10:07:10 * (CONNECT_DATA=(SERVICE_NAME=LINUXDEV)(CID=(PROGRAM=C:\Program Files\ImageSoft FraudGuard\Image Import\ImageImport.exe)(HOST=FGPNC)(USER=fraudguard))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.111.254.78)(PORT=3786)) * establish * LINUXDEV * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
12-OCT-2010 10:07:22 * (CONNECT_DATA=(SERVICE_NAME=LINUXDEV)(CID=(PROGRAM=C:\oracle\product\10.2.0\db_1\bin\sqlplus.exe)(HOST=CONCORD)(USER=dpafumi))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.16.253.15)(PORT=4453)) * establish * LINUXDEV * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor



 
- To find a PORT that is available when installing...
        netstat -a | grep port#
           ex.
        netstat -a | grep 8068

Change The Owner Of Oracle Database Software On Unix
1) Shutdown the database.
2) Shut down all processes running out of the ORACLE_HOME. (Listener, EM etc.)
3) There are some files under Oracle Home which are owned by “root” or other users.  For Example :
              The file ‘nmo’, ’nmb’ , ‘oradism’ etc ... in the ORACLE_HOME/bin directory are owned by “root” user in 10g.In 9i the list may be different.

We can execute the following command to find the files, which are not owned by oracle owner
$ cd $ORACLE_HOME
$ find . ! -user <oracle_owner> | xargs ls -l | tee /tmp/list.txt
        (Replace the <oracle_owner> with the current owner of installations) 

For Example:
$ find . ! -user oracle | xargs ls –l | tee /tmp/list.txt
-rwsr-x--- 1 root root 65319 Jan 8 19:00 ./bin/extjob
-rwsr-s--- 1 root root 18801 Jan 8 18:47 ./bin/nmb
-rwsr-s--- 1 root root 19987 Jan 8 18:47 ./bin/nmo
-r-sr-s--- 1 root root 14456 Feb 4 2006 ./bin/oradism
-rw-r----- 1 root root 1534 Dec 22 2005 ./rdbms/admin/externaljob.ora

Check and make a note of the ownership of the files, which are listed as the result of the above command. The list will be also redirected to the file /tmp/list.txt.

4) Change the ownership of oracle database software by using recursive “chown” command.
Consider if the current owner of oracle software is "oracle92" who is from "dba" group, you want to change it to a new user "oracle" and your oracle software is installed in the directory "/app/oracle/product/ora92" then you should do:
$ cd /app/oracle/product
$ chown -R oracle:dba ora92
(You require root access or help of your System administrator for the same.)

The new owner should be also from the same group as like the current owner. You can verify it by using the “id” command.
$ id <user_name >
Consider your current user is "oracle92" and the group is "dba" then "id oracle92" will give output as uid=1003(oracle92) gid=103(dba) where 1003, “oracle92” is the userid and the user name respectively, 103 and “dba” is the groupid and group name. The new owner “oracle” should be also from the same group “dba”


5) The recursive chown command will change the ownership of all the files in the Oracle Home. So as “root” user ,change the ownership of the files which are listed in the step 3 to the respective users using a chown.
   For example: 
      After recursive chown
   $ ls –l $ORACLE_HOME/bin/extjob
   -rwsr-x--- 1 oracle dba 65319 Jan 8 19:00 ./bin/extjob
      Change the ownership using chown.
   $chown root:root $ORACLE_HOME/bin/extjob
   -rwsr-x--- 1 root root 65319 Jan 8 19:00 ./bin/extjob


6) All the folders that belong to oracle installation are found in ORACLE_HOME except the Oracle Central Inventory.The location of the Central Inventory usually found from the oraInst.loc file, which contains the path as
"inventory_loc=< path >/oraInventory"
You have to change the ownership of the oraInventory also to the new owner.
(oraInst.loc file exists in /var/opt/oracle or /etc/oracle by default)

In 9i Oracle Universal Installer (OUI) is also found outside the ORACLE_HOME.The location of OUI is found in the oraInventory from a file called "oui.loc" which contails the path for
"InstLoc=< path >/oui".
You have to change the ownership of this also to the new owner. (In 10g OUI is found inside Oracle Home by default.)


OS Authentication

OS authentication allows Oracle to pass control of user authentication to the operating system. Non-priviliged OS authentication connections take the following form.
sqlplus /
sqlplus /@service
When a connection is attempted from the local database server, the OS username is passed to the Oracle server. If the username is recognized, the Oracle the connection is accepted, otherwise the connection is rejected.
First, create an OS user, in this case the user is called "diego". Once you created that, if you try to login as sqlplus "/ as sysdba"  it will fail:
The connections failed because we have not told Oracle the users are OS authenticated. To do this, we must create an Oracle user, but first we must check the value of the Oracle OS_AUTHENT_PREFIX initialization parameter.
SQL> SHOW PARAMETER os_authent_prefix

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
SQL>
As you can see, the default value is "ops$". If this is not appropriate it can be changed using the ALTER SYSTEM command, but for now we will use this default value.
Now we know the OS authentication prefix, we can create a database user to allow an OS authenticated connection. To do this, we create an Oracle user in the normal way, but the username must be the prefix value concatenated to the OS username. So for the OS user "tim_hall", we would expect an Oracle username of "ops$tim_hall" on a UNIX or Linux platform.
-- UNIX
CREATE USER ops$diego IDENTIFIED EXTERNALLY;
GRANT CONNECT TO ops$diego;
The situation is complicated slightly on Windows platforms as the domain or machine name forms part of the username presented to Oracle. On Windows platforms you would expect an Oracle username of "OPS$DOMAIN\TIM_HALL" for the Windows user "tim_hall".
-- Windows
CREATE USER "OPS$ORACLE-BASE.COM\DIEGO" IDENTIFIED EXTERNALLY;
GRANT CONNECT TO "OPS$ORACLE-BASE.COM\DIEGO";
When using a Windows server, there is an additional consideration. The following option must be set in the "%ORACLE_HOME%\network\admin\sqlnet.ora" file.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
With the configuration complete, now you can connect as that "diego" user.


Deleting Files from Windows
Delete files more than X days old in Windows "forfiles" is part of the resource kit, but ships with Windows 2003 Server and later.

forfiles -p d:\mssql_backup -s -m *.bak -d -2 -c "cmd /C del @Path"

1. -p: Start in the directory d:\mssql_backup
2. -s: process subdirectories
3. -m: match files using *.bak
4. -d: Find files more than 2 days old
5. -c: Execute the del command to delete the file. @Path has double-quotes around it already.

 

Improving SQL*Net Performance

Load Balancing tnsnames.ora

First setup multiple listeners on listener.ora. Example:
LSNR1666 =
(DESCRIPTION_LIST =
(DESCRIPTION =
 (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 1666))
 )
)
)

LSNR2666 =
(DESCRIPTION_LIST =
(DESCRIPTION =
 (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 2666))
 )
)
)

SID_LIST_LSNR1666 =
(SID_LIST =
(SID_DESC =
 (GLOBAL_DBNAME = lx10r2.us)
 (ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
 (SID_NAME = lx10r2)
)
)

SID_LIST_LSNR2666 =
(SID_LIST =
(SID_DESC =
 (GLOBAL_DBNAME = lx10r2.us)
 (ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
 (SID_NAME = lx10r2)
)
)

lsnrctl start lsnr1666

Next, we configure the client. The client naming is controlled by tnsnames.ora
LX10R2.US =
(DESCRIPTION =
  (ADDRESS_LIST=
     (LOAD_BALANCE=ON)
     (ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 1666))
     (ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 2666))
  )
  (CONNECT_DATA = (SID = lx10r2))
)

Here, the database alias is "lx10r2.us". Normally, when you specify multiple address lines for an alias, Oracle will attempt the first and if that fails will attempt the second. This might actually work for the poster, but would still pound port 1666 until it couldn't handle any connections and THEN try port 2666. The key to having a somewhat even distribution over the two listeners is by using the (LOAD_BALANCE=ON) parameter.


Connect to a system regardless of machine failure

You can place multiple address entries for a single connection alias in the TNSNAMES.ORA file. This means that you can connect to a database, even if some kind of physical failover occurred. Look at the following example:

10gexpress =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =
Machine01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Machine02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

Suppose Machine01 is down, then every new SQL*NET connection using service 10gexpress will automatically login to Machine02. However, there is one restriction, the SID must be the same on both machines. This feature can provide guaranteed login for application servers and for the Oracle Parallel Server.


What can be done to increase SQL*Net performance?

While a SQL statement is running SQL*Net polls the client continuously to catch CONTROL-C situations. This results into a lot of poll and fstat system calls.
The following SQLNET.ORA parameter can be specified to reduce polling overhead on your system:
# Number of packets to skip between checking for breaks (default=4)
BREAK_POLL_SKIP=10000
Prespawned server sessions. You can tell the listener to start up a pool of idle server processes. When a connection request is made, it doesn't have to start a server process; it just hands one of the idle processes to the client (and then starts a new connection in its own time). This is configured in LISTENER.ORA, in the SID_LIST_LISTENER section, as follows:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = yourSID)
(PRESPAWN_MAX = 50)
(PRESPAWN_LIST =
(PRESPAWN_DESC = (PROTOCOL = TCP) (POOL_SIZE = 5)
(TIMEOUT = 2))
))
)

PRESPAWN_MAX: if there are over 50 sessions connected to the database, the listener won't prespawn any more.

POOL_SIZE: the listener will maintain an idle pool of 5 server processes.

TIMEOUT: after a client disconnects, the listener will keep the freed-up server process around for two minutes, waiting for a new connection request, before killing that process.

Multiple listeners with load balancing. You can start multiplelisteners on a server, and reference all of the listeners in the TNSNAMES.ORA file. When a client makes a connection request, the SQL*Net client will randomly pick one of the listeners to contact.

In LISTENER.ORA, specify multiple listeners as in:
# Define listener A...
STARTUP_WAIT_TIME_LISTENER_A = 0
CONNECT_TIMEOUT_LISTENER_A = 10
LISTENER_A=
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = yourHost.domain)
(PORT = 1521)))
SID_LIST_LISTENER_A =
(SID_LIST =
(SID_DESC =
(SID_NAME = yourSID)
(PRESPAWN_MAX = 50)))

# Define the second listener...
STARTUP_WAIT_TIME_LISTENER_B = 0
CONNECT_TIMEOUT_LISTENER_B = 10
LISTENER_B=
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = yourHost.domain)
(PORT = 1522)))
SID_LIST_LISTENER_B =
(SID_LIST =
(SID_DESC =
(SID_NAME = yourSID)
(PRESPAWN_MAX = 50)))

The TNSNAMES.ORA service for this database would be something like:

oradb1.world =
(description_list=
(description=
(address_list=
(address=
(protocol=tcp)
(host=yourHost.domain)
(port=1521)))
(connect_data =
(sid = yourSID)))
(description =
(address_list =
(address=
(protocol=tcp)
(host=yourHost.domain)
(port=1522)))
(connect_data =
(sid = yourSID))))


Changing Oracle UID and GID
In Unix, the file system only contains numeric UID and GID values, they only get converted to a name via lookup in /etc/passwd and /etc/group. (system calls getpwnam(), getpwuid(), getgrnam(), and getgrgid() perform this task).
Oracle software does not know or care anything about the numeric UID/GID, only the names. So the change is pretty easy, just like changing the description for a unique ID in a lookup table in the database.
Here is a sample scenario. Assumes new UID and GID are not already in use, of course.
       users:
       oracle change 101 => 103
       groups:
       dba change 101 => 21

First, run pwck and grpck commands to clean up any problems with the respective files. Optional, but recommended (you'd be surprised what you might find).
# get "before" list of files to be changed for logging purposes
find / -user oracle -exec ls -ld {} \; > /tmp/ora_owned_files.lst
# find files which don't have DBA group, if any (shouldn't be any)
find / -user oracle \! -group dba -exec ls -ld {} \; >> \ /tmp/ora_owned_files.lst

Shut down all oracle software (confirm with "ps -fu oracle" command).

# make the change
find / -user oracle -exec chown 103:21 {} \;

# make backups using RCS or your favorite method
cd /etc
ci -l passwd
ci -l group

# change lookups
vipw [...change oracle UID to 103, GID to 21]
vi /etc/group [change dba GID to 21]

# re-run listing to check for consistency
# check output to see what's changed...should be the same as "before"
# listing
find / -user oracle -exec ls -ld {} \; > ora_owned_files.lst.new
find / -user oracle \! -group dba -exec ls -ld {} \; >> /tmp/ora_owned_files.lst.new &

It might be a little slow, you can experiment with the recursive option of chown instead of using find. Or, instead of -exec option of 'find', pipe output to xargs command. Just be sure you handle symbolic links correctly. (Your SA should understand all of this, in case you don't).


How to use rlwrap to get a command history in sql*plus
SQL*Plus does not have a command history function under Linux and Unix.
rlwrap is a readline wrapper for shell commands which uses input from the controlling terminal.
It adds a persistent input history for each command and supports user-defined completion.
You can download the sources for the rpm for rlwrap from HERE. The most recent version I could find is version 0.26.
There you also find a README and the manpage for rlwrap.

After downloading and unpacking the tar.gz
gunzip rlwrap-0.26.tar.gz
tar -xvf rlwrap-0.26.tar

I ran as root (#)
./configure
make
make check
make install

and that was it.

Now I could call sqlplus this way:
$ rlwrap sqlplus user/password@sid.

Finally I created an alias
alias q="rlwrap sqlplus"
or
alias sqlplus="rlwrap sqlplus"

I would avoid the last one. rlwrap does not support non-interactive mode (echo xxx | sqlplus), is not an oracle support tool, and crashes occasionnaly.
So that I can always use sqlplus to run important script, and keep rlwrap for not-productive stuff.

Now I can simply call sqlplus as I always have done and have a commend history with the  keys on my keyboard.


Backup Putty Settings and Colors
To backup Putty Settings
* Type: regedit
* Hit Enter or Return key, it will open Windows Registry Editor
* Navigate to:
    HKEY_CURRENT_USER\Software\SimonTatham
* Right click on "SimonTatham" key >> Export
* Save as putty.reg and put it somewhere.
or
* On the source computer, open up a command prompt , and run:
    regedit /ea puTTY.reg HKEY_CURRENT_USER\Software\SimonTatham\PuTTY
* The above one will create the file named puTTY.reg in the present working directory

To restore Putty:
* Copy/transfer putty.reg to another machine which already have PuTTY installed.
* Double click putty.reg file to import the entries to registry


PUTTY COLORS
http://www.igvita.com/2008/04/14/custom-putty-color-themes/



SSH Tunnel
Situation:
Windows box connects to work using SafeNet Secure VPN Client
Once connected, I have to ssh to a box (let's call it 192.168.2.29), and once connected to that box, I have to ssh to another box that has the DB Server (192.168.1.28).
I'd like to access the DB Server directly from my PC (if possible).


This are the steps that I performed:
- Open PuTTY and load your saved session.

- Choose Tunnels in the category section. Then in the "source port" section, put any port number that is not in use (example: 1234).

- In the destination text box enter the IP address of the host name (192.168.1.28 in my case) followed by a colon and the destination port number(1521).  Remember to click on the ADD button.

- Save your settings in PUTTY.

- Then modify your tnsnames.ora with something like this:
PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1234))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CCOM)
    )
  )

- Then ssh to the 1st server

- Finally type the following to connect and create the tunnel to the second server:
ssh -L 1234:192.168.1.28:1521 oracle@192.168.1.28
The previous command will create a "tunnel", any request going to 92.168.1.28:1521 will be forwarded to 127.0.0.1:1234.

- Now you can connect from the Original box to the DB


Database Automatic Failover On Another Database
A-on SERVER1:

1-open tnsnames.ora file in this path D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN
2-edit the tnsnames.ora file and modfiy your database service to be like this

ORC1=
(DESCRIPTION=
(FAILOVER=on)
(ADDRESS=(PROTOCOL=tcp)(HOST=SERVER1)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=SERVER2)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ORC1)))

and add another service for the the database ORC2 on SERVER2

ORC2 =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = SERVER2)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORC2)
)
)


B-On SERVER2

1-open tnsnames.ora file in this path D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN
2-edit the tnsnames.ora file and modfiy your database service to be like this

ORC2=
(DESCRIPTION=
(FAILOVER=on)
(ADDRESS=(PROTOCOL=tcp)(HOST=SERVER2)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=SERVER1)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ORC2)))

and add another service for the the database ORC1 on SERVER1


ORC1 =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = SERVER1)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORC1)
)
)

C-at this point your databases can failover on each other

D-To test that your configuration is working try to connect an application to or TOAD to the ORC1 database with schema scott/tiger and then open SQLPLUS and shutdown this database
it will be automatically switched on the other database ORC2




Speed-Up Oracle on Your Notebook PC
Details on how to make Oracle run as fast as possible on minimal computer setup

1- Exclude Oracle data files directory(s) from all anti-virus and anti-spyware automatic and manual scans. Myself, I exclude the entire "C:\Oracle” directory - where I place all my Oracle files.

2- Eliminate all unnecessary background processes (demons) or services. Here are some basic examples (note that your computing environment needs and thus extraneous list will surely vary - so check with your local administrators to be sure and thus safe): 
   1. Windows XP
         1. ATI Hot Key Poller
         2. Computer Browser
         3. Distributed Link Tracking Client
         4. Error Reporting Service
         5. Indexing Service
         6. Upload Manager
            
   2. Redhat Linux(s)
         1.  anacron / cron
         2.   httpd
         3.   ISDN
         4.   Net FS
         5.   NFS Lock
         6.   Send Mail
        
3- Clean up (i.e. purge) the Oracle logging directory tree. For 9i and 10g, this was subdirectories such as BDUMP and UDUMP under your "admin/SID” directory. Starting with 11g, you need to look at the entire "DIAG" directory.

4- Exclude Oracle data files directory(s) from excessive or unnecessary last update timestamp maintenance.
 
   1. Windows
         1. HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\FileSystem\NtfsDisableLastAccessUpdate = 1
   2. Redhat Linux(s)
         1. chattr +A file_name per Oracle control, data and log file
         2. chattr -R +A directory_name per Oracle SID data directory
         3. Edit /etc/fstab for the Oracle Home and/or data file systems
            Example:    /dev/sda6    / home    ext3    defaults,noatime    1 1


VNC - Install Oracle Software on Remote Linux Server
If not already done so download and install a client version of VNC so you can connect to the server. There are many out there, for the purpose of this post I use UltraVNC.
You can download a free version from http://www.uvnc.com/download/index.html

1- By default VNC Server is installed in your RedHat and OEL OS, but it is not configured. The way VNC works is when started it creates a client instance for the user and binds it to a specific port. So if have an account on the Linux box you can setup a VNC Server session for that user, you don’t need to be root. For the purpose of this document I’m going to use oracle as the user to setup a VNC Session as this is the user I want use to install the software. However to start the VNC Service you must be root.
As the root user run the following command:
service vncserver start
Starting VNC server: no displays configured                [  OK  ]

2- Login to the Linux box as oracle to install the Oracle software
Run the command to create a new VNC server instance for the oracle user:
vncserver :1


3- You will be ask to supply password information. This is what you will enter when connecting from your desktop client. This password is also independent of the actual Linux user password.

You will require a password to access your desktops.
Password:
Verify:
xauth:  creating new authority file /home/oracle/.Xauthority
Creating default startup script /home/oracle/.vnc/xstartup
Starting applications specified in /home/oracle/.vnc/xstartup
Log file is /home/oracle/.vnc/lisa.nz.oracle.com:1.log

4- As you can see a new instance server_name:1 has been created. If you were to run the vncserver command again another instance server_name:2 will be created.
If you are going through a firewall you will need to ensure that the port 5901 (port 1) is open between your client desktop and the Linux Server.
Depending on the options chosen at install time a firewall could be in place. The simplest way to disable this is using the command. You will need to be root.
service iptables stop

This will stop the firewall while you install. If you just want to add a port to the accepted lists use the firewall UI. You will need to be root.
system-config-security-level

Now you are ready to connect to the server via the VNC. Using the software installed in step one start the VNC Client. You should be prompted for the server and port.


Other Options:

Launch VNC with X Desktop $HOME/.vnc/xstartup
#!/bin/sh
# Uncomment the following two lines for normal
desktop:
  unset SESSION_MANAGER
  exec /etc/X11/xinit/xinitrc
[ -x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup
[ -r $HOME/.Xresources ] &&xrdb $HOME/.Xresources
xsetroot -solid grey
vncconfig -iconic &
xterm -geometry 80x24+10+10 -ls -title "$VNCDESKTOP
Desktop" &
#twm&

Start VNC with larger window
vncserver -geometry 1280x1024

Cleanly Kill VNC vncserver
kill :1

To open a vncserver on a specific port
vncserver -geometry 1280x1024 :64


Clone Database Software Installation
Goto your Oracle home:
$ cd $ORACLE_HOME

Tar (and zip) the Oracle home (probably you encounter some errors, some files in the bin directory are set to owner ‘root’, this is alright):
$ tar czf /path/to/tarfile.tgz --exclude "*.log" --exclude "network/admin/*" --exclude "$(hostname)*" --exclude "/dbs/*" *

Create a directory for the new home:
$ mkdir -p /path/to/new/home

Untar the created tarball:
$ tar xzf /path/to/tarfile.tgz -C /path/to/new/home

Cd to the new Oracle home:
$ cd /path/to/new/home/

Cd to the clone/bin directory in the home:
cd clone/bin

Run clone.pl:
./clone.pl ORACLE_HOME="/path/to/new/home" ORACLE_BASE="/path/to/base" -defaultHomeName

Here is the output of clone.pl on a testsystem: (see how clone.pl calls runInstaller!)
./runInstaller -clone -waitForCompletion "ORACLE_HOME=/oracle/db/11.2.0.1_test" "ORACLE_BASE=/oracle" -defaultHomeName -defaultHomeName -silent -noConfig -nowait
Starting Oracle Universal Installer...
.
Checking swap space: must be greater than 500 MB. Actual 1852 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-07-02_04-34-52PM. Please wait ...Oracle Universal Installer, Version 11.2.0.1.0 Production
Copyright (C) 1999, 2009, Oracle. All rights reserved.
.
You can find the log of this install session at:
/oracle/oraInventory/logs/cloneActions2010-07-02_04-34-52PM.log
.................................................................................................... 100% Done.
.
.
.
Installation in progress (Friday, July 2, 2010 4:36:13 PM CEST)
............................................................................. 77% Done.
Install successful
.
Linking in progress (Friday, July 2, 2010 4:36:40 PM CEST)
Link successful
.
Setup in progress (Friday, July 2, 2010 4:41:23 PM CEST)
Setup successful
.
.
End of install phases.(Friday, July 2, 2010 4:47:18 PM CEST)
Starting to execute configuration assistants
The following configuration assistants have not been run. This can happen because Oracle Universal Installer was invoked with the -noConfig option.
--------------------------------------
The "/oracle/db/11.2.0.1_test/cfgtoollogs/configToolFailedCommands" script contains all commands that failed, were skipped or were cancelled. This file may be used to run these configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.
The "/oracle/db/11.2.0.1_test/cfgtoollogs/configToolAllCommands" script contains all commands to be executed by the configuration assistants. This file may be used to run the configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.
.
--------------------------------------
WARNING:
The following configuration scripts need to be executed as the "root" user.
/oracle/db/11.2.0.1_test/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts
.
The cloning of OraHome1 was successful.
Please check '/oracle/oraInventory/logs/cloneActions2010-07-02_04-34-52PM.log' for more details.

As the clone.pl output indicates, root.sh needs to be run after the clone.pl command.


Uninstall Oracle from Linux
As root perform:
    * chkconfig oracle off
    * chkconfig oracle -del
    * $ORACLE_HOME/bin/localconfig delete
    * rm -rf /etc/ora*
    * rm -rf $ORACLE_HOME
    * rm -rf $ORACLE_BASE
    * rm -rf /tmp/.oracle
    * rm -rf /var/tmp/.oracle


xhost and DISPLAY

The correct sequence is:
1.Log on to the computer that *owns* an XWindows screen.  This could be a Linux machine with gnome/KDE/twm, or perhaps a Windows machine with cygwin/X, Xming, or perhaps Hummingbird (now OpenText) eXceed ... these are known as XServers.
2.Tell that Xserver (screen) to accept remote requests, by issuing "xhost +" (note that the + disables security)
3.Log on to the computer / user that is to use the XWindows - the one that contains the runInstaller (or that will run Oracle)
4.Tell that to display on the computer that is to be used for display using export DISPLAY=(host):display.screen
5.Run the program that needs a display

Many people, especially students, log in to root on the computer and then su to oracle to do the installs. While this is fundamentally a stupid thing to do, because using root unnecessarily increases potential security risks and is it NOT good practice and will it NOT be allowed in any self-respecting business, the following would be the sequence

1.Log on to XWindows as root.  Do NOT su to root!  Do NOT pass GO.  Do NOT collect $200.
2.Open a terminal
3.Enter: xhost +
4.Find out which DISPLAY is used by entering: set | grep -i DISPLAY
5.Enter: su - oracle
6.Enter: export DISPLAY=:0.0 # of course, base this on step 4
7.Enter: runInstaller # (or whatever is required, such as xeyes)

The most common mistake is running xhost after su. This will not work.
The xhost must be issued by the first user - the one that owns the screen.



Database Tips

Writing to the alert log
If you want to write a message to the alert log, you can use the undocumented KSDWRT procedure of the DBMS_SYSTEM package.
This procedure has two parameters, the first one must be "2" to write to the alert file, the second one is the message you want to write.
Here is an example:
    execute sys.dbms_system.ksdwrt(2,to_char(sysdate)|| ' -- ');

Use 1 instead of 2 to write to the trace file
Use 3 to write to both.

Example:
exec sys.dbms_system.ksdwrt(2, 'CUS-00001: message from me'||chr(10)||'On another line');

--> In the alert.log file:
Wed Jun 25 19:49:30 2003
CUS-00001: message from me
On another line



Outer joins - Usage and efficiency

Outer joins enable rows to be returned from a join where one of the tables does not contain matching rows for the other table.
eg. Suppose we have two tables:
     Person
------
Person_id Name Address_id
--------- ---------------- ----------
00001 Fred Bloggs 00057
00002 Joe Smith 00092
00003 Jane Doe
00004 Sue Jones 00111


Address
-------
Address_id Address_Desc
---------- -------------------------
00057 1, Acacia Avenue, Anytown
00092 13, High Street, Anywhere
00113 52, Main Road, Sometown

Then the simple join:

         SELECT PERSON.NAME, ADDRESS.ADDRESS_DESC
FROM PERSON, ADDRESS
WHERE PERSON.ADDRESS_ID = ADDRESS.ADDRESS_ID
returns:

NAME ADDRESS_DESC
---------- ------------
Fred Bloggs 1, Acacia Avenue, Anytown
Joe Smith 13, High Street, Anywhere

But the outer join:

         SELECT PERSON.NAME, ADDRESS.ADDRESS_DESC
FROM PERSON, ADDRESS
WHERE PERSON.ADDRESS_ID = ADDRESS.ADDRESS_ID(+)
returns:

NAME ADDRESS_DESC
---------- ------------
Fred Bloggs 1, Acacia Avenue, Anytown
Joe Smith 13, High Street, Anywhere
Jane Doe
Sue Jones

Note the two new rows for Jane Doe and Sue Jones. These are the people who do not have matching records on the ADDRESS table. Sue Jones had an address_id on her PERSON record, but this didn't match an address_id on the ADDRESS table. ( Probably a data inconsistency ). Jane Doe had NULL in her PERSON.ADDRESS_ID field, which obviously doesn't match any address_id on the ADDRESS table.
Note that the outer join is created by including (+) on the WHERE clause which joins the two tables. The (+) is put against the column-name on the deficient table, ie. the one with the missing rows. It is very important to put the (+) on the correct table: putting it on the other table will give different results. eg. the query:

         SELECT PERSON.NAME, ADDRESS.ADDRESS_DESC
FROM PERSON, ADDRESS
WHERE PERSON.ADDRESS_ID(+) = ADDRESS.ADDRESS_ID
returns:

NAME ADDRESS_DESC
---------- ------------
Fred Bloggs 1, Acacia Avenue, Anytown
Joe Smith 13, High Street, Anywhere
52, Main Road, Someplace



Managing Dates
You can use fractions with sysdate (or any date column) to add hours, minutes and/or seconds. For hours, use a denominator of 24; for minutes use 1440; for seconds: 86400. 
For example "sysdate + 3/24" will add 3 hours, "sysdate + 5/1440" will add 5 minutes; "sysdate + 45/86400" will add 45 seconds. You can use values like "sysdate + 30/24" to  add one day +6 hours if you need to. Example:
SELECT to_char(sysdate,'dd-mon-yyyy hh:mi:ss'), 
               TO_CHAR(SYSDATE + 10/1440,'dd-mon-yyyy hh:mi:ss') FROM DUAL; 

Using Oracle functions for dates
An alternative to this method is to use the numtodsinterval function. Example to add 2 hours:
SELECT to_char(sysdate, 'DD/MON/YY HH24:MI:SS'), to_char(sysdate + numtodsinterval(2, 'HOUR'), 'DD/MON/YY HH24:MI:SS')
FROM dual;

TO_CHAR(SYSDATE,'D TO_CHAR(SYSDATE+NU
------------------ ------------------
17/JAN/06 12:50:53 17/JAN/06 14:50:53

Here the numtodsinterval function is doing the work of dividing 2/24 for hours.
Valid options for the numtodsinterval are ‘DAY’, ‘HOUR’, ‘MINUTE’, or ‘SECOND’. Here is an example using ‘MINUTE’.
When working with minutes the numtodsinterval function is much more readable.
SELECT to_char(sysdate, 'DD/MON/YY HH24:MI:SS'), to_char(sysdate + numtodsinterval(45, 'MINUTE'), 'DD/MON/YY HH24:MI:SS')
    FROM dual;

TO_CHAR(SYSDATE,'D TO_CHAR(SYSDATE+NU
------------------ ------------------
17/JAN/06 12:50:09 17/JAN/06 13:35:09


Adjusting Months and Years
To work with months and years (either of which may have a varying number of days) Oracle has provided the function numtoyminterval.
This works much like the numtodsinterval function mentioned above by taking a number and a string. Valid options for the string are ‘YEAR’ or ‘MONTH’.
SELECT sysdate, sysdate + numtoyminterval(5, 'MONTH')
FROM dual;
SYSDATE            SYSDATE+NUMTOYMINT
------------------ ------------------
17/JAN/06 12:53:49 17/JUN/06 12:53:49

SELECT sysdate, sysdate + numtoyminterval(2, 'YEAR')
FROM dual;
SYSDATE            SYSDATE+NUMTOYMINT
------------------ ------------------
17/JAN/06 12:54:08 17/JAN/08 12:54:08


Truncate minutes and seconds
select to_char(TRUNC(sysdate,'HH24'),'dd-mon-yyyy hh:mi:ss') from dual;
select to_char(TRUNC(sysdate,'MI'),'dd-mon-yyyy hh:mi:ss') from dual;


How can I get the time difference between two date columns
Look at this example query: 
define date1 = sysdate+3;
define date2 = sysdate ;
select floor(((&date1-&date2)*24*60*60)/3600) || ' HOURS ' ||
floor((((&date1-&date2)*24*60*60) - floor(((&date1-&date2)*24*60*60)/3600)*3600)/60) || ' MINUTES ' ||
round((((&date1-&date2)*24*60*60) - floor(((&date1-&date2)*24*60*60)/3600)*3600 -(floor((((&date1-&date2)*24*60*60) -
floor(((&date1-&date2)*24*60*60)/3600)*3600)/60)*60))) || ' SECS ' time_difference
from DUAL;
undef date1;
undef date2;


Extract Date Information
select sysdate from dual;
SYSDATE
------------------
06/DEC/06 11:06:12

select extract(day from sysdate) from dual;
EXTRACT(DAYFROMSYSDATE)
-----------------------
                      6

select extract(month from sysdate) from dual;
EXTRACT(MONTHFROMSYSDATE)
-------------------------
                       12

select extract(year from sysdate) from dual;
EXTRACT(YEARFROMSYSDATE)
------------------------
                    2006


Adding the Oracle SID to the SQL Prompt
Just add the following statements to the GLOGIN.SQL to populate the SQL command prompt with the Oracle SID:
--  Add any sqlplus commands here that are to be executed when a user
--  starts SQL*Plus on your system

-- Used by Trusted Oracle
column ROWLABEL format A15

-- OLD Added by me to show the SID
-- column sid new_value osid noprint;
-- select upper(substr(global_name,1,(instr(global_name,'.')-1))) sid
-- from global_name;
-- set sqlprompt '&osid SQL> '

set serveroutput on size 1000000
alter session set nls_date_format = 'DD/MON/YY hh24:mi:ss';

-- Added by me to show the SID
set head off
set feed off
set term off
column C01 format A30 new_value DIA
column C02 format A8 new_value NOMBASE
column C03 format A8 new_value NOMUSER
column C04 format A10 new_value TERMINAL
column C05 format A5 new_value SESSION

select decode(upper(substr(global_name,1,(instr(global_name,'.')-1))), null,global_name, upper(substr(global_name,1,(instr(global_name,'.')-1)))) C02,
       USERENV('TERMINAL') C04, USERENV('SESSIONID') C05
from global_name;

select USER C03, to_char(sysdate,'DD Month YYYY - HH24:MI') C01 from dual;

set term on
set verify off
select '****************************************'||CHR(10)||
       ' &DIA                                 '||CHR(10)||CHR(10)||
       ' Connected to  : &NOMBASE'             ||CHR(10)||
       ' As User       : &NOMUSER.'            ||CHR(10)||
       ' Terminal      : &TERMINAL '           ||CHR(10)||
       ' Session_ID    : &SESSION'             ||CHR(10)||
       '****************************************' from dual
/

undef DIA
set verify on
set feed on
set head on
set sqlprompt "&NOMBASE/&NOMUSER> "
undef NOMUSER
undef NOMBASE
clear buffer
clear COLUMNS

-- Used for the SHOW ERRORS command
column LINE/COL format A8
column ERROR    format A65  WORD_WRAPPED

-- Used for the SHOW SGA command
column name_col_plus_show_sga format a24

-- Defaults for SHOW PARAMETERS
column name_col_plus_show_param format a36 heading NAME
column value_col_plus_show_param format a30 heading VALUE

-- For backward compatibility
set pagesize 14

-- Defaults for SET AUTOTRACE EXPLAIN report
column id_plus_exp format 990 heading i
column parent_id_plus_exp format 990 heading p
column plan_plus_exp format a60
column object_node_plus_exp format a8
column other_tag_plus_exp format a29
column other_plus_exp format a44
 


Move Tables to another Tablespace
ALTER TABLE <table> MOVE TABLESPACE <new_tablespace> ;


Rebuilding Indexes
You can use the ALTER INDEX REBUILD command to change the storage and tablespace parameters for an existing index without having to drop it.
The following is an example of an index rebuild via this command. It's storage parameters are changed to use an initial extent size of 3MB and a next extent size of 500K. It is also being moved from the USR7 tablespace to the IDX7 tablespace.

ALTER INDEX fuzzy_pink_slippers REBUILD
(  STORAGE(INITIAL 3M NEXT 500K
           PCTINCREASE 0)
TABLESPACE IDX7;  )

alter index <index_name> REBUILD TABLESPACE <new_tablespace>;
 

Rebuild? There is an alternative
You may wish to consider.
ALTER INDEX vmoore COALESCE;

Coalescing an index, de-fragments the leaf blocks for an index as opposed to a full rebuild cycle. In many instances, this may be sufficient remedial action.
Positives
- Less resource used - in fact no additional space is required
- Faster (typically)
- Good for databases with large block sizes (see the negative point below on index height)

Negatives
- You aren't recreating anything, so obviously you cannot move the index or adjust its storage parameters during a coalesce
- Since only the leaf blocks are coalesced, the height of the index tree will not be changed. For databases with larger block sizes, this should be less significant since the indexes will be "flatter" anyway.
  



Collect Statistics

In 11g You are encouraged to use AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT .
In 11g AUTO_SAMPLE_SIZE is very fast compared to earlier versions and gives accuracy of close to 100 % sample size.
AUTO_SAMPLE_SIZE uses a new Hash-based Sampling for Column Statistics.

By default 11g will collect histograms - to avoid them you need to use method_opt => 'for all columns size 1')

If performance is still a problem you can try to setting back OPTIMIZER_FEATURES_ENABLE='10.2.0.1' and this improved performance.

Create dictionary statistics
EXEC dbms_stats.gather_schema_stats('SYS',options => 'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',cascade => TRUE);
or
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYS',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
Or
EXEC dbms_stats.gather_dictionary_stats;


For a Table
exec dbms_stats.gather_table_stats('&USER','&TABLE_NAME',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 5, cascade => TRUE);

For a Schema
execute dbms_stats.gather_schema_stats (ownname => '&USER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 5, cascade => true);

For the DB
execute dbms_stats.gather_database_stats(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 5, cascade => true);


Script to Check Stale Statistics

-- - - - - - - - - - - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - - - - - - - - - - - -
--  NAME:  CHECK_STALE_STATS.SQL
--   Execute as SYS as sysdba
-- ----------------------------------------------------------------------------------------------------------------
-- AUTHOR: 
--    Raja Ganesh - Oracle Support Services - DataServer Group
--    Copyright 2008, Oracle Corporation     
-- -----------------------------------------------------------------------------------------------------------------
-- PURPOSE:
-- This script is an automated way to deal with stale statistics
-- operations that are required to be done as part of manual
-- upgrade OR when reported by DBUA.
--
-- This script will work in both Windows and Unix platforms from database
-- version 9.2 or higher.
-- ------------------------------------------------------------------------------------------------------------------
-- DISCLAIMER:
--    This script is provided for educational purposes only. It is NOT 
--    supported by Oracle World Wide Technical Support.
--    The script has been tested and appears to work as intended.
--    You should always run new scripts on a test instance initially.
-- -------------------------------------------------------------------------------------------------------------------

SET FEEDBACK OFF
SET LINESIZE 250
SET SERVEROUTPUT ON

DECLARE
-- Variables declared
P_OTAB DBMS_STATS.OBJECTTAB;
MCOUNT NUMBER := 0;
P_VERSION VARCHAR2(10);
-- Cursor defined
CURSOR c1
IS
SELECT distinct schema
FROM dba_registry
ORDER by 1;

-- Beginning of the anonymous block
BEGIN
-- Verifying version from v$instance
SELECT version INTO p_version FROM v$instance;
DBMS_OUTPUT.PUT_LINE(chr(13));
-- Defining Loop 1 for listing schema which have stale stats
FOR x in c1
  LOOP
    DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>x.schema,OPTIONS=>'LIST STALE',OBJLIST=>p_otab);

-- Defining Loop 2 to find number of objects containing stale stats
    FOR i in 1 .. p_otab.count
      LOOP
        IF p_otab(i).objname NOT LIKE 'SYS_%'
            AND p_otab(i).objname NOT IN ('CLU$','COL_USAGE$','FET$','INDPART$',
                                          'MON_MODS$','TABPART$','HISTGRM$',
                                          'MON_MODS_ALL$',
                                          'HIST_HEAD$','IN $','TAB$',
                                          'WRI$_OPTSTAT_OPR','PUIU$DATA',
                                          'XDB$NLOCKS_CHILD_NAME_IDX',
                                          'XDB$NLOCKS_PARENT_OID_IDX',
                                          'XDB$NLOCKS_RAWTOKEN_IDX', 'XDB$SCHEMA_URL',
                                          'XDBHI_IDX', 'XDB_PK_H_LINK')
        THEN
-- Incrementing count for  each object found with statle stats
            mcount := mcount + 1;
        END IF;
-- End of Loop 2
      END LOOP;

-- Displays no stale statistics, if coun  is 0
        IF mcount!=0
            THEN
-- Displays Schema with stale stats if count is greater than 0
                DBMS_OUTPUT.PUT_LINE(chr(13));
                DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
                DBMS_OUTPUT.PUT_LINE('-- '|| x.schema || ' schema contains stale statistics use the following to gather the statistics '||'--');
                DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
               
-- Displays Command to be executed if schema with stale statistics is found depending on the version.
          IF SUBSTR(p_version,1,5) in ('8.1.7','9.0.1','9.2.0')
            THEN
                DBMS_OUTPUT.PUT_LINE(chr(13));
                DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_SCHEMA_STATS('''||x.schema||''',OPTIONS=>'''||'GATHER STALE'||''', ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => '''||'FOR ALL COLUMNS SIZE AUTO'||''', CASCADE => TRUE);');
          ELSIF SUBSTR(p_version,1,6) in ('10.1.0','10.2.0','11.1.0','11.2.0')
            THEN
                DBMS_OUTPUT.PUT_LINE(chr(13));
                DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('''||x.schema||''',OPTIONS=>'''||'GATHER STALE'||''', ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => '''||'FOR ALL COLUMNS SIZE AUTO'||''', CASCADE => TRUE);');
          ELSE
                DBMS_OUTPUT.PUT_LINE(chr(13));
                DBMS_OUTPUT.PUT_LINE('Version is '||p_version);
          END IF;
        ELSE
                DBMS_OUTPUT.PUT_LINE('-- There are no stale statistics in '|| x.schema || ' schema.');
                DBMS_OUTPUT.PUT_LINE(chr(13));
        END IF;
-- Reset count to 0.
            mcount := 0;
-- End of Loop 1
  END LOOP;
END;
/

SET FEEDBACK ON

-- - - - - - - - - - - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - - - - - - - - - - - - - -






Archiving ON
By default, archive logs will be written to the flash recovery area. If you do not want to write archive logs to the flash recovery area you can set the parameter LOG_ARCHIVE_DEST_n to the location in which you wish to write archive logs.

Remember that the default location for Archive Log Files is USE_DB_RECOVERY_FILE_DEST. You can determine its path by looking at the parameter RECOVERY_FILE_DEST:

SQL> show parameter recovery_file_dest

 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\app\flash_recovery_area
db_recovery_file_dest_size           big integer 6G


If you want to use the LOG_ARCHIVE_DEST_1, then you may use:
alter system Set LOG_ARCHIVE_DEST_1='LOCATION=C:\app\oradata\DIE\ARCH' SCOPE=both;
alter system set LOG_ARCHIVE_DEST_STATE_1=enable scope=both;

So after deciding the location, these are the steps:
archive log list;
select log_mode from
gv$database;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
select log_mode from
gv$database;
archive log list;

You can switch to the log file to see that an archive is written to archive log location.
alter system switch logfile;


OPTIONAL
You can also define the format of the archive log files with the  log_archive_format parameter. To define the values for log_archive_format, the following variables can be used in the format:
%s log sequence number
%S log sequence number, zero filled
%t thread number
%T thread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database

Using uppercase letters for the variables (for example, %S) causes the value to be fixed length and padded to the left with zeros.

Example:
alter system set log_archive_format='ARCH_%T_%S_%r.arc' scope=both;

Archiving OFF
Steps Required To Take A Database In Archive Log Mode And Alter It To No Archive Log Mode (for ever)

SELECT log_mode FROM gv$database;
archive log list;
show parameter recovery;   --To see where the parameter DB_RECOVERY_FILE_DEST is pointing (if used)
or
show parameter LOG_ARCHIVE_DEST_1;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;
alter system set LOG_ARCHIVE_DEST_STATE_1=DEFER scope=both;
alter system Set LOG_ARCHIVE_DEST_1='' SCOPE=both;
SELECT log_mode FROM gv$database;
archive log list;




ORA-00257 archiver error. Connect internal, until freed
If you work on any Oracle database you might get in situation like this:
sqlplus user/password
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.

As always, first step in troubleshooting Oracle is to check an alert.log. If you do not know where is it then perform:
select value from v$parameter where name = 'background_dump_dest';
VALUE
--------------------------------------------------------------------------------
c:\oracle\product\diag\rdbms\FGUARD\trace

And inside it, you will see something similar to:
*** 2009-12-23 20:23:57.253
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
*** 2009-12-23 20:23:57.530
   DELETE EXPIRED commands.
************************************************************************

Resolution
All looks that your db_recovery_file_dest_size is full with archive logs. To check that and be certain, perform:
show parameter db_recovery_file_dest_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 2G


select substr(name,1,55) Name,  floor(space_limit / 1024 / 1024) "Size MB",ceil(space_used  / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name;
NAME                                                            Size MB    Used MB
------------------------------------------------------------ ---------- ----------
C:\oracle\product\flash_recovery_area                              2048       2007


Because Oracle has no space to create new archived log file it freeze all operations and could wait in this state until eternity if you do not help him!

So you have two (three) solutions to overcome this.

1) Enlarge db_recovery_file_dest_size (from 2 to 3 GB in our case)
alter system set db_recovery_file_dest_size=3G scope=both;

2) Backup and delete archive logs
rman target / nocatalog
run {
allocate channel t1 type disk;
backup archivelog all delete input format '<temp backup="" location="">/arch_%d_%u_%s';
release channel t1;
}


3) Just delete archive logs (what I'll show as easiest way but I prefer previous two options on any production installations). I'll do that with RMAN as best way for that:
rman target / nocatalog
RMAN> delete archivelog all;


Now, let us look again in alert log to see that situation has changed (as we expect):
Wed Dec 23 20:52:57 2009
db_recovery_file_dest_size of 2048 MB is 2.21% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
kcrrdmx: Successful archiving of previously failed ORL
Archiver process freed from errors. No longer stopped
Wed Dec 23 20:53:00 2009
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Dec 23 20:53:00 2009
Thread 1 advanced to log sequence 148 (LGWR switch)
  Current log# 1 seq# 148 mem# 0: C:\ORACLE\PRODUCT\ORADATA\FGUARD\REDO01.LOG
As you can see db_recovery_file_dest_size of 2048 MB is 2.21% used shows that we have emptied all!

Then connect as SYS and issue the following command to archive the remaining logs:
ALTER SYSTEM ARCHIVE LOG ALL;
This command archives all unarchived log files. This should cause the archiver to start.



Recompiling Invalid Schema Objects
The DBA_OBJECTS view can be used to identify invalid objects using the following query:
COLUMN object_name FORMAT A30
SELECT substr(owner,1,18) owner, object_type, substr(object_name,1,30) object_name, status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

There are FOUR ways to recompile invalid objects in schema.
1. DBMS_DDL
2. DBMS_UTILITY
3. UTL_RECOMP
4. UTLRP.SQL

1- DBMS_DDL.ALTER_COMPILE
Definition
This procedure is equivalent to the following SQL statement:
ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]

Syntax
Exec dbms_ddl.alter_compile ( type , schema, name);
Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.
Schema : Database Username
Name : Objects name

Example
exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');

2- DBMS_UTILITY.COMPILE_SCHEMA
The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus:
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

If you are using 10g, you can use the following to recompile ONLY the invalid ones:
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT', compile_all => 'FALSE');

3- UTL_RECOMP
The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads.  The following examples show how these procedures are used:

-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

There are a number of restrictions associated with the use of this package including:

4- utlrp.sql and utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.



DBMS_XPLAN
In version 9, Oracle finally provides a utility that formats the contents of the plan table. The plan table is one that is used to hold the results of an "Explain Plan" for a particular SQL statement. The output from the explain plan shows the anticipated optimizer execution path, along with the estimated cost of the statement without actually executing the statement against the database. The DBA or developer first needs to create the plan table. The DDL for this table is in the $ORACLE_HOME/rdbms/admin/utllxplan.sql file. The next step in using dbms_xplan is running Explain Plan for a statement.
explain plan for select * from flowdocument where amount > 100000;

The command above will populate the plan table with the data returned from the optimizer. Next, the dbms_xplan utility can be used to view the output

select * from table(dbms_xplan.display);

----------------------------------------------------------------------
| Id  | Operation            |  Name         | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |     1 |   168 |     3 |
|   1 |  TABLE ACCESS FULL   | FLOWDOCUMENT  |     1 |   168 |     3 |
----------------------------------------------------------------------


Delete Duplicate Records

--Very Efficient, deleted 685 rows over a 5443932 rows table in 3 Min
DELETE FROM &&table_name
   WHERE rowid NOT IN (SELECT max(rowid)
                         FROM &&table_name
                         GROUP BY &colums_with_duplicates);

Another way
SELECT a.rowid
   FROM &&table_name a
   WHERE a.rowid > (SELECT min(b.rowid)
                       FROM &&table_name b
                       WHERE a.&&column_name = b.&&column_name);

Then:
DELETE from &&table_name
    WHERE a.rowid > (SELECT min(b.rowid)
                       FROM &&table_name b
                       WHERE a.&&column_name = b.&&column_name);

**** Most efficient way to remove duplicate rows
This script uses a hash join -- the most efficient way of joining huge tables -- to find duplicate rows.
-- Set hash join enabled
DELETE FROM <table>
  WHERE rowid IN (SELECT t1.rowid
                    FROM <table> t1, <same-table> t2
                    -- primary key is (a1, a2)
                    WHERE t1.a1 = t2.a1
                      AND t1.a2 = t2.a2
                      AND t1.rowid < t2.rowid);

****Another METHOD (by METALINK) to find duplicates for one field****
To find duplicate keys from a table tx:
select key, count(key) no_of_duplicates
   from tx
   group by key
   having count(key) > 1;

 
*** This script will remove duplicate rows. Suppose a table contains 3 columns. To remove the duplicate rows write the following command, where a and b are aliases of the same table. 
Delete from table_A a
   where a.rowid > any (select rowid
                           from Table_B b

                           where a. = b.
                             and a. = b.
                             and a. = b.);


Shrinking Datafiles
For example, if a datafile is 100Meg in size, and 70Meg of the datafile is currently in use. Then atleast 70Meg must be left in the datafile. The RESIZE parameter of the ALTER DATABASE command is used to reclaim the space.
ALTER DATABASE datafile '/db01/oracle/fix/data03.ora'  resize 80M;

Reduce UNDO Tablespace
The Undo tablespace file can grow quickly if you load data in the database. To reduce the size of the file, you have to recreate the default undo tablespace because the command
        alter database datafile 'XXX' resize XM;
will not work.

Create a second undo tablespace
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/data/u01/app/oracle/oradata/CCOM/undotbs2.dbf' SIZE 50M AUTOEXTEND OFF ;

Set the second undo tablespace as default undo
ALTER SYSTEM SET undo_tablespace = UNDOTBS2 ;

Drop the first undo tablespace
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;

Create the first undo tablespace
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/data/u01/app/oracle/oradata/CCOM/undotbs01.dbf' SIZE 500M AUTOEXTEND ON NEXT 5M MAXSIZE 1000M ;

Set the first undo tablespace as default
ALTER SYSTEM SET undo_tablespace = UNDOTBS1 ;

Drop the second undo tablespace
DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES ;


Reduce TEMP Tablespace
In many database configurations, the DBA will choose to allow their temporary tablespace (actually the tempfile(s) for the temporary tablespace) to autoextend. A runaway query or sort can easily chew up valuable space on the disk as the tempfiles(s) extends to accommodate the request for space. The obvious action would
be to resize the tempfiles using the following statement:

SQL> alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M;
alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

The procedures above document how to drop a temporary tablespace that is not the default temporary tablespace for the database. You will know fairly quickly if the tablespace is a default temporary tablespace when you are greeted with the following exception:

SQL> DROP TABLESPACE temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
In cases where the temporary tablespace you want to resize (using the drop/recreate method) is the default temporary tablespace for the database, you have several more steps to perform, all documented below. The first step you need to perform is create another temporary tablespace (lets call it TEMP2). The next step would be to remove the temporary tablespace you want to resize from being the default temporary tablespace (in our example, this will be a tablespace named TEMP) by making TEMP2 the default. Drop / recreate the TEMP tablespace to the size you want. Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace. A full example session is provided below:
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/data/u01/app/oracle/oradata/CCOM/TEMP2.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/data/u01/app/oracle/oradata/CCOM/TEMP01.dbf' SIZE 300M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Some good queries:
--Identify Who is using which Tablespace
select srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks, a.sid,
a.serial#, a.username, a.osuser, a.status
from v$session a, v$sort_usage srt
where a.saddr = srt.session_addr
order by srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks;


--Space used by TEMP Tablespace
select file_name, bytes,maxbytes,increment_by from dba_temp_files;

ALTER DATABASE TEMPFILE 'E:\ORACLE\ORADATA\ADWT\TEMP02.DBF' RESIZE 6000M;




Transportable Tablespaces
1- Make the tablespace Read-Only = alter tablespace xxxx  read only;
2- Export it connecting as sys as sysdba =    exp file=tt.dmp log=tt.log tablespaces=xxxx transportable_tablespaces=y
3- Copy the .dmp file and the data_files to the destination
4- Put the tablespace back in write mode = alter tablespace xxxx  read write;
5- In the destination offline and drop the tablespace if exists
6- Import = imp file=tt.dmp log=tt.log tablespaces=test transportable_tablespace=y datafiles=(......., ........)
 


Orakill Utility
The orakill utility is provided with Oracle databases on Windows platforms. The executable (orakill.exe) is available to DBA's to kill Oracle sessions directly from the DOS command line without requiring any connection to the database.
In the Unix world, a DBA can kill a shadow process by issuing the kill -9 commands from the Unix prompt. Unix is able to provide this capability given that the Unix operating system is based on processes that fork other processes. All processes can be listed by using the ps Unix command. The Oracle background processes will be listed separately from all of the Oracle sessions since they have their own process.
Unlike the Unix operating system, Windows systems are thread-based. The background processes and sessions are all contained within the ORACLE.EXE executable and are not listed in the "Processes" tab of Windows Task Manager.
The orakill utility serves the same purpose as kill -9 in Unix. The command requires the instance and the SPID of the thread to kill. The following query will return the SPID for each user connected to the database:

select a.username, a.osuser, b.spid
   from v$session a, v$process b
   where a.paddr = b.addr
     and a.username is not null;

USERNAME                     OSUSER                   SPID
---------------------------- ------------------------ -----
SCOTT                        Scott                    3116
AMOORE                       Alex                     4760
DMOORE                       Dave                     768

Given the SPID for each user listed above, the session for any user can be killed with the orakill command.
C:\oracle9i\bin>orakill ORCL92 4760

Why does Oracle provide a utility to kill sessions from the DOS prompt when a DBA could kill a user session from within Oracle? The following command will also kill the user session:
alter system kill session(sid, serial#);

The sid and serial# used in the command above can be obtained from the v$session view. There are a few reasons a DBA could use orakill instead of the alter system kill session command.
1. The alter system statement will not clear the locks if any exist. Instead, the session will remain connected until it times out, then the session is killed and the locks are released. The orakill command will kill the thread and the locks instantly.
2. A DBA may not be able to gain access to a SQL prompt due to a runaway query consuming all system resources. In this case, the session can be killed without ever logging in to the database.
The orakill utility should be used as a last resort only. If the session cannot be killed more gracefully (via alter system kill session), or the instance is inaccessible via SQL, then orakill can be used to terminate the offending session.
Background processes should not be terminated, only user sessions. Killing a background process can cause serious Oracle errors and can bring the database down. 





The PL/SQL WRAP Utility
The WRAP encrypts PL/SQL code displaying it in hexadecimal format. Use the following command:

wrap iname=script.sql

The output file will be called script.plb. To rename the output file, use the oname option of Wrap (i.e., oname=output.sql).

COPY Command
It allows data to be copied between databases (or within the same database) via SQL*PLUS. The greatest ability of this command is to COMMIT alfter each array of data. Though the copy of an extremely large table can tax the rollback segments, it is possible to break the transaction into smaller entries. The syntax for this command is:

COPY FROM
remote username/remote password@connect string
TO
username/password@connect string
{APPEND, CREATE, INSERT, REPLACE]
table name using subquery

To set the transaction entry size, use the SQL*PLUS SET command to set a value for the ARRAYSIZE parameter. This determines the number of records that will be retrieved in each "batch". The COPY COMMIT parameter tells SQL*PLUS how many batches should be committed at one time. In the following example, the data is committed after every 1,000 records. This reduces the transaction's rollback segment entry size.
SET COPYCOMMIT 1;
SET ARRAYSIZE 1000
COPY FROM batman/androbin @t:gotham:city CREATE batmobile USING SELECT * FROM bat_mobile;
NOTE: The feedback from this command is not acqurate. After the final commit is complete, the database reports the number of records that were committed in the last batch. It does not report the total number of records committed.
 


Authid Current_User vs Authid Definer
A stored procedure is either run with the rights of the caller (authid current_user) or with the rights of the procedure's owner (authid definer).
This authid clause immediatly follows the create procedure or create function statement.
 It can be ommited, in which case the default authid definer is taken. Example:
create procedure pu
 authid definer
as
  v_d t.d%type;
begin
  select d into v_d from u;
  dbms_output.put_line(v_d);
end;
/

or

CREATE OR REPLACE
Package fg_mc_web_insert_update AUTHID CURRENT_USER
  IS
.....




- How to find what is locking a table.
       1. Query from DBA_OBJECTS to find the object_name of the table getting locked.
       2. Query from V$LOCK where id1 = 'table_name', get sid.
       3. Query from v$PROCESS where pid = sid. THis has info on what is locking the table.


Generate Random Data
In some cases you may need to generate random data, this is very easy to perform by using the dbms_random package. Examples:

Generating a random number (positive or negative)
select dbms_random.random from dual;
    RANDOM
----------
-744437494


Generating a random number between 0 and 1.
select dbms_random.value from dual;
     VALUE
----------
.313051535

Generating a random number from a range, between 1 to 1000.
select dbms_random.value(1,1000) num from dual;
       NUM
----------
182.888725

Generating a 12 digit random number.
select dbms_random.value(100000000000, 999999999999) num from dual;
       NUM
----------
7.0770E+11

Generating an upper case string of 20 characters
select dbms_random.string('U', 20) str from dual;
STR
----------------------
KUKWEDHNTNUNVBFDOEBG

Generating a lower case string of 20 characters
select dbms_random.string('L', 20) str from dual;
STR
---------------------
zhddilwfvxqfftoktbyq

Generating an alphanumeric string of 20 characters
select dbms_random.string('A', 20) str from dual;
STR
---------------------
vWFNaoTvlSzPfQpkLPEB

Generating an upper case alphanumeric string of 20 characters
select dbms_random.string('X', 20) str from dual;
STR
---------------------
0XQYACWL35RHL5IH2I11

Generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.
select dbms_random.string('P', 20) str from dual;
STR
--------------------
jqPJ3IxH%]Ij}_Y2j!p[

Select Randomly from Table
This feature allows you to randomly "sample" from a table. This feature has many great uses. The syntax is as follows:

SELECT COUNT(*) * 100 FROM EMP SAMPLE (1);

This will randomly sample 1% of the rows, multiple the count of them x 100 to get a rough estimate of the amount of rows in the table.
You can also randomly sample by blocks for better performance but possibly less random:

SELECT * FROM EMP SAMPLE BLOCK (1);

Again, this samples roughly 1% of the table by blocks, which may not be 1% of the actual rows. But this will cause fewer blocks to be visited and decrease the elapsed time, but if the data is grouped in the table, it may not be very random.
This tool can be used to get a rough idea of the data in the table, or give good estimates when using group functions. For example, a great use of this would be on a 40 million row table:

SELECT AVG(number_of children) * 20 FROM dependants sample (5);

This will give you an average of the number of dependants by only sampling 5% of the table by only visiting 2 million rows and not 40 million.
 


Disk Space Needed for the Archive Files
The output of the above script tells you how many log switches are occurring on your system on a daily basis.  The actual disk space that is required to serve the archiving is given as well in MB.  All you need to determine if the amount of log switches are stable or difference a lot from day to day.  First obtain the size in Mb of your online redo log files, you can execute the following query:

SELECT distinct(to_char((bytes*0.000001),'9990.999')) size_mb FROM v$log;
SIZE_MB
---------
   52.429

Then run:
column ord     noprint
column date_   heading 'Date'         format A15
column no      heading '#Arch files'  format 9999999
column no_size heading 'Size Mb'      format 9999999
compute avg of no      on report
compute avg of no_size on report
break on report
select MAX(first_time) ord, to_char(first_time,'DD-MON-YYYY') date_,
       count(recid) no, count(recid) * &logfile_size no_size
from v$log_history
group by to_char(first_time,'DD-MON-YYYY')
order by ord;
clear breaks
clear computes
clear columns


Unlocking Users
alter user &username account unlock;



Grab Execution Time (DBMS_Utility.get_time)
In order to get the Execution time of a PL/SQL, you can use the following:
set serveroutput on
declare
   v_starttime number;
begin
   v_starttime := dbms_utility.get_time;
   --your code here
   dbms_lock.sleep(10);

   dbms_output.put_line('Elapsed Time: ' || (dbms_utility.get_time - v_starttime)/100 || ' seconds....' );
end;
/
Elapsed Time: .10 seconds....


Using orapwd to Connect Remotely as SYSDBA
The Oracle orapwd utility assists the DBA with granting SYSDBA and SYSOPER privileges to other users.  By default, the user SYS is the only user that has these privileges.  Creating a password file via orapwd enables remote users to connect with administrative privileges through SQL*Net.  The SYSOPER privilege allows instance startup, shutdown, mount, and dismount.  It allows the DBA to perform general database maintenance without viewing user data.  The SYSDBA privilege is the same as connect internal was in prior versions.  It provides the ability to do everything, unrestricted.  If orapwd has not yet been executed, attempting to grant SYSDBA or SYSOPER privileges will result in the following error:
SQL> grant sysdba to scott; 
ORA-01994: GRANT failed: cannot add users to public password file

The following steps can be performed to grant other users these privileges: 
1.      Create the password file.  This is done by executing the following command: 
    $ orapwd file=filename  password=password entries=max_users
The filename is the name of the file that will hold the password information.  The file location will default to the current directory unless the full path is specified.  The contents are encrypted and are unreadable. The password required is the one for the SYS user of the database.  The max_usersis the number of database users that can be granted SYSDBA or SYSOPER.  This parameter should be set to a higher value than the number of anticipated users to prevent having to delete and recreate the password file.  

2.      Edit the init.ora parameter remote_login_passwordfile.  This parameter must be set to either SHARED or EXCLUSIVE.When set to SHARED, the password file can be used by multiple databases, yet only the SYS user is recognized.  When set to EXCLUSIVE, the file can be used by only one database, yet multiple users can exist in the file.  The parameter setting can be confirmed by: 
SQL> show parameter password
NAME                          TYPE        VALUE
----------------------------- ----------- ----------
remote_login_passwordfile     string      EXCLUSIVE

3.      Grant SYSDBA or SYSOPER to users.  When SYSDBA or SYSOPER privileges are granted to a user, that user's name and privilege information are added to the password file. 
SQL> grant sysdba to scott;
Grant succeeded.

4.      Confirm that the user is listed in the password file. 
SQL> select * from v$pwfile_users;
USERNAME                       SYSDBA SYSOPER
------------------------------ ------ -------
SYS                            TRUE   TRUE
SCOTT                          TRUE   FALSE

Now the user SCOTT can connect as SYSDBA.  Administrative users can be connected and authenticated to a local or remote database by using the SQL*Plus connect command.  They must connect using their username and password, and with the AS SYSDBA or AS SYSOPER clause:
SQL> connect scott/tiger as sysdba;

The DBA utilizes the orapwd utility to grant SYSDBA and SYSOPER privileges to other database users.  The SYS password should never be shared and should be highly classified. 



Change DB Name and File paths
A common way to rename all the database and all data files is to rebuild the control file. This can be very labor intensive, however, and possibly dangerous if you have a large number of data files. You could make a mistake and lose a data file or two in the process because the database does not check the validity of the data files when you do a control file rebuild. Here is a low-stress, easy solution:

Step 1
Run the scripts below (change directories for your server) with the target database open. Spool out a file for each of your available mount points.
Yes, we will be trying to move the data files more then once, however, Oracle checks whether the data file exists before accepting the command and returns errors if it does not exist. This uses the Oracle Server to verify the validity of our paths and is completely goof proof!

Data File Script
select 'alter database rename file '''||file_name||''' to ', '''/ora01/oradata/sid/'|| substr(file_name,instr(file_name,'/',-1)+1) ||''';'
from dba_data_Files

Redo Log Script
select 'alter database rename file '''||member||''' to ', '''/ora01/oradata/sid/'|| substr(member, instr(member,'/',-1)+1)||''';'
from v$logfile;


Step 2
Connect as SYS
shutdown immediate;
startup mount;

Run each of the scripts your have created.
 
SQL> alter database open;

Check to make sure all your data files are pointing to the new mount points.
SQL> select file_name from dba_data_files;

If you just needed to rename the paths of your data files, you are done. If you also need to rename the database, you have one more step.

Step 3
After you have verified that all the datafiles and redo logs point to the correct path, we will use the nid Oracle Command to rename the Database.
Use "nid" command-line (Example: testdb2 -> testdb)
shutdown immediate;
startup mount;
exit;

$ nid target=system/password DBNAME=testdb

DBNEWID: Release - Production on Tue Sep 9 12:39:40 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to database TESTDB2 (DBID=986660483)

Connected to server version

Control Files in database:
/oradata/testdb2/control01.ctl
/oradata/testdb2/control02.ctl
/oradata/testdb2/control03.ctl

Change database ID and database name TESTDB2 to TESTDB? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 986660483 to 2448266380
Changing database name from TESTDB2 to TESTDB
Control File /oradata/testdb2/control01.ctl - modified
Control File /oradata/testdb2/control02.ctl - modified
Control File /oradata/testdb2/control03.ctl - modified
Datafile /oradata/testdb2/system01.dbf - dbid changed, wrote new name
Datafile /oradata/testdb2/sysaux01.dbf - dbid changed, wrote new name
Datafile /oradata/testdb2/undotbs01.dbf - dbid changed, wrote new name
Datafile /oradata/testdb2/users01.dbf - dbid changed, wrote new name
Datafile /oradata/testdb2/example01.dbf - dbid changed, wrote new name
Datafile /oradata/testdb2/test01.dbf - dbid changed, wrote new name
Datafile /oradata/testdb2/temp01.dbf - dbid changed, wrote new name
Control File /oradata/testdb2/control01.ctl - dbid changed, wrote new name
Control File /oradata/testdb2/control02.ctl - dbid changed, wrote new name
Control File /oradata/testdb2/control03.ctl - dbid changed, wrote new name
Instance shut down

Database name changed to TESTDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database TESTDB changed to 2448266380.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

-> modified parameter file [don't forget]
*.db_name='testdb'

-> gererated new password file.

If found
ORA-01103: database name 'TESTDB' in control file is not 'TESTDB2'

Perhaps should check parameter file and modify


SQL> startup mount;
ORACLE instance started.

Total System Global Area 1803841536 bytes
Fixed Size 2145304 bytes
Variable Size 956302312 bytes
Database Buffers 838860800 bytes
Redo Buffers 6533120 bytes
Database mounted.

SQL> alter database open RESETLOGS;

Database altered.


SQL> select NAME from v$database;

NAME
---------
TESTDB


Get IP and Name of Server
--Get Server Name and IP Address
declare
  v_host_name v$instance.host_name%type;
  v_ip_address varchar2(50);
begin
  select host_name into v_host_name from v$instance;
  dbms_output.put_line('the database server name is ' || v_host_name);
  SELECT UTL_INADDR.GET_HOST_ADDRESS(v_host_name) into v_ip_address FROM DUAL;
  dbms_output.put_line('the database server ip address is ' || v_ip_address);
end;
/

SELECT host_name, UTL_INADDR.GET_HOST_ADDRESS(host_name) ip FROM v$instance;


  
Creating Scott Example Schema
If it is not already present create the SCOTT schema:
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlsampl.sql

Create a PLAN_TABLE if it does not already exist:

conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT INSERT, UPDATE, DELETE, SELECT ON sys.plan_table TO public;

Install Oracle Java Virtual Machine
How does one install the Oracle JServer/JVM Option?
    * Make sure your database is started with large java_pool_size (>20M) and shared_pool_size (>50M) INIT.ORA parameter values.
    * Run the $ORACLE_HOME/javavm/install/initjvm.sql script from SYS AS SYSDBA to install the Oracle JServer Option on a database.
    * Grant JAVAUSERPRIV to users that wants to use Java:
          SQL> GRANT JAVAUSERPRIV TO FRAUDGUARD;

If you want to uninstall it just execute the following as SYS:
$ORACLE_HOME/rmjvm.sql



Pivot Table
create  table VOL_MONTHLY_REPORT
(
  COUNT_ITEMS_PROT                   VARCHAR2(16)   ,
  COUNT_ITEMS_OPEN                   NUMBER         ,
  DATECREATED                        DATE           ,
  APPLICATIONID                      NUMBER(2, 0)   ,
  ARCHIVEDDATE                       DATE           )
/

Select DATECREATED, APPLICATIONID, COUNT_ITEMS_OPEN
From VOL_MONTHLY_REPORT
order by 1,2;

DATECREATED        APPLICATIONID COUNT_ITEMS_OPEN
------------------ ------------- ----------------
01/JAN/07 00:00:00             3               10
01/JAN/07 00:00:00             4               20
02/JAN/07 00:00:00             3               33
02/JAN/07 00:00:00             4               44


SELECT DATECREATED,
DECODE (APPLICATIONID, 3, COUNT_ITEMS_OPEN, NULL) VRA,
DECODE (APPLICATIONID, 4, COUNT_ITEMS_OPEN, NULL) IRD
FROM (SELECT DATECREATED, APPLICATIONID, COUNT_ITEMS_OPEN FROM VOL_MONTHLY_REPORT);

DATECREATED               VRA        IRD

------------------ ---------- ----------
01/JAN/07 00:00:00         10
01/JAN/07 00:00:00                    20
02/JAN/07 00:00:00         33
02/JAN/07 00:00:00                    44




select DATECREATED, VRA, IRD
from (select DATECREATED,
        max(case when APPLICATIONID=3 then COUNT_ITEMS_OPEN else null end) VRA,
        max(case when APPLICATIONID=4 then COUNT_ITEMS_OPEN else null end) IRD
      from VOL_MONTHLY_REPORT
      group by DATECREATED);

DATECREATED               VRA        IRD

------------------ ---------- ----------
01/JAN/07 00:00:00         10         20
02/JAN/07 00:00:00         33         44

More information on Pivot Tables HERE


What Code is executed by a Session?
Use the following code to see the ACTIVE Sessions:
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
select substr(a.spid,1,9) pid, substr(b.sid,1,5) sid, substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box, substr(b.username,1,10) username,
--       b.server,
       substr(b.osuser,1,8) os_user, substr(b.program,1,30) program
from v$session b, v$process a
 where b.paddr = a.addr
   and type='USER'
   and status = 'ACTIVE'
order by spid;


Use the following script to retrieve the SQL statement for a particular user:
select SQL_TEXT from V$SQLAREA
  where (address, hash_value)
     IN (select SQL_ADDRESS, SQL_HASH_VALUE
              from V$SESSION
              where SID = &SID);




Export Data to Excel
set feed off markup html on
set pagesize 0
alter session set nls_date_format=’YYYY-MM-DD’;
spool emp.xls
  select * from emp;
spool off
set markup html off
exit

or with a Nicer Format:

set term off
set verify off
set feedback off
set markup HTML on spool on head " " -
BODY "TEXT='#000000'" -
TABLE "WIDTH='50%' BORDER='1'"

column count(*) heading 'TOTAL ITEMS' format 999999;
column accountnumber heading 'Account Number' format 0999999999999999;

--set pagesize 0

spool IRDs_Counts.xls
select accountnumber, count(*)
  from flowdocument
  where documentid in (select documentid
                        from flowexceptioncode
                        where exceptionid > 1)
    and trunc(paiddate) = trunc(sysdate-1)
    and isird > 3
  group by accountnumber
  having count(*) > 3
  order by count(*) desc;

spool off
set markup html off
exit






Managing Redo Log Files and Groups
--First Review the groups
select group#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE

-- Then Review the members per group
select group#, member from v$logfile;
    GROUP#  MEMBER
----------  ----------------------------------------------------------
         1  C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEV10G2\REDO01.LOG
         2  C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEV10G2\REDO02.LOG
         3  C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEV10G2\REDO03.LOG

-- In order to remove a group to increase its size, that group must be inactive:
alter database drop logfile group 2;
alter database add logfile group 2 ('/opt/oracle/oradata/XGUARD/redo02.log') size 600M reuse;

--You can force the switch by using:
alter system switch logfile;


Working With Sequences
Alter Sequence
Change Increment
ALTER SEQUENCE <sequence_name> INCREMENT BY <integer>;
ALTER SEQUENCE seq_inc_by_ten INCREMENT BY 20;

Change Max Value
ALTER SEQUENCE <sequence_name> MAX VALUE <integer>
ALTER SEQUENCE seq_maxval MAXVALUE 10;

Change Cycle
ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>
ALTER SEQUENCE seq_cycle NOCYCLE;

Change Cache
ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE
ALTER SEQUENCE seq_cache NOCACHE;

Change Order
ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>
ALTER SEQUENCE seq_order NOORDER;
 
Drop Sequence
DROP SEQUENCE <sequence_name>;
DROP SEQUENCE seq_cache;
 
Sequence Resets
By finding out the current value of the sequence and altering the increment by to be negative that number and selecting the sequence once -- the sequence can be reset to 0.
If any session attempts to use the sequence while this is happening an ORA-08004 error will be generated.    
CREATE SEQUENCE seq;
SELECT seq.NEXTVAL FROM dual;
SELECT seq.NEXTVAL FROM dual;
SELECT seq.NEXTVAL FROM dual;

COLUMN S new_val inc;
SELECT seq.NEXTVAL S FROM dual;

ALTER SEQUENCE seq INCREMENT BY -&inc MINVALUE 0;
SELECT seq.NEXTVAL S FROM dual;

ALTER SEQUENCE seq increment by 1;
SELECT seq.NEXTVAL FROM dual;
/

Sequence Related Queries
Last Number Selected From Sequence
SELECT sequence_name, last_number
   FROM user_sequences;

Next Number From Sequence    
SELECT sequence_name, (last_number + increment_by) NEXT_VALUE
FROM user_sequences;


Restrict Access by IP Address
There are 2 ways to perform this action:

Using sqlnet.ora
By default, the listener will attempt to service any request that comes in on the port that is being listened to. But if you are using the TCP/IP protocol, then you can actually give a list of IP addresses from which requests should be accepted or ignored. To enable this feature, add the following lines on your sqlnet.ora file:
tcp.validnode_checking=yes
tcp.invited_nodes=(address1, address2, ...)
tcp.excluded_nodes=(address1, address2, ...)
Lists of nodes to invite (accept requests from) or exclude (ignore requests from) may be as long as you like. The addresses themselves can be host names or numeric IP addresses. Unfortunately, you cannot specify ranges of IP addresses or network masks. Typically, you just provide a list of invited nodes (meaning all others should be excluded) or a list of excluded nodes (meaning all others should be invited).
Once you have modified the sqlnet.ora file, simply restart or reload the listener. This will cause the changes you have made to the protocol.ora file to take effect. Any request coming from an excluded node (or not included on the list of invited nodes) will be ignored.

From the documentation:
TCP.EXCLUDED_NODES
Purpose: Use the parameter TCP.EXCLUDED_NODES to specify which clients are denied access to the database.
Syntax:
TCP.EXCLUDED_NODES=(hostname | ip_address, hostname | ip_address, ...)
Example:
TCP.EXCLUDED_NODES=(finance.us.acme.com, mktg.us.acme.com, 144.25.5.25)

TCP.INVITED_NODES
Purpose:  Use the parameter TCP.INVITED_NODES to specify which clients are allowed access to the database. This list takes precedence over the TCP.EXCLUDED_NODES parameter if both lists are present.
Syntax:
TCP.INVITED_NODES=(hostname | ip_address, hostname | ip_address, ...)
Example:
TCP.INVITED_NODES=(sales.us.acme.com, hr.us.acme.com, 144.185.5.73)


2- Another way to perform the same, is to have a startup trigger that for each connection validates the IP of that connection and compare the IP against a table or values and allow/restrict the access.
We can get the current connection details by executing the following sentence inside the trigger:
select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;
I'd suggest to go with the first approach, because the 2nd one may slow down just a little the time to connect.


Connecting to a DB without TNSNAMES.ora
Configurationless connections (Direct Connect)enable you to directly connect to the database server without having a service configured in the TNSNAMES.ORA file.

Syntax:
sqlplus username/password@[//][hostname][:db_port][/db_sid]

for example:
sqlplus scott/tiger@//192.168.1.100:1521/orcl

where:
- scott/tiger : username and password of the schema you will connect to.
- 192.168.1.100 : the IP Address for the database server.
- 1521 : database port.
- orcl : db_sid

In order for this to work, you need to add a parameter to the sqlnet.ora file on the database server in this path $ORACLE_HOME/network/admin
NAMES.DIRECTORY_PATH=(ezconnect,tnsnames)


Flushing the Shared Pool and the Buffer Cache
Prior to Oracle Database 10g, the only way to flush the database buffer cache was to shut down the database and restart it. Oracle Database 10g now allows you to flush the database buffer cache with the alter system command using the flush buffer_cache parameter.
The FLUSH Buffer Cache clause is useful if you need to measure the performance of rewritten queries or a suite of queries from identical starting points. Use the following statement to flush the buffer cache.
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
However, note that this clause is intended for use only on a test database. It is not advisable to use this clause on a production database, because subsequent queries will have no hits, only misses.
 

Resetting a table's HWM in Oracle 10g

Traditionally, in order to reset the High Water Mark(HWM), the table will be truncated and then reloaded with the new set of data. But in Oracle 10g, with the help of alter table <xxx> shrink space command, we can reclaim the wasted space in our database as well as reset the HWM also. But keep in mind, prior to using this command on your table, there are some pre-requisites.

 

1. The tablespace in which the table is present must be of Auto Segment Space Management.

2. Row Movement must be enabled in the table. If it is not enabled at the time of creation, then you can use:

ALTER TABLE <TABLE-NAME> ENABLE ROW MOVEMENT;

 

Once the above mentioned pre-requisites are satisfied, then you can use the following command to shrink space and reset the HWM.

ALTER TABLE <TABLE-NAME> SHRINK SPACE CASCADE;

 

If you just want to only defrag the table and not reset the HWM or reclaim the space due to active DML transactions, you can use:

ALTER TABLE <TABLE-NAME> SHRINK SPACE CASCADE COMPACT;

 

In order to see how many empty blocks are still there in the table, first analyze the table and then you can use:

select table_name, blocks, empty_blocks, last_analyzed, global_stats from user_tables where table_name=<TABLE-NAME>;



Detect Block Corrupted
SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = &File_ID
and &Block_Corrupted between block_id AND block_id + blocks - 1;


Print_Table Procedure
The following code will let you select data from SQL*Plus in a nice format:
create or replace procedure print_table ( p_query in varchar2,
                                          p_date_fmt in varchar2 default 'dd-MON-yyyy hh24:mi:ss' )
                                                                                                                           
-- this utility is designed to be installed ONCE in a database and used
-- by all.  Also, it is nice to have roles enabled so that queries by
-- DBA's that use a role to gain access to the DBA_* views still work
-- that is the purpose of AUTHID CURRENT_USER
AUTHID CURRENT_USER
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    l_cs            varchar2(255);
    l_date_fmt      varchar2(255);
                                                                                                   
                       
    -- small inline procedure to restore the sessions state
    -- we may have modified the cursor sharing and nls date format
    -- session variables, this just restores them
    procedure restore
    is
    begin
       if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
       then
           execute immediate
           'alter session set cursor_sharing=exact';
       end if;
       if ( p_date_fmt is not null )
       then
           execute immediate
               'alter session set nls_date_format=''' || l_date_fmt || '''';
       end if;
       dbms_sql.close_cursor(l_theCursor);
    end restore;

begin
    -- I like to see the dates print out with times, by default, the
    -- format mask I use includes that.  In order to be "friendly"
    -- we save the date current sessions date format and then use
    -- the one with the date and time.  Passing in NULL will cause
    -- this routine just to use the current date format
    if ( p_date_fmt is not null )
    then
       select sys_context( 'userenv', 'nls_date_format' )
         into l_date_fmt
         from dual;
                                                                                                   
                       
       execute immediate
       'alter session set nls_date_format=''' || p_date_fmt || '''';
    end if;
                                                                                                   
                       
    -- to be bind variable friendly on this ad-hoc queries, we
    -- look to see if cursor sharing is already set to FORCE or
    -- similar, if not, set it so when we parse -- literals
    -- are replaced with binds
    if ( dbms_utility.get_parameter_value
         ( 'cursor_sharing', l_status, l_cs ) = 1 )
    then
        if ( upper(l_cs) not in ('FORCE','SIMILAR'))
        then
            execute immediate
           'alter session set cursor_sharing=force';
        end if;
    end if;
                                                                                                   
                       
    -- parse and describe the query sent to us.  we need
    -- to know the number of columns and their names.
    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns
    ( l_theCursor, l_colCnt, l_descTbl );
                                                                                                   
                       
    -- define all columns to be cast to varchar2's, we
    -- are just printing them out
    for i in 1 .. l_colCnt loop
        if ( l_descTbl(i).col_type not in ( 113 ) )
        then
            dbms_sql.define_column
            (l_theCursor, i, l_columnValue, 4000);
        end if;
    end loop;
                                                                                                   
                       
    -- execute the query, so we can fetch
    l_status := dbms_sql.execute(l_theCursor);
                                                                                                   
                       
    -- loop and print out each column on a separate line
    -- bear in mind that dbms_output only prints 255 characters/line
    -- so we'll only see the first 200 characters by my design...
    while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
    loop
        for i in 1 .. l_colCnt loop
            if ( l_descTbl(i).col_type not in ( 113 ) )
            then
                dbms_sql.column_value
                ( l_theCursor, i, l_columnValue );
                dbms_output.put_line
                ( rpad( l_descTbl(i).col_name, 30 )
                || ': ' ||
                substr( l_columnValue, 1, 200 ) );
            end if;
        end loop;
        dbms_output.put_line( '--------------------------------------------------' );
    end loop;
                                                                                                   
                       
    -- now, restore the session state, no matter what
    restore;
exception
    when others then
        restore;
        raise;
end;
/
------------------------------------------------------------------

create or replace public synonym print_table for system.print_table;
grant execute on print_table to public;



Example:
SQL> exec print_table( 'select * from T where a = ''X'' ' );

exec print_table( 'select * from center ' );

exec print_table( 'select * from center centercode = ''TDBN5'' ' );



Unlimit Password 11g
ALTER PROFILE DEFAULT LIMIT
  FAILED_LOGIN_ATTEMPTS UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED;


Checking for 2 columns in an IN clause
NON-PAIRWISE Comparison Subquery
This type of subquery is the "standard" one, where you use an "IN" with an "AND" clause. Example:
SELECT employee_id, manager_id, department_id
  FROM employees
 WHERE manager_id IN (SELECT manager_id FROM employees WHERE first_name = 'John')  --inner query has values 100, 108,123
   AND department_id IN (SELECT department_id FROM employees WHERE first_name = 'John')  --inner query has values 50, 80, 100
   AND first_name != 'John'
 ORDER BY 1,2,3;

Using non pairwise comparison you compare a single value (manager_id) extracted from the current record with a set of single values extracted by the subquery.
Then you compare a single value (department_id) extracted from the current record with a set of single values extracted by the subquery


But there are opportunities where you may need to get EXACTLY a specific pair of objects, in that case you can use PAIRWISE COMPARISON SUBQUERY:
SELECT employee_id, manager_id, department_id
  FROM employees
   --inner query returns pair of values (108,100), (123,50) and (100,80)
 WHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE first_name = 'John')
   AND first_name != 'John'
 ORDER BY 1,2,3;
   
Using pairwise comparison you compare a "vector" of values (manager_id, department_id) extracted from the current record with a set of vectors of values extracted by the subquery.
The latter executes the subquery only once, while the former executes it twice.



Change to a Specific Session (Change user)
You can easily connect as another user, once you are connected as SYSTEM, just type the following:
ALTER SESSION SET CURRENT_SCHEMA=&SCHEMA_NAME;


Procedure to calculate DB growth and Schedule DB Job
Fastest Way

select to_char(creation_time, 'YYYY Month') "Month",

sum(bytes)/1024/1024 "Growth in Meg" 

from sys.v$datafile 

where creation_time > SYSDATE-365*2           /*Number of Years: This can be changed according to your requirement. */

group by to_char(creation_time, 'YYYY Month');



Procedural Way
1.Create the Table DB_GROWTH:
create  table DB_GROWTH
(
  DAY                   DATE ,
  DATABASE_SIZE_MB      NUMBER(10)   ,
  DAILY_GROWTH_MB       NUMBER(10) )
/

2. Create a Procedure to collect data
create or replace procedure DATABASE_GROWTH
AS
  today_size NUMBER;
  yesterday_size NUMBER;
  growth_size NUMBER;
  cnt NUMBER;
BEGIN
   SELECT sum(bytes)/(1024*1024) INTO today_size
     FROM SM$TS_USED;
   SELECT COUNT(1) INTO cnt
     FROM db_growth ;
   IF cnt > 0 THEN
      SELECT database_size_mb INTO yesterday_size
        FROM db_growth
        WHERE to_date(day,'dd-mon-yy')=to_date(SYSDATE -1,'dd-mon-yy');
   ELSE
      yesterday_size:=today_size;
   END IF;
   growth_size := today_size - yesterday_size;
   INSERT INTO db_growth
      VALUES(sysdate,today_size,growth_size);
EXCEPTION
   WHEN no_data_found THEN
      INSERT INTO db_growth VALUES(sysdate,today_size,0);
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/


3.Submit in DBMS_JOBS

variable jobno number;
begin
  dbms_job.submit(:jobno,'DATABASE_GROWTH ;',trunc(sysdate+1) + 4/24,'trunc(sysdate+1) + 4/24');
commit;
end;
/
print :jobno


NOTE = After 10g you can use OBJECT_GROWTH_TREND pipelined function
If you have purchased the extra cost performance and diagnostic packs, you can use the dbms_space.object_growth_trend to estimate object growth for any named table or index.
The dbms_space.object_growth_trend prediction mechanism is based on data collected and stored by the AWR, and the growth trend reporting is also built into the Oracle database kernel and is available by default.
Here is a very simpe example:

set serveroutput off
select substr(to_char(timepoint,'yyyy-MON-dd hh24:mi'),1,20) TIMEPOINT,
       round(SPACE_USAGE/1024/1024,2) "SPACE_USAGE(MB)",
       round(SPACE_ALLOC/1024/1024,2) "SPACE_ALLOC(MB)", QUALITY
from table(dbms_space.OBJECT_GROWTH_TREND('FRAUDGUARD','DIBATCH_ACCOUNT','TABLE'))
order by timepoint asc
/

TIMEPOINT         SPACE_USAGE(MB) SPACE_ALLOC(MB) QUALITY
----------------- --------------- --------------- ------------------
2012-JAN-12 09:52             .17             .25 INTERPOLATED
2012-JAN-13 09:52             .17             .25 INTERPOLATED
2012-JAN-14 09:52             .17             .25 INTERPOLATED
2012-JAN-15 09:52             .17             .25 INTERPOLATED
2012-JAN-16 09:52             .17             .25 INTERPOLATED
2012-JAN-17 09:52             .17             .25 INTERPOLATED
2012-JAN-18 09:52             .17             .25 INTERPOLATED
2012-JAN-19 09:52             .17             .25 INTERPOLATED
2012-JAN-20 09:52             .17             .25 INTERPOLATED
2012-JAN-21 09:52             .17             .25 PROJECTED
2012-JAN-22 09:52             .17             .25 PROJECTED
2012-JAN-23 09:52             .17             .25 PROJECTED
2012-JAN-24 09:52             .17             .25 PROJECTED
2012-JAN-25 09:52             .17             .25 PROJECTED




The SPACE_USAGE column shows how many bytes the object actually consumes.
The SPACE_ALLOC reports the size, in bytes, of space used by the object.
The QUALITY column indicates the quality of the output as follows:
    - GOOD - The data for the timepoint relates to data within the AWR repository with a timestamp within 10% of the interval.
    - INTERPOLATED - The data for this timepoint did not meet the GOOD criteria but was based on data gathered before and after the timepoint.
    - PROJECTED - The timepoint is in the future, so the data is estimated based on previous growth statistics.






Monitor Long Operations

Sometimes you run a long process (like RMAN or Index Rebuild) and you want to know how long is going to take. You can get that information from the V$SESSION_LONGOPS table if you know the SID from v$session:
SELECT MESSAGE
FROM V$SESSION_LONGOPS
WHERE SID IN (SELECT SID FROM V$SESSION WHERE USERNAME='SYS' AND STATUS='ACTIVE')
ORDER BY START_TIME;



Remove Special Characters Use following Replace Functions
REGEXP_REPLACE(<Your_String>,'[^[:alnum:]'' '']', NULL)

Example
SELECT REGEXP_REPLACE('##$$$123&&!!__!','[^[:alnum:]'' '']', NULL) FROM dual;

OutPut
123

OR

Use TRANSLATE function as given below
Example
SELECT translate('##$$$1$$2#3&&!!__!', '[0-9]#$&&!_','[0-9]') FROM dual;

OR

SELECT translate('##$$$123&&!!__!', '0#$&&!_','0') FROM dual;


Number of Transactions

set lines 120
col "Total Commits" for 999,999,999
col "Total Rollbacks" for 999,999,999
col "Total User Calls" for 999,999,999
col "Avg Daily DML Transactions" for 999,999,999
col "Avg Daily User Calls" for 999,999,999
set lines 120 heading on pages 100

select v1 "Total Commits", v2 "Total Rollbacks", v3 "Total User Calls",
          t1 "Uptime in days", s1/t1 "Avg Daily DML Transactions", v3/t1 "Avg Daily User Calls"
from (select value v1 from v$sysstat where name='user commits'),
     (select value v2 from v$sysstat where name='user rollbacks'),
     (select sum(value) s1 from v$sysstat where name in ('user commits', 'user rollbacks')),
     (select value v3 from v$sysstat where name='user calls'),
     (select sysdate-startup_time t1 from v$instance);

The above query should give you an idea of how "busy" their database is since it was started.
The column showing "Total User Calls" is counting the number of select statements.

Total Commits Total Rollbacks Total User Calls Uptime in days Avg Daily DML Transactions Avg Daily User Calls
------------- --------------- ---------------- -------------- -------------------------- --------------------
       15,448               6           41,001     11.9276852                      1,296             3,437


If they want to know the #transactions / hour then you could execute the following query:
set pages 9999;
column c1 heading "Start|Time" format a30;
column c2 heading "End|Time" format a15;
column c3 heading "Total|Undo|Blocks|Used" format 9,999,999;
column c4 heading "Total|Number of|Transactions|Executed" format 999,999;
column c5 heading "Longest|Query|(sec)" format 999,999;
column c6 heading "Highest|Concurrent|Transaction|Count" format 9,999;
break on report
compute sum of c3 on report
compute sum of c4 on report

select TO_CHAR( TO_DATE(TO_CHAR(Begin_Time,'DD-MON-YY HH24'), 'DD-MON-YY HH24') , 'DD-MON-YY HH24') c1,
       SUM(Undoblks) c3, SUM(Txncount) c4, MAX(Maxquerylen) c5, MAX(Maxconcurrency) c6
from v$undostat
group by TO_CHAR( TO_DATE(TO_CHAR(Begin_Time,'DD-MON-YY HH24'), 'DD-MON-YY HH24') , 'DD-MON-YY HH24')
order by 1 asc ;