A small command to send your AWR html report (from sqlplus you run @?/rdbms/admin/awrrpt.sql).

Un pequeño comando unix para enviar el reporte HTML de AWR (que se obtiene con sqlplus si corres @?/rdbms/admin/awrrpt.sql).

[oracle@DBSERVER]$ cat << EOF | sendmail -t
> From: oracle@yourserver.yourcompany.com
> To: your.email.address@yourcompany.com
> Mime-Version: 1.0
> Content-type: text/html; charset="iso-8859-1"
> Subject: AWR report inline
> `cat awrrpt_1_10697_10698.html`
> EOF

Finalmente se encuentra en linea el nuevo diseño de 4TM.biz. También estrenamos nuevas secciones como Ask A DBA, donde puedes realizar consultas a un DBA Oracle.

Esperamos que te guste el nuevo diseño.

Finally the new website design is online at www.4tm.biz.

We not only changed the design, we are also preseting a new application called Ask A DBA where you can ask question to an Oracle DBA. It's mainly for Spanish use but we will answer in English too if you ask. Remember asktom.oracle.com already does this too!

These last days have surprised us with several vulnerabilities on Oracle products. Here's a link for an Oracle report from Feb 4th:

http://www.oracle.com/technology/deploy/security/alerts/alert-cve-2010-0073.html

Also it has been circulating a vulnerability that would allow any database user to access any filesystem file with oracle ownership. This is serious since the attacker could potentially delete database files, for example.

Workaround until next patch is easy, just remove execute privilege from PUBLIC to package DBMS_JVM_EXP_PERMS. Another one includes a DBA escalation (sysdba) via the DBMS_JAVA.SET_OUTPUT_TO_JAVA procedure. You can remove execution on the package, which is not the case for all of you that use the package for something else.

More details on http://secunia.com/advisories/38353/

Recently I posted an article about displaying a binary file from pl/sql directly to a browser. This is done with 10.2's feature EPG (Embedded PL/SQL Gateway). Yes, you can serve HTTP content (html, js, json, binary files like images and of course xml) directly from your Oracle database. I'll be posting a detailed article about how to configure it (if you need help now, check out this site). For now I'll post a very handy script I've just written.

Sometimes you don't know how EPG is configured and you have to go through the DBMS_EPG package to find that out. It's not a straight thing: you need to do some plsql programming... it would be nice a v$ view but it's not available as far as I know. So this script show come handy for those in need (I know I'll be one of them in the future ;) ).

set serveroutput on

declare
	/**
	*  Get all the database DADs and their configuration
	*  Written by jose.canciani
	*  Source: http://www.4tm.com.ar/4tmsite/wordpress/?p=31
	*/
	dads dbms_epg.varchar2_table;
	mappings dbms_epg.varchar2_table;
	attr_names dbms_epg.varchar2_table;
	attr_values dbms_epg.varchar2_table;
begin
	dbms_output.put_line('-Global Attributes:');
	dbms_epg.get_all_global_attributes(attr_names,attr_values);
	if attr_names.count > 0 then
		for i in 1..attr_names.count
		loop
			dbms_output.put_line('-  '||attr_names(i)||': "'||attr_values(i)||'"');
		end loop;
	else
		dbms_output.put_line('-  Not global attributes found.');
	end if;
	dbms_output.new_line;
	dbms_epg.get_dad_list(dads);
	for i in 1..dads.count
	loop
		dbms_output.put_line('-DAD: "'||dads(i)||'"');
		dbms_epg.get_all_dad_mappings(dads(i),mappings);
		dbms_output.put('-  Mappings:');
		for j in 1..mappings.count
		loop
			dbms_output.put(' "'||mappings(j)||'"');
		end loop;
		dbms_output.new_line;
		dbms_output.put('-  Authorized Users:');
		for j in (select username from dba_epg_dad_authorization where dad_name = dads(i))
		loop
			dbms_output.put(' "'||j.username||'"');
		end loop;
		dbms_output.new_line;
		dbms_output.put_line('-  Attributes:');
		dbms_epg.get_all_dad_attributes(dads(i),attr_names,attr_values);
		for j in 1..attr_names.count
		loop
			dbms_output.put_line('-    '||attr_names(j)||': "'||attr_values(j)||'"');
		end loop;
	end loop;
