Sean's Oracle Share

November 12, 2010

Utility: Pivot with column names

Filed under: Uncategorized — Sean Molloy @ 11:26 pm
Tags:

Today, I was querying v$instance and got the following output:

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 oel1
oel1
11.2.0.2.0        12-NOV-10 OPEN         NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO
1 row selected.

It seemed to me that it would be nice to have a pivoted output with column names.
This is the result for v$instance:

@pivot v$instance
. INSTANCE_NUMBER: 1
.   INSTANCE_NAME: oel1
.       HOST_NAME: oel1
.         VERSION: 11.2.0.2.0
.    STARTUP_TIME: 12-NOV-10
.          STATUS: OPEN
.        PARALLEL: NO
.         THREAD#: 1
.        ARCHIVER: STARTED
. LOG_SWITCH_WAIT:
.          LOGINS: ALLOWED
.SHUTDOWN_PENDING: NO
. DATABASE_STATUS: ACTIVE
.   INSTANCE_ROLE: PRIMARY_INSTANCE
.    ACTIVE_STATE: NORMAL
.         BLOCKED: NO

This is the result for emp:

@pivot emp
.   EMPNO: 7369, 7499, 7521, 7566, 7654, 7698, 7782, 7788, 7839, 7844, 7876, 7900, 7902, 7934
.   ENAME: SMITH, ALLEN, WARD, JONES, MARTIN, BLAKE, CLARK, SCOTT, KING, TURNER, ADAMS, JAMES, FORD, MILLER
.     JOB: CLERK, SALESMAN, SALESMAN, MANAGER, SALESMAN, MANAGER, MANAGER, ANALYST, PRESIDENT, SALESMAN, CLERK, CLERK, ANALYST, CLERK
.     MGR: 7902, 7698, 7698, 7839, 7698, 7839, 7839, 7566,  , 7698, 7788, 7698, 7566, 7782
.HIREDATE: 17-DEC-80, 20-FEB-81, 22-FEB-81, 02-APR-81, 28-SEP-81, 01-MAY-81, 09-JUN-81, 09-DEC-82, 17-NOV-81, 08-SEP-81, 12-JAN-83, 03-DEC-81, 03-DEC-81, 23-JAN-82
.     SAL: 800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300
.    COMM:  , 300, 500,  , 1400,  ,  ,  ,  , 0,  ,  ,  ,
.  DEPTNO: 20, 30, 30, 20, 30, 30, 10, 20, 10, 30, 20, 30, 20, 10

This is accomplished by two sql files and a c:\tmp directory for temporary files.
pivot.sql:

set feedback off
set heading off
set verify off
set timing off
set termout off
set trimspool on

define session=Bad
column sess new_value session noprint
select 'c:\tmp\pivot_'||trim(userenv('SESSIONID'))||'.sql' sess from dual
/

define max_col=Bad
column max_c new_value max_col noprint
select max(length( column_name )) max_c from  all_tab_columns atc
 where atc.table_name like replace( upper('&1'), 'V$', 'V_$')
/

column column_id_noprint noprint
SPOOL &session
select column_id column_id_noprint, '@pivotone '||column_name||' &1 &max_col' cmd
  from all_tab_columns atc
 where atc.table_name like replace( upper('&1'), 'V$', 'V_$')
   and decode(owner,'SYS',user,owner) = user
order by 1
/
SPOOL OFF
set trimspool off
set termout on
set heading on
set feedback off

@&session

host rm &session

set feedback on
set heading on
set pagesiz 50000

Above is the main file.
Note especially the following:
Line 23: To query column names for V$ views, I need to query V_$ (V$ are synonyms, actual views begin with v_$).
Line 24: Also, I have tables like “emp” in more than on schema. “and decode(owner,’SYS’,user,owner) = user” will ensure that the table pivoted is in my current schema or belongs to SYS.

pivotone.sql:

set heading off
set verify off
set termout off
define this_line=Bad
column this_l new_value this_line noprint
select '.'||lpad('&1',&3)||': '||listagg(nvl(to_char(&1),' '),', ')
         within group (order by rownum) this_l
  from &2
/
set termout on
begin
  dbms_output.put_line( substr('&this_line',1) );
