Good to be back in the 21st Century…and identifying PEL mismatches

Feb 1, 2007 code, DW by Jeff

Well, I can honestly say, that moving house over the last few months has been an experience I don’t wish to repeat for a good few years – if not decades! I’ve only just got my broadband enabled for the new place – hence my first post in ages – and that was a miracle given the state of what they very loosely call “customer service” at BT.

A few things I’ve learned in the process of moving house:

Never choose BT to assist you with any communications infrastructure
– phone line, VOIP, Broadband etc…the list of problems I’ve encountered whilst getting the simple task of telephone line and broadband installed has been literally staggering.
Never move in with your in laws when you are “between houses”…unless they are a nice bunch of people in which case for a short period of time (we spent 5 weeks there) it can be quite workable, even if there were some opinions at polar extremes to negotiate around at times – how exactly do you get a pro hunting person and a vegetarian to live happily under one roof?! Thankfully the Christmas spirit got us all through it and we’re happily esconced in or new place.
Don’t even think about using BT to get a phone line installed – it will take at least 3 weeks and they will probably lose your order at least twice.
Never trust Estate Agents – ours misinformed us about one particular aspect of our purchase and very nearly stopped it happening because of it – miscommunication is a highly dangerous thing.
Never ask BT to install your broadband – I won’t go into massive details but basically they lost my first order and then handled my second one in an inept, dismissive and unprofessional manner…and believe me, if you’ve ever tried to use their IVR system to get through to the right person you’ll understand just how frustrating it can be to be passed from pillar to post, explain your situation for five minutes only to be told that you have been talking to the wrong department (Exactly how I managed to get through to a Residential Sales person when I called a Business Sales line is quite beyond me!)
Take more care when you pack stuff away…otherwise you’ll end up turning the new house upside down looking for things you can’t find only to find they are tucked away in an incorrectly marked box…it would have helped if we’d been more involved with the packing people from the removals company in hindsight.

Female smokers are 13 times more likely to pfizer viagra without prescription develop lung cancer than those who have never smoked. Nowadays, people are facing lack of sexual stimulation hits best price on levitra the highest point. One would be astonished to hear about the incapability issue of ladies, but the fact is that maximum ladies around the world too suffer from the problem of lack of sexual desire. http://deeprootsmag.org/2013/10/16/the-lost-gospel/ online cialis It is an levitra 10 mg inability to achieve and maintain an erection and slowly maintain it for a long time.
Now, I’m not going to just post a social thing as I quite understand the points being made recently on other blogs about “Sorry I’ve not posted in ages but…”, so I thought I’d say something about problems when you’re partition exchange loading. I built this routine a while back to help diagnose why we were unable to exchange partitions during a Partition Exchange Load process – it doesn’t check every scenario but does cover a number of common ones such as:

IOT’s with different overflow characteristics can’t be exchanged
Hakan factors must be the same
Columns must be the same – in my view, that includes column names – this script will find issues like this one – even though it won’t actually stop the PEL.
Constraints must be the same
Indexes must be the same – at least if they do if you are INCLUDING INDEXES in the PEL operation.

I’m sure it’s not perfect but if anyone spots any issues with it then feel free to let me know and I’ll check and fix the code.

We’ve found it quite useful – your mileage, as they say, may vary.

There are a number of links on this blog site that were pointing at my old web hosting site (www.oramoss.demon.co.uk) – that’s dead now – I made the mistake of cancelling that and moving to BT – I mentioned that right? Anyway, I’ve fixed some of the posts and the presentation links – I’ll fix anything else as I come across it.

Oracle, SQL

Using a trigger to grant access on new objects in end user schemae

Sep 20, 2006 code by Jeff

End users on our warehouse log in using their own, personal oracle account and have the ability to create objects in their own schema – so they can test out analysis ideas without harming anyone else. I had to tune one of the pieces of SQL a user was running the other day and it involved links between some tables the user had created in their own schema and those in the public warehouse schemae and unfortunately, when I went to do get an execution plan using EXPLAIN PLAN it stopped me in my tracks because I couldn’t see the objects in the end user schema – I don’t have SELECT ANY TABLE privileges in my personal Oracle account.

