Oracle. Who Is Waiting, Who Is Locking

This Oracle (tested on 11g) SQL script shows who is waiting on locked tables.

SELECT SUBSTR(TO_CHAR(w.session_id),1,5) "W.SID", p1.spid "W.PID", SUBSTR(s1.username,1,12) "W.User", SUBSTR(s1.osuser,1,8) "W.OS User", SUBSTR(s1.program,1,20) "W.Program", s1.client_info "W.Client", '<< WAITING | HOLDING >>' "|", SUBSTR(TO_CHAR(h.session_id),1,5) "H.SID", p2.spid "H.PID", SUBSTR(s2.username,1,12) "H.User", SUBSTR(s2.osuser,1,8) "H.OS User", SUBSTR(s2.program,1,20) "H.Program", s2.client_info "H.Client", o.object_name "H.Object" FROM gv$process p1, gv$process p2, gv$session s1, gv$session s2, dba_locks w, dba_locks h, dba_objects o WHERE w.last_convert > 60 AND h.mode_held != 'None' AND h.mode_held != 'Null' AND w.mode_requested != 'None' AND s1.row_wait_obj# = o.object_id AND w.lock_type(+) = h.lock_type AND w.lock_id1(+) = h.lock_id1 AND w.lock_id2 (+) = h.lock_id2 AND w.session_id = s1.sid (+) AND h.session_id = s2.sid (+) AND s1.paddr = p1.addr (+) AND s2.paddr = p2.addr (+) ORDER BY w.last_convert DESC ;

Waiting......

Unprotecting Excel Worksheets

After being given an Excel spreadsheet and being asked to modify it and finding the Worksheets were protected, I came across this website:

McGimpsey & Associates

All the explaination is on their website, but I took the macro code below, ran it and within a few seconds my worksheet was unprotected.

Public Sub AllInternalPasswords() ' Breaks worksheet and workbook structure passwords. Bob McCormick ' probably originator of base code algorithm modified for coverage ' of workbook structure / windows passwords and for multiple passwords ' ' Norman Harker and JE McGimpsey 27-Dec-2002 (Version 1.1) ' Modified 2003-Apr-04 by JEM: All msgs to constants, and ' eliminate one Exit Sub (Version 1.1.1) ' Reveals hashed passwords NOT original passwords Const DBLSPACE As String = vbNewLine & vbNewLine Const AUTHORS As String = DBLSPACE & vbNewLine & _ "Adapted from Bob McCormick base code by" & _ "Norman Harker and JE McGimpsey" Const HEADER As String = "AllInternalPasswords User Message" Const VERSION As String = DBLSPACE & "Version 1.1.1 2003-Apr-04" Const REPBACK As String = DBLSPACE & "Please report failure " & _ "to the microsoft.public.excel.programming newsgroup." Const ALLCLEAR As String = DBLSPACE & "The workbook should " & _ "now be free of all password protection, so make sure you:" & _ DBLSPACE & "SAVE IT NOW!" & DBLSPACE & "and also" & _ DBLSPACE & "BACKUP!, BACKUP!!, BACKUP!!!" & _ DBLSPACE & "Also, remember that the password was " & _ "put there for a reason. Don't stuff up crucial formulas " & _ "or data." & DBLSPACE & "Access and use of some data " & _ "may be an offense. If in doubt, don't." Const MSGNOPWORDS1 As String = "There were no passwords on " & _ "sheets, or workbook structure or windows." & AUTHORS & VERSION Const MSGNOPWORDS2 As String = "There was no protection to " & _ "workbook structure or windows." & DBLSPACE & _ "Proceeding to unprotect sheets." & AUTHORS & VERSION Const MSGTAKETIME As String = "After pressing OK button this " & _ "will take some time." & DBLSPACE & "Amount of time " & _ "depends on how many different passwords, the " & _ "passwords, and your computer's specification." & DBLSPACE & _ "Just be patient! Make me a coffee!" & AUTHORS & VERSION Const MSGPWORDFOUND1 As String = "You had a Worksheet " & _ "Structure or Windows Password set." & DBLSPACE & _ "The password found was: " & DBLSPACE & "$$" & DBLSPACE & _ "Note it down for potential future use in other workbooks by " & _ "the same person who set this password." & DBLSPACE & _ "Now to check and clear other passwords." & AUTHORS & VERSION Const MSGPWORDFOUND2 As String = "You had a Worksheet " & _ "password set." & DBLSPACE & "The password found was: " & _ DBLSPACE & "$$" & DBLSPACE & "Note it down for potential " & _ "future use in other workbooks by same person who " & _ "set this password." & DBLSPACE & "Now to check and clear " & _ "other passwords." & AUTHORS & VERSION Const MSGONLYONE As String = "Only structure / windows " & _ "protected with the password that was just found." & _ ALLCLEAR & AUTHORS & VERSION & REPBACK Dim w1 As Worksheet, w2 As Worksheet Dim i As Integer, j As Integer, k As Integer, l As Integer Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer Dim PWord1 As String Dim ShTag As Boolean, WinTag As Boolean Application.ScreenUpdating = False With ActiveWorkbook WinTag = .ProtectStructure Or .ProtectWindows End With ShTag = False For Each w1 In Worksheets ShTag = ShTag Or w1.ProtectContents Next w1 If Not ShTag And Not WinTag Then MsgBox MSGNOPWORDS1, vbInformation, HEADER Exit Sub End If MsgBox MSGTAKETIME, vbInformation, HEADER If Not WinTag Then MsgBox MSGNOPWORDS2, vbInformation, HEADER Else On Error Resume Next Do 'dummy do loop For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 With ActiveWorkbook .Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _ Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) If .ProtectStructure = False And _ .ProtectWindows = False Then PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _ Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) MsgBox Application.Substitute(MSGPWORDFOUND1, _ "$$", PWord1), vbInformation, HEADER Exit Do 'Bypass all for...nexts End If End With Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next Loop Until True On Error GoTo 0 End If If WinTag And Not ShTag Then MsgBox MSGONLYONE, vbInformation, HEADER Exit Sub End If On Error Resume Next For Each w1 In Worksheets 'Attempt clearance with PWord1 w1.Unprotect PWord1 Next w1 On Error GoTo 0 ShTag = False For Each w1 In Worksheets 'Checks for all clear ShTag triggered to 1 if not. ShTag = ShTag Or w1.ProtectContents Next w1 If ShTag Then For Each w1 In Worksheets With w1 If .ProtectContents Then On Error Resume Next Do 'Dummy do loop For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 .Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) If Not .ProtectContents Then PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _ Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) MsgBox Application.Substitute(MSGPWORDFOUND2, _ "$$", PWord1), vbInformation, HEADER 'leverage finding Pword by trying on other sheets For Each w2 In Worksheets w2.Unprotect PWord1 Next w2 Exit Do 'Bypass all for...nexts End If Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next Loop Until True On Error GoTo 0 End If End With Next w1 End If MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER End Sub