end;
/

Here's a quick output of the script (I only have one DAD configured and no global attributes):

-Global Attributes:
-  Not global attributes found.
-DAD: "ORAMON"
-  Mappings: "/oramon/*"
-  Authorized Users: "ORAMON"
-  Attributes:
-    database-username: "ORAMON"
-    default-page: "showpage?p_name=index.html"
-    error-style: "DebugStyle"
-    owa-debug-enable: "On"

That's all!

UPDATE 16-Jul-2009: apparently it's not working anymore! It took several weeks for facebook to fix it!

In order for Facebook to patch this, I'm writing this method that has become so popular these last days. You can see any album of any user in Facebook by following the next steps:

1) Get the person's ID: search him/her on facebook and look in the "View Frinds" link where you'll see something like this: http://www.facebook.com/friends/?id=1660869234. This number is the user's ID.

2) Go to http://developers.facebook.com/tools.php?api -> API Test Console

3) Choose "Facebook PHP Client" in Response Format.

4) Choose "fql.query" in Method.

5) Now you'll see a text area called "query": wrote there the following replacing xxxxxxxxxx with the contact's ID you find in the first step:

SELECT name, link FROM album WHERE owner=xxxxxxxxxx

6) Run the query to find all the contact's albums. Just take the URL of the album you want and paste it in your browser.

That's it! Let's hope Facebook fix it soon, but until then, enjoy looking at pictures your enemies shouldn't have uploaded ;)

I've been working in a simple reporting tool with PL/SQL Web Toolkit in Oracle 10g. Basically you just configure a DAD and the listener opens an HTTP port (8080 by default) and you just access your procedures and print html from PL/SQL. Pretty cool and no extra software or server configuration (no Apache or iAS or APEX needed).

For static content I needed to display image files, so I started looking a bit on Google and I end up with this configuration.

First, I created two tables: one to store my character lob content (CLOB) like html and js files, and another for my binary (BLOB content like images and other binary documents.

create table static_cfiles (
	file_name varchar2(50),
	file_type varchar2(20),
	contents clob,
	primary key (file_name)
);

create table static_bfiles (
	file_name varchar2(50),
	file_type varchar2(20),
	contents blob,
	primary key (file_name)
);

Then, I develop a procedure to read the tables based on the page name parameter. The CLOB is easy to read, you just assign it to a CLOB variable on PL/SQL and print it with HTP.P. But the BLOBS are a bit different. Here's a sample code for getting the blobs:

create or replace procedure showblob (p_name IN VARCHAR2)
as
	page_b_contents blob;
	intImgSize INTEGER;
	content_type static_bfiles.file_type%TYPE;
begin
	
	select file_type, contents
	into content_type, page_b_contents
	from static_bfiles
	where file_name = p_name;
		
	-- get image size
	intImgSize := dbms_lob.getlength(page_b_contents);
	
	-- print the header    	
	owa_util.mime_header(content_type, FALSE, NULL);
    	htp.p('Content-length: '|| intImgSize);
    	-- htp.p('Pragma: no-cache');
    	-- htp.p('Cache-Control: no-cache');
    	-- htp.p('Expires: Thu, 01 Jan 1970 12:00:00 GMT');
    	owa_util.http_header_close;

 	-- download BLOB
	wpg_docload.download_file(page_b_contents);
	
end;
/

The wpg_docload.download_file procedure does all the magic: it signals the PL/SQL Gateway that the blob is to be downloaded to the client's browser. No copy is needed!

I've seen examples that do a dbms_lob.copy to a PL/SQL variable previously to displaying the file: that's dangerous! Imagine you have a 4GB document... you'll be eating all the PGA!

The Content-Length is useful when you are sending a big file since your browser will be able to know how much is left and can draw you a progress bar or just give you the percent done. I've also put there other common headers, feel free to use them as you need.

For static images you better not use the No-Cache option... unless you are changing images from time to time :)

