Thursday, November 26, 2009

DBMS_METADATA_DIFF with 11gR2

A comment on Tom Kyte's blog alerted me to DBMS_METADATA_DIFF in 11gR2.
This allows you to generate a set of 'ALTER ...' scripts that bring two objects into line.

It is documented as being part of
DBMS_METADATA at the PSOUG (formerly Morgan's Library) reference. The latest Morgan's Library reference says it is part of 11.1.0.7 but undocumented for that release.

Say you have added a column, or some constraints to a development or test environment, this can be used to compare that table definition to the production one and give you the statements that need to be applied to production to bring the tables into line.


The bad news is that it
requires the Change Management Option.

Hey Oracle. Can you please STOP installing stuff by default that you can't use without extra payment. Or at least don't create the public synonyms and grants that make it trivial to use.


To use the PL/SQL Profiler, you need to create some SYS tables. There's a script to do this but it has to be manually run by the DBA. Can't they do something similar with the extra cost options ?


Anyway, for the financially challenged, remember
that "Personal Edition includes all of the components that are included with Enterprise Edition, as well as all of the options that are available with Enterprise Edition", and this functionality works with database links. So for a relatively small outlay a DBA can get have Personal Edition sitting on a Windows box and use this handy addition.

Since this is first documented with 11gR2 you may want to until the Windows port of 11gR2 comes out for Personal Edition. If you want to risk it with 11gR1, you'd still need the first patchset, so the OTN version won't do.


09:41:05 [email protected] > desc scott.gm_x;
Name Null? Type
--------------- -------- ----------------
1 ID NUMBER
2 VALUE VARCHAR2(10)

09:41:10 [email protected] > desc gary.gm_x;
Name Null? Type
------------- -------- ----------------
1 ID NUMBER

09:41:14 [email protected] >
select DBMS_METADATA_DIFF.COMPARE_ALTER('TABLE','GM_X','GM_X','SCOTT','GARY') from dual;
DBMS_METADATA_DIFF.COMPARE_ALTER('TABLE','GM_X','GM_X','SCOTT','GARY')
----------------------------------------------------------------------
ALTER TABLE "SCOTT"."GM_X" DROP ("VALUE")

Thursday, November 19, 2009

The amazing disappearing row trick

This curiosity was prompted by a question on stackoverflow
The code inserts a row into a table and then raises an exception.
Despite the exception being caught by an exception handler, and with no explicit rollback, the inserted row has vanished.


drop table dummy;

create table dummy (id number);

DECLARE
v_num NUMBER;
begin
begin
execute immediate
'declare
v_num number(2);
begin
insert into dummy values (1);
dbms_output.put_line(SQL%ROWCOUNT);
v_num := 100;
end;';
exception
when value_error then null;
end;
select count(*) into v_num from dummy;
dbms_output.put_line(v_num);
end;
/


The simple answer is that EXECUTE IMMEDIATE executes an SQL statement (which may be an anonymous PL/SQL block), and that statement will either succeed or fail. If it errors, atomicity states that changes made as part of that statement must be rolled back.

Without the EXECUTE IMMEDIATE, the entirety of the code is a single statement and there is no need for a rollback, since the statement succeeds due to the exception handler.


DECLARE
v_num NUMBER;
begin
begin
declare
v_num number(2);
begin
insert into dummy values (1);
dbms_output.put_line('X:'||SQL%ROWCOUNT);
v_num := 100;
end;
exception
when value_error then null;
end;
select count(*) into v_num from dummy;
dbms_output.put_line('Y:'||v_num);
--
end;
/


PS. You get the same result using EXECUTE IMMEDIATE or DBMS_SQL.

Thursday, November 05, 2009

A quick example of proxy authentication

It is fairly typical to have a bunch of developers working in a single schema. Often you want to keep track of which person did what in that schema. [Ideally, you want to know why, but reading minds is beyond the scope of this article.] One way to improve the traceability of activity in the schema is to give everyone a user account with their own password and then allow those development users to connect as a proxy to the schema user. Since everyone connects to the single schema using their own individual username/password, no-one needs to know the schema password and the DBA can even set it to some random jumble of 20 to 30 characters to prevent guessing.

The proxy user doesn't naturally appear in the V$ views (as far as I can tell), but can be derived from SYS_CONTEXT, and therefore used in a LOGON trigger to set CLIENT_INFO which is visible, or you could trace DDLs with AUDIT or a trigger and store the value there.


C:\>sqlplus [email protected]
SQL*Plus: Release 10.2.0.1.0 - Production

SQL> create user schema1 identified by ***** default tablespace users;
User created.
SQL> alter user schema1 quota unlimited on users;
User altered.
SQL> create table schema1.test (id number);
Table created.
SQL> create user devuser identified by dangermouse default tablespace users;
User created.
SQL> alter user schema1 grant connect through devuser;
User altered.
SQL> grant create session to schema1;
Grant succeeded.
SQL> conn devuser[schema1][email protected]
Connected.
SQL> select user from dual;
USER
------------------------------
SCHEMA1
SQL> select sys_context('userenv','proxy_user') from dual;
SYS_CONTEXT('USERENV','PROXY_USER')
--------------------------------------------------------------------------------
DEVUSER


With a JDBC client, such as SQuirrel, I simply put the username as DEVUSER[SCHEMA1] and use the DEVUSER password.