Learning is not a spectator sport

Complete listing of posts

  • How big will my index be ?

    You’ve got a big table…you want to add an index. Will you have enough space ? Well, its not perfect but you can a reasonable approximation using DBMS_SPACE SQL> drop table T purge; Table dropped. SQL> create table T nologging as 2 select d.* from dba_objects d, 3 ( select 1 from dual connect by… Read more

  • 12c invisible columns

    12c invisible columns

    As several bloggers have already pointed out, column order changes when you start playing with invisible columns, for example SQL> create table T ( c1 int, c2 int, c3 int ); Table created. SQL> desc T Name Null? Type —————————————– ——– ————— C1 NUMBER(38) C2 NUMBER(38) C3 NUMBER(38) SQL> alter table T modify c1 invisible;… Read more

  • 12c ddl logging

    In new releases, its often the little things that really catch my attention. Like this one…. I love it SQL> alter system set enable_ddl_logging = true; System altered. SQL> create table t ( x int ); Table created. SQL> drop table t purge; Table dropped. SQL> host cat ddl_cdb12.log diag_adl:create table t ( x int… Read more

  • 12c install on Windows

    This is not a complete how-to, because I do not think its warranted…you pretty much click Next Next Next (which in itself is a credit to the product) But one thing to note – on the few machines I’ve installed it on, the installer gets to 100% and then just sits there for a LONG… Read more

  • from a friend…

    A friend was recently describing Agile to me…I stress, not Agile the principle, but Agile the way its been implemented at their site…I thought I’d share it with you 🙂 [warning: profanity] “A few years back I was struggling financially, but I needed a broom.  So I went down to the shop and got the… Read more

  • Feeling raw about raw…

    Its been well documented for a while that Oracle were not going to allow raw devices in 12….but this still seems brutal. 8.1.12 Desupport for Raw Storage Devices Starting with Oracle Database 12c, block file storage on raw devices is not supported. You must migrate any data files stored on raw devices to Oracle ASM,… Read more

  • Expunging the Expand all

    I’m sure many people love the collapsed form of the Oracle documentation, with the associated “Expand All” function… Me….I hate it.  The first thing I do is click “Expand All” and “Hide Navigation” to make it look like a standard html page and the Oracle documentation of old.. If you’re a dinosaur like me, then… Read more

  • Even on an ocean liner…

    …. Perth still looks good 🙂 Read more

  • A little quiz on tkprof

    I found this in a trace file recently INSERT INTO T (X) VALUES(:B1) call count cpu elapsed disk query current rows ——- —— ——– ———- ———- ———- ———- ———- Parse 15 0.00 0.00 0 0 0 0 Execute 15 0.00 0.00 0 4 44 2 Fetch 0 0.00 0.00 0 0 0 0 ——- ——… Read more

  • can you lock part of a table ?

    I should stress that this is all well covered in the documentation, but it was something I stumbled upon just recently. Its common knowledge that you can lock a table: SQL> create table T 2 ( x int ) 3 partition by range ( x ) 4 ( 5 partition p1 values less than (10),… Read more

  • hash keys … almost unique

    In the Oracle documentation, the specification for ORA_HASH is: The optional max_bucket argument determines the maximum bucket value returned by the hash function. You can specify any value between 0 and 4294967295. The default is 4294967295. So when I had a set of attributes come in, and I was looking for a simple way of… Read more

  • Datatype discipline

    (This is based on some ‘issues’ a client was having, so the example is architected to make a particular point, but is relevant nonetheless) In the distributed database world, we often have different names for the same piece of information. Consider two tables which hold customer information. In one table, they called the customer key… Read more