General Application Tips

Chane 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
SHUTDOWN the target database and STARTUP MOUNT.
Run each of the scripts your have created.
 
SQL> alter database open;

Step 3
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 4
After you have verified that all the datafiles and redo logs point to the correct path, back up the control file to trace.
 
SQL> alter database backup controlfile to trace;

Because your data files have already been renamed to use the new database name, all you have to do is focus on the renaming the database in the script and running the rebuild control file script. This process reduces the stress of rebuilding the control file and is an easy way to rename your data files, even if you have no idea where they reside.

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; 

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;

- 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.

- How to start up context server
1. ctxctl [this starts up the process to start context server]
2. at prompt type:
- start 2 ddl [or substitute another number for the number of servers you want to start]
- to check if the processes started, just type 'status'

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

Connect as SYSDBA
For most scripts it should be sufficient to replace:
 CONNECT INTERNAL
  with
 CONNECT / AS SYSDBA

but it would be sensible to review the alternatives before making such a change. Additionally , scripts which call 'svrmgrl' should be ammended thus:

    Old form of command                     New form of command
    ~~~~~~~~~~~~~~~~~~~            ~~~~~~~~~~~~~~~~~~~
     svrmgrl                                           sqlplus /nolog
     svrmgrl internal                               sqlplus "/ as sysdba"
                                                           (Notice the quotation marks)

To connect from SQL*Plus:
sqlplus "system@solaris as sysdba"

Administrative Users
There are two main administrative privileges: SYSOPER and SYSDBA
SYSOPER privilege allows operations such as:
    Instance startup, mount & database open ;
    Instance shutdown, dismount & database close ;
    Alter database BACKUP, ARCHIVE LOG, and RECOVER.
This privilege allows the user to perform basic operational tasks without the ability to look at user data.

SYSDBA privilege includes all SYSOPER privileges plus full system privileges (with the ADMIN option), plus 'CREATE DATABASE' etc..
This is effectively the same set of privileges available when previously connected INTERNAL.

Password Authentication
Unless a connection to the instance is considered 'secure' then you  must use a password to connect with SYSDBA or SYSOPER privilege. Users can be added to a special 'password' file using either the  'ORAPWD' utility, or 'GRANT SYSDBA to USER' command. Such a user can  then connect to the instance for administrative purposes using the syntax:
    CONNECT username/password AS SYSDBA
  or
    CONNECT username/password AS SYSOPER

  Users with SYSDBA or SYSOPER privilege can be seen in the view V$PWFILE_USERS.

Operating System Authentication
If the connection to the instance is local or 'secure' then it is possible to use the operating system to determine if a user is allowed SYSDBA or SYSOPER access. In this case no password is required. The syntax to connect using operating system authentication is:
 CONNECT / AS SYSDBA
  or
 CONNECT / AS SYSOPER
 

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

Update DB Version (example from 8.1.6 to 8.1.7)
To update the database run as SYS or INTERNAL the script $ORACLE_HOME/rdbms/admin/u*.sql. Check to use the apropiate number. Here are the steps:

1. Shutdown the database
2. Copy the initSID.ora from 8.1.6 to the 8.1.7 dbs directory.
3. startup restrict with new software(8.1.7)
4. Run as internal or SYS the file u0801060.sql (in this case) under $ORACLE_HOME/rdbms/admin/u*.sql
5. Shutdown and startup with 8.1.7
6. Modify /var/opt/oracle/oratab and your own scripts

Archiving ON
Enabling Automatic Archiving before Instance Startup
Shutdown the instance and change the folowing parameters:
Example:
Log_archive_start    = True
Log_archive_format = "LOG_%t%s.ARC"
log_archive_dest_1 = "location=/u01/oradata/TICPBT09/ARCH MANDATORY"
log_archive_dest_state_1 = enable
log_archive_dest_2 = "service=TICPBP02 OPTIONAL reopen=60"
log_archive_dest_state_2 = enable
log_archive_min_succeed_dest = 1
 

• Startup the instance and type:

alter database archivelog;
• Verify your database is now in archivelog mode.

SVRMGRL> archive log list;
• Archive all your redo logs at this point.

SVRMGRL> archive log all;

Enabling Automatic Archiving After Instance Startup
To enable automatic archiving of filled online redo log groups without shutting down the current instance, use the SQL command ALTER SYSTEM with the ARCHIVE LOG START parameter;
The following statement enables archiving:
    SVRMGRL> ALTER SYSTEM ARCHIVE LOG START;
