Tutorials

-- This PL/SQL procedure will write records to an Excel File.
-- We will use UTL_FILE ( You need an init.ora parameter set for this to work correctly).
-- UTL_FILE allows us to write a file on the server.


create or replace
package spread_sheet as
--
type owaSylkArray is table of varchar2(2000);
--
procedure show(
sheet_file in utl_file.file_type,
sheet_query in varchar2,
sheet_parm_names in owaSylkArray default owaSylkArray(),
sheet_parm_values in owaSylkArray default owaSylkArray(),
sheet_sum_column in owaSylkArray default owaSylkArray(),
sheet_max_rows in number default 10000,
sheet_show_null_as in varchar2 default null,
sheet_show_grid in varchar2 default 'YES',
sheet_show_col_headers in varchar2 default 'YES',
sheet_font_name in varchar2 default 'Courier New',
sheet_widths in owaSylkArray default owaSylkArray(),
sheet_titles in owaSylkArray default owaSylkArray(),
sheet_strisheet_html in varchar2 default 'YES' );
--
procedure show(
sheet_filein utl_file.file_type,
sheet_cursor in integer,
sheet_sum_column in owaSylkArray default owaSylkArray(),
sheet_max_rows in number default 10000,
sheet_show_null_as in varchar2 default null,
sheet_show_grid in varchar2 default 'YES',
sheet_show_col_headers in varchar2 default 'YES',
sheet_font_name in varchar2 default 'Courier New',
sheet_widths in owaSylkArray default owaSylkArray(),
sheet_titles in owaSylkArray default owaSylkArray(),
sheet_strisheet_html in varchar2 default 'YES' );
--
end spread_sheet;
/
show error

create or replace
package body spread_sheet as
--
g_cvalue varchar2(32767);
g_desc_t dbms_sql.desc_tab;

type vc_arr is table of varchar2(2000) index by binary_integer;
g_lengths vc_arr;
g_sums vc_arr;
--
--

g_file utl_file.file_type;


procedure p( sheet_str in varchar2 )
is
begin
utl_file.put_line( g_file, sheet_str );
exception
when others then null;
end;

function build_cursor(
q in varchar2,
n in owaSylkArray,
v in owaSylkArray ) return integer is
c integer := dbms_sql.open_cursor;
i number := 1;
begin
dbms_sql.parse (c, q, dbms_sql.native);
loop
dbms_sql.bind_variable( c, n(i), v(i) );
i := i + 1;
end loop;
return c;
exception
when others then
return c;
end build_cursor;
--
--
function str_html ( line in varchar2 ) return varchar2 is
x varchar2(32767) := null;
in_html boolean := FALSE;
s varchar2(1);
begin
if line is null then
return line;
end if;
for i in 1 .. length( line ) loop
s := substr( line, i, 1 );
if in_html then
if s = '>' then
in_html := FALSE;
end if;
else
if s = '<' then
in_html := TRUE;
end if;
end if;
if not in_html and s != '>' then
x := x || s;
end if;
end loop;
return x;
end str_html;
--
function ite( b boolean,
t varchar2,
f varchar2 ) return varchar2 is
begin
if b then
return t;
else
return f;
end if;
end ite;
--
procedure print_comment( sheet_comment varchar2 ) is
begin
return;
p( ';' || chr(10) || '; ' || sheet_comment || chr(10) || ';' );
end print_comment;
--
procedure print_heading( font in varchar2,
grid in varchar2,
col_heading in varchar2,
titles in owaSylkArray )
is
l_title varchar2(2000);
begin
p( 'ID;ORACLE' );
print_comment( 'Fonts' );
p( 'P;F' || font || ';M200' );
p( 'P;F' || font || ';M200;SB' );
p( 'P;F' || font || ';M200;SUB' );
--
print_comment( 'Global Formatting' );
p( 'F;C1;FG0R;SM1' ||
ite( upper(grid)='YES', '', ';G' ) ||
ite( upper(col_heading)='YES', '', ';H' ) );
for i in 1 .. g_desc_t.count loop
p( 'F;C' || to_char(i+1) || ';FG0R;SM0' );
end loop;
--
print_comment( 'Title Row' );
p( 'F;R1;FG0C;SM2' );
for i in 1 .. g_desc_t.count loop
g_lengths(i) := g_desc_t(i).col_name_len;
g_sums(i) := 0;
begin
l_title := titles(i);
exception
when others then
l_title := g_desc_t(i).col_name;
end;
if i = 1 then
p( 'C;Y1;X2;K"' || l_title || '"' );
else
p( 'C;X' || to_char(i+1) || ';K"' || l_title || '"' );
end if;
end loop;
end print_heading;
--
function print_rows(
c in integer,
max_rows in number,
sum_columns in owaSylkArray,
show_null_as in varchar2,
strisheet_html in varchar2 ) return number is
row_cnt number := 0;
line varchar2(32767) := null;
n number;
begin
loop
exit when ( row_cnt >= max_rows or
dbms_sql.fetch_rows( c ) <= 0 );
row_cnt := row_cnt + 1;
print_comment( 'Row ' || row_cnt );
--
p( 'C;Y' || to_char(row_cnt+2) );