Many Thanks McGimpsey & Associates

Css ... But only for Chrome

We all know browsers like to apply css differently. So to only apply certain css rules to chrome, wrap the chrome specific code as so.

@media screen and (-webkit-min-device-pixel-ratio:0) { .myclass{ padding-top:1px; } /* Correct alignment */ }

Easy.

Oracle: Querying XML from a CLOB (Part 2)

Using Oracle 11g again...Moving on from yesterdays XML examples, I added some more complexity and things started to go wrong!

• Why am I getting null values back?
• How do I get attribute values?
• Does this method scale to multiple rows?

After some investigation...
I'm getting null values back because my new XML had a default namespace. A default namespace needs to be passed into the extract function, otherwise it returns null. I could just hard code this in, or have a variable, but I think that would be a pain, so I've added a new inline query to get the default namespace value dynamically

Getting attribute values is straight forward XPath stuff, just use the @ symbol. Easy.

But all of my data is being concatinated together in one row! Well of course is it, the table it's selecting from only has one row. So this needs the XML to be converted into a table.

OK Here we go.

1. Create the table (if not done so on the previous post)

CREATE TABLE xml_test ( id NUMBER(9,0), xml CLOB );

2. Insert some XML with 2 records, a default namespace and an attribute.

INSERT INTO xml_test VALUES ( 2, '<?xml version="1.0" encoding="iso-8859-1"?> <mydata MyAttribute="Fuzzy" xmlns:="http://www.abc.com"> <dude> <id>100</id> <name>John</name> <address> <line1>10 High Road</line1> <line2>London</line2> </address> <phone>123456789</phone> </dude> <dude> <id>101</id> <name>Frank</name> <address> <line1>11 High Road</line1> <line2>London</line2> </address> <phone>987654321</phone> </dude> </mydata>');

3. Query the data with the new query