Jose.

When the Oracle Optimizer needs to generate an execution plan for a query it goes to through the already taken statistics for the tables and indexes involved.

Histograms are one of those statistics taken from tables. Basically they show how the values of a column are distributed. Oracle will use that to determine how many number of rows will be return by a certain filter on the column. If there are few rows, then it will choose to use an index (if any on that column). If there are a lot of rows then Oracle could choose to do a full table scan.

Doing these calculations requires resources (CPU) so we use Bind Variables to avoid re-parsing of the SQL. So once the plan has been calculated, Oracle will use it again and again until it is removed from the shared pool. The execution plan is calculated only once. But when using Bind Variables, what values are used to calculate the plan? Yes, the values of the first query it processes.

Do you start to see the problem here?

Lets say your table has a column called callerid. This column stores the caller phone number, and "0" if no callerid was registered. Now let's say you store millions of calls per month. Your table is now 60 million rows.

Each phone number does an average of 5 calls per month. Top call registered from one number is 2000. Those are very good numbers for creating a btree-index on "callerid".

But what happens with the "0" value? You'll have million of rows and the index is not a good choice (since it will be cheaper to do a full scan than to use the index with so many rows).

That's exactly the problem: if the first query Oracle recieves has the "0" value in the callerid column, then the stored plan will not use an index, and all future queries will be slow even with different values on callerid!!!

Let's get the histogram information:

select num_rows from dba_tables where owner = 'OWNER' and table_name = 'CALLS';

  NUM_ROWS
----------
  63627796


select HISTOGRAM from dba_tab_col_statistics where owner = 'OWNER' and table_name = 'CALLS' and column_name = 'CALLERID';

HISTOGRAM
---------------
HEIGHT BALANCED


select callerid, count(1) 
from calls
group by callerid 
order by 2 desc

CALLERID          COUNT(1)      
--------------------------
0                 3127825 
310596574            1808 
293584543            1405          
915089527            1229          
915144244             694 
...

And now let's take a look at the histograms and see how even is this data distributed on the table:

SQL> select ENDPOINT_NUMBER, ENDPOINT_VALUE
  2  from dba_histograms
  3  where table_name = 'CALLS' and column_name = 'CALLERID' and owner = 'OWNER'
  4  order by 1;
 
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              8              0
              9      269304330
             10      283241184
             11      289850129
             12      295923724
             13      296092273
             14      296248610
             15      296416759
             16      296585656
             17      296757192
...

As you can see the table is HEIGHT BALANCED according to the statistics taken previously, that is their values are evenly distributed, except for the first 8 buckets of the histogram. Each bucket in the histogram represents (63627796/254) ~= 250503 rows. So in our case the "0" value is repeated (8*250503)~= 2 million rows.

Remember we said that the Optimizer builds the execution path based on the values of the first query it receives? What happens if the first query it receives involves a CALLERID = 0?

select * 
from calls 
where callerid = :b1 

:b1 bind variable is 0, so oracle receives the query and determine that it does not need to use the index on callerid. This is the best thing to do with a "0" value, but next time the application will do the same select with callerid = 269304330 and what will happen? Since the execution plan is already on the Shared Pool, it will be reused, WITHOUT THE INDEX!!!!

This is called "Variable Peeking", and is a know problem. It can happen at any time. Imagine your gather_stats_job run on the CALLS table at Sunday night. The execution plan becomes invalidated. On Monday a user opens the application and do the query with callerid = 0 and BAM! Your new execution plan is created without getting the index.

For this situation you can do several options. The best thing would be to HINT your SQL to use the right index (or not to use it when the value is 0, if you can know it before running it).

But sometimes you can't touch the application code. In those cases you can create a Store Outline for the query with the DBMS_OUTLINE package. See Metalink note 463288.1 for more details.

Jose

DBAs usually don't have access to schema passwords. We can only see an encrypted version and that's the way it should be. But from time to time we are asked to create a Database Link. Database Links should never be public because other users in the database can also have access to it, and by extension, to all objects in the remote database.

