Oakies Blog Aggregator

Here's part two!

For your entertainment – there’s nothing up my sleeves, this was a simple cut-n-paste after real-time typing with no tricks:

20:39:51 SQL> create table t1 (t1 timestamp);

Table created.

20:39:55 SQL> insert into t1 values(systimestamp);

1 row created.

20:39:59 SQL> select t1 - systimestamp  from t1;

T1-SYSTIMESTAMP
---------------------------------------------------------------------------
+000000000 04:59:50.680620

1 row selected.

20:40:08 SQL>

My laptop runs Oracle so quickly that it took only 4 seconds for 5 hours to elapse !
11.2.0.3 on 64-bit Linux – the client is running with TZ=EST5EDT, while the server is running UK Time (currently BST (GMT+1))

Comments available on MOS: 340512.1 Timestamps & time zones – Frequently Asked Questions
Another MOS note, thanks to Jure Bratina in the comments: 227334.1 – “Dates & Calendars – Frequently Asked Questions” in the question

Update:

As Niall quotes in the comments: “times are difficult”.
Oracle supplies three timestamps: systimestamp, localtimestamp, and current_timestamp. (For reasons of consistency, only one of uses an underscore ;) )
Oracle also supplies three timestamp types: timestamp, timestamp with time zone, and timestamp with local time zone.
Oracle also supplies two timezone calls: dbtimezone, and sessiontimezone

If you need to figure out all the details of how these things hang together, I think you need to set your machine timezone to something that isn’t UTC (or GMT as I still tend to call it), then use two separate machines as clients, with their timezones set to two other timezones (again avoiding UTC).

I’ve done a few experiments but without being so rigorous in my settings – my machine was running on GMT, but I opened a (UNIX) session and set the session time zone to EST5EDT to start the database, while running other (UNIX) session with different TZ settings. The reason I should have restarted the machine in a different timezone is that Oracle “normalises” some timestamps to UTC – which means there are cases when I can’t be certain whether the stored value is in UTC because it has been normalised or because it simply was the actual machine time.

So here’s a little experiment (11.2.0.2, instance started in EST5EDT, unix session running in UTC, connecting across the network to the server).

select
        current_timestamp,
        localtimestamp,
        systimestamp
from
        dual
;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
17-APR-13 11.37.10.870658 AM +01:00
17-APR-13 11.37.10.870658 AM
17-APR-13 06.37.10.870554 AM -04:00

Notes:
systimestamp reflects the instance timestamp – which is 5 hours earlier than the session timestamp.
systimestamp returns a timestamp with time zone, not just a timestamp
localtimestamp and current_timestamp show the client time, but localtimestamp doesn’t show the timezone, current_timestamp does (the +1:00 appears because Daylight Saving Time (British Summer Time) is active so my session is one hour ahead of UTC, while the database is 4 hours behind.)

Another quick test:

create table t1 (
        t0              timestamp,
        tz              timestamp with time zone,
        tl              timestamp with local time zone,
        ts_type         varchar2(20)
)
;

insert into t1 values(
        systimestamp, systimestamp, systimestamp,
        'sys Timestamp'
);

commit;

select * from t1;

T0
---------------------------------------------------------------------------
TZ
---------------------------------------------------------------------------
TL                                                                          TS_TYPE
--------------------------------------------------------------------------- --------------------
17-APR-13 06.44.04.353489 AM
17-APR-13 06.44.04.353489 AM -04:00
17-APR-13 11.44.04.353489 AM                                                sys Timestamp

select
        dump(t0,16),
        dump(tz,16),
        dump(tl,16),
        ts_type
from
        t1
;
DUMP(T0,16)
------------------------------------------------------------------------------------------------------------------------
DUMP(TZ,16)
------------------------------------------------------------------------------------------------------------------------
DUMP(TL,16)
------------------------------------------------------------------------------------------------------------------------
TS_TYPE
--------------------
Typ=180 Len=11: 78,71,4,11,7,2d,5,15,11,d0,68
Typ=181 Len=13: 78,71,4,11,b,2d,5,15,11,d0,68,10,3c
Typ=231 Len=11: 78,71,4,11,b,2d,5,15,11,d0,68
sys Timestamp

Notes:
T0 – the timestamp column, has the instance timestamp in it – but doesn’t have any timezone information stored; the raw dump show the value 6:44:04 (7, 2d, 5 – convert from hex and substract one). Anyone on ANY timezone will see their output showing 6:44:04 if they select this column.

TZ – the timestamp with time zone column, has the instance timestamp, but has stored it as (b, 2d,5 – 11:44:04) with time zone information (10,3c) that allows the session to know what “global” moment the information really represents and the location (or, rather, time zone) where is was entered.

TL – the timestamp with local time zone, has the instance timestamp, but has stored it as (b, 2d, 5 – 11:44:04) with NO timezone information. So the output when you query this column is adjusted to suit the local timestamp. It’s the right “global” moment, and it displays as the relevant local time. But, as a penalty, it’s lost the information about where (in which time zone) it was entered.

I think that examination of the content of the raw dumps of the three different types may help you understand why you need to store timestamps in a column type that includes a time zone – if you don’t then you lose some information, and time-based arithmetic will give you some surprises if your application crosses timezones.

Next Issue:

Indexing time (though the link in the comments below to Tony Hasler’s blog probably gives you all the answers you need), and an Oracle design error that I’ve visited before.

Assuming my current WordPress prefix was “wp_” and I wanted to change it to “banana_”, then I would do the following…

Take a backup of your database and file system. Remember, if you screw up and trash your blog you will thank yourself for the extra time you spent doing this!

Amend the “$table_prefix” setting in the “wp-config.php” file.