for i in 1 .. g_desc_t.count loop
dbms_sql.column_value( c, i, g_cvalue );
g_cvalue := translate( g_cvalue,
chr(10)||chr(9)||';', ' ' );
g_cvalue := ite( upper( strisheet_html ) = 'YES',
str_html( g_cvalue ),
g_cvalue );
g_lengths(i) := greatest( nvl(length(g_cvalue),
nvl(length(show_null_as),0)),
g_lengths(i) );
line := 'C;X' || to_char(i+1);
line := line || ';K';
begin
n := to_number( g_cvalue );
if upper( sum_columns(i)) = 'Y' then
g_sums(i) := g_sums(i) + nvl(n,0);
end if;
exception
when others then
n := null;
end;
line := line ||
ite( n is null,
ite( g_cvalue is null,
'"'||show_null_as||
'"', '"'||g_cvalue||'"' ),
n );
p( line );
end loop;
--
end loop;
return row_cnt;
end print_rows;
--
procedure print_sums(
sum_columns in owaSylkArray,
row_cnt in number ) is
begin
if sum_columns.count = 0 then
return;
end if;
--
print_comment( 'Totals Row' );
p( 'C;Y' || to_char(row_cnt + 4) );
p( 'C;X1;K"Totals:"' );
--
for i in 1 .. g_desc_t.count loop
begin
if upper(sum_columns(i)) = 'Y' then
p( 'C;X' || to_char(i+1) || ';ESUM(R3C:R' ||
to_char(row_cnt+2) || 'C)' );
end if;
exception
when others then
null;
end;
end loop;
end print_sums;
--
procedure print_widths( widths owaSylkArray ) is
begin
print_comment( 'Format Column Widths' );
p( 'F;W1 1 7' );
for i in 1 .. g_desc_t.count loop
begin
p( 'F;W' || to_char(i+1) || ' ' ||
to_char(i+1) || ' ' ||
to_char(to_number(widths(i))) );
exception
when others then
p( 'F;W' || to_char(i+1) || ' ' ||
to_char(i+1) || ' ' ||
greatest( g_lengths(i), length( g_sums(i) )));
end;
end loop;
p( 'E' );
end print_widths;
procedure show(
sheet_filein utl_file.file_type,
sheet_cursor in integer,
sheet_sum_column in owaSylkArray default owaSylkArray(),
sheet_max_rows in number default 10000,
sheet_show_null_as in varchar2 default null,
sheet_show_grid in varchar2 default 'YES',
sheet_show_col_headers in varchar2 default 'YES',
sheet_font_name in varchar2 default 'Courier New',
sheet_widths in owaSylkArray default owaSylkArray(),
sheet_titles in owaSylkArray default owaSylkArray(),
sheet_strisheet_html in varchar2 default 'YES' ) is
--
l_row_cnt number;
l_col_cnt number;
l_status number;
begin
g_file := sheet_file;
dbms_sql.describe_columns( sheet_cursor, l_col_cnt, g_desc_t );
--
for i in 1 .. g_desc_t.count loop
dbms_sql.define_column( sheet_cursor, i, g_cvalue, 32765);
end loop;
--
print_heading( sheet_font_name,
sheet_show_grid,
sheet_show_col_headers,
sheet_titles );
l_status := dbms_sql.execute( sheet_cursor );
l_row_cnt := print_rows(
sheet_cursor,
sheet_max_rows,
sheet_sum_column,
sheet_show_null_as,
sheet_strisheet_html );
print_sums( sheet_sum_column, l_row_cnt );
print_widths( sheet_widths );
end show;
--
procedure show(
sheet_filein utl_file.file_type,
sheet_query in varchar2,
sheet_parm_names in owaSylkArray default owaSylkArray(),
sheet_parm_values in owaSylkArray default owaSylkArray(),
sheet_sum_column in owaSylkArray default owaSylkArray(),
sheet_max_rows in number default 10000,
sheet_show_null_as in varchar2 default null,
sheet_show_grid in varchar2 default 'YES',
sheet_show_col_headers in varchar2 default 'YES',
sheet_font_name in varchar2 default 'Courier New',
sheet_widths in owaSylkArray default owaSylkArray(),
sheet_titles in owaSylkArray default owaSylkArray(),
sheet_strisheet_html in varchar2 default 'YES' ) is
begin
show( sheet_file => sheet_file,
sheet_cursor => build_cursor( sheet_query,
sheet_parm_names,
sheet_parm_values ),
sheet_sum_column => sheet_sum_column,
sheet_max_rows => sheet_max_rows,
sheet_show_null_as => sheet_show_null_as,
sheet_show_grid => sheet_show_grid,
sheet_show_col_headers => sheet_show_col_headers,
sheet_font_name => sheet_font_name,
sheet_widths => sheet_widths,
sheet_titles => sheet_titles,
sheet_strisheet_html => sheet_strisheet_html );
end show;
--
end spread_sheet;
/
show error
declare
l_cursor number := dbms_sql.open_cursor;
output utl_file.file_type;
begin
output := utl_file.fopen( 'c:\temp\', 'emp2.slk', 'w',32000 );

dbms_sql.parse( l_cursor,
'select empno id, ename employee,
sal Salary, comm commission ' ||
'from scott.emp ' ||
'where job = ''MANAGER'' ' ||
'and sal > 2000',
dbms_sql.native );

spread_sheet.show(
sheet_file => output ,
sheet_cursor => l_cursor,
sheet_sum_column =>
spread_sheet.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
sheet_show_grid => 'NO' );
dbms_sql.close_cursor( l_cursor );
utl_file.fclose( output );
end;

CREATE OR REPLACE PACKAGE ENCRY_PASS AS
FUNCTION encrypt( Str VARCHAR2 ) RETURN RAW;
FUNCTION decrypt( xCrypt VARCHAR2 ) RETURN VARCHAR2;
END ENCRY_PASS;
/

CREATE OR REPLACE PACKAGE BODY ENCRY_PASS AS
crypt_raw RAW(2000);
crypt_str VARCHAR(2000);

-- Encrypt the string --
FUNCTION encrypt( Str VARCHAR2 ) RETURN RAW AS

l INTEGER := LENGTH(str);
i INTEGER;
padblock RAW(2000);
Cle RAW(8) := UTL_RAW.CAST_TO_RAW('frankzap');

BEGIN
i := 8-MOD(l,8);
padblock := utl_raw.cast_to_raw(str||RPAD(CHR(i),i,CHR(i)));

dbms_obfuscation_toolkit.DESEncrypt(
input => padblock,
KEY => Cle,
encrypted_data => crypt_raw );
RETURN crypt_raw ;
END;

-- Decrypt the string --
FUNCTION decrypt( xCrypt VARCHAR2 ) RETURN VARCHAR2 AS
l NUMBER;
Cle RAW(8) := UTL_RAW.CAST_TO_RAW('frankzap');
crypt_raw RAW(2000) := utl_raw.cast_to_raw(utl_raw.cast_to_varchar2(xCrypt)) ;
BEGIN
dbms_obfuscation_toolkit.DESDecrypt(
input => xCrypt,
KEY => Cle,
decrypted_data => crypt_raw );
crypt_str := utl_raw.cast_to_varchar2(crypt_raw);
l := LENGTH(crypt_str);
crypt_str := RPAD(crypt_str,l-ASCII(SUBSTR(crypt_str,l)));
RETURN crypt_str;
END;
END ENCRY_PASS;
/

SELECT OBJECT_NAME, SQL_TEXT,SID, OSUSER, SCHEMANAME, RECURSIVE, LAST_CALL_ET/(60) MINUTES,
(case type when 'TM' then 'DML/TABLE LOCK'
when 'TX' then 'TRANSACTION LOCK'
when 'MR' then 'MEDIA RECOVERY'
when 'ST' then 'DISK SPACE TRANSACTION'
when 'UL' then 'USER SUPPLIED'
else 'LOCKED'
end) LOCK_TYPE,
decode(lmode,0,'NONE',1,'NULL',2,'ROW S',3,'ROW X',4,'TABLE S',5,'SHARED ROW X',6,'TABLE X',NULL) LOCK_MODE,
decode(REQUEST,0,'NONE',1,'NULL',2,'ROW S',3,'ROW X',4,'TABLE S',5,'SHARED ROW X',6,'TABLE X',NULL) REQUEST, CTIME/(60) LOCKED_SINCE_MIN
FROM V$TRANSACTION JOIN V$SESSION ON SADDR = SES_ADDR JOIN V$LOCK USING (SID)
JOIN V$SQLAREA ON ADDRESS = PREV_SQL_ADDR AND HASH_VALUE = PREV_HASH_VALUE
JOIN V$LOCKED_OBJECT ON SID=SESSION_ID
JOIN DBA_OBJECTS USING (OBJECT_ID)

SELECT OBJECT_NAME, SID, OSUSER, SCHEMANAME, RECURSIVE, LAST_CALL_ET/(60) MINUTES,
(case type when 'TM' then 'DML/TABLE LOCK'
when 'TX' then 'TRANSACTION LOCK'
when 'MR' then 'MEDIA RECOVERY'
when 'ST' then 'DISK SPACE TRANSACTION'
when 'UL' then 'USER SUPPLIED'
else 'LOCKED'
end) LOCK_TYPE,
decode(lmode,0,'NONE',1,'NULL',2,'ROW S',3,'ROW X',4,'TABLE S',5,'SHARED ROW X',6,'TABLE X',NULL) LOCK_MODE,
decode(REQUEST,0,'NONE',1,'NULL',2,'ROW S',3,'ROW X',4,'TABLE S',5,'SHARED ROW X',6,'TABLE X',NULL) REQUEST,
CTIME/(60) LOCKED_SINCE_MIN,SQL_TEXT
FROM V$TRANSACTION JOIN V$SESSION ON SADDR = SES_ADDR JOIN V$LOCK USING (SID)
JOIN V$SQLAREA ON HASH_VALUE = SQL_HASH_VALUE AND SQL_ADDRESS = ADDRESS
JOIN V$LOCKED_OBJECT ON SID=SESSION_ID
JOIN DBA_OBJECTS USING (OBJECT_ID)

prompt "Identify the blocking session"
prompt "================================================="

select * from v$lock ;

prompt ""
prompt ""
prompt ""
prompt "Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1."
prompt "Further, you can tell which session is being blocked by comparing the values in ID1 and ID2."
prompt "The blocked session will have the same values in ID1 and ID2 as the blocking session,"
prompt "and, since it is requesting a lock it's unable to get, it will have REQUEST > 0."
prompt ""
prompt ""

prompt "================================================="
prompt "Detailed View"
prompt "================================================="

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

prompt ""
prompt ""
prompt "================================================="
prompt "Readable View"
prompt "================================================="


select s1.username || '@' || s1.machine|| ' ( SID=' || s1.sid || ' ) is blocking '|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

select 'Ratio Should be < 15%' from dual; select sum(getmisses), sum(gets) , (sum(getmisses) * 100)/sum(gets) "Misses To Gets Ratio" from v$rowcache select parameter, getmisses, gets from v$rowcache; select sum(pins), sum(reloads) , sum(reloads) / sum(pins) * 100 "Reload Ratio" from v$librarycache select 'GETHITRATIO should be high > 90%' from dual

select sum(pinhits)/sum(pins) "lib cache hit ratio" from v$librarycache

select namespace
, gethitratio
, pinhitratio
, reloads
, invalidations
from v$librarycache

select pool, name, bytes/(1024*1024) from v$sgastat where pool = 'shared pool' and name = 'free memory'
select * from v$sqlarea where parse_calls != executions
select * from v$sqlarea where sql_text like '%v$sqlarea%'
select * from v$sysstat where name like '%parse%'
select * from v$sysstat where name in ('parse count (total)','execute count')
select * from v$sysstat where name in ('parse time cpu','CPU used by this session')
select * from v$sysstat where name like '%sort%'

select * from v$statname

select * from v$sgastat

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS

select owner, object_name, count(*) from v$bh ,dba_objects where objd = object_id and
owner not in ('SYS','SYSTEM')
and owner = 'HCLT43_PERF'
group by cube(owner, object_name)
order by 1,3,2 desc

Weblogic 8	Weblogic 9/10
Console is an applet and uses JCX JCS JPF and Netui page flows	Console is portal and uses JSTL (JSP 2.0)
No folder called config	Folder called config is available
No Prepare state for application. Only active state	Prepare state for application, This optimises memory utilization.
No app inf lib and classes	App inf lib and app inf classes are added
Persistant store is defined Under JMS	Persistant store is defined Under JDBC
We have connection pools and datasources	We have datasources and connection pools are inside datasources.
We have exclude queues.	We have work managers
No lock and edit	Lock and edit is the new feature
We need to delete and redeploy from admin console	We can update the application using admin console
All configuration information is in one config.xml	Seperate xml files for domain config and jms modules are added
No concept of JMS modules and sub deployments	Jms modules and subdeployments are included.
Queue/topic is configured under distributed destination	Queue/topic/dd etc are all clubbed in a JMS modules
Side by site deployment is not possible	Side by side deployment is possible
Server dosent come up if deployment fails	Server boots in ADMIN mode if deployment failes

bootinfo –K

/unix -> /usr/lib/boot/unix_up # 32 bit uniprocessor kernel /unix -> /usr/lib/boot/unix_mp # 32 bit multiprocessor kernel /unix -> /usr/lib/boot/unix_64 # 64 bit multiprocessor kernel

ln -sf /usr/lib/boot/unix_64 /unix ln -sf /usr/lib/boot/unix_64 /usr/lib/boot/unix bosboot -ad /dev/hdiskxx shutdown -r

lslv -m hd5

Note:

bootinfo -y

bootinfo -r

lsattr -El sys0 -a realmem

lsattr -l rmt0 -E

lsattr -l rmt0 -D

lsattr -l tty0 -a login -R

lsattr -E -l sys0

lscfg | grep proc

lspv

lspv hdisk1

How do I get a detailed configuration of my system?

lscfg

-p	Displays platform-specific device information. The flag is applicable to AIX 4.2.1 or later.
-v	Displays the VPD (Vital Product Database) found in the customized VPD object class.

lscfg -vl rmt0

uname -p	Displays the chip type of the system. For example, PowerPC.
uname -r	Displays the release number of the operating system.
uname -s	Displays the system name. For example, AIX.
uname -n	Displays the name of the node.
uname -a	Displays the system name, nodename, version, machine ID.
uname -M	Displays the system model name. For example, IBM, 9114-275.
uname -v	Displays the operating system version.
uname -m	Displays the machine ID number of the hardware running the system.
uname -u	Displays the system ID number.

oslevel -r

lslpp -h bos.rte

oslevel -rl 5300-04

oslevel –s 5300-04-02

Is a CSP (Concluding Service Pack) installed on my system?

oslevel –s 5300-03-CSP

How do I create a file system?

crfs -v jfs -g testvg -a size=10M -m /fs1

The following command will create, within volume group testvg, a jfs2 file system of 10MB with mounting point /fs2 and having read only permissions:

crfs -v jfs2 -g testvg -a size=10M -p ro -m /fs2

How do I change the size of a file system?

chfs -a size=+1000000 /usr

mount -V cdrfs -o ro /dev/cd0 /cdrom

mount /dev/fslv02 /test

mount {-a|all}

umount /test

mount

rmfs /test

defragfs /home

lslpp -w /usr/bin/vmstat

which_fileset svmon

lslpp -l

How do I determine if all filesets of maintenance levels are installed on my system?

instfix -i | grep ML

instfix -ik IY24043

instfix -k IY73748 -d /dev/cd0

How do I verify if filesets have required prerequisites and are completely installed?

lppchk -v

dump -Htv

lsps -a

chps -s 3 hd6

How do I reduce a paging space?

chps -d 4 hd6

How would I know if my system is capable of using Simultaneous Multi-threading (SMT)?

smtctl [ -m off | on [ -w boot | now]]

-m off	Sets SMT mode to disabled.
-m on	Sets SMT mode to enabled.
-w boot	Makes the SMT mode change effective on next and subsequent reboots if you run the bosboot command before the next system reboot.
-w now	Makes the SMT mode change immediately but will not persist across reboot.

mkvg -y name_of_volume_group -s partition_size list_of_hard_disks

chvg

mklv -y name_of_logical_volume name_of_volume_group number_of_partition

extendlv lv05 3

How do I display all logical volumes that are part of a volume group (for example, rootvg)?

lsvg -l rootvg

lslv lv1

rmlv lv7

1. mklvcopy LogicalVolumeName Numberofcopies
2. syncvg VolumeGroupName

rmlvcopy testlv 2

lsvg

lsvg rootvg

lsvg -p rootvg

extendvg VolumeGroupName hdisk0 hdisk1 ... hdiskn

/usr/sbin/lquerypv -M hdisk0 256

syncvg -p hdisk4 hdisk5

To synchronize all physical partitions from volume group testvg, use:

syncvg -v testvg

How do I replace a disk?

1. extendvg VolumeGroupName hdisk_new
2. migratepv hdisk_bad hdisk_new
3. reducevg -d VolumeGroupName hdisk_bad

alt_disk_copy -d hdisk1

ifconfig -a host Fully_Qualified_Host_Name

lsdev -Cc if

ifconfig -a

ifconfig tr0

ifconfig tr0 up

ifconfig tr0 down

You can add a startup script to your UNIX rc scripts to run WebLogic Server at UNIX boot time. Here is an example from an HP-UX 11 system, running under JDK 1.1. You need to supply the URL for your WebLogic Server and your system password. This file, wlstart, is placed in the /sbin/init.d directory and there is a link to it in the /sbin/rc2.d directory:

export SHLIB_PATH=\
/home/user1/weblogic/lib/hpux11:/oracle/8.0.4/lib
export CLASSPATH=/home/user1/weblogic/classes:\
/home/user1/weblogic/lib/weblogicaux.jar
export ORACLE_HOME=/oracle/8.0.4
export ORACLE_SID=DEMO
export ORACLE_TERM=vt100
export QAT=/home/user1/weblogic
cd $QAT
PATH=/sbin:/usr/sbin:/usr/bin:/opt/java/bin
export PATH
case $1 in
'start')
java -ms64m -mx64m -verbosegc weblogic.Server > \
/home/user1/weblogic/server.out 2> #emp;
;;
'stop')
java weblogic.Admin URL shutdown system password
;;
*)
echo "usage: $0 {start|stop}"
;;
esac

You should work with your UNIX system administrator to set up scripts similar to this for your system.