Which Temporary Tablespace is used for sorts?

Written by Paweł Barut
This time I will write about "Which Temporary Tablespace is used for sorts" ?. I was not wondering about this much, as usually there is only one Temporary tablespace in DB. Lets assume situation, as show on picture:

• User A
• Assigned to Temporary Tablespace TEMP_A
• has table TA
  
• User B
• Assigned to Temporary Tablespace TEMP_B
• has table TB
• owns procedure PB (definer rights)
  
• Both users has access rights for all above object (Select on Tables and Execute on Procedure)
  

So let's discuss some situations:

1. User A runs query on tables TA or TB (or any other) - when disk sort is needed then tablespace TEMP_A is used
2. User A executes procedure PB. Procedure PB opens cursor on table TB (or TA or any other). If disk sort is required then tablespace TEMP_B is used.
   

For me it was bit surprising. Especially that I did not find anywhere in Oracle documentation description for this behaviour:

TEMPORARY TABLESPACE Clause
Specify the tablespace or tablespace group for the user's temporary segments.

I was expecting that all sort segments will be created in tablespace that is assigned to that user. I was hopping to solve one of my issues that way. But it occurred that, sort segment is created by user B, because procedure PB uses user B rights. It is reasonable, as this is consistent with granting access to objects, and temporary objects are treated the same way as permanent ones. On the other hand select is run for user A - shouldn't TEMP_A be used in all cases? What is your opinion on that?

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• How to shrink Oracle Context Index
• Using Named Parameters within SQL
• Alternative Syntax for Insert
• Extracting Data to Excel using only SQL*Plus
• Rows to single string or aggregating strings

Horrible practice of storing users passwords

Written by Paweł Barut
I've read today terrifying article about un-ethical storage of users password. It is often common practice that users passwords are stored unencrypted in database. But this case is even more dreadful. It about software, G-Archiver, that is available to everyone. If you ever used this software, you should change your G-mail password right now.
So what is the problem:

• G-Archiver has build in user-name and password for one of g-mail accounts.
• Whenever someone uses G-Archiver and provides his credentials for g-mail, user-name and password is send to author of this software
• Additionally, any one who finds out the user-name and password stored in G-Archiver, can get passwords of thousand of previous users.

Go and read. Hope there are not too many of software that works like this one. It is really terrifying, that some people are so irresponsible. It could be well planed action to collect gmail users and passwords, or it could be just lack of imagination what are the consequences. I would like to believe that it was the second one.

But also big corporations do not care for security enough. It is common practice, that users password is stored internally as plain text. It can be easily found out by using "I forgot my password" feature. If in return you will get email with your original password, then it is a crappy web side. And you should never reuse password passed to such web side as this is big risk for you. Take a look at Password Security: It’s Not That Hard (But You Still Can’t Get It Right) for further explanation and examples.

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• Security: getting DBA rights quite easy
• „Do not use Linux, please, pirate our software” - Microsoft
• Spam, Spam filters, Being Spammer, Being Filtered-out ...
• Do not trust Google Maps

Useful links on Oracle XML DB

Written by Paweł Barut
Here is bunch of useful links related to Oracle XML DB and handling XML data in Oracle PL/SQL. It's mostly for my own reference, but I hope it will be useful for others too.

• How do i store the counts of all tables by Laurent Schneider
• Converting Between XML and Oracle Objects by Lewis Cunningham
  
• Problems Calling .Net Web Services from UTL_DBWS by Stellan
• First Tests of 11g Native Web Services and Revisiting 11g Native Web Services by Paul Gallagher
• Oracle 11g Security - XMLDB is here to stay… by Marco Gralike
• HOWTO: Create XMLType Table for Binary XML Usage by Marco Gralike
• HOWTO: Saving XML data directly to disk by Marco Gralike
• select from a comma-separated string by Laurent Schneider. BTW. Note that I've demonstrated this technique 7 month earlier :)

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• Solving ORA-22905: cannot access rows from a non-nested table item
• Binding list variable
• Oracle XMLType: exctractvalue vs. extract
• ORA-30625 and XMLType
  

CPUJan2008: One thing that every DBA should know about it.

Written by Paweł Barut
While reading documentation for latest Oracle security patch CPUJan2008 for database I've noticed one new required step. This step is: Recompile Views. One whould say that it's nothing strange, but to run this script database have to be started in UPGRADE MODE. For non-RAC installations it's not big issue, as anyway you have to plan downtime. But Oracle suggest that this recompilation can take 30 minutes:

"In one internal Oracle test with approximately 2000 views and 4000 objects, the total execution time for view_recompile_jan2008cpu.sql and utlrp.sql was about 30 minutes."

Practically in my case it took no more than 5 minutes, so it's not big deal.
But it could be problem for RAC installations, that used when CPU is applied on one node, then catcpu.sql is run, then second node is being patched. So normally DB will be all the time available. But if it's needed to start database in UPGRADE mode it means that downtime is required. According to documentation it's not required to run view recompilation script during paching process. This script can be run after,

"however, the CPU installation will not be complete until the view recompilation is completed."

You can ommit this step only if:

• Databases was created with Release 11.1.0.6 or later

• Databases was created with any release (for example, 10.2.0.3, 10.1.0.5, or 9.2.0.8) after CPUJan2008 or a later CPU has been applied

I hope it also means that after CPUJan2008 was applied you will not have to run this script after next CPU came out (CPUApr2008, etc).

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• Security: getting DBA rights quite easy
• CPUApr2007 and Interim patch 3 for 10.2.0.3 on Windows
• Applying Oracle 10.2.0.3 patch
• Autobackup of SPFILE and CONTROLFILE
• Blogger Beta Backup tool