$table_prefix = 'banana_'; // Only numbers, letters, and underscores please!

Rename all the tables, swapping the “wp_” prefix with “banana_”. For example, the following statement would rename the “wp_comments” table to “banana_comments”.

RENAME TABLE wp_comments TO banana_comments

Perform the following updates.

UPDATE banana_options SET option_name = 'banana_user_roles' WHERE option_name = 'wp_user_roles'
UPDATE banana_usermeta SET meta_key = 'banana_capabilities' WHERE meta_key = 'wp_capabilities'
UPDATE banana_usermeta SET meta_key = 'banana_user_level' WHERE meta_key = 'wp_user_level'
UPDATE banana_usermeta SET meta_key = 'banana_autosave_draft_ids' WHERE meta_key = 'wp_autosave_draft_ids'

If you don’t do these updates, the blog will work, but when you try to access the admin site you will be greeted with a message saying,

You do not have sufficient permissions to access this page.

That’s it!

Cheers

Tim…

---

How to Change the WordPress Table Prefix was first posted on April 14, 2013 at 10:36 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Here’s a deadlock graph that might cause a little confusion:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
 Resource Name          process session holds waits  process session holds waits
 TX-001a0015-00014787        34      90     X             32       3           S
 TX-00190008-0000601b        32       3     X             34      90           S

 session 90: DID 0001-0022-00000327      session 3: DID 0001-0020-000009E9
 session 3: DID 0001-0020-000009E9       session 90: DID 0001-0022-00000327

 Rows waited on:
  Session 90: obj - rowid = 00030CE5 - AAAwzlAAGAABDiuAAA
  (dictionary objn - 199909, file - 6, block - 276654, slot - 0)
  Session 3: obj - rowid = 00030CE5 - AAAwzlAAGAABp8gAAA
  (dictionary objn - 199909, file - 6, block - 433952, slot - 0)

Both sessions are holding X and waiting S on a TX lock. There are several well-known reasons why you might see a share (mode 4) lock on a transaction table slot: collisions on bitmap indexes, unique indexes, index organized tables, or referential integrity checks are the commonest “external” examples, and problems with interested transaction lists (ITLs) or freelists are the common “internal” ones – so the presence of the waits in share mode shouldn’t, of themselves, a source of confusion.

The confusion is in the reported rowids. If you try to interpret them as real rowids you may be unlucky and discover that they seem to be related to the SQL reported for the deadlocked sessions when really the information they hold is garbage. (Just to avoid confusion, let me make it clear that there are cases where the rowids reported definitely WILL be garbage; on the other hand, there may be some scenarios where the rowids are relevant – although I haven’t done any exhaustive check to see if there really are such scenarios.)

When I see a deadlock graph on transaction locks and the waits are for S mode I tend to assume that the information about the rows waited on is probably misleading; when the slot number for the rowid is zero this increases my confidence that the rowid is rubbish. (Zero is a legal value for a rowid slot, of course, so a zero doesn’t prove that the rowid is rubbish, it’s just a coincidence that allows me to continue following a hypothesis.)

The problem is that Oracle doesn’t waste resources tidying up after itself, and in the case of deadlock graphs this laziness shows up in the rowids reported. The trace is simply reporting whatever happens to be in the row_wait_obj#, row_wait_file#, row_wait_block# and row_wait_row# columns of v$session; and if the waiting process hasn’t updated these columns with current row information you could be looking at the details of the last row (or block) that the session waited for. Here’s the description of a test to demonstrate the behaviour:

create table t1 (n1 number, n2 number);
insert into t1 values(1,1);
create unique index t1_i1 on t1(n1);
create unique index t1_i2 on t1(n2);

session 1:	insert into t1 values(2,11);
session 2:	insert into t1 values(3,21);
session 1:	insert into t1 values(4,21);
session 2:	insert into t1 values(5,11);

With this table, and sequence of events following it, session 1 raised a deadlock error, and dumped the following trace:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-000a0021-0000c4aa        16      93     X             13      90           S
TX-00020020-0000fdb9        13      90     X             16      93           S
session 93: DID 0001-0010-00000057	session 90: DID 0001-000D-000000F2
session 90: DID 0001-000D-000000F2	session 93: DID 0001-0010-00000057
Rows waited on:
Session 90: obj - rowid = 00000009 - AAAAAJAABAAAQJcAAA
  (dictionary objn - 9, file - 1, block - 66140, slot - 0)
Session 93: obj - rowid = 0002E7DC - AAAufaAAFAAAAAJAAA
  (dictionary objn - 190428, file - 5, block - 9, slot - 0)

Object 9 is the I_FILE#_BLOCK# index in the data dictionary – and session 90 is definitely not doing anything with that object in this transaction.
Object 190428 is another table in the test schema, but session 93 didn’t access it in this transaction, and the block referenced is the segment header block, not a block that could hold a row.

In fact, just before I started the sequence of inserts I ran this query from a third session (connected as sys) with the following results:

select
	sid,
	row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from
	v$session
where
	sid in (90,93)
;

       SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ------------- -------------- --------------- -------------
        90             9              1           66140             0
        93        190428              5               9             0

The “rows” reported for the deadlock simply echoed the values that were already in the row_wait columns before the test started. (The sessions were waiting on “SQL*Net message from client” at the time.)

Footnote:

Session 90 created the tables and indexes, that’s probably why it happened to have an outstanding reference to the i_file#_block# index.
Session 93 had just run a script to drop all the objects in the schema, which may explain why it happened to have an outstanding reference to a segment header block
If the row_wait_obj# had been set to -1 for either session then the deadlock graph would have reported “No row” for that session.