WITH xmlData AS ( SELECT xt.id, XMLTYPE(xt.xml) xml FROM xml_test xt WHERE xt.id = 2 ), namespace AS ( SELECT xd.id, 'xmlns="'||XMLCAST(XMLQUERY('namespace-uri(.)' PASSING xd.xml RETURNING CONTENT) AS VARCHAR2(4000))||'"' defaultNs FROM xmlData xd ) SELECT xd.id, xd.xml.EXTRACT('//mydata/@MyAttribute',ns.defaultNs).GETSTRINGVAL() myAttribute, x.COLUMN_VALUE.EXTRACT('//dude/name/text()',ns.defaultNs).GETSTRINGVAL() dudeName, x.COLUMN_VALUE.EXTRACT('//dude/address/line1/text()',ns.defaultNs).GETSTRINGVAL() dudeAddress FROM xmlData xd, namespace ns, TABLE(XMLSEQUENCE(EXTRACT(xd.xml,'/mydata/dude',ns.defaultNs))) x WHERE xd.id = ns.id ORDER BY 1 ;

And you should get...

ID MYATTRIBUTE DUDENAME DUDEADDRESS -- ----------- -------- ------------ 2 Fuzzy John 10 High Road 2 Fuzzy Frank 11 High Road

Phew

Oracle: Querying XML from a CLOB

Using Oracle 11g, a quick example of one way to query XML data from a CLOB.

1. Create the table.

CREATE TABLE xml_test ( id NUMBER(9,0), xml CLOB );

2. Insert some xml data

INSERT INTO xml_test VALUES ( 1, '<?xml version="1.0" encoding="iso-8859-1"?> <mydata> <id>100</id> <name>John</name> <address> <line1>10 High Road</line1> <line2>London</line2> </address> <phone>123456789</phone> </mydata>');

3. Query it

SELECT XMLTYPE(t.xml).EXTRACT('//name/text()').getStringVal(), XMLTYPE(t.xml).EXTRACT('//address/line1/text()').getStringVal() FROM xml_test t;

4. Alternative query

WITH xmlData AS ( SELECT XMLTYPE(t.xml) xml FROM xml_test t WHERE t.id = 1 ) SELECT x.xml.EXTRACT('//name/text()').getStringVal(), x.xml.EXTRACT('//address/line1/text()').getStringVal() FROM xmlData x ;

Next step: Load the XML data into the table from a file.

Oracle: Reading a Text File into a CLOB

Using Oracle11g, this procedure reads a text file, in this case an xml file, into a CLOB.

DECLARE l_clob CLOB; l_bfile BFILE := BFILENAME('SCRATCH','test.xml'); BEGIN INSERT INTO xml_test ( id, xml ) VALUES ( 2, EMPTY_CLOB() ) RETURNING xml INTO l_clob; DBMS_LOB.OPEN(l_bfile, DBMS_LOB.LOB_READONLY); DBMS_LOB.LOADFROMFILE ( dest_lob => l_clob, src_lob => l_bfile, amount => DBMS_LOB.GETLENGTH(l_bfile) ); DBMS_LOB.CLOSE(l_bfile); END; /

If you need to worry about different character sets etc, then use DBMS_LOB.LOADCLOBFROMFILE, which has several more parameters.

File Read and Inserted. See also: Querying XML from a CLOB

Timing Sections of PLSQL Code

Your PLSQL routine is running like a dog with three legs? Don't know where to start looking as it could be any of a number of issues? Then adapt this timing code and drop it in for testing. Wrap the start and end calls around suspected sections of code to find where the hold up is occuring.