Spam, Spam filters, Being Spammer, Being Filtered-out ...

Written by Paweł Barut
My thoughts about Spam. Some time agou I wrote about spam in comments on my blog, but this time it will be about email spam. This is something that from time to time irritates me a lot. Spam is something that nobody wants to see in his mails. And to solve this problem there are many spam filters, IP Block lists and other solutions. But none of them is 100% accurate. And this is what causes problems. Spam filers should be solving problems, but many times creates new ones.
In ideal situation spam filter eliminates 100% of spam, and passes 100% of emails that are expected by users. But it's not true. I will now show example situations, that lead me to conclusion, that spam filter are useless.
Situation 1.
Spam filter did not recognized spam mail, and I have to manually figure out that this is spam. So I need to one more click to delete message.
Situation 2.
Spam filter deletes mail that was intended for me. This was false alarm as it wasn't spam.
Situation 3.
I've send email to customer/friend. His spam filter blocked it. I did not received any delivery failure message.

In my opinion situation 2 and 3 are very dangerous and I would like to avoid any of those situations. In my opinion those situations makes spam filters useless. It is especially dangerous if this block is done by service provider, and when you cannot see list of spam being filtered out. This is what really annoys my and makes me angry. In fact it makes whole email system unreliable (I do not want to say useless), as you never know if you recipient get your email or not.

I do not know what is solution for this. I can see few options, but none of them is perfect:

1. Each and every email should be signed digitally by sender, and additionally by his service provider. Spam filers should be able to verify this and honor such signing, and not consider this to be spam. Of course spammers could find way to sign theirs mail too, and vanish this approach.
   
2. Everybody should use "return receipt" to confirm mail delivery. Well, quite simple, but personally I never allow my mailer to send confirmations, as I do not want to reveal when I've read mail.
   
3. Make mail system payable. So for every mail you send you have to pay small amount of money. $0.01 per email should not be problem for real email users, but could cost fortune for spammers. For this money service providers should ensure that your mail will reach recipient.
4. Use captcha to validate that email is send by real user. I could work as this: when spam filter suspects spam, it sends back email to sender with link to web page on which user will have to provide answer to captcha to make his mail pass throu spam filter.
   

At the end I would like to ask you: How do you deal with spam?

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• I've got spammed (in comments)
  
• "Do not use Linux, please, pirate our software" - Microsoft
• 13 – Happy or Unhappy Number?
• I've been tagged 3 times; 8 things about me
  

How to shrink Oracle Context Index

On of databases I've found that one of Context Indexes is getting bigger and bigger everyday. It was around 5-10% grow per day for table DR$MY_TEXT_I$I which contains tokens(word). No of records indexed was almost the same, but index grown was unexpectedly big. It was due fact, that quite many rows were modified every day, and it caused re-indexing those rows during index synchronization (CTX_DDL.SYNC_INDEX). So I've decided to reclaim this space.

Method 1.

This was my first though:

• Drop Index ... /Create Index ...
• or Alter Index ... Rebuild

But those methods have some disadvantages:

• Users cannot perform text searches during this operation,
• It does not solve problem for longer time, as index will still grow ...

So I had to find root cause and eliminate it.

Method 2.

While looking for root cause I've find out, that Index has never been optimized, so it kept old (obsolete) data. My solution was:
SQL> exec ctx_ddl.optimize_index('MY_TEXT_I', 'FULL', 120);
SQL> alter table DR$MY_TEXT_I$I enable row movement;
SQL> alter table DR$MY_TEXT_I$I shrink space cascade;

With this method table DR$MY_TEXT_I$I took 30% of its original size.
So let me explain why this worked. With ctx_ddl.optimize_index context index was internally optimized. It means information about old documents were completely deleted and index was internally minimized. Remember - third parameter limits time (in minutes) allowed for optimization. If it is really big index it can take hours. But you can run this optimization many times, until your index will be fully optimized.
Then I've just shrunk table (shrink space cascade). But this operation requires to enable row movement on table first.

But this was one time operation. To avoid this problem in future I've scheduled job to run ctx_ddl.optimize_index on regular basis. Now this table has grown a little, but is no longer growing so fast.

Foot note:

• It was tested on Oracle EE 10gR2 (10.2.0.3),
• I'm not sure if row movement is supported by Oracle for Context Index table DR$<index>$I. It worked for me and I did not experience any problems since turning it on.
  

Cheers, Paweł

I've been tagged 3 times; 8 things about me

Few busy days without any blog reading and after I've gone through my feeds it occurred that I've been tagged 3 times:

1. By Eddie Awad
2. By H.Tonguç YILMAZ
   
3. By Jorrit Nijssen (Jornica)
   

Thanks You for tagging.
Now I feel that I really need to write something about me:

1. I was born and grow up in Krosno in south-east Poland (see satellite picture),
2. I 15th when Communism fall down in Poland. At this moment Poland is member of Schengen Agreement, and I can travel around Europe without passport. Lot of changes in last 19 years,
3. I'm Thawte Web of Trust Notary,
   
4. In 1993 I took 2nd place in Nation Contest in Programming for College Students. Unfortunately I was 6 month too old to take part in international final :(
5. In 1990 I've sold my first program; small DB written in Clipper. I did not think that this program will be used for so many years. In 1999 I had to do some changes to support year 2000,
6. My first program was written in Basic and was run on PSP-80 (page in Polish)
   
7. I'm married and we have 2 daughters (6 and 2 years old),
8. My favourite TV show is Top Gear with Jeremy Clarkson , Richard Hammond and James May.
   

I will not invite anyone directly as I do not want to accused of spam. All my readers that have blogs are welcome to post 8 things about themselves.

Cheers,
Paweł