Anyway, remeber to modify the init.ora parameters.
 
 

Archiving OFF
Disabling Automatic Archiving at Instance Startup (best method)
Shutdown the instance and change the "LOG_ARCHIVE_START" parameter
    Log_archive_start    = False
Then:
startup mount
alter database noarchivelog;
alter database open;

Disabling Automatic Archiving After Instance Startup
To disable the automatic archiving of filled online redo log groups without shutting down the current instance, use the SQL command ALTER SYSTEM with the ARCHIVE LOG STOP parameter.
The following statement stops archiving:

  SVRMGRL> ALTER SYSTEM ARCHIVE LOG STOP;

If ARCH is archiving a redo log group when you attempt to disable automatic archiving, ARCH finishes archiving the current group, but does not begin archiving the next filled online redo log group.
The instance does not have to be shut down to disable automatic archiving. However, if an instance is shut down and restarted after automatic archiving is disabled, the instance is reinitialized using the settings
of the parameter file ("LOG_ARCHIVE_START"), which may or may not enable automatic archiving.

NOTE: If you choose to disable automatic archiving and have not disabled archiving altogether, you are responsible to archive
           all filled redo log groups or else database operation is  temporarily suspended (you will experience a database hang)
           until the necessary archiving is performed.
 

Performing Manual Archiving
If a database is operating in ARCHIVELOG mode, inactive groups of filled online redo log files must be archived. You can manually archive groups of the online redo log whether or not automatic archiving is enabled. If automatic archiving is not enabled, you must manually archive groups of filled online redo log files in a timely fashion. If all online redo log groups are filled but not archived, LGWR cannot reuse any inactive groups of online redo log members to continue writing redo log entries. Therefore, database operation is temporarily suspended until the necessary archiving is performed. You can exercise this scenario by executing
alter system switch logfile;
command when automatic archival is disabled.
Attempting to repeat that command with a last redo log group will show hang, and it won’t be completed with ‘statement processed’ message until archiving is done.
If automatic archiving is enabled, but you want to rearchive an inactive group of filled online redo log members to another location, you can use manual archiving. (However, the instance can decide to reuse the redo log group before you have finished manually archiving, thereby overwriting the files. If this
happens, Oracle will display an error message in the ALERT file.)
To manually archive a filled online redo log group, you must be connected with administrator privileges.
Manually archive inactive groups of filled online redo log members using the SQL command:
 SVRMGRL> ALTER SYSTEM ARCHIVE LOG ALL;.

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;

Example output:    SIZE_MB
                            -------
                            1.024

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
 

Add/Remove Services to Oracle Names Server
Launch NAMESCTL and enter the following to add a Service name:

To ADD:
 REGISTER <alias> -t DATABASE -d (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=1521))(CONNECT_DATA=(SID=<sidname>)))

  Where -t is the type of object, in this example it's DATABASE and
  where -d is the connect string information.

To REMOVE
  UNREGISTER <alias> -t DATABASE -d (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SID=<sidname>)))
 
 

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;

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;  )

Rebuild? There is an alternative
From 8i onwards, there is another option 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)

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:
column sid new_value osid noprint;
select upper(substr(global_name,1,(instr(global_name,'.')-1))) sid
from global_name;
set sqlprompt '&osid SQL> '

-- spool v.sql
-- set heading off
--select 'set sqlprompt '||value||'>'||user
-- select 'set sqlprompt '|| user ||'@'|| value || '>'
-- from sys.v_$parameter,dual where name='db_name';
-- spool off
-- set heading on
-- start v.sql

-- set termout off
-- col x new_value y
-- select rtrim(instance,chr(0)) x from v$thread;
-- select rtrim(name,chr(0)) x from v$database;
-- set sqlprompt '&y SQL> '
-- set termout on
 

Set TIMING On
The following SQL*Plus command will display the execution duration of all following SQL statements. This is especially helpful when performance tuning.

SQL> SET TIMING ON

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

wrap iname=script.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

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=(......., ........)
 

Delete Duplicate Records
rem Column_name should be the column name(s) that represent the columns in your constraint
rem (see table dba_cons_columns)
rem Note: If your constraint consists of more than one column,you will also need to modify the first SELECT statement
rem -- adding the columns to the WHERE clause

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.);

Consider an example: 
Table: EMP
EMPNO NUMBER
ENAME VARCHAR2(20)
JOB VARCHAR2(20)

