Tag Archives: display

Oracle

Oracle Universal Installer fails with error DISPLAY Variable:

Leave a comment

Oracle Universal Installer on UNIX and Linux normally uses a Java GUI (Graphical User Interface) to display installation options to the operator. I say normally, due to the option of a silent installation which does not require any type of GUI to install, however does require the configuration of certain files, before installation occurs. In this procedure we will cover the error message you will find if the DISPLAY variable is not properly setup and how to resolve.

1. Move to the location of you Oracle binary installer files in this case we have stored our Oracle binaries under /opt/oracle/software/linux/10.2.0.1/database

cd /opt/oracle/software/hp/10.2.0.1/database

2. List the files located under this directory.

myhpux:> ls
doc response stage
install runInstaller welcome.html
myhpux:>

3. Execute the runInstaller shell script to begin your installation.

myhpux:> ./runInstaller -ignoresysprereqs
Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be B.11.23. Actual B.11.31
Failed < <<< >>> Ignoring required pre-requisite failures. Continuing…

Preparing to launch Oracle Universal Installer from
/tmp/OraInstall2010-05-02_08-41-03AM. Please wait …
DISPLAY not set. Please set the DISPLAY and try again.
Depending on the Unix Shell, you can use one of the following commands as
examples to set the DISPLAY environment variable:
– For csh: % setenv DISPLAY 192.168.1.128:0.0
– For sh, ksh and bash: $ DISPLAY=192.168.1.128:0.0; export DISPLAY
Use the following command to see what shell is being used:
echo $SHELL
Use the following command to view the current DISPLAY environment variable
setting:
echo $DISPLAY
– Make sure that client users are authorized to connect to the X Server.
To enable client users to access the X Server, open an xterm, dtterm or xconsole
as the user that started the session and type the following command:
% xhost +
To test that the DISPLAY environment variable is set correctly, run a X11 based
program that comes with the native operating system such as ‘xclock’:
%
If you are not able to run xclock successfully, please refer to your PC-X Server
or OS vendor for further assistance.
Typical path for xclock: /usr/bin/X11/xclock

4. NOTE: the error message received references the lack of a defined DISPLAY variable, the OUI (Oracle Universal Installer) does not know where to send output to and thus fails.

5. To resolve this issue simple set your DISPLAY variable to the local host or client machine you are currently using. If using a remote machine you will need a tool to except the output from your server: VNC, Hummingbird, KEA!, etc will all work. NOTE: VNC is free for client use. Example: If my client machines IP is 192.168.0.110

myhpux:> export DISPLAY=192.168.0.110:0.0

6. Now re-execute the runInstaller variable again and you will be presented with the OUI GUI.

myhpux:> ./runInstaller -ignoresysprereqs
Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be B.11.23. Actual B.11.31
Failed < <<< >>> Ignoring required pre-requisite failures. Continuing…

Preparing to launch Oracle Universal Installer from
/tmp/OraInstall2010-05-02_08-54-42AM. Please wait …myhpux:> Oracle Universal
Installer, Version 10.2.0.1.0 Production
Copyright (C) 1999, 2005, Oracle. All rights reserved.

This completes correcting failure of OUI (Oracle Universal Installer) due to DISPLAY error.

Larry J Catt OCP 9i. 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, script, sqlplus, unix

Oracle – SQL*PLUS does not show the SQL being executed from a script file.

Leave a comment

Oracle SQL*PLUS utility provides several options to customize the display of information from your database session. In this article we will review how to display the DML and DDL statements which are stored in an OS layer file upon execution. By default SQL*PLUS will not display the DML or DDL statement from a script file.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called test.sql with the following SQL.

select * from v$instance;

3. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Apr 23 22:00:09 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. Execute the SQL file test.sql with the SQL*PLUS command: @./test.sql

SQL> @./test.sql

INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 orcl
mylinux
10.2.0.4.0 23-APR-10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL>

NOTE: The SQL contained within the file test.sql is not displayed, thus the end user executing such a file may not be aware of the output they are looking at.

5. To remedy this situation we will have to setup our SQL*PLUS environment to display the commands within our file. To do this we will have to turn two options on: ECHO and FEEDBACK.

