Dynamic temporary tablespace switching bug/feature.

Up to last day I was absolutely sure that in Oracle terms USER and SCHEMA actually synonyms. The schema does not have attributes and exists just like container for user objects. I was doing some R&D about allocating different TEMPORARY Tablespaces to different users in a system when discover that the same user use different tablespaces in different sessions, without any configuration changes.

I have two temporary tablespaces TEMP and TESTTEMP. Tablespace TEMP is default and associated to all users except TEST.

SELECT USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='TEST';
USERNAME TEMPORARY_TABLESPACE
TEST TESTTEMP

We have table TESTTABLE with 100000 rows. And I create spacial sql with massive sort operation.

SELECT * FROM TEST.TESTTABLE a FULL OUTER JOIN TEST.TESTTABLE b on 
a.COL1=b.COL2 FULL OUTER JOIN TEST.TESTTABLE c on b.COL1=c.COL1 ORDER BY 
1,2,3,4,5,6,7,8,9;

By default the query use expected TESTTEMP tablespaces

SELECT S.sid, S.username, SUM (T.blocks) * 8192 / 1024 / 1024 mb_used, T.tablespace
FROM gv$sort_usage T, gv$session S
WHERE T.session_addr = S.saddr AND t.inst_id=S.inst_id
GROUP BY S.sid, S.username, T.tablespace
ORDER BY sid;
SID USERNAME MB_USED TABLESPACE
23 TEST 408 TESTTEMP

But when I change the CURRENT_SCHEMA in a session (ALTER SESSION SET CURRENT_SCHEMA=SYSTEM;) I got different results

SID USERNAME MB_USED TABLESPACE
23 TEST 487 TEMP

The same query using the same objects from the same session without changing user configuration use different Temporary Tablespaces. I believe this feature can be very helpful for fine grand sort operation development.

Couple bonus remarks:
a) Calling SQL from PL/SQL objects created with definer rights works like switching to the object owner schema.
b) Using synonyms does not switch temporary tablespace to the tablespace of the referencing objects.

RAC/Exadata Shell Aliases

I’ve been working on Oracle RAC systems for a while now and the subject of how best to setup shell environment variables and aliases seems to be something that there is a fair range of opinion on.

  • Do you have entries in oratab for both the database and the instance on that server?
  • Do you use oraenv or set the variables required for each environment via another method?
  • If you use oraenv, do you customise it?
  • How do you switch ORACLE_SID between the db_name and instance_name, if you do?

Please feel free to answer these questions via a comment below, however, the main point of this post is to share a few aliases that I’m finding very useful at the moment, and to encourage you to share aliases you use in your RAC environments. Actually, the non-RAC specific, Oracle related, aliases you create would be interesting to me too, but more on that later…

As I mentioned in a much earlier post I feel that in the world of RAC databases there is scope for an environment variable of ORACLE_DB or ORA_DB_NAME… But, I also feel that there’s a lot to be said for not introducing more variables then is necessary. To this end I have taken to creating the following aliases in RAC environments that I’m working in[1].

alias inst='ORACLE_SID=${ORACLE_SID}$($(grep "^+ASM[1-8]" /etc/oratab | cut -d":" -f2)/bin/olsnodes -l -n | cut -f2)'

alias db='ORACLE_SID=${ORACLE_SID%$($(grep "^+ASM[1-8]" /etc/oratab | cut -d":" -f2)/bin/olsnodes -l -n | cut -f2)}'

These aliases allow me to quickly switch ORACLE_SID from being set to the relevant value for the database to it being set to the relevant value for the instance on the given host. Obviously it doesn’t stop you modifying the value of ORACLE_SID to something that is not valid, but at least it is easy to undo a mistake you might have made.

He’s a quick example:

[ORCL@rac-02 ~]$ inst
[ORCL2@rac-02 ~]$ inst
[ORCL22@rac-02 ~]$ inst
[ORCL222@rac-02 ~]$ inst
[ORCL2222@rac-02 ~]$ db
[ORCL222@rac-02 ~]$ db
[ORCL22@rac-02 ~]$ db
[ORCL2@rac-02 ~]$ db
[ORCL@rac-02 ~]$ 