Database Links cannot be created in other user's schema, even if you are a DBA. For example, a "create database link [schemauser].[dblink_name] ...." will give you an error. So usually what DBAs do is finding out the schema password (which in most cases should not have) or worst, resetting it. Resetting the password is not a bad idea, since you can return to the old password if you save the hash of the original value and then issue an "alter user [schemauser] identified by values '[hash_value]';". But this has a problem: when you are in a live environment you cannot change a password without interrupting sessions, and this is unacceptable even if it is for only a few seconds.

In this cases I like a cleaner approach: create a package in the schema user that will later create the database link using dynamic SQL. Let's go to the example.

(use Sqlplus, or adapt it for your client)

First let's grant a create database link (a not very common privilege that is not usually granted to typical users). Then we create the procedure that will do the "magic" (replace OWNER_NAME for the future owner of the dblink):

grant create database link to OWNER_NAME;  

create or replace procedure OWNER_NAME.e(p_sql varchar2)
is
begin
    execute immediate p_sql;
end;
/  

Now lets create the database link. Replace LINK_NAME, USER_NAME, USER_PASSWORD and TNS_ENTRY for the ones that apply in your case:

exec OWNER_NAME.e('CREATE DATABASE LINK "LINK_NAME"  CONNECT TO  "USER_NAME" IDENTIFIED BY "USER_PASSWORD"  USING ''TNS_ENTRY''');

That should have created the DB link. We can create a second proc to validate if it's working:

reate or replace procedure OWNER_NAME.ee is
v_pp varchar2(30);
begin
   select 1 into v_pp from dual@link_name;
   dbms_output.put_line('pp = '||v_pp);
end;
/
 
set serveroutput on
 
exec OWNER_NAME.ee

You should see "pp=1" when you run the "ee" proc. If you have any error, then work it out! Usually TNS errors are the typical ones (verify you have the appropriate tns entry in your server's tnsnames.ora file). Finally let's clean all:

revoke create database link from OWNER_NAME;

drop procedure OWNER_NAME.e;

drop procedure OWNER_NAME.ee;

That's all!

Being a DBA, this is a recurrent issue I have every several months with developers: Why my "not in" query is not returning any rows?

One query, two tables:

select count(1) 
from first_table 
where my_row NOT IN (select my_row from second_table);

There's two downsides in using an inner query (subquery) with a NOT IN. The first one is performance: "not in" does not scale well when the second_table is big. You are forcing Oracle to do a full scan on the table, and then compare my_row with the list of values. This is not good.

The second issue is NULL. If my_row is Nullable, and in fact there are null values in some rows of the second_table, then the NOT IN will always give you "TRUE". This is because a NULL value compared with a NOT NULL value always returns FALSE, and since you are using NOT, then FALSE becames TRUE and even if there is no match you will return the row at the end. A quick workaround is to add "where my_row is not null" in the subquery, but the first issue will still be there.

Both issues are solved using NOT EXISTS:

select count(1) 
from first_table t1 
where NOT EXISTS (select 1 
                  from second_table t2 
                  where t2.my_row = t1.my_row);

If you have an index on my_row this will scale perfectly no matter how big is your table.

Just a quick tip: you are out of your home, need to access your mac desktop, you can login through ssh but Screen Sharing (VNC) is not open.

Simply login as root or admin user and run:

echo -n enabled > /Library/Preferences/com.apple.ScreenSharing.launchd

Now, what if you are connecting from windows with a low bandwidth connection? Here are some tips to run before connecting:

1. Enable dock auto hide (useful when you have dynamic icons like CPU usage or bittorrent metrics):

defaults write  com.apple.dock autohide 1 

2. Disable desktop background, set a simple plain color

Not sure how to do this last one over a ssh shell. Auto hide should be ok, maybe you'll need to kill the dock process to use the new value... Any tips you would like to add will be appreciate, at least until Apple release a viewer for windows that let us tweak connection settings for low bandwith conditions.

Cheers!