Showing posts with label 11gR2. Show all posts
Showing posts with label 11gR2. Show all posts

Friday, July 16, 2010

Creating objects in tablespaces without any quota

Say you've got a schema with privileges on multiple tablespaces and you want to make sure that objects get created in the appropriate one. One way to do that is make the user's default tablespace one that they don't have a quota on.

That way, if they try to create an object without specifying the tablespace, it picks the default one and errors because there is no quota. Or at least it used to.

Apparently in 11gR2 it doesn't work this way any more. With deferred segment creation, you can create objects in tablespaces where you don't have any quota, and it won't fail until a row gets inserted.

I can see another 'gotcha' for this if you are progressing code from DEV/TEST. The creation DDL make succeed by sticking the table in a USERS tablespace (which isn't expected to be used, except maybe for some trivial temporary tables) whereas before it would fail and red-flag it for re-assignment to the appropriate tablespace.

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 GARY@db11gr2 > desc scott.gm_x;
Name Null? Type
--------------- -------- ----------------
1 ID NUMBER
2 VALUE VARCHAR2(10)

09:41:10 GARY@db11gr2 > desc gary.gm_x;
Name Null? Type
------------- -------- ----------------
1 ID NUMBER

09:41:14 GARY@db11gr2 >
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")

Friday, September 04, 2009

11gR2 nuggets

Haven't installed 11gR2 yet, but found a couple of nuggets in the documentation.

Firstly, WAIT_ON_PENDING_DML in DBMS_UTILITY.
Image this situation. At 11:59, transaction A inserts a record into a table with a CREATED_ON of SYSDATE, but is not yet committed. At 12:00 a batch job kicks off to pick out all the new records, using the CREATED_ON date. Since the one from transaction A isn't committed yet, it is missed by the batch job. If the batch job says "I picked up everything dated before 12:00" it will miss it next time too.
Previously, you might work around this with v$transaction or queuing. A long time ago I even used a LOCK TABLE IN EXCLUSIVE MODE. It was immediately released after it had been obtained but still provided a potential for an issue (though it isn't so dangerous if you add a WAIT 5 on the end). Now you can simply tell your transaction to wait until any other transaction on the table that started before 12:00 has completed.

Secondly, having the option of an EXIT in SQL*Plus default to quitting with a ROLLBACK rather than a COMMIT.
It is not one I'd thought was an issue, but it came up on Stackoverflow at the same time. Coincidence, I guess. Anyway, I'd expect Instant Clients for 11gR2 to come out fairly soon, so this can be put into production a lot quicker. Be warned though, a DISCONNECT or CONNECT will (apparently) still do an implicit commit. That all seems odd to me, as mentally I'd expect an EXIT to do a disconnect from the database session then exit the client (and a CONNECT to do a disconnect from one session and then a connect to a new one) and that any COMMIT/ROLLBACK option would relate to the disconnect option.

Thirdly, hints.
I'm not sure I like this but IGNORE_ROW_ON_DUPKEY_INDEX allows the statement to silently ignore ORA-0001 errors. The row won't be inserted, but the SQL statement won't fail either. Not sure if AFTER ROW triggers will fire or not. Personally I prefer the thought of the error logging clause for this sort of functionality. I just don't like hints which change what the statement does at a logical level.

One hint I want to experiment with is APPEND_VALUES, which is like the APPEND hint, but for FORALL...INSERT in PL/SQL. Not sure how often you would have an array in memory large enough to benefit from this. Also, once you've done a direct-path insert, the table wants a commit/rollback before you can do another. If APPEND_VALUES is the same, you'd need to introduce a commit if you want to use this for looping big chunks of data.