SQL> set echo on
SQL> set feedback on
SQL>

6. Now re-execute the file test.sql with the SQL*PLUS command: @./test.sql.

SQL> @./test.sql
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 orcl
mylinux
10.2.0.4.0 23-APR-10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

1 row selected.

SQL>
SQL>

As show above, SQL*PLUS is now displaying the SQL contained within our file, before the output. This will allow the end user to know exactly how the output of our script file was produced, thus turning our script into more usefully information.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, script, SQL, sqlplus

Oracle – Viewing errors generated when writing a PL/SQL program unit.

Leave a comment

When you create a PL/SQL block of code within Oracle in the form of a procedure, function, or package and a compilation error occurs; Oracle will not immediately give you the error details. This article details the procedure to view errors generated by a PL/SQL block within SQL*PLUS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called create_file.sql with the following SQL.

create or replace procedure test_file
is

v_record varchar2(50) := ‘Testing file creation’;
v_file varchar2(30) := ‘test_file.txt’;
v_dir varchar2(512) := ‘/home/lcatt’;
v_write utl_file.file_type;
begin

v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);

utl_file.put_line(v_write, v_record);

utl_file.fclose(v_write);

end test_file;
/

3. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Apr 22 19:00:09 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. Set FEEDBACK and ECHO to on and execute the file create_file.sql.

SQL> set echo on
SQL> set feedback on
SQL> @./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /

Warning: Procedure created with compilation errors.

SQL>

5. The procedure was created with compilation errors, however it does not show you the errors. To see the errors generated in your procedure creation statement, use the SQL*PLUS command: show errors

SQL> show errors
Errors for PROCEDURE TEST_FILE:

LINE/COL ERROR
——– —————————————————————–
10/1 PL/SQL: Statement ignored
10/1 PLS-00201: identifier ‘V_WRITE’ must be declared
12/1 PL/SQL: Statement ignored
12/19 PLS-00201: identifier ‘V_WRITE’ must be declared
14/1 PL/SQL: Statement ignored
14/17 PLS-00201: identifier ‘V_WRITE’ must be declared
SQL>

6. Now you can see that the procedure creation statement is missing the definition for the variable V_WRITE. To correct this error replace the contents of your create_file.sql file with the following text, where the variable v_write is defined as utl_file.file_type.

create or replace procedure test_file
is

v_record varchar2(50) := ‘Testing file creation’;
v_file varchar2(30) := ‘test_file.txt’;
v_dir varchar2(512) := ‘/home/lcatt’;
v_write utl_file.file_type;
begin

v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);

utl_file.put_line(v_write, v_record);

utl_file.fclose(v_write);

end test_file;
/

7. Re-enter SQL*PLUS, set ECHO and FEEDBACK to on, and execute the file create_file.sql.

Mylinux:/home/lcatt:>sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Apr 22 19:43:17 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set feedback on
SQL> set echo on
SQL>@./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7 v_write utl_file.file_type;
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /

Procedure created.

SQL>

The procedure now compiles without error, through the use of the SQL*PLUS command ‘show errors’ which allowed us to see the detailed error message provided by the Oracle RDBMS.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i

Changing the Date format of an Oracle database:

Leave a comment

The date format of the oracle database normally defaults to DD-MON-YYYY in American Language standards. However, there are times when you want to see the hours, minutes and even seconds in which a transaction occurred. There are to ways of performing this task in the Oracle database: convert the date field to a char with the to_char function, specifying the desired format or changing the default NLS_DATE_FORMAT of the system or session. In this procedure we will demonstrate changing the NLS_DATE_FORMAT of the session.

1. Logon to you oracle database as an administrator.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Apr 14 10:18:48 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Perform a select on the V$INSTANCE view for startup_time to see the current default date format of your NLS_DATE_FORMAT parameter.

SQL> select startup_time from v$instance;

STARTUP_T
———
14-APR-10

SQL>

3. NOTE: The current format only shows the day-month-year of the instance startup.

4. Perform a select on the view NLS_SESSION_PARAMETERS and you can see that the format of NLS_DATE_FORMAT is DD-MON-RR.

SQL> select * from nls_session_parameters;

PARAMETER VALUE
—————————— —————————————-
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 rows selected.