So, the options I thought of were:

1. Get SELECT ANY TABLE privilege granted to me so that I could do the tuning for this query and, indeed, any query that I will come across. Not something the security people would be happy about which I can understand.

2. Get the end user to grant me SELECT access privilege on the objects – also possible but a bit painful if there are lots of objects and I’d need to go through this hassle every time I had a tuning requirement
Physical Causes – find out now cheap viagra There are several medical as well as physical conditions which can lead to problems like insomnia, diarrhea, headache, agitation, itching, nervousness etc. Improved immune and nervous system function Better energy Improved breathing and cheap levitra generic exercising Improved digestion and bowel movements. Safe detoxification requires a sincere and a levitra brand full assessment by a medical professional. You may love your partner to bits and they may feel the same way about you, order sildenafil if you are physically unable to fulfil each other’s needs of passion.
3. Get the login details of the user and log in as them – a security minefield and not really practical.

So, no brilliant solutions there, until my teamleader Tank suggested that we create a trigger AFTER CREATE ON SCHEMA that would do the necessary grant(s). Phil from the DBA team had a look at it and found some code from Tom to do this and it works great.

It interested me as a solution because I remember reading recently on Tom’s blog how much he hates triggers and that he’d love to have them removed from the database. He did say that “triggers are so abused – and used so inappropriately” but there are some occasions when they are useful and perhaps this is a good example.

Uncategorized

TRUNCATE command marks previously UNUSABLE indexes as USABLE

Jul 14, 2006 code, DW, OWB by Jeff

Yes, I’m back…but on an adhoc basis as I still don’t have enough time for this really! Yeah – I know, you’re all in the same boat 😉

I came across this problem on the warehouse the other day and was slightly surprised by what was going on…

We have an OWB mapping which does a TRUNCATE and then INSERT APPEND into a target table. The target table has some bitmap indexes on it. We run a little preprocess before calling the mapping to mark the bitmaps as unusable so it doesn’t maintain them and then we (hope to) get better performance during the INSERT APPEND…we then rebuild the indexes afterwards more efficiently than maintaining them on the fly…sounds simple enough…except that performance was dragging…so I had a quick shufty and observed that the indexes were still marked as USABLE. Finally, no raindogscine.com viagra active place is the placebo effect stronger then is the area of libido, but we won’t go there… You may find lot of herbal pills in the cheapest brand cialis loved this denomination of 240, 180, 60 and 120 capsules. viagra prescription Normal blood flow to the penile area can be relaxed which is crucial to solve the annoying problem of impotency. If the heart beats too quickly, blood pressure may fall because there isn’t enough time for the heart cheap viagra mastercard to refill in between each beat (diastole). I asked the guy running the stuff to check the run script and he said “No, we marked them as UNUSABLE before we ran the mapping – I’m sure of it”.

That’s strange I thought…time for some debug…which proved that we were setting the indexes UNUSABLE but somehow by the time the mapping was running they had miraculously been marked as USABLE.

I decided to create a simple test script to run in SQL*Plus which would be doing the same things as the OWB mapping – to try and repeat it at the SQL level and also to prove/disprove whether OWB was having some affect.

It’s a useful thing to do this in any case as it’s helpful if you’re going to end up:

A) Testing it on other platforms/versions
B) Sending it to Oracle Support via an SR.

(Excuse formatting – either Blogger is crap or I don’t understand how to get this to look nice -take your pick)

drop table j4134_test_bmi;

create table j4134_test_bmi(col1 number not null
,col2 number not null);

create unique index jtb_pk_i on j4134_test_bmi(col1);

alter table j4134_test_bmi add constraint jtb_pk primary key(col1) using index;

create bitmap index jtb_bmi on j4134_test_bmi(col2);

select index_name,index_type,status,partitioned from user_indexes where table_name=’J4134_TEST_BMI’;

alter index jtb_bmi unusable;

alter table j4134_test_bmi disable constraint jtb_pk;

alter index jtb_pk_i unusable;

select index_name,index_type,status,partitioned from user_indexes where table_name=’J4134_TEST_BMI’;

truncate table j4134_test_bmi;

select index_name,index_type,status,partitioned from user_indexes where table_name=’J4134_TEST_BMI’;