end;
/
set verify on
set heading on

Initially I was just using the select to screen. This was leaving a blank line between each output line. So I added a lot of code here to use dbms_output.put_line.

Line 6: I wanted to leftpad the column names so that the output would be easier to read. Unfortunately dbms_output.put_line seems to trim leading spaces. So the ‘.’|| preserves the indenting. A tab character also works but would indent several more character spaces

Also on Line 6 note the nvl(to_char(&1),’ ‘). This was required so that nulls would show up in the list of values. Without the nvl, listagg would only list the non-null values. The Emp table, comm field is a good illustration of this.

I hope this code benefits someone. Also I am eager to hear on how it can be further improved

March 16, 2010

conditional login.sql in the SQLPATH

Filed under: sqlplus — Sean Molloy @ 3:57 am

 

Further to my post on Self-inflicted unable to startup or shutdown database from OEM, I wondered if there might be a way to make the login.sql conditional. 

Background: 

The windows registry HKey_Local_Machine\ORACLE\Key_OraDb11g_home1 key has a Value field named SQLPATH 

When you run a file from insied sqlplus using @ or “start”, sqlplus will search any folders listed in sqlpath for the file. 

Also on starting sqlplus, it will check folders listed in sqlplus for any file named “login.sql”. If it finds a file, then this gets run before a prompt is presented. Also if any “connect /” is done in sqlplus, then a login.sql is again run. 

Problem: 

If my pc does a sqlplus/nolog then I do not want my login.sql to do anything (because there is no connection) 

Likewise once someone is connected, if they do a “connect sys/password as sysdba”, I do not want my login.sql to do anything (namely because this blocks OEM from starting/shutting down my database) 

Otherwise for any non-sys/non-system connect, I do want a login.sql to be run. 

Solution: 

In sqlplus there are some predefined variables. 

You can see these by typing “define”. The following is what you get after a sqlplus/nolog 

SQL> define
DEFINE _DATE        = "15-MAR-10" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "" (CHAR)
DEFINE _USER        = "" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000700" (CHAR)
DEFINE _EDITOR        = "Notepad" (CHAR) 

 

After connect as test, you get

SQL> define _user
DEFINE _USER           = "TEST" (CHAR)  

So I created the following files: 

“login.sql” with one line:

@start_&_user..sql

“start_.sql”  and “start_SYS.sql” and “start_SYSTEM.sql” are empty files 

start_TEST.sql with any number of lines to do something useful such as


@@init.sql

This works as planned for “sqlplus/nolog”, “connect sys…” and “connect test/…”
Of course, a big downside here is that for each username you need a file named “start_username.sql”

Danger: 

With any login.sql script you need to worry about someone adding lines to your start_SYS.sql such as

grant dba to bad_guy;

March 10, 2010

Installing OEL 5 on Win 7 Virtual PC

Filed under: OEL — Sean Molloy @ 4:12 am

 

I wanted to setup a virtualized linux environment on my 64 bit windows 7 laptop. (OEL 5 is Oracle Enterprise Linux 5).

I looked at a few alternatives:

  • Virtual PC on Win 7: only supports 32bit OSs and requires hardware virtualization, Linux not supported
  • Virtual PC 2007: Supports 32/64 bit OSs, does not require hardware vitualization, Linux supported
  • Vmware Server: Supports 32/64 bit, linux supported, most popular

I decided to try Virtual PC on Win 7, mostly because my laptop has the hardware virtualization. Also vmware website was not taking registrations last weekend. Also wanted to see if the lack of linux support would cause any issues.

Today, I have discovered Oracle VM VirtualBox. VirtualBox came over with the Sun acquisition and it seems this will be well supported in the future. See http://www.virtualization.info/2010/01/oracle-vm-30-and-virtualbox-roadmaps.html

If I was starting again I would try Oracle VM VirtualBox. But since I have used Virtual PC on win 7 and my experiences may be of benefit to others, I will describe them.

Does your laptop have Hardware Virtualization?

Here’s a link to the Intel utility to run to see if your CPU supports Hardware Virtualization:

http://www.intel.com/support/processors/tools/piu/