And, for a little more alias sharing… In my time working on Exadata environments I’ve found myself using dcli a lot and created the aliases below to save me typing on the basis that I, almost always, either want to use dcli to communicate with all “compute nodes” or all “storage cells” so why not simplify the process and shorten the commands:

alias ddcli='dcli -g ~/dbs_group -l oracle'

alias cdcli='dcli -g ~/cell_group -l cellmonitor'

I’d be keen to get a page going for useful aliases so we can all benefit from the ideas of each other… If you’ve got something to share please comment and if the collection builds up we’ll start a page for alias sharing.
__________
[1] – If I’m working in an environment where a shared “oracle” account is in use then I define my aliases in a separate file and source this when I login so that my preferences are not inflicted on others :-)

Simple, But Effective

I thought I’d posted something about this before, but looking back it appears not.

This post details something that I only discovered in the last year, and was left with a feeling that it is something so fundamental that I should have known it for years. However, as it was something new for the team I was working with at the time and doesn’t appear to be common knowledge in my current environment, it seems that it could be something worth sharing…

Note: The example below is only going to be of value for a RAC database; even then, only if footnote 1 applies. Also, to some degree it has been made redundant by the introduction of ADRCI in 11g, but there are many other use aside from the example.

As DBAs we deal with some common environment variables all that time, for example: $ORACLE_HOME, $ORACLE_SID, $ORACLE_BASE… One variable that isn’t set by “oraenv”, but would be useful in a RAC environment is something along the lines of “$ORACLE_DB” or “$ORA_DB_NAME”, as a variable holding the database name.

Why would I want this variable? I wanted to create an alias called “alert” that would run “tail -f” on the alert log for the database that I currently had the environment set for. My problem was that either I create individual aliases for all the databases on a given server or I hack oraenv to set a new environment variable that I would then use in something along the lines of:

alias alert_10g='tail -f ${ORACLE_BASE}/admin/${ORACLE_DB}/bdump/alert_${ORACLE_SID}.log'

The problems with individual aliases are obvious, especially when new databases were popping up on servers all the time. I’d end up with aliases as alert_db1, alert_db2, etc.

The problem with hacking oraenv is that even if I thought it was a great idea, which I didn’t, then it was definitely going to be hard to sell it to the others in the team, so I didn’t even try.

I needed something else. It was pretty simple really. I just needed a way to remove the digits off the end of $ORACLE_SID1… After a bit of searching and a bit of testing I found what’s shown below:

sh-3.2$ ORACLE_SID=testdb1
sh-3.2$ echo $ORACLE_SID
testdb1
sh-3.2$ echo ${ORACLE_SID%[1-9]}
testdb
sh-3.2$ 

I’ll forgive you for not being excited immediately, but this allow me to modify my alias to:

alias alert_10g='tail -f ${ORACLE_BASE}/admin/${ORACLE_SID[1-9]}/bdump/alert_${ORACLE_SID}.log'

I’d eliminated need for a new variable. Note that we were managing RAC databases with only 2 nodes, but if you’re luck enough to play with 10+ node RAC databases then you can always modify the code as demonstrated:

sh-3.2$ ORACLE_SID=testdb10
sh-3.2$ echo $ORACLE_SID
testdb10
sh-3.2$ echo ${ORACLE_SID%[1-9][0-9]}
testdb
sh-3.2$

Clearly there are limitation and you’d be wise to test out what you do with this, but it allowed us to come up with generic commands that could be run with the desired affect simply after setting the Oracle environment via oraenv.

I hope you find this useful. If you already knew it then I’m sorry for wasting your reading time, but that would suggest that you have some other tricks up your sleeve that I don’t know about, so please share them :-)

1 This assumes that the instances in the clustered database are named in what I believe is the logical way, i.e. DB_NAME with instance number as a suffix… I recently found myself working on a database where this wasn’t the case, so I feel it’s appropriate to state that as always, “Your mileage may vary.”