SQL>

5. Perform an alter session statement to change the default format to include hours, minutes, and seconds.

alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

6. Again perform a select on the view NLS_SESSION_PARAMETERS.

SQL> select * from nls_session_parameters;

PARAMETER VALUE
—————————— —————————————-
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-YYYY HH24:MI:SS
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 rows selected.

SQL>

7. Now select the startup_time from the view V$INSTANCE and you will see the hours, minutes, and seconds of the instance startup. All date variables will be displayed in this format for the rest of the current session.

SQL> select startup_time from v$instance;

STARTUP_TIME
——————–
14-APR-2010 07:27:18

SQL>

NOTE: Changing this parameter at the system level will change the display of dates for all sessions in the database.

This completes changing the default date format in an Oracle database.

Larry J Catt, OCP 9i. 10g
oracle@allcompute.com
www.allcompute.com

11g, audit, Oracle

Viewing all auditing occurring within an Oracle RDBMS:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which providing the administrator the ability to find suspicious activity. This article covers the display of all auditing which is currently defined in an Oracle RDBMS. This procedure will work regardless of OS type.

NOTE: The initialization parameter AUDIT_TRAIL controls auditing at the entire database level and can be set to three definitions: 1. DB – audit trail in the database; 2. OS – audit trail on the OS; and 3. none – no auditing. In this procedure AUDIT_TRAIL must be set to DB or OS and the procedure does not shutdown auditing at the database level.

1. Auditing definitions can be seen in three views: DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS

2. Logon to your Oracle database server as the Oracle software owner.

3. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Dec 17 07:11:59 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. Execute the following PL/SQL block to display all auditing which is currently defined in your ORACLE RDBMS.

————————————————————–
Beginning of PL/SQL Block
———————————————————————–

set serveroutput on

declare

v_ct number;

begin

–this block removes all auditing from an oracle RDBMS system.

for v_stmt in(select ‘audit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

for v_stmt in(select ‘audit ‘ ||audit_option||’ by ‘ ||user_name as stmt from
sys.dba_stmt_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

for v_stmt in(select ‘audit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

end;
/
———————————————————————–
end of PL/SQL Block
———————————————————————–

———————————————————————–
Beginning of PL/SQL Block output
———————————————————————–

SQL> set serveroutput on
SQL>
SQL> declare
2
3 v_ct number;
4
5 begin
6
7 –this block removes all auditing from an oracle RDBMS system.
8
9 for v_stmt in(select ‘audit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
10 loop
11 dbms_output.put_line(v_stmt.stmt);
12 end loop;
13
14 for v_stmt in(select ‘audit ‘ ||audit_option||’ by ‘ ||user_name as stmt
from sys.dba_stmt_audit_opts)
15 loop
16 dbms_output.put_line(v_stmt.stmt);
17 end loop;
18
19 for v_stmt in(select ‘audit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)
20 loop
21 dbms_output.put_line(v_stmt.stmt);
22 end loop;
23
24 end;
25 /
audit all by LJCATT
audit all by LJCATT
audit CREATE SESSION by LJCATT
audit CREATE TABLE by LJCATT
audit all on LJCATT.TEST

PL/SQL procedure successfully completed.

SQL>
———————————————————————–
Ending of PL/SQL Block output
———————————————————————–

This completes viewing of all auditing occurring within an Oracle RDBMS.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle 10g, oracle 9i, SQL, sqlplus

Display the time at your SQL*PLUS Prompt

Leave a comment

Oracle SQL*PLUS provides the ability to change the default prompt in your session. This allows you to easily determine information about your current session without having to query the database. In this article we will review displaying the current system time at your SQL*PLUS prompt.

1. Connect to your database through SQL*PLUS.

SQL>connect ljcatt/xxxx@mydb
Connected.
SQL>

2. Execute the following SQL*PLUS command: set time on.

SQL>set time on
19:53:56 SQL>
19:53:58 SQL>

3. The time will now be displayed in your SQL*PLUS prompt.
4. To turn off the time execute the following SQL*PLUS command: set time off

19:53:58 SQL>set time off
SQL>
SQL>

5. This completes displaying the time at your Oracle SQL*PLUS prompt.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com