Here’s a link to Microsoft’s instructions to configuring the BIOS to enable Hardware Virtualization:

http://www.microsoft.com/windows/virtual-pc/support/configure-bios.aspx

Steps taken

1. Downloaded “XP mode” and “Virtual PC” from http://www.microsoft.com/windows/virtual-pc/download.aspx

2. Installed “XP mode”. Installed “Virtual PC”. It is not necessary to configure “XP mode”, before using “Virtual PC”

3. Downloaded OEL 5 update 4 dvd iso from http://edelivery.oracle.com/linux

4. Ran the “create virtual machine” wizard. Options 1024MB ram, use computer network selected, named virtual hard disk.

I left the Virtual hard disk with its Total Disk size set to 130GB and dynamic type. My budget is 20GB. OEL 5 does not seem to grab the full size, but supports the dynamic nature of the Virtual hard disk file.

5. Changed the vm settings to point the DVD drive to the OEL 5 dvd iso file.

6. Started the vm

7. Selected default Graphical mode. This resulted in:

8. The issue is that Virtual PC supports 16 bit color, but not 24 bit. The linux install uses 24 bit.

The solution I chose was to do the install in text mode. So after restarting the vm, I typed “linux text”

9 I skipped the media test and chose the following options:

language: english
Keyboard: US
initialize drive
Use GRUB Boot Loader
no boot options
no GRUB password
/dev/hda1 for boot loader
eth0 parameters
gateway 192.168.1.1
no primary, secondary DNS ip
hostname: oel5
timezone: Chicago (counld not find St. Paul or Minneapolis!!)
Root password:
Options: “Software Development” and Customize
Defaults + unselected Games, selected Legacy SW development, Server config tools, system tools, Windows File Server, unselected X Software Development, text-based internet, office/productivity, gnome software development.

10. The installer formatted the virtual disk and did the install in 25 minutes.

The install grew the Virtual Hard Disk to 5GB

11. I changed the vm setting for network to “Shared networking”

Post-install issues

After logging in as root and entering “startx” to see the gnome desktop, I had a few issues.

No Mouse:

    The mouse was not working. The solution was to  Edit /boot/grub/menu.lst and add “i8042.noloop” to the end of the “kernal” line. See http://arcanecode.com/2007/04/23/ubuntu-704-and-virtual-pc-2007-no-mouse-issue/#comment-8249

800×600 Screen Resolution:

  My desktop resolution was set to 800×600 and this was too small. The only available resolutions were 800×600 and 640×480. This was the most painful issue to resolve.

It involved adding some entries to /etc/X11/xorg.conf

I have highlighted the additions/changes (highlights visible at the website)


Section "Device"
 Identifier  "Videocard0"
 Driver      "vesa"
EndSection

Section "Monitor"
 Identifier "WXGA+ Inspiron"
 HorizSync 31.5-90.0
 VertRefresh 60.0-60.0
 Option "DPMS"
EndSection

Section "Screen"
 Identifier "Screen0"
 Device     "Videocard0"
 Monitor    "WXGA+ Inspiron"
 DefaultDepth     16
 SubSection "Display"
  Viewport   0 0
  Depth     16
  Modes "1280x1024" "1024x768" "800x600" "640x480"
 EndSubSection
 SubSection "Display"
  Viewport   0 0
  Depth     24
  Modes "1280x1024" "1024x768" "800x600" "640x480"
 EndSubSection
EndSection

These changes made it possible to achieve a 1280×1024 resolution

Lastly, I also needed to set a static ip address.

Future

  • I need to configure samba or ftp to allow the exchange of files between the vm and the win 7 host. Unfortunately I cannot copy/paste between the two. So I have had to use yahoo mail to exchange data
  • My main goal is to install ASM and 11g R2

March 3, 2010

Failing to install the Jan2010 CPU using OEM on Windows

Filed under: CPU,OEM,opatch — Sean Molloy @ 4:37 am

 

  Problem:
Attempting to install the Jan2010 using OEM but failing

  Environment:
Oracle 11g r1
Win 7 64 bit

  Investigation:

The log file is reporting:
———————————————————————
Running prerequisite checks…
Prerequisite check “CheckActiveFilesAndExecutables” failed.
The details are:
Following files are active :
C:\app\sean\product\11.1.0\db_1\bin\oraclient11.dll
C:\app\sean\product\11.1.0\db_1\bin\orageneric11.dll
C:\app\sean\product\11.1.0\db_1\bin\oracommon11.dll
C:\app\sean\product\11.1.0\db_1\bin\orapls11.dll
C:\app\sean\product\11.1.0\db_1\bin\orahasgen11.dll
C:\app\sean\product\11.1.0\db_1\bin\oraocr11.dll
C:\app\sean\product\11.1.0\db_1\bin\oran11.dll
C:\app\sean\product\11.1.0\db_1\bin\oranro11.dll
C:\app\sean\product\11.1.0\db_1\bin\orantcp11.dll
C:\app\sean\product\11.1.0\db_1\bin\oraxml11.dll
C:\app\sean\product\11.1.0\db_1\bin\oracore11.dll
C:\app\sean\product\11.1.0\db_1\bin\orannzsbb11.dll
C:\app\sean\product\11.1.0\db_1\bin\orasql11.dll
C:\app\sean\product\11.1.0\db_1\bin\nmo.exe
C:\app\sean\product\11.1.0\db_1\bin\oranls11.dll
C:\app\sean\product\11.1.0\db_1\bin\oraunls11.dll
ApplySession failed during prerequisite checks: Prerequisite check “CheckActiveFilesAndExecutables” failed.
System intact, OPatch will not attempt to restore the system

OPatch failed with error code = 74
—————————————————————————-
Note: 15 dlls and nmo.exe above.

I used Process Explorer to see which process was using the Dlls.  http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

A process named “wmiprvse.exe” had the dlls open.

From http://en.wikipedia.org/wiki/Windows_Management_Instrumentation :
  ”  The purpose of WMI is to define a non-proprietary set of environment-independent specifications which allow management information to be shared between management applications. “

From some scattered references on Metalink, it seems that Oracle does interact with WMI on windows.

For my second attempt, I could have turned off the “Windows Management Instrumentation” service. As I was testing on my own machine, I decided to kill the wmiprvse process which had the Dlls open.

The second attempt again using OEM failed as the nmo.exe process was locked and this again caused the “CheckActiveFilesAndExecutables” failure.

This time I tried an old win3.1 trick. I renamed nmo.exe to nmo.exe_dis. So nmo.exe could be replaced.

The third attempt using OEM failed. OEM did not get as far as checking for pre-requisites. It seems that OEM needed the nmo.exe executable file.
nmo.exe seems to be Oracle Network Manager Objects. This is part of the Oracle networking stack

I read a post on otn which said that Oracle had not guaranteed that OEM could install all patch sets. Some CPU (Critical Patch Update) would need to be installed via opatch. Unfortunately I did not save the url.

Attempt four used opatch. I shutdown the oracle related services. The attempt failed again for “CheckActiveFilesAndExecutables”. This time, onmly the dlls were listed (not nmo.exe).

So attempt five using opatch involved killing the wmiprvse.exe which was locking the DLLs and it did work. The CPU (Critical Patch Update) was installed.

The Readme for the Jan2010 CPU descibes installing using opatch. I did not see any reference to using OEM. I decided to try OEM just as an exercise. I am impressed with the way OEM shutdown the dtabase and services during my attempts.
    My Learnings:

 1. OEM cannot install all CPUs (Critical Patch Update). Sometimes opatch needs to be invoked at the command-line to get around nmo.exe being active.
 2. WMI impacts by loadings some of the oracle dlls. I have not seen any instructions about how to avoid this. Shutting down the “Windows Management Instrumentation” service for the duration of the install would probably work.

Note all of the above was tested on my test installation. If it was my employer’s database, the a lot more care would be applied.

February 27, 2010

February 16, 2010

Hello world!

Filed under: Uncategorized — Sean Molloy @ 12:42 am

My intention is to highlight oracle related content that I find interesting. Also as time allows, I intend to blog on Oracle topics sharing my discoveries. I have had great personal satisfaction in sharing technical insights in the past. I hope that I can continue this by blogging.

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.