Classic AskTom Post

Everyone should read this one at least once. Yes even you. The question and his initial answer, at least - AskTom's "perfect example" post

Logging DDL in Oracle

I could have sworn I had drilled into my head (from studying for the Oracle 10g OCA exam) that DDL is audited in the alert log. I had a need for this today but it wasn't showing up.

Turns out, DDL in the alert log is a new feature in 11g, and to enable it you'll want to run:

ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;

For older versions you'll have to audit another way. One way that worked for my purposes today is a simple DDL trigger: http://www.dba-oracle.com/t_ddl_triggers.htm

Pgpool-II Load Balancing

NOTE: this post is surely hopelessly out of date. It is from 2011! I say this because the post is still getting views well over a decade later!

---

I've been trying, so far unsuccessfully, to get load balancing working in a pgpool-II replication environment with two nodes. This is supposed to spread SELECT queries across multiple nodes, according to the nodes' values of backend_weight. Presumably it does so in a round-robin manner, and if the two nodes had the same weight, query 1 would go to one node, query 2 would go to the other node, query 3 would go back to the first node, and so on...However, my queries are all being sent to one node.

This table, taken from the pgpool-II docs, shows whether a SELECT query will replicate, be sent to master only, or be load balanced based on three conditions:

SELECT is inside a transaction block	Y	Y	Y	N	N	N	Y	N
replicate_select is true	Y	Y	N	N	Y	Y	N	N
load_balance_mode is true	Y	N	N	N	Y	N	Y	Y
results(R:replication, M: send only to master, L: load balance)	R	R	M	M	R	R	M	L

It's absolutely clear that I have replicate_select set to false and load_balance_mode set to true. I seem to be getting a result of "M", which must mean my SELECT is in a transaction block. It doesn't appear to be though, acc. to my pg logs - at least, I don't see any BEGIN before the SELECT. Hmm!

If I fiddle with my two values of backend_weight and restart pgpool, it will switch to the node with the greater weight. Doesn't help me much, but it shows it's doing...something.

Stay tuned...

A useful tip - Emacs and Oracle

I'm going to try to get the hang of using this instead of plain ol' sqlplus. Emacs and Oracle

News: MySQL.com Database Compromised By Blind SQL Injection

MySQL.com Database Compromised By Blind SQL Injection

As Tom Kyte puts it: JUST BIND
(Go here and laugh.)

Year 0 in Oracle

While troubleshooting a customer's date-related issue, it turned out he had a year 0000 date in his table. He showed me this query of his data looking good, however two-digit years always smell fishy to me:

SQL> select trunc(min(time_stamp_h), 'MONTH') as t_min from mytable;

T_MIN
---------
01-SEP-00

At any rate the issue was resolved, but I couldn't even figure out how year 0 would have gotten in the database in the first place. Oracle seems to have a validation that does not allow year 0:

SQL> select to_date('9/1/0000', 'mm/dd/yyyy') from dual;
select to_date('9/1/0000', 'mm/dd/yyyy') from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

But...This blog post revealed that there's more to the story - (Short summary: Oracle doesn't always check for year 0. Only sometimes.)

New feature in Postgresql 9.0

Finally getting to use Postgres 9.0. This very simple feature alone makes me want to cry tears of joy. :)

GRANT SELECT ON ALL TABLES IN SCHEMA public TO toto;

(You can replace "TABLES" with "FUNCTIONS" or "SEQUENCES", and of course the inverse, REVOKE, is available.)

Brief chat with a buddy about MySQL index size limitation

(2:44:19 PM) Buddy: so many hacks in mysql :) like, you can't use functions as a default value for a column unless that function is called CURRENT_TIMESTAMP which is a single exception
(2:44:59 PM) Me: ug :)
(2:45:59 PM) Buddy: index lengths are a major limit..I've been creating columns with the value of unhex(sha1(value)) and creating my unique indexes on that
(2:46:57 PM) Me: what's that expression?
(2:47:11 PM) Me: i think i have an idea... :)
(2:47:19 PM) Buddy: returns a 20 byte binary sha1 hash
(2:47:25 PM) Me: yeah..bleh :)
(2:48:59 PM) Me: i guess that's just for enforcing the uniqueness? Or would you look up by that too? (thus you have to remember to call unhex(...)) :)
(2:49:33 PM) Buddy: if I wanted to lookup using that index, then it would be lookup too
(2:49:47 PM) Me: yeah
(2:49:59 PM) Buddy: so my queries are like select value from table where valuesha1=unhex(sha1(thingIwant));
(2:50:14 PM) Me: bleh :)
(2:54:09 PM) Buddy: as long as your index is less then 767 bytes yer ok :)
(2:54:32 PM) Me: what are you indexing on that's bigger? you might have told me before...
(2:54:57 PM) Buddy: URLs :)
(2:55:00 PM) Me: ahh

:eye roll:

Oracle Universal Installer can't be run in a directory with spaces in the name (Unix), or else it bombs! This is apparently due to lack of quotes in the runInstaller script, where a local variable is being set to pwd.

Get a database working...NOW

Problem: "I need an Oracle 11g database NOW and I think there's one at [hostname spit] but it doesn't work!"

Solution: Well as I didn't know of any others I jumped onto spit. First I checked if an Oracle instance was running:

oracle@spit:~$ ps -ef | grep pmon
oracle 17973 17946 0 10:55 pts/4 00:00:00 grep --color=auto pmon

Apparently not. OK, so are any databases installed?

oracle@spit:~$ tail -n 5 /etc/oratab
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
orcl:/opt/oracle/product/11.2.0/dbhome_1:N
spitfire:/opt/oracle/product/11.2.0/dbhome_1:N

Yes, two SIDs; orcl and spitfire. OK let's see if we can get into sqlplus to get one of them started.

oracle@spit:~$ /opt/oracle/product/11.2.0/dbhome_1/bin/sqlplus
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Doh! Apparently there's no $ORACLE_HOME set…Therefore $ORACLE_SID is probably not set as well:

oracle@spit:~$ echo $ORACLE_HOME

oracle@spit:~$ echo $ORACLE_SID

Well, let's set them.

oracle@spit:~$ export ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1
oracle@spit:~$ export ORACLE_SID=orcl

Next, I checked whether the listener was running, and when I found it wasn't I started it:

oracle@spit:/opt/oracle/product/11.2.0/dbhome_1$ ./bin/lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-FEB-2011 11:13:03

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /opt/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/spot/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=spot)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 11-FEB-2011 11:13:05
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/spot/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=spot)(PORT=1521)))
The listener supports no services
The command completed successfully

Now let's start up the instance!

oracle@spit:/opt/oracle/product/11.2.0/dbhome_1$ sqlplus sys/******* as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 11 11:27:30 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1690705920 bytes
Fixed Size 1336960 bytes
Variable Size 1291848064 bytes
Database Buffers 385875968 bytes
Redo Buffers 11644928 bytes
Database mounted.
Database opened.
SQL> quit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

As a side note, I had set $ORACLE_SID to the wrong value earlier. It's going to look up the spfile's name by this sid, so if it's wrong the file will not be found (here I had set it to "oracl" instead of "orcl":

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/opt/oracle/product/11.2.0/dbhome_1/dbs/initoracl.ora'

Let's check out if the instance is picked up by the listener…

oracle@spit:/opt/oracle/product/11.2.0/dbhome_1$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-FEB-2011 11:29:14

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 11-FEB-2011 11:13:05
Uptime 0 days 0 hr. 16 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/spot/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=spot)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

And finally let's start up the EM DB Control:

oracle@spit:/opt/oracle/product/11.2.0/dbhome_1$ emctl start dbconsole
ulimit: 25: bad number
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://spot:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ............. started.

(Note that "ulimit 25: bad number" wackiness. Some info on that here: http://johanlouwers.blogspot.com/2010/04/emctl-ulimit-25-bad-number.html)

At any rate, my coworker who needed this database ASAP gave me the thumbs up...w00t! But, will all these components start up automagically upon reboot? Stay tuned...

Once again...

Once again I'm thwarted slowed down by a database limitation that, to me, seems silly and arbitrary.

4000 character limit in SSIS

Disabling/enabling indexes (Oracle)

I whipped up the following two procedures for disabling and enabling all indexes on a specified table. They do not handle every kind of index just yet. Technically it is marking indexes as unusable then rebuilding them. Unique/Primary Key indexes are not disabled/enabled in these functions. There may be a few other types of indexes not yet supported.

SQL Developer made the caps wacky - need to find the setting. :)

create or replace procedure DISABLE_INDEXES_ON_TABLE(P_OWNER_NAME in varchar2, P_TABLE_NAME in varchar2) as

DISABLE_STR long;

begin

for REC in (select * from ALL_INDEXES where OWNER=P_OWNER_NAME and TABLE_NAME=P_TABLE_NAME and UNIQUENESS='NONUNIQUE') LOOP

if REC.INDEX_TYPE in ('NORMAL', 'NORMAL/REV', 'FUNCTION-BASED DOMAIN', 'FUNCTION-BASED NORMAL') then

DISABLE_STR := 'ALTER INDEX ' || REC.INDEX_NAME || ' UNUSABLE';

end if;

execute immediate DISABLE_STR;

end loop;

end;





create or replace procedure ENABLE_INDEXES_ON_TABLE(P_OWNER_NAME in varchar2, P_TABLE_NAME in varchar2) as

REBUILD_STR long;

begin

for REC in (select * from ALL_INDEXES where OWNER=P_OWNER_NAME and TABLE_NAME=P_TABLE_NAME and UNIQUENESS='NONUNIQUE') LOOP

if REC.INDEX_TYPE in ('NORMAL', 'NORMAL/REV', 'FUNCTION-BASED DOMAIN', 'FUNCTION-BASED NORMAL') then

REBUILD_STR := 'ALTER INDEX ' || REC.INDEX_NAME || ' REBUILD';

end if;

execute immediate REBUILD_STR;

end LOOP;

end;

Some useful SQL Server links (for me)

As a fairly well experienced database developer who's new to MS SQL Server, here are some links that have been useful to me so far:

SQLTeam.com: Introduction to Dynamic SQL Part 1
SQLTeam.com: Introduction to Dynamic SQL Part 2 - here sp_executesql is what I was really looking for, as it lets you execute dynamic SQL with bound parameters

Transact-SQL Data types reference (MS)

Translating from Oracle to SQL Server
SQL Server Stored Procedure Basics