DELETE FROM EMP E
   WHERE E.ROWID > ANY (SELECT ROWID 
                                                     FROM EMP M
                                                     WHERE M.EMPNO = E.EMPNO
                                                          AND M.ENAME = E.ENAME
                                                          AND M.JOB = E.JOB )

 

Unlocking Users
alter user &username account unlock;

How to wake up SMON to clean up temp object segments
select ts#, name from sys.ts$;
To find the Tablespace number in question.

Then use it in the following command:
alter session set events 'immediate trace name drop_segments level ts#+1';

So for tablespace# 6, you would use:
alter session set events 'immediate trace name drop_segments level 7';

Select Randomly from Table
In Oracle 8i, there is a new feature that 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.
 

Writing to the alert log
The following PL/SQL package can be used to add your own custom entries to the alert log.
    dbms_system.ksdwrt(2,'A line of text from Diego');
Will write the text to the alert log.
Use 1 instead of 2 to write to the trace file
Use 3 to write to both.
 

Install or Application Setup Tips

- SGA Size
 You can calculate the approximate size of the SGA as shown below:
   Total SGA size = ((shared_pool_size + db_block_buffers) * db_block_size + log_buffer)/.9
   For Example:
      SHARED_POOL_SIZE = 200 MB
      DB_BLOCK_BUFFERS = 200000 # 1600 MB
      DB_BLOCK_SIZE = 8192 #8K
      LOG_BUFFER = 65536 # 64K

   Total SGA size becomes about 1800 MB for this instance.

   *NOTE:  If you have other instances running on the same server,
           you need to calculate SGA size requirements for the other
           instances also.

- 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

- 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;
 

- Web Listener
        Starting
         1. owsctl start wrb
         2. owsctl start admin

        Stopping
         1. owsctl stop admin
         2. owsctl stop wrb

- recontrol commands to use when recreating a db from backup and changing the database name
         Following is an example of how to modify the recontrol script:
         The key is to use the resetlogs command, otherwise it will expect the original database name to be used when it reads it from the .dbf files

         STARTUP NOMOUNT
         CREATE CONTROLFILE
         SET DATABASE "FRESHUPG"
            MAXLOGFILES 16
            MAXLOGMEMBERS 2
            MAXDATAFILES 30
            MAXINSTANCES 1
            MAXLOGHISTORY 100
         LOGFILE
            GROUP 1 '/u04/oracle/dbf/FRESHUPG/log/log1.dbf' SIZE 1M,
            GROUP 2 '/u04/oracle/dbf/FRESHUPG/log/log2.dbf' SIZE 1M,
            GROUP 3 '/u04/oracle/dbf/FRESHUPG/log/log3.dbf' SIZE 1M resetlogs
         DATAFILE
            '/u04/oracle/dbf/FRESHUPG/system.dbf',
            '/u04/oracle/dbf/FRESHUPG/ROLLBACK.dbf',
            '/u04/oracle/dbf/FRESHUPG/aolm.dbf',
            '/u04/oracle/dbf/FRESHUPG/aoli.dbf',
            '/u04/oracle/dbf/FRESHUPG/modm.dbf',
            '/u04/oracle/dbf/FRESHUPG/modi.dbf',
            '/u04/oracle/dbf/FRESHUPG/temp.dbf';
         RECOVER DATABASE
         ALTER DATABASE OPEN RESETLOGS;

- Net Listener
        Starting
            lsnrctl start [Listener name from listener.ora]
        Stopping
            lsnrctl stop [Listener name from listener.ora]
         Stopping it will not affect other users that have already connected, because their connection has been established.

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

ENV Variables
- Type 'env' to see all of the enviroment variables
  You can also grep for certain variables, such as
         env | grep OA

Appending current PATH variable
         PATH=$PATH:[additional directory]; export PATH
 

UNIX Tips

- 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

- 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)

- 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]

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

- 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.

- 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

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

- 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 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#

- 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/

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

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

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)

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

To remove all files from directory /home/dpafumi that have ctime greater than 5 days, enter
find /home/dpafumi -type f -ctime +5 -exec rm {} \;

To test this, first use something like the above command to print out what files have a ctime greater than 5 days with
find /home/dpafumi -type f -ctime +5 -print

Both commands will go down recursively through subdirectories of /home/dpafumi.

To only go search /home/dpafumi, you have to use the GNU version of find
/usr/local/bin/find /home/dpafumi -type f -ctime +5 -maxdepth 1 -print

or

gnufind instead of find (depends on the system)