DECLARE ----------------------------- -- Timing Declaration Start TYPE tt_timingStart IS TABLE OF TIMESTAMP(9) INDEX BY VARCHAR2(30); l_timingStart tt_timingStart; TYPE tt_timingTotal IS TABLE OF NUMBER INDEX BY VARCHAR2(30); l_timingCount tt_timingTotal; l_timingTotal tt_timingTotal; -- PROCEDURE timingStart( p_timingIndex IN VARCHAR2 ) IS BEGIN BEGIN IF l_timingTotal(p_timingIndex) IS NULL THEN NULL; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN l_timingCount(p_timingIndex) := 0; l_timingTotal(p_timingIndex) := 0; END; l_timingStart(p_timingIndex) := SYSTIMESTAMP; END timingStart; -- PROCEDURE timingEnd( p_timingIndex IN VARCHAR2 ) IS l_duration INTERVAL DAY(9) TO SECOND(5) := SYSTIMESTAMP - l_timingStart(p_timingIndex); BEGIN l_timingCount(p_timingIndex) := l_timingCount(p_timingIndex) + 1; l_timingTotal(p_timingIndex) := l_timingTotal(p_timingIndex) + (EXTRACT(SECOND FROM l_duration)*1000) + (EXTRACT(MINUTE FROM l_duration)*60000) + (EXTRACT(HOUR FROM l_duration)*3600000) + (EXTRACT(DAY FROM l_duration)*86400000); END timingEnd; -- PROCEDURE timingOutput IS l_file UTL_FILE.FILE_TYPE; l_index VARCHAR2(30); BEGIN l_file := UTL_FILE.FOPEN('SCRATCH_DIR','TimingOutput.txt','W'); l_index := l_timingTotal.FIRST; WHILE l_index IS NOT NULL LOOP UTL_FILE.PUT_LINE(l_file,LPAD(l_index,20,'_')||': ' ||LPAD(ROUND(l_timingCount(l_index)),10)||'iterations' ||LPAD(ROUND(l_timingTotal(l_index)),10)||'ms' ||LPAD(ROUND(l_timingTotal(l_index)/1000),10)||'s' ||LPAD(ROUND(l_timingTotal(l_index)/60000),10)||'m'); l_index := l_timingTotal.NEXT(l_index); END LOOP; UTL_FILE.FCLOSE(l_file); END timingOutput; -- Timing Declaration End ----------------------------- BEGIN timingStart('Procedure'); FOR i IN 1..20 LOOP timingStart('First Sleep'); DBMS_LOCK.SLEEP(0.3); timingEnd('First Sleep'); timingStart('Second Sleep'); DBMS_LOCK.SLEEP(0.5); timingEnd('Second Sleep'); timingStart('Third Sleep'); DBMS_LOCK.SLEEP(0.1); timingEnd('Third Sleep'); END LOOP; timingEnd('Procedure'); timingOutput; END; /

This does assume there is a SCRATCH directory to output the file to. If you haven't got one it's easily modified to DBMS_OUTPUT.PUT_LINE.

Timed

Oracle Processes CPU Usage

Is that long running job doing anything? This SQL query (Oracle 11) will list user processes and the amount of CPU usage the current job has used so far. Re-query to see how the CPU usage progesses.

SELECT vs.username, vs.sid, vs.serial#, vp.spid, vs.machine, vs.module, vs.program, vs.action, vs.status, vs.last_call_et, ROUND((vss_sess.value-vss_call.value)/100) "CPU Usage" FROM gv$session vs, gv$process vp, v$sesstat vss_sess, v$sesstat vss_call WHERE vs.paddr = vp.addr(+) AND vs.inst_id = vp.inst_id(+) AND vs.sid = vss_sess.sid AND vs.sid = vss_call.sid AND vss_call.statistic# = 13 -- CPU used when call started AND vss_sess.statistic# = 14 -- CPU used by this session AND vs.username IS NOT NULL ORDER BY ( CASE vs.status WHEN 'INACTIVE' THEN 0 ELSE vss_sess.value-vss_call.value END ) DESC, vs.status, vs.last_call_et;

Maybe useful here...

Several ways to kill a process.

The kill session syntax. The immediate option will not kill the session faster, however it will return the prompt immediately after the statement has been issued.

ALTER SYSTEM KILL SESSION 'sid,serial#'; ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

The kill session statement asks the session to end, whereas the disconnect session statement cuts the session process.

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION; ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

The post transaction option is equivilent to kill session.

For those even more stubborn processes, you need to move to the operating system, using the spid value from the query above.

In Windows:

orakill ORACLE_SID spid

In UNIX:

kill -9 spid

Done and Killed (hopefully).

Solving ORA-28001 Without Changing the Password

Unexpectedly, several months after upgrading to Oracle 11g, our SYSMAN and DBSNMP account passwords expired, creating lots of errors in our audit logs. We never had this with 10g, they never expired.

Not wanting to dive into lots of unfamilar config files, we looked at unexpiring the accounts, keeping the existing passwords.

A little googling and tweaking later, the following SQL command outputs alter user statements to "unexpire" the accounts.

I'm not sure if this method is supported by Oracle or not but it worked for us.

SELECT 'ALTER USER '||u.name||' IDENTIFIED BY VALUES '''||u.spare4||';'||u.password||''';' cmd FROM sys.user$ u WHERE u.name IN ('SYSMAN','DBSNMP') ;

User altered