How to use the glogin.sql file to set global sqlplus attributes

The file $ORACLE_HOME/sqlplus/admin/glogin.sql can be used to define global attributes for users of sqlplus on that particular host.

For example:

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER SQL>"
SET LINESIZE 200
SET PAGSEIZE 200

On Production servers, you may consider adding a colored prompt, for example red:

SET SQLPROMPT "'^[[41m'_USER'@'_CONNECT_IDENTIFIER'^[[0m' SQL> "

How to create the libobk.a link in $ORACLE_HOME/lib

su - oracle
cd $ORACLE_HOME/lib
ln -s /usr/lib/libobk64.a libobk.a

How to confirm that the latest PSU has been applied to your ORACLE_HOME

Use opatch lsinventory to extract the information. In my case:

hostname:PRODDB01>opatch lsinventory | egrep -i 'PSU|DATABASE PATCH SET UPDATE'

Patch description:  "Database Patch Set Update : 11.2.0.4.2 (18031668)" 
Sub-patch  17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
Patch description:  "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Sub-patch  16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch  14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch  14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"

What is the difference between the views nls_database_parameters and nls_instance_parameters?

The NLS_DATABASE_PARAMETERS view will display what the NLS settings were when the database was created. These are fixed at the database level and cannot be changed.

The NLS_INSTANCE_PARAMETERS view reflects parameters set for your instance in the init.ora file or the server parameter file (spfile).


Source: James Koopman in Databasejournal.com

This matches my settings for a random database:

SQL> show parameter nls_
 
NAME                                  TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 nls_comp                             string      BINARY
 nls_language                         string      AMERICAN
 nls_length_semantics                 string      BYTE
 nls_nchar_conv_excp                  string      FALSE
 nls_territory                        string      AMERICA
 
SQL> SELECT * FROM NLS_INSTANCE_PARAMETERS WHERE value IS NOT NULL;
 
PARAMETER                       VALUE
 ------------------------------ ----------------------------------------
 NLS_LANGUAGE                   AMERICAN
 NLS_TERRITORY                  AMERICA
 NLS_COMP                       BINARY
 NLS_LENGTH_SEMANTICS           BYTE
 NLS_NCHAR_CONV_EXCP            FALSE
 
5 rows selected.

What are literals?

The terms "literal" and "constant" are synonymous and refer to a fixed data value.

There are:

•Text Literals
Examples: 'JACK', 'BLUE ISLAND', '101'.

•Numeric Literals
Examples: 5001, +256

•Datetime Literals
Example: DATE '1998-12-25'.

Note that the ANSI (American National Standards Institute) date literal contains no time portion.

•Interval Literals
Examples:
INTERVAL '123-2' YEAR(3) TO MONTH
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)


Source: Oracle Documentation

How to instruct OUI to ignore prerequisites

./runinstaller -ignoreSysPrereqs

Using the procedure SEARCHFILES in DBMS_BACKUP_RESTORE

I found the following great article about how to list files in directory from within sqlplus here, written by David Marco. As pointed out by several oracle bloggers, there is very little documentation around for this package.

It just so happened that a user was in need of such functionality, and his code could be used straight-off.

However, my customer had already ammended a very useful functionality, by adding support for sending the directory name in as a parameter.

The code as it looks now:

CREATE OR REPLACE FUNCTION LIST_FILES( p_directory IN VARCHAR2, p_file_pattern IN VARCHAR2 default null)
RETURN file_array pipelined AS

l_path VARCHAR2(1024);
l_dummy VARCHAR2(1024);

BEGIN

   SELECT directory_path
   INTO l_path
   FROM all_directories
   WHERE directory_name = p_directory;

   sys.DBMS_BACKUP_RESTORE.SEARCHFILES(l_path, l_dummy);

   FOR file_list IN (SELECT FNAME_KRBMSFT AS file_name
                     FROM X$KRBMSFT
                     WHERE FNAME_KRBMSFT LIKE '%'|| NVL(p_file_pattern, FNAME_KRBMSFT)||'%' ) LOOP
      PIPE ROW(file_list.file_name);
   END LOOP;

END;
/

The call will thus be:

SELECT * FROM TABLE(LIST_FILES('MY_DIR','%*.dmp%'));