WordPress 4.4.2 has been released.
You can see the list of fixes here. Three of the five installations I maintain had already updated by the time I got to them, so by the time you read this you will probably already have it too.
If you don’t want to read the story, the summary for this article is:
If you create bitmap join indexes on a partitioned table and you use partition exchanges to load data into the table then make sure you create the bitmap join indexes on the loading tables in exactly the same order as you created them on the partitioned table or the exchange will fail with the (truthful not quite complete) error: ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION.
My story starts with this OTN posting from John Hall where he found after a year of successful batch loading one of his partition exchanges was raising error 14098. After an exchange of ideas, user rp0428 came up with a query against sys.jijoin$ (one of the tables behind bitmap join indexes) that allowed John Hall to see that the indexes on the exchange table had been created in a different order from that of the partitioned table. I did a quick test to see if this might be relevant (it shouldn’t be, it isn’t with “normal” indexes or function-based indexes, or virtual columns) and didn’t manage to reproduce the problem with two dimension tables and two bitmap join indexes.
Fortunately John didn’t take my word for it and tested the idea on a clone of the production system – and found that the order of creation did matter. His system, however, had 9 dimension tables and 33 bitmap join indexes – which shouldn’t have made any difference in principle, but maybe it was something to do with having several indexes on the same table, maybe it was something to do with have far more tables or far more indexes than I had. So I built a larger test case with 6 dimension tables and six indexes per table – and reproduced the problem.
Then I started cutting back to see where the problem appeared, and found that all it took was one dimension with two indexes, or two dimensions with one index each – whatever I had done in my “quick test” I had clearly done it too quickly and done something wrong. (Unfortunately I had overwritten most of the code from the original quick test while building the larger test, so I couldn’t go back and see where the error was.)
Here, then, is the minimal test case that I finally ran to demonstrate that switching the order of index creation on the exchange table causes the exchange to fail:
drop table pt_range purge; drop table t1 purge; drop table dim_1 purge; drop table dim_2 purge; prompt ================= prompt Partitioned table prompt ================= create table pt_range ( id, grp1, grp2, padding ) nologging partition by range(id) ( partition p2001 values less than (2001), partition p4001 values less than (4001), partition p6001 values less than (6001), partition p8001 values less than (8001) ) as select rownum id, trunc(rownum/100) grp1, trunc(rownum/100) grp2, rpad('x',100) padding from all_objects where rownum <= 8000 ; prompt ================================================ prompt Exchange table - loaded to match partition p8001 prompt ================================================ alter table pt_range add constraint pt_pk primary key (id) using index local; create table t1 ( id, grp1, grp2, padding ) as select rownum + 6000 id, trunc(rownum/100) grp1, trunc(rownum/100) grp2, rpad('x',100) padding from all_objects where rownum <= 2000 ; alter table t1 add constraint t1_pk primary key (id); execute dbms_stats.gather_table_stats(user,'pt_range') execute dbms_stats.gather_table_stats(user,'t1') prompt ================ prompt dimension tables prompt ================ create table dim_1 as select distinct grp1, cast('A'||grp1 as varchar2(3)) agrp1, cast('B'||grp1 as varchar2(3)) bgrp1 from t1 ; create table dim_2 as select * from dim_1; prompt =============================== prompt Primary keys required for BMJIs prompt =============================== alter table dim_1 add constraint d1_pk primary key (grp1); alter table dim_2 add constraint d2_pk primary key (grp1); execute dbms_stats.gather_table_stats(user,'dim_1') execute dbms_stats.gather_table_stats(user,'dim_2') prompt ============================ prompt Creating bitmap join indexes prompt ============================ create bitmap index pt_1a on pt_range(d1.agrp1) from pt_range pt, dim_1 d1 where d1.grp1 = pt.grp1 local ; create bitmap index pt_2a on pt_range(d2.agrp1) from pt_range pt, dim_2 d2 where d2.grp1 = pt.grp2 local ; prompt ==================================================== prompt Pick your index creation order on the exchange table prompt ==================================================== create bitmap index t1_1a on t1(d1.agrp1) from t1, dim_1 d1 where d1.grp1 = t1.grp1 ; create bitmap index t1_2a on t1(d2.agrp1) from t1, dim_2 d2 where d2.grp1 = t1.grp2 ; -- create bitmap index t1_1a on t1(d1.agrp1) from t1, dim_1 d1 where d1.grp1 = t1.grp1 ; prompt ================== prompt Exchanging (maybe) prompt ================== alter table pt_range exchange partition p8001 with table t1 including indexes without validation ;
I’ve got the same create statement twice for one of the bitmap join indexes – as it stands the indexes will be created in the right order and the exchange will work; if you comment out the first t1_1a create and uncomment the second the exchange will fail. (If you comment out the ‘including indexes’ then the exchange will succeed irrespective of the order of index creation, but that rather defeats the point of being able to exchange partitions.)
I’ve reproduced the problem in 18.104.22.168, 22.214.171.124 and 10.2.0.5
Footnote: running an extended trace didn’t help me work out how Oracle is detecting the mismatch, presumably it’s something that gets into the dictionary cache in a general “load the index definition” step; but it did show me that (in the “without validation” case) the code seems to check the correctness of the exchange table’s primary key data BEFORE checking whether the indexes match properly.
I saw this on an ideas forum today
and whilst most people would take care of this with a source code control system, its also true that people might do several compilations / tests with their PLSQL source before checking it in officially to their source repository.
So the idea has some merit… and maybe we can roll our own without too much fuss. I make no claims that this is a ‘complete’ solution, but it should get you going
SQL> set timing off SQL> drop trigger plsql_trigger; Trigger dropped. SQL> SQL> drop table plsql_params purge; Table dropped. SQL> drop table plsql_history cascade constraints purge; Table dropped. SQL> drop table plsql_history_source cascade constraints purge; Table dropped. SQL> drop table plsql_log cascade constraints purge; Table dropped. SQL> SQL> SQL> create table plsql_params 2 as select 3 versions_kept from dual; Table created. SQL> SQL> create table plsql_history ( 2 TSTAMP TIMESTAMP 3 ,OWNER VARCHAR2(128) 4 ,NAME VARCHAR2(128) 5 ,TYPE VARCHAR2(12) 6 ,constraint plsql_history_pk primary key ( tstamp,owner,name,type) 7 ) 8 organization index; Table created. SQL> SQL> create table plsql_history_source ( 2 TSTAMP TIMESTAMP 3 ,OWNER VARCHAR2(128) 4 ,NAME VARCHAR2(128) 5 ,TYPE VARCHAR2(12) 6 ,LINE NUMBER 7 ,TEXT VARCHAR2(4000) 8 ,constraint plsql_history_source_pk primary key ( tstamp,owner,name,type,line) 9 ,constraint plsql_history_source_fk foreign key ( tstamp,owner,name,type) references plsql_history ( tstamp,owner,name,type ) 10 ); Table created. SQL> SQL> create table plsql_log 2 ( 3 TSTAMP TIMESTAMP 4 ,MSG varchar2(1000) 5 ); Table created. SQL> SQL> SQL> create or replace trigger plsql_trigger 2 before create on SCHEMA 3 declare 4 l_owner varchar2(128) := ora_dict_obj_owner; 5 l_name varchar2(128) := ora_dict_obj_name; 6 l_type varchar2(128) := ora_dict_obj_type; 7 l_archived timestamp := systimestamp; 8 l_tstamp_to_clear timestamp; 9 10 procedure logger(m varchar2) is 11 pragma autonomous_transaction; 12 begin 13 insert into plsql_log values (systimestamp,m); 14 commit; 15 end; 16 begin 17 if l_type in ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') then 18 insert into plsql_history values (l_archived, l_owner, l_name, l_type); 19 20 insert into plsql_history_source 21 select l_archived, owner, name, type, line, text 22 from dba_source 23 where owner = l_owner 24 and name = l_name 25 and type = l_type; 26 27 logger('Archived '||l_type||'-'||l_owner||'.'||l_name); 28 29 select max(case when tot > versions_kept and seq = tot - versions_kept then tstamp end) 30 into l_tstamp_to_clear 31 from plsql_params, 32 ( select ph.*, 33 row_number() over ( order by tstamp ) as seq, 34 count(*) over () as tot 35 from plsql_history ph 36 ) 37 where owner = l_owner 38 and name = l_name 39 and type = l_type; 40 41 if l_tstamp_to_clear is not null then 42 logger('Clearance timestamp for '||l_type||'-'||l_owner||'.'||l_name||' is '||l_tstamp_to_clear); 43 44 delete from plsql_history_source where tstamp <= l_tstamp_to_clear; 45 delete from plsql_history where tstamp <= l_tstamp_to_clear; 46 logger('Cleared '||sql%rowcount||' versions for '||l_type||'-'||l_owner||'.'||l_name); 47 end if; 48 end if; 49 end; 50 / Trigger created. SQL> SQL> create or replace procedure P_TEST is 2 begin 3 null; -- version 1 4 end; 5 / Procedure created. SQL> SQL> create or replace procedure P_TEST is 2 begin 3 null; -- version 2 4 end; 5 / Procedure created. SQL> SQL> SQL> create or replace procedure P_TEST is 2 begin 3 null; -- version 3 4 end; 5 / Procedure created. SQL> SQL> SQL> create or replace procedure P_TEST is 2 begin 3 null; -- version 4 4 end; 5 / Procedure created. SQL> SQL> SQL> create or replace procedure P_TEST is 2 begin 3 null; -- version 5 4 end; 5 / Procedure created. SQL> select * from plsql_history; TSTAMP OWNER NAME TYPE ---------------------------------- ------------ ------------ --------------- 02-FEB-16 126.96.36.1990000 PM MCDONAC P_TEST PROCEDURE 02-FEB-16 188.8.131.526000 PM MCDONAC P_TEST PROCEDURE 02-FEB-16 184.108.40.2069000 PM MCDONAC P_TEST PROCEDURE SQL> SQL> select tstamp, text from plsql_history_source; TSTAMP TEXT ---------------------------------- ------------------------------------------------------------ 02-FEB-16 220.127.116.110000 PM procedure P_TEST is 02-FEB-16 18.104.22.1680000 PM begin 02-FEB-16 22.214.171.1240000 PM null; -- version 2 02-FEB-16 126.96.36.1990000 PM end; 02-FEB-16 188.8.131.526000 PM procedure P_TEST is 02-FEB-16 184.108.40.2066000 PM begin 02-FEB-16 220.127.116.116000 PM null; -- version 3 02-FEB-16 18.104.22.1686000 PM end; 02-FEB-16 22.214.171.1249000 PM procedure P_TEST is 02-FEB-16 126.96.36.1999000 PM begin 02-FEB-16 188.8.131.529000 PM null; -- version 4 02-FEB-16 184.108.40.2069000 PM end; 12 rows selected. SQL> SQL> select * from plsql_log; TSTAMP MSG ---------------------------------- -------------------------------------------------------------------------------- 02-FEB-16 220.127.116.11000 PM Archived PROCEDURE-MCDONAC.P_TEST 02-FEB-16 18.104.22.168000 PM Archived PROCEDURE-MCDONAC.P_TEST 02-FEB-16 22.214.171.1241000 PM Archived PROCEDURE-MCDONAC.P_TEST 02-FEB-16 126.96.36.1997000 PM Archived PROCEDURE-MCDONAC.P_TEST 02-FEB-16 188.8.131.527000 PM Clearance timestamp for PROCEDURE-MCDONAC.P_TEST is 02-FEB-16 184.108.40.206000 PM 02-FEB-16 220.127.116.113000 PM Cleared 1 versions for PROCEDURE-MCDONAC.P_TEST 02-FEB-16 18.104.22.1680000 PM Archived PROCEDURE-MCDONAC.P_TEST 02-FEB-16 22.214.171.1240000 PM Clearance timestamp for PROCEDURE-MCDONAC.P_TEST is 02-FEB-16 126.96.36.199000 PM 02-FEB-16 188.8.131.520000 PM Cleared 1 versions for PROCEDURE-MCDONAC.P_TEST 9 rows selected.
Today marks the 10th anniversary of Lex de Haan passing away. Although 10 years is a long time, I think about my dear friend Lex at least once a week. Lex assisted me adapting to new teaching skills when I progressed to blindness. It was Lex his idea to use colored magnets on my classroom […]
It’s interesting that we still see lots of code out “in the wild” that leans itself to taking forever. Now I’m not talking here about poorly performing SQL etc…because (at least theoretically) no matter how bad the SQL is, presumably it will finish one day, even if that day is years into the future. What I’m referring to here is the default mechanism for locking in Oracle.
If you try access a row that is locked by someone else, you will wait. Don’t get me wrong … that’s a very good thing. The alternative – of letting two people change the same data at the same time is equivalent to saying “I dont care about my data at all”.
And how long will we wait ? Well… we’ll wait forever. We will never stop waiting, until that lock has been released.
I don’t know about you … but for me… forever is long time. A really long time. I cannot recall ever seeing any kind of Service Level Agreement for an application’s response times as stating “Yup…forever is cool. Take as long as you want”
So perhaps consider that when you’re building your applications in Oracle. Yes, the default is to wait forever, but a little code (which has been available since version 9) can really go a long way.
Rather than just coding:
select * from T where …for update
delete from T where …
You can opt to wait for a nominated amount of time to attempt to get that lock (and then report something sensible back to the user).
SQL> select * from T for update wait 60; (60 seconds pass) select * from T for update wait 60 * ERROR at line 1: ORA-30006: resource busy; acquire with WAIT timeout expired
Note that the error code is not the same as the error you could get with a NOWAIT clause, which is:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
But you don’t even need to stop there. Why not catch that error with a SERVERERROR trigger – and then do some session level probing to get more information. You could even empower the users to take their own action :-)
SQL> create or replace trigger lock_expiry 2 after servererror on database 3 declare 4 l_err varchar2(4000); 5 begin 6 if ( is_servererror(30006) ) then 7 for i in ( 8 select /*+ leading(lk sess) */ sess.sid, sess.username, sess.module 9 from v$lock lk, 10 v$session sess, 11 v$session s 12 where lk.id1 = s.row_wait_obj# 13 and lk.type = 'TM' 14 and lk.sid = sess.sid 15 and s.sid = sys_context('USERENV','SID') 16 ) 17 loop 18 l_err := l_err || 'Session '||i.sid||' who is '||i.username||' using '||i.module || chr(10); 19 end loop; 20 raise_application_error(-20000,l_err); 21 end if; 22 end; 23 / Trigger created. SQL> select * from t for update wait 60; * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-20000: Session 717 who is MCDONAC using SQL*Plus ORA-06512: at line 18 ORA-30006: resource busy; acquire with WAIT timeout expired
Just for completeness, note that if you having locking issue, V$SESSION gives you direct access to find out who is blocking you.
SQL> select sid, last_call_et, 2 nvl2(lockwait,'BLOCKED',status) status, 3 blocking_session 4 from v$session s; SID LAST_CALL_ET STATUS BLOCKING_SESSION ---------- ------------ ---------- ---------------- 39 376 INACTIVE 40 412 INACTIVE 41 412 INACTIVE 44 421 INACTIVE 46 340 BLOCKED 39 49 4 ACTIVE 50 453 INACTIVE 51 453 INACTIVE
Welcome to my first in a series of posts in response to Tim Ford's #EntryLevel Challenge, which I learned about indirectly from reading a post by Steve Hood. SQL is what I'm good at, so I will be focusing on SQL and T-SQL in this series.
To learn or practice with SQL requires two things: A database engine, and some example data. Microsoft SQL Server Express is a freely available and easy-to-install engine. Microsoft's Adventure Works example database provides a good set of tables with data designed to show off all that SQL is capable of doing. Put SQL Server Express together with Adventure Works, and you have a nice platform on which to learn and practice the SQL language.
SQL Server Express 2014 is the current version of Microsoft's free database engine. There are five editions of Express, and you can learn about them on the MSDN download page in Figure 1:
The edition I recommend is termed as "Express with Tools". Begin the download process from the page in Figure 1. You'll need to fill out the form in Figure 2. You'll also need a Microsoft Live account.
Figure 1. Choose "Express with Tools"
Figure 2. Fill out the form
Download the installer. Run it. Take all the defaults, and just click Next, Next, Next in the usual manner. The defaults are suitable for a learning and practice environment.
When the install is over, find and run the program named SQL Server 2014 Management Studio. Search on "Management Studio" from the Start menu, and you should find it.
Login as shown in Figure 3. Select the SQLEXPRESS server name from the dropdown menu in the dialog. Management Studio will open. Click the plus sign (+) to drill into your databases. You should see just the System databases as shown in Figure 4.
Figure 3. Logging in the first time
Figure 4. There's no example data yet
There's no adventure yet. But we're working on it. Installing Adventure Works is next.
Microsoft makes the Adventure Works database available from their SQL Server Product Examples page at msftdbprodsamples.codeplex.com. You can download a set of scripts to create the database, but I prefer to grab the full backup and restore from that.
Choose the Adventure Works year as shown in Figure 5. I choose 2014 to go along my 2014 install of Express. Then choose the option to download a database backup as in Figure 6. Finally, it's worth grabbing the Readme file shown later in Figure 7. The readme is at the very bottom of the download page.
Figure 5. Choose the year of Adventure Works
Figure 6. Choose the database backup
Here is where things get a tad tricky. You want to unzip the downloaded archive and copy the .BAK file that's within it to the Backup folder under your SQL Server Express install. Figure 8 shows the Backup folder and its location on my own system.
Be aware that the Readme file from Figure 7 provides the path for the non-Express edition of SQL Server. Your path will be somewhat different by descending through MSSQL12.SQLEXPRESS.
Figure 7. Grab the Rreadme file
Figure 8. Put the .BAK file into your Backup folder
The reason for moving the .BAK file is to avoid permissions issues during the restore. SQL Server has access to its own folders, but not to other folders on your system outside the install. So copy the .BAK file to your Backup folder, and save yourself the trouble of sorting out a file access error during the restore operation.
Note: You might receive a permissions error when navigating to your Backup folder in order to make the copy. Just click Continue when that happens to give yourself access to the folder.
Now run a RESTORE DATABASE statement, and it's "job done". Following is a script showing the statement that I ran on my own system. Find the command in Figure 7's Readme file. It'll be in the section on restoring from a backup. Then modify the paths to be correct for your system.
USE [master] RESTORE DATABASE AdventureWorks2014 FROM disk= 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\AdventureWorks2014.bak' WITH MOVE 'AdventureWorks2014_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorks2014.mdf', MOVE 'AdventureWorks2014_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorks2014.ldf' ,REPLACE
Be sure the FROM path points to your .BAK file. The other two paths should point to your DATA directory underneath your SQL Server Express install.
The change you are most likely to need to make—if you copy and paste from the Readme file in Figure 7—is to change "MSSQL12.MSSQLSERVER" to "MSSQL12.SQLEXPRESS" in all three paths. That's the one change I made before executing the statement.
Now it's time to execute the RESTORE DATABASE statement. Click Databases in the tree at the left pane of the management Studio window. Then click the New Query toolbar button to open a query window. Paste the USE and RESTORE DATABASES commands into the query box as shown in Figure 9, and press the Execute button in the toolbar to create the example Adventure Works database shown in Figure 10.
Figure 9. Restoring from the backup
Figure 10. Adventure Works is available
The Adventure Works database will be restored from the .BAK file and made available in your environment. Right-click Databases in the left pane. Select Refresh. You should see the Adventure Works database. Expand that entry and you can drill down to see what tables, columns, indexes, views, and other structures are available.
Congratulations! You've just installed one of the best environments for learning about the SQL language and how it can be used to query and analyze data. It's an environment shared by some of the best and most helpful database professionals whom it is my pleasure to know. Look for more posts from me this year on SQL.
And get to know the community! A good way to do that is by attending one of the many SQL Saturday events held locally around the planet.
Welcome! To SQL Server.
YesSQL Summit 2016 sponsored by O'Reilly and Axxana was held by the Northern California Oracle Users Group on January 26–28 at the Oracle conference center in Redwood City, California in conjunction with BIWA Summit 2016 and Spatial Summit 2016. The grand raffle prize sponsored by O'Reilly was a full pass to Strata + Hadoop World on March 28–31 in San Jose, California. Save 20% on Strata + Hadoop World conference passes with discount code UGNOCOUG. YesSQL Summit will return to the Oracle conference center on January 31, 2017.(read more)
RMOUG volunteers compile notifications of webinars, meetings, and meetups from the internet and post them here for everyone to use.
The information technology (IT) industry is always evolving and therefore always changing.
Free webinars, even if they seem too commercial at times, always have at least one solid nugget of solid information that can make the difference in a professional’s life and career.
You never know when the need for new information that nobody else knows is going to come in handy.
Stay a step ahead…
The latest video on my YouTube Channel is a run through of using the Database as a Service (DBaaS) offering on Oracle Cloud.
There have been a few minor changes in the interface since I last ran through capturing images, so the related article has been brought up to date.
I used my dad for the cameo in this video. Hopefully this will help him get a little more recognition, as he’s pretty much a nobody on the Oracle scene at the moment. With your help this could change!
Update: Almost as soon as I released this blog post the footage was out of date as Oracle released some minor changes to the interface. I rerecorded the video and re-uploaded it, so it is up to date as of now. All links from my website and this blog post point to the new video. If you have read this post via an RSS reader, you may still be seeing the old version of the post, and as a result see the link to the video as broken. But in that case, you won’t be able to read this either.