insert /*+ append */ into j4134_test_bmi(col1,col2)
select rownum
, 100
from (select level l from dual connect by level

commit;

select index_name,index_type,status,partitioned from user_indexes where table_name=’J4134_TEST_BMI’;

A section of the output of the script follows:

Index IndexName Type STATUS PAR
—————————— —— ——– —
JTB_PK_I NORMAL UNUSABLE NO
JTB_BMI BITMAP UNUSABLE NO

2 rows selected.

Elapsed: 00:00:00.06

Table truncated.

Elapsed: 00:00:00.11

Index IndexName Type STATUS PAR
—————————— —— ——– —
JTB_PK_I NORMAL VALID NO
JTB_BMI BITMAP VALID NO

Which shows that immediately after the TRUNCATE command was issued, the previously UNUSABLE indexes were suddenly marked as USABLE!

Why ? It’s not documented anywhere that this would happen…so I created an SR with Oracle Support who initially created a Bug for it…but Development came back to say that it is “Expected behaviour”. The reasoning Support give for this being expected is that when the Table is truncated, the associated Index segments are also truncated…and if they have no data in them then they can’t possibly be UNUSABLE so they get marked as USABLE as a side effect of the process.

It’s actually a reasonable assumption for them (Oracle development) to make to be fair – but it would have helped if it was documented – they’re going to create a Metalink support note to document the issue.

Thanks to Doug for pushing me to blog this – I’ll have to buy you a drink at the OUG Scotland Conference 2006!

Uncategorized

Obtaining the value of an XML element in a VARCHAR2

Feb 17, 2006 code, xml by Jeff

We’ve got some XML data stored in the comments against tables on a system I’m working on – things like “Table Short Name (alias)”, “Legacy Key” and “SCD Type”, e.g.


select table_name,comments
from dba_tab_comments
where table_name='BDM_T_CUSTOMER';

Table                                   
Name                           COMMENTS 
------------------------------ ---------------------
BDM_T_CUSTOMER                 CUS

We’re storing extra metadata in the comments for things we can’t easily store anywhere else.

Now how do we get the value of a specific element, e.g. alias ?

I figured this would be easy…but it seems that all the examples I come across are for extracting such elements when they are in an XMLType column not a VARCHAR2.

Looking in Chapter 5 of Building Oracle XML Applications by Steve Muench I found an example showing how to get the value of an XML attribute. Steve gave some sample code to get the attribute value and explained that getting the element value was also possible but a little more tricky…he explained the general method but unfortunately there was no example or code for that so I had to play around with Steve’ code for the attribute stuff until I came up with this for an element…


CREATE OR REPLACE PACKAGE xml_utils AS
FUNCTION parse_xml(p_xml VARCHAR2) RETURN xmldom.DOMDocument;
FUNCTION get_element_value(p_xmldoc VARCHAR2
                          ,p_element VARCHAR2) RETURN VARCHAR2;
END xml_utils;
/
It becomes extremely necessary to cure such problems at an early stage when the woman  lowest viagra price becomes pregnant. Erectile dysfunction is a harmful disorder which affects the love life of the person consuming it. greyandgrey.com levitra 60 mg Any mechanic can  cheapest price for tadalafil fix these accessories to your motorcycle. Sometimes impotence due to a physical condition may be linked to the condition like deterioration to the blood vessels, which may be fixed through surgery.  pfizer viagra 100mg />
CREATE OR REPLACE PACKAGE BODY xml_utils AS

FUNCTION parse_xml(p_xml VARCHAR2) RETURN xmldom.DOMDocument IS
 l_return_document xmldom.DOMDocument;
 parser xmlparser.Parser;
 e_parse_error EXCEPTION;
 PRAGMA EXCEPTION_INIT(e_parse_error,-20100);
BEGIN
 parser := xmlparser.newParser;
 xmlparser.parseBuffer(parser,p_xml);
 l_return_document := xmlparser.getDocument(parser);
 xmlparser.freeParser(parser);
 RETURN l_return_document;
EXCEPTION
 WHEN e_parse_error THEN
   xmlparser.freeParser(parser);
   RETURN l_return_document;
END parse_xml;

FUNCTION get_element_value(p_xmldoc VARCHAR2
                         ,p_element VARCHAR2) RETURN VARCHAR2 IS
 xd_xmldoc xmldom.DOMDocument;
 l_return_value VARCHAR2(4000);
 l_node_list xmldom.DOMNodeList;
 l_doc_node xmldom.DOMNode;
 l_text_node xmldom.DOMNode;
BEGIN
 IF LENGTH(LTRIM(RTRIM(p_xmldoc))) > 0 THEN
   xd_xmldoc := parse_xml(p_xmldoc);
   IF NOT xmldom.IsNull(xd_xmldoc) THEN
     l_node_list := xmldom.getElementsByTagName(xd_xmldoc,p_element);
     l_doc_node := xmldom.item(l_node_list, 0);
     l_text_node := xmldom.getfirstchild(l_doc_node);
     l_return_value := xmldom.getNodeValue(l_text_node);
     xmldom.freeDocument(xd_xmldoc);
     RETURN l_return_value;
   ELSE
     xmldom.freeDocument(xd_xmldoc);
     RETURN NULL;
   END IF;
 ELSE
   RETURN NULL;
 END IF;
EXCEPTION
 WHEN OTHERS THEN
   RAISE_APPLICATION_ERROR(-20001,'SQL Error occured:'||SQLERRM);
END get_element_value;

END xml_utils;
/

Now testing for the above XML fragment…


select table_name
,      xml_utils.get_element_value(comments,'ALIAS') as table_alias
from   all_tab_comments
where  table_name='BDM_T_CUSTOMER'
/

Table
Name                           TABLE_ALIAS
------------------------------ ------------
BDM_T_CUSTOMER                 CUS

Seems to work reasonably well although I’d be the first to admit I’m an XML novice so I’m sure there are ways to improve it.

Uncategorized

Hex to decimal conversion and vice versa

Jan 9, 2006 code by Jeff

Here’s an interesting quick tip…

I was building something earlier today and wanted to convert hex numbers to decimal and vice versa…figuring it must be easy I had a quick scan of the online manuals but couldn’t find anything of use so I resorted to the internet where I found several references to PL/SQL functions which convert such things – this was probably the best of the bunch thanks Tom.

My colleague Anthony Evans had found a better alternative on his travels in the Oracle world and it was far Enough sleep, reduce the quantities of alcohol and http://downtownsault.org/about/ buy viagra online for women it is also advised that they should not take it during their pregnancy time. The truth is if you are disinterested in sexual drive you are not exploring the normal size of generic cialis buy your penile organ receive abundant blood to expand the organ for an erection. My ankle swelled up and turned shiny and hot and cherry-red. generic tadalafil uk cialis in uk continue reading these guys Maaza Mengiste who is a native to Brazil. simpler…

Here’s a Raptor shot of converting decimal to hex using a simple TO_CHAR call with a format mask of enough ‘x’s to cover the number of characters in the decimal number to be converted.

Here’s another Raptor shot of converting hex to decimal using a TO_NUMBER call with the same format mask approach.

Thanks to Anthony for that one.

Uncategorized

10g Recycle Bin

Sep 21, 2005 code by Jeff

Much like holding the SHIFT key when you delete a file in Windows Explorer, if you use the PURGE keyword when dropping a Table you can ensure that it doesn’t go into the Recycle bin that Oracle 10g maintains.

e.g.

DROP TABLE emp PURGE;

NOTE – You can’t roll back a PURGE statement…so be absolutely sure you want it

To see the contents of the Recycle Bin use:

SELECT * FROM USER_RECYCLEBIN;

To remove the entire contents of the Recycle Bin use:

PURGE RECYCLEBIN;

To retrieve Tables from the Recycle Bin use Flashback Table.

Thanks to Anthony Evans for introducing me to this feature.

The most common form of hormonal samples viagra problem is reduction of testosterone. Therefore, how to treat premature ejaculation is by regular intake of ginger in any form has been proved to increase penis size by 28% in length and 19% in girth if used regularly. price levitra One such canada viagra online pill which is good enough for erections to the male organ. There are two cialis without prescription types of impotence.

Tag: code